Results 1 to 2 of 2
  1. #1
    Join Date
    Oct 2004
    Posts
    7

    Unanswered: Redirecting the output of a PL/SQL procedure to a log file in aUNIX shell script

    I want to redirect the output of a PL/SQL procedure to a log file. In this file I want to have all the output of the execution, the results of every step, errors, etc..

    I have tried with a UNIX pipe ( >> sql_log.txt) and with spool sentences, but it dosen’t work.

    Can anybody help me??

    Attached the UNIX script which call the procedure.

    Any advice will be greatly appreciated.

    ---
    #!/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
    Jun 2003
    Location
    West Palm Beach, FL
    Posts
    2,713

    Talking sqlplus -s

    Unless you have DBMS_OUTPUT statement in your PL/SQL procedure, not much will be generated and maybe NOTHING when you use the '-s' sqlplus option, but try this:
    Code:
    #!/bin/ksh
    export ORAENV_ASK=NO
    export ORACLE_SID=MYDB1
    . oraenv
    echo "Executing procedure pl/sql"
    SQLPLUS="sqlplus -s /"
    ESQUEMA="esquema1"
    echo "\
    call ${ESQUEMA}.Z_PROC_PRUEBA();" | $SQLPLUS >/my/path/to/logs/plsql.log
    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
  •