Home » SQL & PL/SQL » SQL & PL/SQL » Do we need to close Ref Cursors (Oracle 10g)
Do we need to close Ref Cursors [message #408179] Mon, 15 June 2009 01:50 Go to next message
sonikumari
Messages: 74
Registered: May 2006
Location: Mumbai
Member

Hi,
I am new to using ref cursors...I have written a procedure and below code is just to ask my question. The procedure is only a sample.

Do we need to close the ref cursors. I have opened the cursor using :-
OPEN cur_ref_type FOR v_query;
But I have not closed it . Do we need to close the ref cursors like other explicit cursors or do they release the memory and close themselves.

I might sound very funny but please tell me.

CREATE OR REPLACE PROCEDURE TEST(p_val in number, p_num in number)

IS
TYPE cur_ref IS REF CURSOR;
cur_ref_type cur_ref;
cn_val number;
cur_rec_test AMS_IA_DATA%ROWTYPE;

v_query varchar2(500);

begin

v_query := 'SELECT  *  FROM AMS_IA_DATA '
          || 'WHERE slno = '||p_val;

OPEN cur_ref_type FOR v_query;
      LOOP
         BEGIN
            FETCH cur_ref_type
             INTO cur_rec_test;
            EXIT WHEN cur_ref_type%NOTFOUND;
                 for cnt_val in 1..p_num
                 loop
                 -- do something
                 END LOOP;
                 
         end;
     end loop;
     commit;
end;


Regards,
Soni
Re: Do we need to close Ref Cursors [message #408229 is a reply to message #408179] Mon, 15 June 2009 07:02 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
It's not vital, as this example shows, but it is definitely a good habit to get into.
Closing a cursor explicitly marks when the code is finished with it, and makes it clear that it is not needed after that point.
DECLARE
TYPE cur_ref IS REF CURSOR;
cur_ref_type    cur_ref;

v_val           pls_integer;
v_query         varchar2(500);
BEGIN

v_query := 'SELECT level FROM dual connect by level <=10';

OPEN cur_ref_type FOR v_query;
LOOP
  FETCH cur_ref_type INTO v_val;
  EXIT WHEN cur_ref_type%NOTFOUND;
  dbms_output.put_line(cur_ref_type%rowcount);
end loop;

OPEN cur_ref_type FOR v_query;
LOOP
  FETCH cur_ref_type INTO v_val;
  EXIT WHEN cur_ref_type%NOTFOUND;
  dbms_output.put_line(cur_ref_type%rowcount);
end loop;
end;
/
Re: Do we need to close Ref Cursors [message #408261 is a reply to message #408179] Mon, 15 June 2009 09:12 Go to previous messageGo to next message
l0b0
Messages: 13
Registered: November 2006
Junior Member
Depending on how it's used, you could end up with a "ORA-01000: Maximum open cursors exceeded" error. More about ORA-01000
Re: Do we need to close Ref Cursors [message #408557 is a reply to message #408261] Tue, 16 June 2009 18:10 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
There are some rules of thumb here that you should keep in mind:

1) as was stated perviously by JRowbottom, since you explicitely opened the cursor, you should be explicitely closing it, unless it is your intention to pass a refcursor back as a function return item our procedure parameter. This is common in the JAVA/Oracle world where refcursors are easy ways to pass data back to a calling program.

2) in general Oracle will automatically "deinstantiate" objects when they "GO OUT OF SCOPE". So, when the code that opened the cursor exits, Oracle will close the cursor for you if you forget to do it. This rule applies to several kinds of objects when Oracle is executing your code. For example, LOBs follow the rule. However some people get confused about scoping rules. What happens when you pass your cursor (or lob) back to a caller as a variable? What happens when you make copies of one of these objects? So in the end, if you create a cursor, you should have code the closes it.

3) while we are on the topic, if you do pass objects like refcursors and lobs back to callers as parameters, the issue does not go away. It simply moves out of your control and becomes the responsibility of the caller to deinstantiate the object in question. In the past this has caused problems for Oracle. For example, it is common practice in some places to pass temporary CLOBS around between java and oracle. However, until recently, there was no way for java to release the temporary clob when done. As a result memory would be consumed by the temp clobs until a session ended. With products like weglogic, sessions never end. So be careful to make sure you client code knows to "end" what ever it gets with respect to cursors and clobs. Otherwise, as l0b0 said, you have a nasty error of some sort headed your way.

Good luck, Kevin

[Updated on: Tue, 16 June 2009 18:17]

Report message to a moderator

Re: Do we need to close Ref Cursors [message #420629 is a reply to message #408557] Tue, 01 September 2009 01:18 Go to previous messageGo to next message
pashish80
Messages: 4
Registered: May 2008
Location: Mumbai
Junior Member
HI ,
Prior to #3 , I m facing a problem in which temp clob is being used in java server program which is using Glassfish app. server 9.2 As a result it seems memory is not getting free by java.
Subsequently we are getting ora-1652(temp tablespace full) error after some in oracle 10g.
Please suggest is there any way to close/detroy/free clob objects in java.
Thanks
Re: Do we need to close Ref Cursors [message #420681 is a reply to message #420629] Tue, 01 September 2009 08:19 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
To my knowledge (admittedly it is often limited), there is only one way to free a temporary clob which has been passed to your external application.

Your app must call a some kind of "release clob" method on the clob. For java, only the more recent versions of java offer this call. It was sadly lacking in early releases of java. Someone said to me once that this was by design originally bacause java was never meant to be a language that dealt with large pieces of data. I have no clue if this is true, but if so, with the proliferation of XML that design ideal went out the window.

Do some research on Google. You can find out which java release has the release call(s) in it. I am pretty sure the clob has a method. Additionally I am pretty sure there is a general procedure to which your clob can be passed and it will be released as well (I am doing this from memory).

So here is the problem: this means you have to change code in order to use temporary clobs. For home grown clobs, this is a pain in the butt. For vendor supplied apps this is almost impossible because you have to get the vendor to change their code. Otherwise, you can't use temporary clobs.

This clob issue reared its ugly head when Oracle8i came out (or maybe it was Oracle7). One of these releases saw temp clobs introduced but they only worked well when used inside plsql because plsql closes temporary clobs for you when they go "out-of-scope". But when people started passing temp clobs to java, there was no plsql "out-of-scope" anymore so temporary clobs which usually consume only memory would cause memory would fill up so oracle would start writing them out to TEMP TABLESPACE and that would eventually fill up giving you the error you are getting.

To deal with this Oracle has a workaround.

use regular clobs. This solution works, but which has its own set of issues, mainly it requires a specific coding sequence which is not very performant and which requires you to split your transaction up unless you do an autonomous transaction which were not available when this problem first showed up on the Oracle RADAR.

1) write out a row to a temp with an empty clob.
2) update the row to fill the clob.
3) do a commit
4) read the row back into your clob variable
5) do what ever you want

