Re: Calling Stored Procedure using REF CURSORS

[Denesa K Shaw]

I tried with and without closing it and got the following error:
Is there something more I needed to add?

Without Closing:
DBI::db=HASH(0x2035aef0)->disconnect invalidates 1 active statement handle
(either destroy
 statement handles or call finish on them before disconnecting)

With Closing:
DBI::db=HASH(0x2035b630)->disconnect invalidates 1 active statement handle
(either destroy
 statement handles or call finish on them before disconnecting)

This is what I have:
$dbh = TMH::dbConnect(*LOGFILE);

my $IN_FILE_NM = "TEST";

my $out_cursor;

$sql = qq(
BEGIN
 Package.Procedure(:p1,:out_cursor);
END;
);

my $func = $dbh->prepare($sql);
$func->bind_param(":p1",$IN_FILE_NM);
$func->bind_param_inout(":out_cursor", \$out_cursor, 0,{ ora_type=>ORA_RSET
} );
$func->execute;

$dbh->commit;

my $sql = $dbh->prepare('BEGIN close :out_cursor; END;');
$sql->bind_param(":out_cursor",$out_cursor, {ora_type =>
DBD::Oracle::ORA_RSET()});
$sql->execute;

TMH::dbDisconnect(*LOGFILE, $dbh);





                                                                                
                              
                      Thilo Planz                                               
                              
                      <[EMAIL PROTECTED]         To:      Denesa K Shaw <[EMAIL 
PROTECTED]>     
                      ax.co.jp>                cc:      dbi-users@perl.org      
                              
                                               Subject: Re: Calling Stored 
Procedure using REF CURSORS        
                      04/17/2005 07:21                                          
                              
                      PM                                                        
                              
                                                                                
                              
                                                                                
                              




Denesa,

(cc to dbi-users, hope you don't mind)

glad it worked.

> Now I'm trying to close the
> cursor, Would I close the cursor similar to the code below (does not
> work
> by the way)?

I don't really understand that code below...

According to the DBD::Oracle docs, you have to close a ref cursor like
so:

> my $sql = $conn->prepare('BEGIN   close :curref; END;');
>            $sql->bind_param(":curref", $sth, {ora_type =>
> DBD::Oracle::ORA_RSET()});
>            $sql->execute;

( $sth is the ref cursor )

There was recently a discussion on this mailing list, whether this is
really necessary.
Someone, who seemed to know what they were talking about, claimed that
once you release the parent statement handle (the one that produced the
cursor), the ref cursor will also be freed.

That would be nice.

Has a final verdict on this been reached ?


Thilo