| How to get a var from SQL in a unix shell [message #126770] |
Wed, 06 July 2005 09:45  |
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  |
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 |
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
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
