FAQ
Hi Listers,


I have a korn shell script which needs to run 3 sqlplus scripts in parallel then run the final sqlplus script. Is there a way to ensure that sqlplus scripts 1 thru 3 completes before running the final sqlplus script? I am a newbie in shell scripting.


<< shell script snippet >>
...

sqlplus un/pw @script1.sql &
sqlplus un/pw @script2.sql &
sqlplus un/pw @script3.sql &

...
sqlplus un/pw @final.sql
...
exit


Thank you in advance for your help!


David

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com

--
http://www.freelists.org/webpage/oracle-l

Search Discussions

  • Allen, Brandon at Aug 3, 2005 at 4:55 pm
    David,


    I believe if you just put a 'wait' command after your first 3 sqlplus commands, the script will stop at that point until the first 3 complete, e.g.


    sqlplus . . . &
    sqlplus . . . &
    sqlplus . . . &

    wait


    sqlplus . . .


    Regards,
    Brandon Allen


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of d cheng
    Sent: Wednesday, August 03, 2005 2:49 PM
    To: Oracle-L_at_freelists.org
    Subject: shell scripting help

    Hi Listers,


    I have a korn shell script which needs to run 3 sqlplus scripts in parallel then run the final sqlplus script. Is there a way to ensure that sqlplus scripts 1 thru 3 completes before running the final sqlplus script? I am a newbie in shell scripting.


    << shell script snippet >>
    ...

    sqlplus un/pw @script1.sql &
    sqlplus un/pw @script2.sql &
    sqlplus un/pw @script3.sql &

    ...
    sqlplus un/pw @final.sql
    ...
    exit


    Thank you in advance for your help!


    David

    Do You Yahoo!?
    Tired of spam? Yahoo! Mail has the best spam protection around
    http://mail.yahoo.com

    Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.

    --
    http://www.freelists.org/webpage/oracle-l
  • D cheng at Aug 3, 2005 at 4:59 pm
    thank you for your suggestion. I will try this out.

    "Allen, Brandon" wrote:
    David,


    I believe if you just put a 'wait' command after your first 3 sqlplus commands, the script will stop at that point until the first 3 complete, e.g.


    sqlplus . . . &
    sqlplus . . . &
    sqlplus . . . &

    wait


    sqlplus . . .


    Regards,
    Brandon Allen


    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of d cheng
    Sent: Wednesday, August 03, 2005 2:49 PM
    To: Oracle-L_at_freelists.org
    Subject: shell scripting help

    Hi Listers,


    I have a korn shell script which needs to run 3 sqlplus scripts in parallel then run the final sqlplus script. Is there a way to ensure that sqlplus scripts 1 thru 3 completes before running the final sqlplus script? I am a newbie in shell scripting.


    << shell script snippet >>
    ...

    sqlplus un/pw @script1.sql &
    sqlplus un/pw @script2.sql &
    sqlplus un/pw @script3.sql &

    ...
    sqlplus un/pw @final.sql
    ...
    exit


    Thank you in advance for your help!


    David

    Do You Yahoo!?
    Tired of spam? Yahoo! Mail has the best spam protection around
    http://mail.yahoo.com

    Privileged/Confidential Information may be contained in this message or attachments hereto. Please advise immediately if you or your employer do not consent to Internet email for messages of this kind. Opinions, conclusions and other information in this message that do not relate to the official business of this company shall be understood as neither given nor endorsed by it.



    Start your day with Yahoo! - make it your home page

    --
    http://www.freelists.org/webpage/oracle-l
  • Reidy, Ron at Aug 3, 2005 at 4:58 pm
    Try wait(1).


    Ron Reidy
    Lead DBA
    Array BioPharma, Inc.

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org On Behalf Of d cheng
    Sent: Wednesday, August 03, 2005 3:49 PM
    To: Oracle-L_at_freelists.org
    Subject: shell scripting help

    Hi Listers,


    I have a korn shell script which needs to run 3 sqlplus scripts in parallel then run the final sqlplus script. Is there a way to ensure that sqlplus scripts 1 thru 3 completes before running the final sqlplus script? I am a newbie in shell scripting.


    << shell script snippet >>
    ...

    sqlplus un/pw @script1.sql &
    sqlplus un/pw @script2.sql &
    sqlplus un/pw @script3.sql &

    ...
    sqlplus un/pw @final.sql
    ...
    exit


    Thank you in advance for your help!


    David

    Do You Yahoo!?
    Tired of spam? Yahoo! Mail has the best spam protection around
    http://mail.yahoo.com

    This electronic message transmission is a PRIVATE communication which contains
    information which may be confidential or privileged. The information is intended
    to be for the use of the individual or entity named above. If you are not the
    intended recipient, please be aware that any disclosure, copying, distribution
    or use of the contents of this information is prohibited. Please notify the
    sender of the delivery error by replying to this message, or notify us by
    telephone (877-633-2436, ext. 0), and then delete it from your system.

    --
    http://www.freelists.org/webpage/oracle-l
  • Stephen booth at Aug 3, 2005 at 5:04 pm

    On 03/08/05, d cheng wrote:
    << shell script snippet >>
    ...
    sqlplus un/pw @script1.sql &
    sqlplus un/pw @script2.sql &
    sqlplus un/pw @script3.sql &
    ...
    sqlplus un/pw @final.sql
    ...
    exit
    Is there any particular reason you want to be running the initial
    scripts in the background? If not then just lose the ampersands and
    it should execute them in order, one at a time. This avoids the
    problem of final.sql being run before the others have completed.

    Alternatively at the start of each script (apart from final.sql) put
    in a command to create a lock file (e.g. !touch script2.lock) and at
    the end a command to delete those files. Then before the call to
    final.sql put in a while loop that checks if those files exist and if
    they do sleeps for a period before checking again. When none of those
    files are present the loop quits and final.sql will be run. Maybe, to
    reduce any load from the while loop, estimate how long you think it
    will take the scripts to run and put a sleep command for that time
    period before the loop.

    Stephen

    --
    It's better to ask a silly question than to make a silly assumption.
    --
    http://www.freelists.org/webpage/oracle-l
  • D cheng at Aug 3, 2005 at 5:12 pm
    Thank you all for responding with your suggestions. The WAIT command worked!


    I split my original single sql script into three sql scripts to parallelize my workload.

    stephen booth wrote:
    On 03/08/05, d cheng wrote:
    << shell script snippet >>
    ...
    sqlplus un/pw @script1.sql &
    sqlplus un/pw @script2.sql &
    sqlplus un/pw @script3.sql &
    ...
    sqlplus un/pw @final.sql
    ...
    exit
    Is there any particular reason you want to be running the initial
    scripts in the background? If not then just lose the ampersands and
    it should execute them in order, one at a time. This avoids the
    problem of final.sql being run before the others have completed.

    Alternatively at the start of each script (apart from final.sql) put
    in a command to create a lock file (e.g. !touch script2.lock) and at
    the end a command to delete those files. Then before the call to
    final.sql put in a while loop that checks if those files exist and if
    they do sleeps for a period before checking again. When none of those
    files are present the loop quits and final.sql will be run. Maybe, to
    reduce any load from the while loop, estimate how long you think it
    will take the scripts to run and put a sleep command for that time
    period before the loop.

    Stephen

    --
    It's better to ask a silly question than to make a silly assumption.

    ---------------------------------
    Start your day with Yahoo! - make it your home page
    --
    http://www.freelists.org/webpage/oracle-l
  • Smith, Ron L. at Aug 4, 2005 at 7:44 am
    Don't run them in the background (&). The second and third statements
    won't run until the previous SQL finishes.


    Ron

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of d cheng
    Sent: Wednesday, August 03, 2005 4:49 PM
    To: Oracle-L_at_freelists.org
    Subject: shell scripting help

    Hi Listers,

    I have a korn shell script which needs to run 3 sqlplus scripts

    in parallel then run the final sqlplus script. Is there a way to ensure
    that sqlplus scripts 1 thru 3 completes before running the final sqlplus
    script? I am a newbie in shell scripting.


    << shell script snippet >>
    ...
    sqlplus un/pw @script1.sql &
    sqlplus un/pw @script2.sql &
    sqlplus un/pw @script3.sql &
    ...
    sqlplus un/pw @final.sql
    ...
    exit

    Thank you in advance for your help!

    - David

    __________________________________________________
    Do You Yahoo!?
    Tired of spam? Yahoo! Mail has the best spam protection around
    http://mail.yahoo.com

    Important Notice!!
    If you are not the intended recipient of this e-mail message, any use, distribution or copying of the message is prohibited.
    Please let me know immediately by return e-mail if you have received this message by mistake, then delete the e-mail message.
    Thank you.

    --
    http://www.freelists.org/webpage/oracle-l
  • Leandro Guimaraes Faria C. Dutra at Aug 4, 2005 at 7:50 am

    oracle-l-bounce_at_freelists.org gravou em 2005-08-04 09:42:27:

    Don't run them in the background (&). The second and third
    statements won't run until the previous SQL finishes.
    Sorry for not testing or googling for the answer, but what if one
    does:

    process1 &
    process2 &
    bg 1
    bg 2
    wait

    Or what would be the correct solution?

    --
    Leandro Guimarães Faria Corcete DUTRA
    Administrador de Bases de Dados +55 (11) 4390 5383
    Toyota do Brasil Ltda ldutra_at_toyota.com.br
    São Bernardo do Campo, SP BRASIL

    This message (including any attachments) is confidential and may be privileged and intended solely for the use of the person/entity to whom it is addressed. If you have received it by mistake please notify the sender by returning via e-mail as well as delete this message from your system. Any unauthorized use or dissemination of this message in whole or in part is prohibited. Please note that e-mails are susceptible to change. TOYOTA DO BRASIL LTDA (including its group companies) shall not be liable for the improper or incomplete transmission of the information contained in this communication, neither for personal, nonbusiness related information nor opinion sent through this email or even for any delay in its receipt or damage to your system. TOYOTA DO BRASIL LTDA (or its group companies) does not guarantee that the integrity of this communication has been kept nor that this communication is free of viruses, interceptions or interference.
    --
    http://www.freelists.org/webpage/oracle-l
  • Sudhi at Aug 4, 2005 at 8:01 am
    how about just putting the sql contents in a shell script and call it
    using 'nohup'

    say :

    1.sh

    sqlplus /@ <

    nohup 1.sh >> /tmp/1.log &
    nohup 2.sh >> /tmp/2.log &

    Thats what I do for running things in bg.

    Thanks,
    -Sudhi.

    --
    Legal Disclaimer: The statements expressed here are of my own and do not
    represent those of Yahoo Inc !
    --
    http://www.freelists.org/webpage/oracle-l
  • Jared Still at Aug 4, 2005 at 12:48 pm
    That sounds incorrect to me.

    Give it a try:

    s.sql
    exec dbms_lock.sleep(5)

    exit

    s.sh

    sqlplus scott/tiger @s.sql 2>&1 >/dev/null &
    sqlplus scott/tiger @s.sql 2>&1 >/dev/null &
    sqlplus scott/tiger @s.sql 2>&1 >/dev/null &

    echo waiting on sqlplus jobs
    ps -flaujkstill | grep [s]qlplus

    wait

    ---

    10:47-6 > ./s.sh
    waiting on sqlplus jobs
    000 S jkstill 10926 10889 0 69 0 - 3737 read_c 10:24 pts/0 00:00:00
    /u01/app/oracle/product/9.2.0/bin/sqlplus
    000 S jkstill 11344 11343 0 73 0 - 3364 pipe_w 10:48 pts/6 00:00:00 sqlplus
    000 R jkstill 11345 11343 0 74 0 - 3442 - 10:48 pts/6 00:00:00 sqlplus
    000 S jkstill 11346 11343 0 73 0 - 3442 pipe_w 10:48 pts/6 00:00:00 sqlplu
    On 8/4/05, Smith, Ron L. wrote:

    Don't run them in the background (&). The second and third statements
    won't run until the previous SQL finishes.
    Ron

    -----Original Message-----
    *From:* oracle-l-bounce_at_freelists.org [mailto:
    oracle-l-bounce@freelists.org] *On Behalf Of *d cheng
    *Sent:* Wednesday, August 03, 2005 4:49 PM
    *To:* Oracle-L_at_freelists.org
    *Subject:* shell scripting help

    Hi Listers,
    I have a korn shell script which needs to run 3 sqlplus scripts in
    parallel then run the final sqlplus script. Is there a way to ensure that
    sqlplus scripts 1 thru 3 completes before running the final sqlplus script?
    I am a newbie in shell scripting.

    --
    Jared Still
    Certifiable Oracle DBA and Part Time Perl Evangelist
  • Johnson, George at Aug 4, 2005 at 8:20 am
    Apologies, if I have misunderstood your requirements, but seems
    straight forward.


    If you want to complete, regardless of the results produced, why not
    simply write one sql script to run the sql scripts.

    main_runner.sql script contains:
    @@script1.sql
    @@script2.sql
    @@script3.sql
    @@final.sql

    Then simply run the main script:
    sqlplus un/pw @main_runner.sql.

    Each sql script will be run one after another.

    If you are interested in testing the results of 1-3 scripts,

    then these will need to drop some kind of status "marker", say insert
    "SUCCESS" from 1-3 into a table and get final.sql to pick it and run if 1-3
    have a row of SUCCESS from the work status table.


    Rgds


    -----Original Message-----
    From: Smith, Ron L.
    Sent: 04 Aug 2005 13:42
    To: dc4oracle_at_yahoo.com; Oracle-L_at_freelists.org
    Subject: RE: shell scripting help

    Don't run them in the background (&). The second and third statements won't
    run until the previous SQL finishes.


    Ron

    -----Original Message-----
    From: oracle-l-bounce_at_freelists.org
    On Behalf Of d cheng
    Sent: Wednesday, August 03, 2005 4:49 PM
    To: Oracle-L_at_freelists.org
    Subject: shell scripting help

    Hi Listers,


    I have a korn shell script which needs to run 3 sqlplus scripts in parallel
    then run the final sqlplus script. Is there a way to ensure that sqlplus
    scripts 1 thru 3 completes before running the final sqlplus script? I am a
    newbie in shell scripting.


    << shell script snippet >>
    ...

    sqlplus un/pw @script1.sql &
    sqlplus un/pw @script2.sql &
    sqlplus un/pw @script3.sql &

    ...
    sqlplus un/pw @final.sql
    ...
    exit


    Thank you in advance for your help!


    David

    Do You Yahoo!?
    Tired of spam? Yahoo! Mail has the best spam protection around
    http://mail.yahoo.com

    Important Notice!!
    If you are not the intended recipient of this e-mail message, any use,
    distribution or copying of the message is prohibited.
    Please let me know immediately by return e-mail if you have received this
    message by mistake,
    then delete the e-mail message.
    Thank you.

    This message contains confidential information and is intended only
    for the individual or entity named. If you are not the named addressee
    you should not disseminate, distribute or copy this e-mail.
    Please notify the sender immediately by e-mail if you have received
    this e-mail by mistake and delete this e-mail from your system.
    E-mail transmission cannot be guaranteed to be secure or error-free
    as information could be intercepted, corrupted, lost, destroyed, arrive
    late or incomplete, or contain viruses. The sender therefore does not
    accept liability for any errors or omissions in the contents of this
    message which arise as a result of e-mail transmission.
    If verification is required please request a hard-copy version.
    This message is provided for informational purposes and should not
    be construed as an invitation or offer to buy or sell any securities or
    related financial instruments.
    GAM operates in many jurisdictions and is
    regulated or licensed in those jurisdictions as required.

    --
    http://www.freelists.org/webpage/oracle-l
  • Oracle-l-bounce_at_freelists.org at Aug 4, 2005 at 3:23 pm
    Hi Listers,


    I have a korn shell script which needs to run 3 sqlplus
    scripts in parallel then run the final sqlplus script. Is there a way
    to ensure that sqlplus scripts 1 thru 3 completes before running the
    final sqlplus script? I am a newbie in shell scripting.


    use wait (a shell builtin):

    foo1 &
    foo2 &
    wait
    foo3
  • Enrique Fernandez-Pampillon at Aug 5, 2005 at 3:05 am
    Hello everybody first of all I'm sorry for the long and for my
    english. I think all things said related to this subject are not 100%
    correct.

    I'm trying to clarify how to work with backgroud processes in shell.

    The only way to execute a process in background mode is adding "&"
    at the end of the sentence.
    nohup command is used for deattach the process from a terminal, i.e
    you close your connection (telnet, X, ...) and the process will keep
    alive.
    It's not necessary to used nohup command in a shell scripts that
    will be executed with nohup, crontab, at, ...

    A shell like:

    p1&
    p2&
    p3&

    wait
    i1

    it seems to be ok because of it executes p1 and p2 and p3 in
    background and it waits to finish three process before execute i1.
    But for me it has very important bugs:

    This code doesn't control any error, it excutes i1 whether p1 and
    p2 and p3 are ok or whether all or one of them do not terminate ok.
    The stdout of p1 and p2 and p2 are mixed up.

    The command "wait" waits untill all processes haved terminated but it
    "always" return 0.
    The command "wait pid1 ... pidN" waits until pid1, ..., pidN processes
    have terminated and it returns the return status of the pidN process.

    Let's do me some examples:

    mymachine:/usr/users/myuser> (sleep 40 && exit 1) &
    [1] 1060183
    mymachine:/usr/users/myuser> sleep 40 &
    [2] 1181744
    mymachine:/usr/users/myuser> wait
    [2] + Done sleep 40 &
    [1] + Done(1) (sleep 40 && exit 1) &
    mymachine:/usr/users/myuser> echo $?


    It seems to have a good behaviour.

    mymachine:/usr/users/myuser> sleep 40 &
    [1] 1462190
    mymachine:/usr/users/myuser> (sleep 40 && exit 1) &
    [2] 1171882
    mymachine:/usr/users/myuser> wait
    [2] + Done(1) (sleep 40 && exit 1) &
    [1] + Done sleep 40 &
    mymachine:/usr/users/myuser> echo $?


    Ummmm, the last process executed returns 1 but the wait process returns 0

    mymachine:/usr/users/myuser> sleep 40 &
    [1] 1192834
    mymachine:/usr/users/myuser> (sleep 40 && exit 1) &
    [2] 1092754
    mymachine:/usr/users/myuser> wait 1092754 1192834
    mymachine:/usr/users/myuser> echo $?


    Be careful, in spite of the fact the pid "1192834" has started before
    the wait returns its return status.

    mymachine:/usr/users/myuser> sleep 40 &
    [1] 1169544
    mymachine:/usr/users/myuser> (sleep 40 && exit 1) &
    [2] 1221012
    mymachine:/usr/users/myuser> wait 1169544 1221012
    mymachine:/usr/users/myuser> echo $?
    1

    This is the correct behaviour.

    In my expertise I recommend the following:

    Sql template:

    whenever sqlerror exit sql.sqlcode rollback --- or commit
    whenever oserror exit failure rollback --- or commit

    ...
    ... sql code
    ...

    exit sql.sqlcode

    To execute sqlplus use:

    sqlplus -L user/pass_at_tns @sqlscript

    An example of shell scripts (in korn):

    p1 1>$TMPDIR/p1_$$.log 2>&1 &
    Pid1=$!
    p2 1>$TMPDIR/p2_$$.log 2>&1 &
    Pid2=$!
    p3 1>$TMPDIR/p3_$$.log 2>&1 &
    Pid3=$!

    typeset -i TotalErros=0
    for n in 1 2 3; do # In this case I have 3 forks

    wait $(eval echo $"Pid${n}")
    ret_status=$?
    cat p${n}_$$.log
    if [ "${ret_status}" != "0" ]; then

    echo "ERROR - Process p${n} have terminated with ${ret_status}"
    TotalErros=$((${TotalErros}+1))
    else
    echo "OK - Process p${n} have terminated with ${ret_status}"

    fi
    done
    if [ ${TotalErrors} -ne 0 ]; then

    ### ERRROR

    else

    ### OK
    fi

    Be carefuly if you use csh because of it has the wait function built
    in and is possible different behaviour.

    HTH

    Enrique
    On 8/4/05, oracle-l-bounce_at_freelists.org wrote:

    Hi Listers,

    I have a korn shell script which needs to run 3 sqlplus
    scripts in parallel then run the final sqlplus script. Is there a way
    to ensure that sqlplus scripts 1 thru 3 completes before running the
    final sqlplus script? I am a newbie in shell scripting.

    use wait (a shell builtin):

    foo1 &
    foo2 &
    wait
    foo3
    --
    http://www.freelists.org/webpage/oracle-l
    --
    ------------------------------------------------
    Enrique
    --
    http://www.freelists.org/webpage/oracle-l

Related Discussions

Discussion Navigation
viewthread | post
Discussion Overview
grouporacle-l @
categoriesoracle
postedAug 3, '05 at 4:50p
activeAug 5, '05 at 3:05a
posts13
users11
websiteoracle.com

People

Translate

site design / logo © 2022 Grokbase