Home » Infrastructure » Unix » get the values in shell script variable within sql block? (Linux)
get the values in shell script variable within sql block? [message #497180] Fri, 04 March 2011 05:19 Go to next message
kashifchughtai
Messages: 125
Registered: October 2007
Senior Member
Hi guys,

i have small question, as not very much faimilar with shell scripting.

Is this possible to get the values in shell script variables within sql block. if not how is this possible?

i.e my shell script looks like this

v_sid_id=0
v_ser_no=0
. /var/oracle/oravars10g
sqlplus -s /nolog @/home/abc_login<<!

set serveroutput on size 1000000
/
declare

v_sid number;
v_serial number;
v_TEXT varchar(32000);

cursor c1 is
select col1,col2 from table1 where active_status='Y';

begin


for i in c1 loop

if v_sid is null and v_serial is null then
v_sid :=  i.col1;
v_serial := i.col2;
end if;

end loop;


if v_sid is not null and v_serial is not null then
v_sid_id := v_sid;-- note that v_sid_id is shell script variable
v_ser_no := v_serial; -- v_ser_no 
end if;


exception when others then
null;
end;
/
!


thanks for your help.

regards,
kashif
Re: get the values in shell script variable within sql block? [message #497196 is a reply to message #497180] Fri, 04 March 2011 06:32 Go to previous messageGo to next message
Michel Cadot
Messages: 68625
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Yes it is possible and there are many examples in this forum.
Pleasze try to search a little bit and come back if you don't find anything.

Regards
Michel
Re: get the values in shell script variable within sql block? [message #497200 is a reply to message #497196] Fri, 04 March 2011 06:51 Go to previous message
ThomasG
Messages: 3211
Registered: April 2005
Location: Heilbronn, Germany
Senior Member
While it is possible with some techniques, they all rely on STDOUT to transfer the data, which will lead to indeterminable results when there is some sort of database error at any point. ( I know of a company who sold a lot of "ORA-XXXXX" according to their statistics one day for example. ;-P )

I would suggest that you move away from pure shell script in this one. Once you mix script and pl/sql it get's easier with a scripting language that has better data base support. Take this PERL script for example:

#!/usr/bin/perl

use DBI;

$perl_variable_one = 0;
$perl_variable_two = 0;

$db  = DBI->connect("dbi:Oracle:DATABASE","user",  "password"   ) or die "Database connection failed ";

my $SQL = qq{
 DECLARE
   v_plsqlvar_one number := 1;
   v_plsqlvar_two number := 2;
 BEGIN

  :BIND_ONE := v_plsqlvar_one;
  :BIND_TWO := v_plsqlvar_two;

 END;
};

my $sth = $db->prepare($SQL);
$sth->bind_param_inout( ":BIND_ONE", \$perl_variable_one, 400);
$sth->bind_param_inout( ":BIND_TWO", \$perl_variable_two, 400);
$sth->execute();
 
print "Result for var 1 : $perl_variable_one\n";
print "Result for var 2 : $perl_variable_two\n";

$db->disconnect();


$ ./test.pl
Result for var 1 : 1
Result for var 2 : 2
$
Previous Topic: Oracle User monitoring
Next Topic: vi editor
Goto Forum:
  


Current Time: Thu Mar 28 11:39:08 CDT 2024