Cannot avoid DBDish::Oracle: Error: ORA-01000: maximum open cursors exceeded

[ancientwizard]

I believe the docs suggest and the proper behavior is that this condition is avoidable as long as every $sth has it .dispose method called promptly. The following pseudo code always produces the exception even though its usage is serialized. I have not proven it but timings suggest that some kind of Raku housekeeping may also play a role; however .dispose clearly is not getting the job done.

$dbh = DBI.connect( ... );
for  ^1000 {
  $sth = $dbh.prepare('SELECT * FROM WHEREVERULIKE WHERE BLAH');
  $sth.execute(...);
  for $sth.allrows -> $r { ... }
  $sth.dispose;
}
$dbh.disconnect;

It's an unfinished work but illustrates the example. 58-oracle-cursor-bug.t.txt

[ancientwizard]

The issue has gone undetected because the .HandleFree return value is untested. I added temp inspection code to show what is taking place. The failing free certainly would cause Oracle to toss its cookies given some time.

CODE

method _free() {
    with $!stmth {
        say sprintf '# %s (L#%d) - %s', self.^name, $?LINE, .^name;
        say '# ', .HandleFree, ' != ', OCI_SUCCESS; # OCI_INVALID_HANDLE;
        $_ = Nil;
    }   
}   

Output snip: # DBDish::Oracle::StatementHandle (L#208) - DBDish::Oracle::Native::OCIStmt # DBDish::Oracle::Native::OCIStmt (L#148) - OCIHandleFree(self,4) # -1 != 0 ok 118 - .dispose (TYPES)

[ancientwizard]

I'm sure you'll want to do this differently but for the moment this handles the error; Now we just need to find the cause of the Oracle complaint.

method _free() {
    with $!stmth {
        my $errcode = .HandleFree;
        given $errcode
        {
            when OCI_SUCCESS { ... }
            when OCI_SUCCESS_WITH_INFO { ... }
            # OCI_INVALID_HANDLE (etc);
            default {
              self!handle-err(OCIErr.new( Str => 'Invalid Handle', Numeric => $errcode ))
            } 
        }
        $_ = Nil;
    }
}

UPDATE: I found that OCI_INVALID_HANDLE is -2 but the code is -1 a plain OCI_ERROR so it's not very helpful

[ancientwizard]

I've written a small C program (more like repurposed Oracle OCI cdemo81.c) in an attempt to discover how the DBIish Oracle driver is failing to free handles. So far I have been unable to reproduce the same issue; other than those I have deliberately caused.

Outcomes

• When the "C" code repeatedly creates a statement handle; prepares, defines, executes and fetches data without using OCIHandleFree() it will cause the oracle cursor error; completely expected
• When the "C" code frees the handle after each use it will run without error; forever
• When a handle is free'd more than once your receive OCI_INVALID_HANDLE
• When a NULL is passed as a handle you receive OCI_INVALID_HANDLE
• I've been unable to get OCIHandleFree() to return OCI_ERROR using "C"

I also refactored the Raku call to OCIHandleFree as a sub rather than a method; same results as a method; had to try it.

[jonathanstowe]

Hi, Thanks for this.

Any chance that you could create a pull request with your changes, preferably with some test? I'm not sure how many of the people who may see this are regularly testing with Oracle (I know I'm not.)

[ancientwizard]

I think I just discovered how to fix the bug. I'll know for sure in the next 15 min or less. I'm not likely to send a pull request; they haven't been welcome in the past. Confirmed I have a fix for the bug.

[ancientwizard]

Reminder: A fix for all the Oracle issues I've run into are available on my fork.