Extract Oracle DB Connect and SQL execution log

UNIX for Advanced & Expert Users
1

Hi,

I am trying to write a generic script as a part of a framework which will establish Oracle DB connection once and loops in to check for some files which gives the SQL statements to execute.

The script is running but I am stuck with capturing errors ( ORA and SP) and outputs. Example:

${ORACLE_HOME}/bin/sqlplus -s ${loginid}/${password}@//${server}:${port}/${servicename} |&

while [[ $flag -eq 1 ]]
do
#obtain the $sql_stmt from a file here
print -p -- "${sql_stmt}"
# if it is select statement i use read -p to obtain the output
done
print -p -- exit

Question:
I tried :

${ORACLE_HOME}/bin/sqlplus -s ${loginid}/${password}@//${ server}:${port}/${servicename} > ${connect_output} 2>&1 |&

And it is capturing all the sql error logs instead of only connect error
Any ideas on how to capture the individual sql statement errors?

Thanks for your help
mirage0809

2

Clue perhaps.
The "sqlplus" switch "-s" means "silent".

Also

Btw: The "|&" sequence in post #1 loooks weird. Did you mean "|\" (i.e pipeline then escaped new line) ?

1 Like
3

|& is to open the sqlplus thread asynchronously in the background, and we enter the sql statements in the pipe using print p.

removing -s is of no use as I am still not able to capture the errors for individual sql stmt and check the DB connection before proceeding further.

We want to capture any errors thrown dynamically

4

Maybe use Oracle "spool" statements to capture the output (rather than outputting to screen and then capturing in unix shell).

SPOOL <logfilename1>
.... connnect sequence ...
SPOOL OFF

SPOOL <logfilename2>
... sql statements
SPOOL OFF

EXIT;
5

methyl,

I checked it now..Spool is working for the DB connection as well as for the sql statements.

Thanks!!