Re: Oracle variable to unix script/environment

From: QuestionExchange <USENET_at_questionexchange.com>
Date: 26 Oct 1999 1:51:29 GMT
Message-ID: <2092qx@questionexchange.com>

>
> How does one create a variable from a select statement and
pass it back to the
> shell script? In other words, I want to do something like
this from sqlplus:
>
> column aa new_value bb noprint;
> select sysdate aa from dual;
>
> The oracle variable &bb now contains the value of sysdate
but, how do I pass
> &bb either to a shell script or as an environment variable
outside of Oracle?
>
>

here is an example of some code (bourne/korn shell) that can pass a variable from an sqlplus session to the unix environment.
getinterval () {
 getoraid
sqlplus -SILENT /NOLOG <<sqlplusdone
connect $oraid/$orapassword@$oraconnectstring set feedback off
declare
interval integer;
begin
select failinterval into interval from ctms.jobdefaults where procedurename='$1';
interval:=-20224-interval;
if (interval<-20999) then
interval:=-20999;
end if;
raise_application_error(interval,'return value'); end;
.
/
exit sql.sqlcode
sqlplusdone
 export delayinterval=$?
 }
please note that this only works with numeric variables and the shell will truncate the return value to the lowest 8 bits (value of 0 to 255) since it views this information as a return code from the sqlplus program.
it is possible to get any kind of data out of oracle by using PERL. here is a code snippet that executes a stored function in an oracle database and returns the value to the unix environment.

###############
#
# set up uses statements here
#

use DBI;

#
# body of code here
#

my $db;
my $fileid;
#my $datatype;
#my $processid;
$db=DBI-
>connect("dbi:Oracle:$ENV{'oraconnectstring'}",$ENV{'oraid'},$E
NV{'orapassword'})

        or die "unable to connect: $DBI::errstr"; $db->{RaiseError} = 1;
$csr=$db->prepare(q{

        begin
                :fileid :=
ctms.statustrack.getfileid(to_number(:iseqid));
        end;    });

$csr->bind_param_inout(":fileid", \$fileid, 256); $csr->bind_param_inout(":iseqid" , \$ARGV[0], 256); #$csr->bind_param_inout(":processid" , \$ARGV[1], 256); $csr->execute;
$db->disconnect;
print "$fileid\n";
# end
note that this snippet prints the return to the system console. but you can assign the output to any variable by writing this code into an executable program and doing something like the following (assuming the executable is named getfileid) #
export somevariable=getfileid
#
if you need any further information please let me know andre azaroff
aazaroff_at_redrose.net

--
  This answer is courtesy of QuestionExchange.com   http://www.questionexchange.com/showUsenetGuest.jhtml?ans_id=6402&cus_id=USENET&qtn_id=4513 Received on Mon Oct 25 1999 - 20:51:29 CDT