OraFAQ Forum: Unix » Variable value passing between Unix shell

Home » Infrastructure » Unix » Variable value passing between Unix shell - PL/SQL block

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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
}

Report message to a moderator

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

Report message to a moderator

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

Report message to a moderator

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.

Report message to a moderator

Re: Variable value passing between Unix shell - PL/SQL block [message #312809 is a reply to message #126774]

Wed, 09 April 2008 17:02

glenshewchuck
Messages: 2
Registered: April 2008
Location: World Wide

Junior Member

Easy way to load a value returned by a sqlplus session (sql or pl/sql)into a UNIX variable is as follows (please note the enclosing quotes are backquotes).

#!/bin/ksh

SID=`sqlplus -s '/ as sysdba'<<EOF
set pages 0
set feedback off
select name from v\\$database;
exit;
EOF
`

echo $SID

Glen Shewchuck

[Updated on: Wed, 09 April 2008 17:02]

Report message to a moderator