DBI Stored Procedures and Cursors


I have a stored procedure that belongs within a package in Oracle.

This is my first exposure to DBI, so I'm sure i'm doing something
wrong. The stored procedure takes a cursor as one of the parameters.
The stored procedure is supposed to initialize the cursor and all I
need to do is print the contents of the cursor.

I can execute without any problems, but I cannot perform a
fetchrow_array.

Thanks in advance for any help.

#==========================================

use strict ;
use DBI ;

# Note inclusion of DBD::Oracle data types. Without it, we cannot
# work with REF Cursor variables.
use DBD::Oracle qw(:ora_types);

print "connecting..." ;
my $dbh = DBI->connect( 'dbi:Oracle:----','----','-----')
|| die "failed to connect to db" ;
print "connected!\n" ;

my $iDate ;
my $cursor ;

my $sql = qq{ begin
MYDB.MYPKG.myPROC(to_date('2007/05/07', 'yyyy/mm/dd'), :cursor) ;
end ; } ;

my $sth = $dbh->prepare( $sql ) ;
$sth->bind_param_inout( ":cursor", \$cursor, 0, { ora_type =>
ORA_RSET} ) ;

print "Executing ..." ;
$sth->execute() || die "$sql\n statement didn't work! " ;
print "Executed!\n" ;

while( my @row = $cursor->fetchrow_array ) {
# Never gets here.
print "@row\n" ;
}

$sth->finish() ;
$dbh->disconnect() ;

.

Relevant Pages

  • Re: calling one stored procedure from another
    ... but how do I "execute the stored procedure with the local ... >> There is an example of using a cursor in BOL under "declare cursor". ... >> columns, fetch them into local variables, and then execute the stored ...
    (microsoft.public.sqlserver.programming)
  • Re: calling one stored procedure from another
    ... but how do I "execute the stored procedure with the local ... "Scott Morris" wrote in message ... > define a cursor with a select statement that selects the appropriate ... > columns, fetch them into local variables, and then execute the stored ...
    (microsoft.public.sqlserver.programming)
  • Best Practices
    ... An important part of the conversion is moving a large number of Oracle stored procedure to VB.NET; the reason is that they containt too much business logic. ... define cursor 1 ... Should I execute the select statements at the beginning of the function, or should I execute them in the "middle" of the function? ... Do I use the same datareader for all the select statements (eg call ExecuteReader on the same datareader with different command object - or maybe the same command object with a different command string)? ...
    (microsoft.public.dotnet.framework.adonet)
  • RE: Can a cursor return rows from a stored procedure?
    ... > set nocount on ... > execute dbo.usp_proc1 ... >> I now want to call the same procedure within another stored procedure and ... Kind of like defining a cursor with a stored ...
    (microsoft.public.sqlserver.programming)
  • Re: noise words, @@ERROR, and stop and resume indexing
    ... these words at the beginning before sending them to the cursor. ... Looking for a FAQ on Indexing Services/SQL FTS ... >>Create table Noise ... >>> 1) check noise words inside stored procedure ...
    (microsoft.public.sqlserver.fulltext)