René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback
 

Oracle ref cursors with Perl DBI

Note: I have started to move some scripts and source code from this website to GitHub.

This affects some or all of the scripts found on this page. They should be found under
github.com/ReneNyffenegger/development_misc/ ... perl/cpan/DBD/Oracle/ref_cursor.

I don't intend to maintain the scripts and or sources on this page any longer (so they might be outdated). But I will try to improve the code in the GitHub repository and accept Push Requests. 

create or replace function perl_refcursor_func(p_a in number)
return sys_refcursor as
  ret sys_refcursor;
begin
  open ret for 
    select * from perl_refcursor_tab
     where a > p_a;

  return ret;
end perl_refcursor_func;
/

create table perl_refcursor_tab (
  a  number,
  b  varchar2(10)
);

insert into perl_refcursor_tab values ( 1, 'one'  );
insert into perl_refcursor_tab values ( 2, 'two'  );
insert into perl_refcursor_tab values ( 3, 'three');
insert into perl_refcursor_tab values ( 4, 'four' );
insert into perl_refcursor_tab values ( 5, 'five' );
insert into perl_refcursor_tab values ( 6, 'six'  );
insert into perl_refcursor_tab values ( 7, 'seven');
insert into perl_refcursor_tab values ( 8, 'eight');
insert into perl_refcursor_tab values ( 9, 'nine' );
insert into perl_refcursor_tab values (10, 'ten'  );

commit;

use DBI;
use DBD::Oracle qw(:ora_types);

use warnings;
use strict;

print "User: ";     my $user = <STDIN>;
print "Password: "; my $pw   = <STDIN>;
print "SID: ";      my $sid  = <STDIN>;

chomp($user, $pw, $sid);

my $dbh = DBI->connect("dbi:Oracle:$sid", $user, $pw);

my $sth = $dbh -> prepare("
declare
  rc sys_refcursor;
begin
  :rc := perl_refcursor_func(:p_a);
end;
");

$sth->bind_param(":p_a", 5);

my $sth_cursor;
$sth->bind_param_inout(":rc", \$sth_cursor, 0, {ora_type => ORA_RSET });

$sth->execute();

while (my $r = $sth_cursor->fetchrow_hashref) {
  print $r->{A}, "\n";
}

use DBI;
use DBD::Oracle qw(:ora_types);

use warnings;
use strict;

print "User: ";     my $user = <STDIN>;
print "Password: "; my $pw   = <STDIN>;
print "SID: ";      my $sid  = <STDIN>;

chomp($user, $pw, $sid);

my $dbh = DBI->connect("dbi:Oracle:$sid", $user, $pw);

my $sth = $dbh -> prepare("
begin
  open :rc for 
    select * from perl_refcursor_tab
     where a > :p_a;
end;
");

$sth->bind_param(":p_a", 5);

my $sth_cursor;
$sth->bind_param_inout(":rc", \$sth_cursor, 0, {ora_type => ORA_RSET });

$sth->execute();

while (my $r = $sth_cursor->fetchrow_hashref) {
  print $r->{A}, "\n";
}