timo has asked for the wisdom of the Perl Monks concerning the following question:

Here is the error I'm receiving: DBD::Oracle::db prepare_cached failed: ORA-01000: maximum open cursors exceeded (DBD ERROR: OCIStmtExecute/Describe) I'm running a sql query on a table with 7000 records. I've done similar queries throughout my program and undef the db handles after $sth->finish. Everything works fine until this last query and then I recieve the above error after about 800 successful results. Here is an excerpt where the error is occurring (as an aside, I'm not using any placeholders yet - I'm still trying to get a successful run first) 
sub Backwards
{
    my $db =shift;
    my $database =shift;
        
    #-- Returns any EDIT_RELD_EDIT_IDs and OIDs that point back to the
+mselves 
     my $sth_all = $db->prepare_cached("
                    SELECT OID, EDIT_RELD_EDIT_ID FROM AG_EDIT_RELD_ED
+IT 
                                      ") or print STDOUT "Query: Check
+ing For Backwards Links Failed";
    $sth_all->execute();
    my $all_rows = $sth_all->fetchall_arrayref;
    $sth_all->finish;
    undef $sth_all;
    
    print "******Backwards Links******\n\n" if ($#$all_rows > -1);
    
    for $all_rows_r (@{$all_rows})
    {
        #print "SELECT OID, EDIT_ID FROM AG_EDITORIAL WHERE OID = $all
+_rows_r->[0] AND EDIT_ID = '$all_rows_r->[1]'\n";
        #print "Next\n"; 
        my $sth_back = $db->prepare_cached("
                        SELECT OID, EDIT_ID FROM AG_EDITORIAL WHERE OI
+D = $all_rows_r->[0] AND EDIT_ID = '$all_rows_r->[1]' 
                        AND OID NOT IN (SELECT OID FROM AG_EDIT_RELD_E
+DIT WHERE OID NOT IN 
                                    (SELECT OID FROM AG_EDITORIAL)
                                          )
                                          ") or print STDOUT "Query: C
+hecking For Backwards - Part 2 Links Failed at OID: $all_rows_r->[0] 
+and EDIT_ID: $all_rows_r->[1]";
        $sth_back->execute();
        my $back_links = $sth_back->fetchall_arrayref;
        $sth_back->finish;
        undef $sth_back;
    
        for $back_links_r (@{$back_links})
        {    
            push (@{$Backward_Links_2{OID}}, $back_links_r->[0]);
            push (@{$Backward_Links_2{EDIT_ID}}, $back_links_r->[1]);
            print &Pad_Text_Left("The following EDIT_RELD_EDIT_ID does
+ not exist in AG_Editorial:OID => $back_links_r->[0]   EDIT_ID => $ba
+ck_links_r->[1]\n\n", 80);
        }
    }
}

[download]
Any suggestions to avoid the error will be greatly appreciated. This is the last piece on my verification code for testing. It's heavily customized but basically I'm checking for primary keys that are related to each other. For example: Table A (key1, key2) and Table B(key3, key 4). The sought after condition is where Table B(key 4) = Table A (key 2) while Table A(key1) = Table B(key3) by row. (Hope that wasn't too confusing)

Replies are listed 'Best First'.
Re: Oracle Cursors Exceeded
by runrig (Abbot) on Dec 14, 2001 at 13:50 UTC
    I'd say that 7000 cursors is a bit much, so its no wonder your running out of them. See a previous post of mine. If you are going to use prepared_cached, then you MUST use placeholders. The variables belong in the execute() argument list, not in the SQL statement itself. See the DBI docs and What are placeholders in DBI, and why would I want to use them?

    And especially with a database like Oracle, you should be using placeholders whenever possible whether you use prepare OR prepare_cached.

[reply]
      Let's see if we can get a meme going. :-)

      The underlying principle here is Don't Parse (and on the flip side, don't quote). Placeholders are better because they are the solution that doesn't require the SQL's metadata to be correctly generated from parsing your handrolled quoting. Hence you avoid a common source of error.

[reply]
      Thank you. I changed the prepare_cached to: 
      $sth_back = $db->prepare_cached("
   SELECT OID, EDIT_ID FROM AG_EDITORIAL WHERE OID = ? 
   AND EDIT_ID = ? AND OID NOT IN 
   (SELECT OID FROM AG_EDIT_RELD_EDIT WHERE OID NOT IN 
    (SELECT OID FROM AG_EDITORIAL)
   )
") or print STDOUT "Query: Checking For Backwards - Part 2 Links Faile
+d";

$sth_back->execute($all_rows_r->[0], $all_rows_r->[1]);

      [download]
      ...and everything worked beautifully!
[reply]
[d/l]
Re: Oracle Cursors Exceeded
by Steve_p (Priest) on Dec 14, 2001 at 22:35 UTC
    The function finish() does not actually get rid of the statement handle, in Oracle terms of closing a cursor. Inside of a block, a statement handle is a perl object, so it will continue to exists (and keep its cursor open) while the block its created in still exists. The undef just insures that you won't be able to access the object again.
    To fix your problem, I'd try putting all the code related to the cursor in the for loop in an anonymous block (i.e. put {...} around the code). Start the line before the "my $sth_back" and end it after the undef $sth_back. You'll also need to move your initial declaration of $back_links to the top of the function, but you can still set the value in this block. This should force Perl's garbage collection to destroy the statement handles and close the cursors.
    Also, placeholders may work to automagically open/close/re-open the cursors for you. If placeholders don't work try what I wrote above.
    Hope this helps, Steve
[reply]
      Shuffling the code around won't help, and using undef won't destroy the cursor when you use prepare_cached, because DBI will keep a reference to the $sth even if you don't. But I would probably move the prepare_cached from inside the for loop to outside of the loop for a small efficiency gain (so you don't have to refetch the $sth on every iteration of the loop).

      Also, you don't need to call finish when you fetch all of the rows from a cursor (it should only be used when you, e.g., fetch the first n rows and then exit the loop early), and you don't need to undef the $sth. And I would be cautious in using the fetchall_* methods; if there are alot of rows it can eat up memory; and if you don't really need the entire result set all at once, you're just as well off just using fetch (maybe even combined with bind_columns).

[reply]

Back to Seekers of Perl Wisdom