Re: Unix 'wait' on 'sqlplus' exit status reliable?

Try adding '&&' at the end of SQlplus Shell Script . This not only runs the Sqlplus session in the background but also makes the next Command in the Script (ie SQLloader) to start only when the last SqlPlus session is complete..

-Thiru

zuot_at_my-deja.com wrote:

> Hi, All,
>
> We try to delete from a table and immediately load data to it after the
> delete is complete. To speed up the delete we do this:
>
> #!/bin/ksh
> ...
> sqlplus -s user/password @del_tbl 1 >>logfile &
> sqlplus -s user/password @del_tbl 2 >>logfile &
> sqlplus -s user/password @del_tbl 3 >>logfile &
> sqlplus -s user/password @del_tbl 4 >>logfile &
> sqlplus -s user/password @del_tbl 5 >>logfile &
> sqlplus -s user/password @del_tbl 6 >>logfile &
> sqlplus -s user/password @del_tbl 7 >>logfile &
> (where 1,2,...,7 is a day_of_week parameter that passed into
> del_tbl.sql, day_of_week is a field in the table has only 1-7 values.
> So the above 7 statement is mutually exclusive)
>
> wait # to wait on all the above background job to finish
>
> sqlldr ... to the same table.
> -------------------------------------
>
> The above scheme works fine sometime but fails same other time at the
> sqlldr stage due to duplicated records. This should never happen if the
> delete step finishs successfully.
>
> We try to figure out this...
> It appears that even though the unix 'wait' waits correctly on the
> return of the sqlplus's exit status. By the time sqlldr is invoked, the
> delete via del_tbl.sql has not yet finished up or cleaned-up (there is
> a commit at the end of del_tbl.sql script).
>
> My question is: why can this happen? Wouldn't the single sqlplus ... be
> considered an atomic transaction? Or maybe wait on the exit status of
> sqlplus is not reliable. If so, what one should wait for ... the
> parallel delete is certainly attractive.
>
> We're using Oracle 7.3.3.5.
>
> Your comments are highly appreciated.
>
> Tao Zuo
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
Received on Fri Jul 23 1999 - 14:39:28 CDT