Home » Infrastructure » Unix » How to send an sqlplus two output values to shell variables
How to send an sqlplus two output values to shell variables [message #174397] Mon, 29 May 2006 02:03 Go to next message
adroit.ramesh
Messages: 14
Registered: November 2005
Junior Member
Hi
I am running the following shell script

DATAFILENAME=`sqlplus -s scott/tiger@test <<EOF1
variable file_name varchar2(50)
variable record_count number
set heading off
set pagesize 0
set feedback off
set serveroutput on size 1000000
exec TEST_PKG.EXTRACT_DATA(:file_name,:record_count)
select :file_name from dual;
EOF1`

I want to assign sqlplus fil_name and record_count values to the shell script variables.

Thanks
Ramesh
Re: How to send an sqlplus two output values to shell variables [message #174834 is a reply to message #174397] Tue, 30 May 2006 13:54 Go to previous messageGo to next message
BlackSwan
Messages: 26756
Registered: January 2009
Location: SoCal
Senior Member
what problem are you really trying to solve?

I ask because your sample code is less than intuitive.

FWIW - process environmental variable can be used within the "here doc".

MY_VAR=FUBAR

<< EOF1

SELECT $MY_VAR FROM DUAL;
EOF1
Re: How to send an sqlplus two output values to shell variables [message #174868 is a reply to message #174834] Tue, 30 May 2006 18:02 Go to previous message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
#!/usr/bin/ksh
RETVAL=`sqlplus -s /nolog <<EOF
connect scott/tiger@dev
--create or replace procedure do_stuff(p_fname out varchar2, p_cnt out number)
--is
--begin
--  p_fname := 'abc.txt';
--  p_cnt  := '5';
--end;
--/

set serveroutput on
declare
  v_fname varchar2(10);
  v_cnt number;
begin
  do_stuff(v_fname, v_cnt);
  dbms_output.put_line(v_fname);
  dbms_output.put_line(to_char(v_cnt));
end;
/
EOF`

#echo $RETVAL
FNAME=`echo $RETVAL | awk '{print $2}'`
CNT=`echo $RETVAL | awk '{print $3}'`
echo File $FNAME
echo Count $CNT


dev>>t.ksh   
File abc.txt
Count 5


you can also just redirect the output to a temp file, then grep for whatever you want. e.g. dbms_output.put_line('KEEP_FileName '||v_fname);

Then just grep for KEEP_FileName in the temp file...


Previous Topic: How to call a Shell Script from the Stored Procedure ?
Next Topic: Upgrade to 10G
Goto Forum:
  


Current Time: Tue Nov 24 01:20:27 CST 2020