How to get sql command with output in files?

Shell Programming and Scripting
1

I tried executing set of queries from shell script but not able to capture the input query in the log file,but the input query is not displayed along with output. Only the outputs are being captured in the log file. Is there a way to capture the input query along with the corresponding input??

2

Try adding exec > "logfile" 2>&1 to the beginning of your script

3

is it before the sql connection or after that

4

These might be a terrible ways, but a few things spring to mind.

You could do something with xargs like:-

echo " "|xargs -t your required command here

You might so:-

set -x
your required command here
set +x

Maybe even:-

echo "your required command here"
your required command here

None of these are necessarily very pretty and I'm happy to be told they are silly, but they were just thoughts.

What have you tried so far and what are you missing?

Robin
Liverpool/Blackburn
UK

5

hi friends, my concern in when i make sql connection with below command in between i need to run 3-4 queries and i can not use UNIX command in between sql connection, what i need to do in this case.

i am using AIX and ksh shell

6

Well, you haven't actually given us your code, but I will take a guess:-

$ sqlplus <<-EOSQL
$userid/$password
select count(*) from user_tables ;
!echo "I've just counted the user tables"
select 1 from dual;
!echo "I've just worked out the number 1 is, erm 1!"
EOSQL

Is this what you are after, or have I missed the point?

If you are after trapping queries that are keyed in, then you are probably looking at Oracle Auditing, a whole project in itself.

Robin

7

thanks rbatte, yes that what i want bceasue only echo is not working in sql connection.
! before echo work in my script. thanks a lot.,

please let me know one query, what i need to do if i want to use unix command in between sql connection

8

You can use "!" before unix command as shown by rbatte for "echo" command

9

There should be a note of caution though if you are planning to use the result of your shell commands back into the second part of the SQL, similar to this:-

column="COL1"
sqlplus <<-EOSQL
$username/$password
select $column from my_table;
!column="COL2"
select $column from my_table;
EOSQL

I think that the variables in the section before the EOSQL label (called a Here document) will be evaluated by the shell before the sqlplus command is called, so you will actually still select COL1 for the second line.

I hope that this gives you something to consider. It may be that there is still a need to execute as separate queries if this is the case. The alternate would be to have a reference table in your database and then when you ! out to the shell, call another SQL to update this table before returning to the main execution. It's not pretty, but it might work.

Many things are achievable from within SQL already. What are you trying to do in the shell? It might be that we can capture it into the SQL.

Robin