9 Replies Latest reply: Sep 11, 2013 7:58 AM by Vikrant_singh RSS

    Get PLSQL stored procedure output in unix shell script

    user12262355

      Hi Experts,

       

      I have a PLsql procedure with the below specification-

       

      PROCEDURE SEND_INV_VIA_EMAIL (p_pdf_file_name IN VARCHAR2,p_submitted_frm IN VARCHAR2);

       

      I can pass the two in parameters from SHELL script and it is working fine-

       

      # Calling the PLSQL procedure

      sqlplus -s $APPS_USER <<EOSQL

      SET SERVEROUTPUT ON SIZE 1000000

      whenever sqlerror exit 1;

      execute MOB_XMLPUB_ARCHIVE_ARINV_PCK.send_inv_via_email('`basename $DATA_FILE`','ARCHIVE');

      exit; 

      EOSQL

       

      But now i want to have an out parameter in the plsql procedure-

       

      PROCEDURE SEND_INV_VIA_EMAIL (p_pdf_file_name IN VARCHAR2,p_submitted_frm IN VARCHAR2,p_return_status   IN OUT   VARCHAR2);

       

      based on this i want to determine the concurrent request completion status since shell script is getting called via concurrent program.

      how will i pass out parameter from shell and How can i use this out parameter in SHELL script to determine completion status?

       

      Many thanks.

      Vikrant