| Variable value passing between Unix shell - PL/SQL block [message #98224] |
Fri, 14 May 2004 11:47  |
Pranav
Messages: 4
Registered: January 2002
|
Junior Member |
|
|
Hi,
I have following code snippet where i am starting an SQL block from within shell script and intend to get a value back into shell script from SQL query. Issue is I am unable to find a way to get the value (lv_base_storage) returned by SQL query back into shell domain for me to use that.
If any one has clue it will be appreciated.
Sample code:
---------------------------------------
get_database_values()
{
echo "Inside get_database_values..." >> ${LogFileName}
sqlplus -s $SQL_USER/$SQLPASSWD@$SQLHOST << EOSQL
SET SERVEROUTPUT ON
SET FEEDBACK OFF
SET VERIFY OFF
VARIABLE exit_status NUMBER
WHENEVER SQLERROR EXIT SQL.SQLCODE
DECLARE
lv_base_storage VARCHAR2(100);
ln_total_count NUMBER;
BEGIN
:exit_status := 0;
SELECT count(*)
INTO ln_total_count
FROM kl_clf_transcontrol;
IF (ln_total_count > 0) THEN
BEGIN
SELECT inivalue
INTO lv_base_storage
FROM kini
WHERE inisection = 'Livelink.DocStorage;
EXCEPTION
WHEN NO_DATA_FOUND THEN
:exit_status := 1;
${LogFileName}
END;
ELSE
:exit_status := 1;
END IF;
END;
/
EXIT:exit_status
EOSQL
if [[ $? != "0" ]]
then
echo "Error in getting required database values. Quitting..." >> ${LogFileName}
exit 0
else
echo "Need the value of lv_base_storage here to proceed"
fi
}
|
|
|
|
| Re: Variable value passing between Unix shell - PL/SQL block [message #98253 is a reply to message #98224] |
Fri, 28 May 2004 22:37  |
Tak Tang
Messages: 142
Registered: May 2004
|
Senior Member |
|
|
You need to display the value of lv_base_storage (with PROMPT perhaps?) along with some kind of tag, and then grep for it, and read it into a variable.
#
somefunc()
{
cat <<EOSQL
hello world
lv_base_tag one
some more output
EOSQL
if [[ $? != "0" ]]
then
echo "Error"
exit
fi
}
somefunc | grep lv_base | if read dummy lv_base
then
echo "You got mail! $lv_base"
fi
Takmeister
|
|
|
|
| Re: Variable value passing between Unix shell - PL/SQL block [message #98545 is a reply to message #98224] |
Thu, 23 December 2004 23:20 |
Viji
Messages: 5
Registered: December 2000
|
Junior Member |
|
|
Make the sql script to print in the screen and get the output in a file
sqlplus -s $SQL_USER/$SQLPASSWD@$SQLHOST << EOSQL > < ANY FILE>
SET SERVEROUTPUT ON
SET HEAD OFF
..........
........
IF (ln_total_count > 0) THEN
BEGIN
SELECT inivalue FROM kini
WHERE inisection = 'Livelink.DocStorage;
SINCE INTO VARIABLE WORD IS DELETED, HERE inivalue will be displayed in the screen which will be captured in the file..
...............
Remove spaces in the file by tr command and use the value alone
|
|
|
|
| Re: Variable value passing between Unix shell - PL/SQL block [message #126774 is a reply to message #98545] |
Wed, 06 July 2005 10:21 |
Ganjz
Messages: 2
Registered: July 2005
|
Junior Member |
|
|
HI,
i'm trying somtrhing similar as this but my sql proc dont writes anything. i just need to get the returned parameter from the procedure.
I have a sql file with this in it :
DECLARE
a varchar2 (10);
b number;
c number;
d varchar2 (100);
begin
a := 'LOG';
scc_lpr_pkg.driver(a,b,c,d);
end;
and i want my .KSH file to run that procedure (no problem here) AND i want my .KSH to get the value (a,b,c,d) returned from the sql procedure.
thx.
|
|
|
|
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
