Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Get PLSQL stored procedure output in unix shell script

user12262355Sep 10 2013 — edited Sep 11 2013

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

This post has been answered by kendenny on Sep 10 2013
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Oct 9 2013
Added on Sep 10 2013
9 comments
12,660 views