Home » Infrastructure » Unix » Variable value passing between Unix shell - PL/SQL block
Variable value passing between Unix shell - PL/SQL block [message #98224] Fri, 14 May 2004 11:47 Go to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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.
Re: Variable value passing between Unix shell - PL/SQL block [message #312809 is a reply to message #126774] Wed, 09 April 2008 17:02 Go to previous messageGo to next message
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

Re: Variable value passing between Unix shell - PL/SQL block [message #312817 is a reply to message #98224] Wed, 09 April 2008 18:49 Go to previous messageGo to next message
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
Senior Member
SID will always be "1" for previous post.

[Updated on: Wed, 09 April 2008 18:50] by Moderator

Report message to a moderator

Re: Variable value passing between Unix shell - PL/SQL block [message #313145 is a reply to message #312817] Thu, 10 April 2008 20:05 Go to previous message
glenshewchuck
Messages: 2
Registered: April 2008
Location: World Wide
Junior Member
Try it. Just cut-n-paste into a file. I ran it on our servers got the SID answer and cut-n-pasted here.
Previous Topic: Could not locate Java runtime
Next Topic: How to launch a program when arrival of the file (merged)
Goto Forum:
  


Current Time: Thu Mar 28 07:42:50 CDT 2024