Thursday, May 24, 2012

How to display Oracle PL/SQL "OUT" variable in perl


Step 1: Create a procedure in oracle with OUT variable

create procedure testing (timendate out varchar2)
as
begin
select systimestamp into timendate from dual;
end;
/

Note: Above proc outputs systimestamp.

Step 2: Test the proc output in Oracle

set serveroutput on
declare
time varchar2(200);
begin
testing(time);
dbms_output.put_line('Returned: ' ||time);
end;
/

The output should be like:
Returned: 23-MAY-12 03.20.00.502124 PM -07:00

PL/SQL procedure successfully completed.

Step 3: Get this output from a perl script


#!/usr/bin/perl


#Adding the Oracle DBD Perl modules
use DBI;
use DBD::Oracle;


#Defining the connection to the Oracle DB
my $dbh = DBI->connect("dbi:Oracle:host=hostname;port=1521;sid=test","system","oracle")
or die "Error: " . $DBI->errstr;


#Perl variable for storing the PL SQL Proc output.
my $proc_out;


#Preparing the Oracle Statement to be execute. ":proc_out" is the oracle variable.
my $sql = "BEGIN testing(:proc_out); END;";
my $stm = $dbh->prepare($sql);


#bind_param_inout takes a REFERENCE to program variable as an argument. It passes the pointer to variable to Oracle, not the variable itself


$stm->bind_param_inout(":proc_out", \$proc_out,400);


#execute the SQL Statement
$stm->execute()
  or die "Database Error: " . $sth->errstr;


#Print the output
print "Current System Time Is: $proc_out\n";




Output should be like:

Current System Time Is: 23-MAY-12 03.25.55.364674 PM -07:00


Detailed reference from http://www.dba-oracle.com/t_dbi_perl_bind_calls.htm

0 Comments: