Home » SQL & PL/SQL » SQL & PL/SQL » Ref Cursor Problem ... Urgent
Ref Cursor Problem ... Urgent [message #193006] Thu, 14 September 2006 09:32 Go to next message
Messages: 162
Registered: February 2006
Senior Member
Hai Frns,

I have a one problem. we are using Ref cursors for our application. But we are facing some problems while executing the stored procedures from front-end. we r getting problems like

SQL * statement failed due to recursive call.

I think the ref cursors have benn opened but not closed properly. now my qry is that how to find out all the opened cursors in the database currently and how to close them. But the same application is being run nicely in local environment but when we r trying deploy the application on client environment and trying to execute we r facing problems.

Can anyone plz help me out.Its urgent.

Thanks in advance,

Re: Ref Cursor Problem ... Urgent [message #193011 is a reply to message #193006] Thu, 14 September 2006 09:40 Go to previous messageGo to next message
Messages: 189
Registered: May 2006
Location: NJ,USA
Senior Member

here is the query to find all the active cursor...

SELECT sql_text, address, hash_value ,a.sid,a.user_name,machine,terminal,program,b.type,logon_time
FROM v$open_cursor a , v$session b
WHERE a.sid = b.sid
AND a.address = b.sql_address
AND a.hash_value = b.sql_hash_value
AND b.status = 'ACTIVE'

using cursors is to bear in mind that Oracle leaves cursors open by default
The CLOSE_CACHED_OPEN_CURSORS initialization parameter:

->The default value is FALSE, so cursors are left open by default.
->Setting this parameter to TRUE will result in the automatic closing of cursors upon COMMIT or ROLLBACK.
->Setting this parameter to TRUE will also release memory that is used by the cursor.
->Recommendation: Experiment with each value (FALSE and TRUE) to determine the best course for your application environment.

To implement these changes: (In the INIT.ora file for the Oracle database in question )

1)The DBA should edit the INIT.ora file using Notepad in NT or vi in Unix.
As a precaution, recommend backing up this file before editing.
2)Search the text of this file for the word "CURSOR."
3)If this parameter is not in the file, then the default of 50 is in effect.
4)To raise the value, type the following line (at or near the end of the file): OPEN_CURSORS = 1500
5)To implement the automatic close of cached cursors, type the following line (directly below the above parameter): CLOSE_CACHED_OPEN_CURSORS = TRUE
6)Save and close the INIT.ora file.
7)Stop and restart the database.
Since this parameter is STATIC, the database instance must be stopped and restarted for the change to take effect.

Hope this will help you. As killing the cursor from backend is not a proper way.


[Updated on: Thu, 14 September 2006 10:06]

Report message to a moderator

Re: Ref Cursor Problem ... Urgent [message #193029 is a reply to message #193011] Thu, 14 September 2006 10:33 Go to previous message
Messages: 162
Registered: February 2006
Senior Member
Hi frns,

I am getting the following errorcodes

ora-00604 and ORA-01003

Thanks in advance.
Previous Topic: Export Data From Oracle to MS-Excel
Next Topic: Reference a variable
Goto Forum:

Current Time: Sun Oct 23 22:36:47 CDT 2016

Total time taken to generate the page: 0.06593 seconds