You are creating a new handle (cursor) every time you prepare this
statement with a different set of values. You need to use placeholders
('?' in the values clause) in the SQL statement, and execute the statement
with your different values.
-----Original Message-----
From: Xiaoping Gu [mailto:[EMAIL PROTECTED]]
Sent: Tuesday, June 05, 2001 1:37 PM
To: Wilson, Doug
Cc: '[EMAIL PROTECTED]'; Michael A. Chase; Sterin, Ilya; 'Dan, Ephraim ';
[EMAIL PROTECTED]
Subject: Re: Run out of number of cursor using DBD::Oracle when
inserting/updating
Below is one piece of code,
$i2th = $dbh->prepare_cached("insert into svc_request
(svc_request_id, customer_id, partial_order_name, svc_request_name,
origin, status, status_entered_date, entered_date, note) values
(svc_request_id.NextVal, $customer_id, '$po_filename', '$so_name',
$usrp_origin, $status_value, $timestamp, $timestamp, $comments_q) ")
or die "Couldn't prepare statement: " . $dbh->errstr;
my $success = $i2th->execute();
my $result = ($success ? $dbh->commit : $dbh->rollback);
unless ($success) { die "Couldn't insert: " . $i2th->errstr }
unless ($result) { die "Couldn't finish transaction: " .
$dbh->errstr }
And I used 'prepare_cached' statement. The total number of statement
handles is 6.
"Wilson, Doug" wrote:
>
> You may need to post a small sample which demonstrates the problem.
>
> You don't need, e.g., 3 different cursors (statement handles)
> to update 3 different rows in the same table. I suspect you
> are creating cursors and not reusing them.
>
> -----Original Message-----
> From: Xiaoping Gu [mailto:[EMAIL PROTECTED]]
> Sent: Tuesday, June 05, 2001 1:23 PM
> Cc: Michael A. Chase; Sterin, Ilya; 'Dan, Ephraim '; [EMAIL PROTECTED]
> Subject: Run out of number of cursor using DBD::Oracle when
> inserting/updating
>
> Hi,
>
> We have a perl program to insert/update/select to Oracle database
> using DBI and DBD::Oracle. After each execute, we commit it.
> However, it only inserts/updates about 100 rows then runs out of
> number of cursors. The open_cursor is set to 100. However, why
> the cursors are not released after commit? Can somebody asnwer
> this? Thanks.
