#!/usr/bin/perl

use strict;

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

DBI->trace(0);

my $dbname = 'dbname';
my $dbuser = 'dbuser';
my $dbpass = 'dbpass';

my $dbh = DBI->connect("dbi:Oracle:$dbname", $dbuser, $dbpass,
    { AutoCommit => 0, RaiseError => 0, PrintError => 0 } )
    or die "Unable to connect to $dbname: $DBI::errstr\n";

my $limit = $dbh->selectrow_array(<<'SQLEND');
SELECT value - 2 FROM v$parameter WHERE name = "open_cursors";
SQLEND

$limit -= 2 if $limit && $limit >= 2;

unless (defined $limit) {
    print "Can't determine open_cursors from v\$parameter, so using default\n";
    $limit = 1;
}

print "Max cursors: $limit\n";


print "Opening cursors...\n";
my $open_cursor = $dbh->prepare(<<SQLEND);
BEGIN OPEN :kursor FOR
    SELECT * FROM all_objects WHERE rownum < 5;
END;
SQLEND
if ($open_cursor) {
    print "Statement prepared\n";
} else {
    print "Couldn't prepare statement\n";
    exit;
}

my @cursors;

for (1 .. $limit) {
    print "Opening cursor $_\n";
    my $ok = $open_cursor->bind_param_inout(":kursor",
        \my $cursor, 0, {ora_type => ORA_RSET} );
    $ok ? print "bound\n" : (print("not bound\n") && exit);
    $ok = $open_cursor->execute;
    $ok ? print "executed\n" : (print("execute failed\n") && exit);
    $ok = ! $open_cursor->{'Active'};
    $ok ? print "statement not active\n" : (print("statement active\n") && exit);

    $ok = $cursor->{'Active'};
    $ok ? print "cursor active\n" : (print("cursor not active\n") && exit);
    $ok = $cursor->fetchrow_arrayref;
    $ok ? print "fetch succeeded\n" : (print("fetch failed\n") && exit);
    $ok = $cursor->fetchrow_arrayref;
    $ok ? print "fetch succeeded\n" : (print("fetch failed\n") && exit);
    $ok = $cursor->finish;
    $ok ? print "finish succeeded\n" : (print("finish failed\n") && exit);
    $ok = !$cursor->{'Active'};
    $ok ? print "cursor not active\n" : (print("cursor active\n") && exit);
    push @cursors, $cursor;
}

print "Closing cursors...\n";

my $close_cursor = $dbh->prepare('BEGIN CLOSE :kursor; END;');
if ($close_cursor) {
    print "Close statement prepared\n";
} else {
    print "Failed to prepare close statement\n";
    exit;
}

for (1 .. @cursors) {
    print "Closing cursor $_\n";
    my $cursor = $cursors[$_-1];
    #my $ok = $close_cursor->bind_param(':kursor', $cursor, {ora_type => ORA_RSET});
    my $ok = $close_cursor->bind_param_inout(':kursor', \$cursor, 0, {ora_type => ORA_RSET});
    $ok ? print "cursor bound\n" : (print("cursor not bound\n") && exit);
    $ok = $close_cursor->execute;
    if ($ok) {
        print "execute succeeded\n";
    } else {
        print "execute failed\n";
        print $close_cursor->errstr;
        exit;
    }
}



$dbh->disconnect
    or die "Unable to disconnect from $dbname: $DBI::errstr\n";