Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2006
    Posts
    20

    Unanswered: Controlling a PL/SQL procedure execution from a UNIX shell script

    I want to control the execution of a PL/SQL procedure from a UNIX shell script.
    Below, I include the script.
    The control variable which should recive the return of the procedure, dosen't work well.
    I want to control the return, because I wanr to make a UNIX script to control the execution of
    a load data process with some Oracle procedures.


    ---

    #!/bin/ksh

    echo "Executing procedure pl/sql"
    SQLPLUS="sqlplus -s /"
    ESQUEMA="esquema1"
    echo "\
    call ${ESQUEMA}.Z_PROC_PRUEBA();" | $SQLPLUS
    echo "Controlling pl/sql execution"
    var_err=$?
    if [ $var_err -gt 0 ]
    then
    echo "Error executing pl/sql"
    else
    echo "pl/sql finished sucessfully"
    fi

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Apr 2006
    Posts
    20

    One possible solution

    Thanks,
    I have found one possible solution. I have included in the code executed by sqlplus the next sentence:

    whenever SQLERROR exit 1

    It works!!

    ---
    #!/bin/ksh
    echo "Executing procedure pl/sql"
    SQLPLUS="sqlplus -s /"
    ESQUEMA="esquema1"
    echo "
    whenever SQLERROR exit 1
    call ${ESQUEMA}.Z_PROC_PRUEBA();" | $SQLPLUS
    echo "Controlling pl/sql execution"
    var_err=$?
    if [ $var_err -gt 0 ]
    then
    echo "Error executing pl/sql"
    else
    echo "pl/sql finished sucessfully"
    fi

  4. #4
    Join Date
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking !eosql

    You could try this other solution:
    Code:
    #!/bin/ksh
    echo "Executing procedure pl/sql"
    SQLPLUS="sqlplus -s /"
    ESQUEMA="esquema1"
    $SQLPLUS <<!EOSQL
    whenever SQLERROR exit 1
    exec ${ESQUEMA}.Z_PROC_PRUEBA();
    !EOSQL
    var_err=$?
    echo "Controlling pl/sql execution"
    if [ $var_err -gt 0 ]
    then
      echo "Error executing pl/sql"
    else
      echo "pl/sql finished sucessfully"
    fi
    The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •