For a start, your SQL is not a valid anonymous PL/SQL block. Did you
try ...
$func_close = $dbh->prepare("BEGIN CLOSE :rv ; END ;");
$func_close->bind_param_inout(":rv", \$func_open, 0, { ora_type =>
ORA_RSET });
$func_close->execute;
You may get a different error, but at least it is now valid PL/SQL
syntax.
Steve
-----Original Message-----
From: Patrick Kuijvenhoven [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 10 December 2003 7:04 PM
To: [EMAIL PROTECTED]
Subject: RE: (Fwd) RE: DBD::Oracle: closing bound cursors
Still, no luck. Hoping not to spam the list by this piece of code:
...
my $rv; # Result value.
my $func_open = $dbh->prepare(q{
BEGIN
:rv := blabla$change.pf_something(pi_nr => :pi_nr);
END;
});
$func_open->bind_param(":pi_nr", $nr);
$func_open->bind_param_inout(":rv", \$rv, 0, { ora_type => ORA_RSET });
$func_open->execute;
if( $@ ) {
warn "Execution of stored procedure failed: $DBI::errstr\n"; }
my $rec = $rv->fetchrow_hashref();
$func_close = $dbh->prepare("BEGIN CLOSE :rv END");
$func_close->bind_param_inout(":rv", \$func_open, 0, { ora_type =>
ORA_RSET }); $func_close->execute;
$rv->finish;
$rv->DESTROY;
...
This returns:
DBD::Oracle::db prepare failed: ORA-06550: line 1, column 17:
PLS-00103: Encountered the symbol "END" when expecting one of the
following:
; (DBD: error possibly near <*> indicator at char 17 in 'BEGIN CLOSE
:rv <*>END') [for statement ``BEGIN CLOSE :rv END'']) at ./code.pl line
84.
What am i doing wrong? (Allready tried to put a ';' in serveral places.
No luck.).
Yours,
Patrick
-----Oorspronkelijk bericht-----
Van: Fox, Michael [mailto:[EMAIL PROTECTED]
Verzonden: dinsdag 9 december 2003 22:30
Aan: [EMAIL PROTECTED]
CC: Patrick Kuijvenhoven
Onderwerp: RE: (Fwd) RE: DBD::Oracle: closing bound cursors
Patrick,
Its all in the documentation now (as in perldoc DBD::Oracle), but in
case this doesn't work for you it says: "
To close the cursor you (currently) need to do this:
$sth3 = $dbh->prepare("BEGIN CLOSE :cursor END");
$sth3->bind_param_inout(":cursor", \$sth2, 0, { ora_type => ORA_RSET
} );
$sth3->execute;
"
where $sth2 was the statement handle with the open cursor.
It all works - I use it all the time. Just make sure that you actually
have a valid cursor to close, as in this case, test if the
$sth1->execute actually worked or not.
-----Original Message-----
From: Tim Bunce [mailto:[EMAIL PROTECTED]
Sent: Wednesday, 10 December 2003 7:09 AM
To: [EMAIL PROTECTED]
Cc: [EMAIL PROTECTED]
Subject: (Fwd) RE: DBD::Oracle: closing bound cursors
----- Forwarded message from Patrick Kuijvenhoven
<[EMAIL PROTECTED]> -----
Delivered-To: [EMAIL PROTECTED]
Subject: RE: DBD::Oracle: closing bound cursors
Date: Tue, 9 Dec 2003 14:18:21 +0100
From: "Patrick Kuijvenhoven" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Hi Tim,
sorry for bothering you by e-mail. I have a problem similar to
http://www.bitmechanic.com/mail-archives/dbi-users/Jul1999/0802.html.
This thread ended with your reply: no solution. :(
Unfortunatly hours of searching Google didn't help me. The
documentations says something about closing cursors, but that doesn't
work for me.
I am using
my $func = $dbh->prepare(q{
BEGIN
:rv := caizen$change.pf_ref_abonnement(pi_abokab_abonnee_nr
=> :pi_abokab_abonnee_nr);
END;
});
$func->bind_param(":pi_abokab_abonnee_nr", $nr);
$func->bind_param_inout(":rv", \$rv, 0, { ora_type => ORA_RSET });
$func->execute;
to bind my cursor. Now i want to close it. Can you help me?
Kind regards,
Patrick Kuijvenhoven,
Research and Development Team
Kabelfoon
----- End forwarded message -----
Australia Post is committed to providing our customers with excellent
service. If we can assist you in any way please either telephone 13 13
18 or visit our website www.auspost.com.au.
CAUTION
This e-mail and any files transmitted with it are privileged and
confidential information intended for the use of the addressee. The
confidentiality and/or privilege in this e-mail is not waived, lost or
destroyed if it has been transmitted to you in error. If you have
received this e-mail in error you must (a) not disseminate, copy or take
any action in reliance on it; (b) please notify Australia Post
immediately by return e-mail to the sender; and (c) please delete the
original e-mail.
