Exceed Cursor Allocation in Oracle

, ,
1

We are using Oracle and the JDBC Oracle driver and have custom-coded an error trapping service. We trap an error in a customer catalog file and then map the error to a “friendly” error that is relayed to our customer via a web GUI.

The problem is that we are working on the end of the year data dump and the files are huge.

The way currently process is by reading one line of the flat file at a time, processing, and then committing the transaction to the database.

The first time one of these monster files ran, webMethods “hung” and had to be restarted. After this, the file processed fine.

The second time a monster file came in (we’ve been getting in about one per day so far), we received an ORA-01000 error, exceeded allocated cursors.

There is no place that I can recall in the B2B Server that we as programmers can call a cursor. All we can do is use the DBAlias and use execSQL and webMethods takes care of these types of chores. Apparently, it is caching the use of the cursor because we are hitting this one error trapping service so much. Does anyone have any idea how we can expose or close the cursor ourselves?

As an overview, we StartTransaction, process, and then commitTransaction and then clear pipeline.

Any Ideas?

We are using B2B 3.6

Thanks,

Ray Moser rmoser@buildscape.com
Technical Project Manager
919-540-5600

2

I believe that this is a known bug; we have experienced the same behavior and received a patch for it.

Basically it appears that the webM code is not explicitly closing its connections and is relying on java garbage collection to destroy/free these resources. SO if you have a moderate number of transactions in a short amount of time, gc can’t keep up and you run out of cursors in Oracle.

Hope this helps.

3

In one of our java project we came across the same problem. We found that it was because we were not closing Statement objects after using them. It seems that services in WmDB package does not close statements expilicitely.

4

Oracle default open_cursors is usually too small for a production environment. Add the open_cursors statement to your oracle instances file and make the number above 500 ie open_cursors=500. Then restart your oracle instance. Also, make sure to specify the scope variable to “session” when using the remote invoke service.