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)

In reply to Oracle Cursors Exceeded by timo

Title:
Use:  <p> text here (a paragraph) </p>
and:  <code> code here </code>
to format your post, it's "PerlMonks-approved HTML":


  • Posts are HTML formatted. Put <p> </p> tags around your paragraphs. Put <code> </code> tags around your code and data!
  • Titles consisting of a single word are discouraged, and in most cases are disallowed outright.
  • Read Where should I post X? if you're not absolutely sure you're posting in the right place.
  • Please read these before you post! —
  • Posts may use any of the Perl Monks Approved HTML tags:
    a, abbr, b, big, blockquote, br, caption, center, col, colgroup, dd, del, details, div, dl, dt, em, font, h1, h2, h3, h4, h5, h6, hr, i, ins, li, ol, p, pre, readmore, small, span, spoiler, strike, strong, sub, summary, sup, table, tbody, td, tfoot, th, thead, tr, tt, u, ul, wbr
  • You may need to use entities for some characters, as follows. (Exception: Within code tags, you can put the characters literally.)
            For:     Use:
    & &amp;
    < &lt;
    > &gt;
    [ &#91;
    ] &#93;
  • Link using PerlMonks shortcuts! What shortcuts can I use for linking?
  • See Writeup Formatting Tips and other pages linked from there for more info.