Re: ORA-01000 Max. No. of Open Cursors Exceeded in Function

michael.dietz_at_gmx.de (Michael) wrote in message news:<e92fb27e.0212170128.34722a00_at_posting.google.com>...
> An interface of our application server is currently blocked after the
> error message "ORA-01000 Maximum number of open cursors exceeded"
> occurs. The only way to be able to send request is to shutdown and
> restart the application server.
>
> Together with the error message ORA-01000 we get an ORA-06512 error
> with reference to a PL/SQL function. It seems to me that the open
> cursor problem is caused by this function, which I inserted below:
>
> CREATE OR REPLACE FUNCTION "FCT_GET_ELEMENT_LONG_NAME"
> (assignedElement IN
> PD_PROD_HIER.ASSIGNED_ELEMENT_ID%TYPE,
> assignedArticle IN
> PD_PROD_HIER.ASSIGNED_ARTICLE_ID%TYPE)
> RETURN
> PD_HIER_ELEM.ELEMENT_LONG_NAME%TYPE
> IS
> elementLongName PD_HIER_ELEM.ELEMENT_LONG_NAME%TYPE;
> BEGIN
> IF assignedElement IS NOT NULL THEN
> SELECT ELEMENT_LONG_NAME
> INTO elementLongName
> FROM PD_HIER_ELEM
> WHERE UNIQUEID = assignedElement;
> ELSE
> SELECT ARTICLE_NAME
> INTO elementLongName
> FROM PD_ART_DATA
> WHERE UNIQUEID = assignedArticle;
> END IF;
>
> RETURN(elementLongName);
> END;
> /
>
>
> Does anybody have an idea why this function causes the error with the
> exceeding number of open cursors? Or is the problem somewhere else?
>
> Thanks
> Michael

The real problem, alas, most likely is your application design. First of all, *any* error in a pl/sql function will be accompanied by ora-6512.
So the error you should be looking at is ora-1000, not ora-6512. It simply states your open_cursors init.ora parameter is too low. The default is 150, which is clearly too low for most applications. Increase the parameter and bounce the database. However, it looks like, given the nature of your function, you are calling it everywhere, and you are calling it in other selects. That is one of the safest ways to get lousy performance. Apart from that you would be better of by replacing your 2 implicit cursors in the function above, by 2 explicit cursors in a package specification, which you just open and close. You will save on number of cursors and you will also save on soft parses, as the code in a package spec is only parsed *once*.

Hth

Sybrand Bakker
Senior Oracle DBA Received on Wed Dec 18 2002 - 09:51:05 CET