Oracle Database connection from UNIX

Shell Programming and Scripting
1

Hi I have a question regarding Oracle connection using the below code

${ORACLE_HOME}/bin/sqlplus -s $user/$pwd@$sid <<!EOF 1>> $v_log_dir/$v_job_log.out 2>> $v_log_dir/$v_job_log.err
/
prompt  stored procedure beginning . . . 
exec xx_interface_pkg.pr_xx_clms_out($datayears,$keepmonths);

exit
!EOF

Now wherever i get an oracle error i want the output to go the v_job_log.err file however. If the password is wrong for the database the result is still passing to v_job_log.out file. How can i change this? your help will be greatful

2

Well, sqlplus is like that, logging on buffered old stdout. You can divert messages using sed by content. I often "select 'dAtA' x, ..." to help separate data from logging. You can aways switch to xigole JISQL and jdbc jars and see if it acts differently.

3

You can use the perl module DBI::Oracle. This might help a bit. But you need to change some coding there. The module is very easy to implement.

4

Yes, SQL*Plus has delusions of grandeur with many funky settings like array and row sizes, really a pain in scripting compared to Sybase isql, for instance. Much of it may have been written in the bad old days of tight ram.