You should consider using an autonomous transaction pragma inside a plsql function to do the above. This way you can at least avoid the splitting of transactions with a commit, and have one routine that implements the workaround.

This sequence means the clob is actually physically instantiated on a row in a table (and thus is not temporary).

It also means you have exchanged the need to release a temporary clob when you are done with it, for the need to delete a physical clob when you are done with it (by deleting the row).

This in turn means you need identifying information on the clob row (some kind of key and/or date), so you can find it again later when it comes time to delete it. Most people just write a plsql procedure to delete clobs older than X hours or X days (they put a date on the clob row) and use DBMS_JOB to run it periodically. After all the clob was only supposed to be avaliable for a short period of time right (it was origianlly temporary). Thus it should be ?safe? to delete the clob row after some predefined period of time even though we do not actually know that the transaction that created it no longer needs it.

As you can see, clobs are kind of messy (it is their nature). You must choose to either use real clobs which perform badly in situations where you do not actually want to persisit the clob data (e.g. your intent was only to build a clob dynamically in plsql and pass it to the outside world but never actually store the clob data permanently). Or you must make sure your code executes a "release temporary clob" call. If you are writing your own code on an update-to-date java installation, this is no big deal but if like most places, you have an older java release then the call does not exist for you, or if you have lots of old java code, then you have to change you code, or if you have a vendor program you have to get the vendor to change their code (in which case what release of java are they on and will they do it for you).

Good luck, Kevin
Re: Do we need to close Ref Cursors [message #420699 is a reply to message #420681] Tue, 01 September 2009 11:29 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
One idea that's worth exploring is to assign an empty clob to you java clob once you are done with it. See this 2002 (possibly outdated) pl/sql topic.

http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:5010126814159#6248024533115

Re: Do we need to close Ref Cursors [message #420700 is a reply to message #420699] Tue, 01 September 2009 11:41 Go to previous message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
pashish80

if you give this a try and it works, let me know please. I have a use for it.

Kevin
Previous Topic: grouping , based on similarity in percentage (merged 2) 8i-9
Next Topic: bulk update is not working..
Goto Forum:
  


Current Time: Fri Dec 09 09:28:56 CST 2016

Total time taken to generate the page: 0.11182 seconds