Home » Infrastructure » Unix » How to get a var from SQL in a unix shell
How to get a var from SQL in a unix shell [message #126770] Wed, 06 July 2005 09:45 Go to next message
Ganjz
Messages: 2
Registered: July 2005
Junior Member
HI,

Here's a SQL script.

DECLARE
a varchar2 (10);
b number;
c number;
d varchar2 (100);

begin
a := 'LOG';
scc_lpr_pkg.driver(a,b,c,d);
end;
/

ok now my question:
I try to get the value returned from the SQL procedure in a UNIX shell.

i have already a shell calling the sql and i just don't know how to get the vars in that shell.

thank for reading.





[Updated on: Wed, 06 July 2005 09:49]

Report message to a moderator

Re: How to get a var from SQL in a unix shell [message #126785 is a reply to message #126770] Wed, 06 July 2005 11:29 Go to previous messageGo to next message
somnath1974
Messages: 15
Registered: July 2005
Junior Member
Hi,
In the sqlplus session that you might have opened decalre a host variable like for example

sqlplus -S $USERNAME/$USERPASSWORD@$DATABASENAME <<EOJ >>$LOGFILE
SET FEEDBACK ON
SET SERVEROUTPUT ON
# The following declares the variable
VARIABLE exec_status NUMBER
exec my_Stored_proc
exit :exec_status

Best Regards,
Somnath
Re: How to get a var from SQL in a unix shell [message #127108 is a reply to message #126785] Fri, 08 July 2005 13:04 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
#!/bin/ksh
#======================================================
# stored proc with parms
#======================================================
## CREATE OR REPLACE PROCEDURE p1 (i_parm IN NUMBER, o_parm OUT NUMBER)
## IS
## BEGIN
##    o_parm := 5 * i_parm;
## END;
## /
my_in_parm=5
RETVAL=`sqlplus -s scott/tiger@dev <<EOF
set serveroutput on
--WHENEVER SQLERROR EXIT 1
DECLARE
   x   NUMBER := $my_in_parm;
   y   NUMBER;
BEGIN
   p1 (x, y);
   DBMS_OUTPUT.put_line ('o_parm from p1 is ' || y);
END;
/
exit;
EOF`

echo $RETVAL
X=`echo $RETVAL | awk '{print $5}'`
echo $X


>t.ksh

o_parm from p1 is 25 PL/SQL procedure successfully completed.
25
Previous Topic: help ! why can't i use @ to run a script file in HP-UX 11i (b23)
Next Topic: pw-syscall problem on AIX 5.1
Goto Forum:
  


Current Time: Fri Mar 29 09:06:35 CDT 2024