We help IT Professionals succeed at work.

Calling pl/sql procs from UNIX and having it return a value

rsippos
rsippos asked
on
Medium Priority
649 Views
Last Modified: 2013-12-12
Can I call a PL/SQL procedure from a UNIX script and have it reutrn a parameter to a UNIX variable?

If this is true, then how?
Comment
Watch Question

I will assume that the UNIX variable is an environment variable.

Do you know that when you run a unix script, it has a copy of all environment variable. You will be able to modifiy environment variable within the unix script but when the unix scipt is done, all the changes you made in the environment variable is gone. you will have the original value of before you run the script.

How about, the PL/SQL procedure will output to a file.
Then set the environment variable manually.
VAR=`cat /tmp/xfile`

Note : ` - not a single quote it is a grave accent. see ~ key

Commented:
You'll have to write an sql file like
--------------------------------
1.sql


SET VERIFY Off;
SET FEEDBACK Off;
SET HEADING Off;
SET TERMOUT Off;
set linesize 80;
SET TRIMSPOOL ON;
SET TRIMOUT On;
SET ECHO Off;

variable retval1 varchar2(100);
declare
retVal number;
begin
plsql_procedure(retval);  -- assuming proc returns number
select retVal into :retval1 from dual;
end;
/
spool 1.log;
select :retval1 from dual;
spool off;

SET VERIFY ON;
SET FEEDBACK ON;
SET HEADING ON;
SET TRIMSPOOL OFF;
SET TERMOUT ON;
SET TRIMOUT OFF;
set linesize 80;
SET ECHO ON;

-----------------------------------
Shell

sqlplus / -s @1.sql;
for i in `cat 1.log`
do
var1=$i
done
---------------------------------

Thus variable var1 contains value sent out by stored procedure.
Hope this helps.
CERTIFIED EXPERT
Author of the Year 2009

Commented:
Hi rsippos,
It appears that you have forgotten this question.  Please move toward finalizing it.

If rahulak's answer did not help you, you may reject that answer.   If another Expert's comment was helpful, you can then select a comment as an answer.   Or you can delete this question to get a refund of your points.

EXPERTS: Post a comment if you think somebody deserves credit here!
==========
DanRollins -- EE database cleanup volunteer

Author

Commented:
There was a much easier way to do this...

I can call the pl/sql stored proc and get the variable in one step.

result=`sqlplus -s $usr_pwd <<EOF
        set pages 0 feedback off echo off termout off
        set serveroutput on
        variable result_date varchar2(10);
        execute chk_program_success('$program_name',:result_date);
        select :result_date from dual;
        exit
        EOF`

CERTIFIED EXPERT
Author of the Year 2009

Commented:
Recommended disposition:

   Refund points to rsippos and save as 0-pt PAQ

DanRollins -- EE database cleanup volunteer
Points refunded and moved to PAQ

** Mindphaser - Community Support Moderator **

Explore More ContentExplore courses, solutions, and other research materials related to this topic.