Wait command

[intp]

Hi,

Below is my requirement,

1: Connect Oracle , Run a SQL (which takes 3-4 hours to build the extract file).

2: Wait for extract to be completed (above step)

3:copy extract file to someother location.

4:exit

I'm more concerned about Step 2:"Wait". I'm just using the "Wait" command and it works fine. Is this enough ? or i need to include PID for wait command ? also is there any restrictions for Wait command? i remember reading it may clash with foreground/background processes if too many are running etc...i'm worried because it shouldnt goto copy step until step 2 finishes. How to make sure ?

Thanks .

[Jonathan Fife · Accepted solution]

If you have more than one background process spawned by the current shell, wait will wait until they have all completed.

If that is a risk for your script, try doing step 1 and then using the $! variable to grab the pid of the process in step 1, and using that as an argument for wait.

You could also do the commands joined by &&, which will wait until the first command finishes before executing the second -- eg.

sqlplus "user/passwd" < myscript.sql && cp resultfile /somewhere/else

[James R. Ferguson]

Hi:

I assume that we are speaking of a shell script. Therefore, it is sufficient to simply specify 'wait' without a pid if your process structure is single-threaded. If you have multiple processes running, a simple 'wait' will wait until all of them have finished.

If you have launched a background process, I'd capture its pid from '$!' and then wait using that pid, for example:

...
do_something &
PID_OF_THING = $!
...
wait ${PID_OF_THING}
...

Regards!

...JRF...

[Peter Nikitka]

Hi,

if you use the form 'wait pid', you'll get the exit status of the background command, e.g.:

..
backcmd &

...
wait $!
if [ $? -gt 0 ]
then print -u2 backcmd failed
fi

...

mfG Peter

[spex]

Hello,

You can also perform the copy operation from your PL/SQL script by shelling to the OS:

begin
--Perform extract here
if --Extract completed successfully
then
host cp /dir1/* /dir2;
end if;
end;
/

PCS

[TwoProc]

spex is right, his suggested method would avoid the whole problem of having to wait with a shell script, the sql job itself could handle the extract and perform the copy for you if that works for you. Of course, I could see a multitude of reasons why it wouldn't be, among them you don't want to mess with the code for the extract for each possible landing spot it may or may not have to go to...

BTW, good posting from the new guy (Jonathan) - keep it up!

[Dave Walley]

Hi, I would suggest you do something like this. Create a script as follows

#!/usr/bin/sh
export ORACLE_SID=sidname
export ORAENV_ASK=NO
. oraenv
unset ORAENV_ASK

sqlplus user/password <@file_to_generate_outfile
exit
EOF

cp file_name_from_script /dir/filesys/newloc

exit

Dave