Home » SQL & PL/SQL » SQL & PL/SQL » Creating a procedure to close cursors
Creating a procedure to close cursors [message #429976] Fri, 06 November 2009 11:59 Go to next message
Faith007
Messages: 23
Registered: November 2009
Junior Member
I'm wondering if there is a way to create a procedure that closes a cursor name that is passed to it.
I tried the below code, but it does not work because you can not use a passed cursor name in a cursor attribute statement.
Thank You.

PROCEDURE Close_Cursor
(p_cursor_name IN VARCHAR2)
AS

BEGIN
IF p_cursor_name%ISOPEN THEN
CLOSE p_cursor_name;
END IF;
END;
Re: Creating a procedure to close cursors [message #429979 is a reply to message #429976] Fri, 06 November 2009 12:02 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
You do not pass a cursor name you pass a reference to a cursor.
So your procedure should be, for instance:
PROCEDURE Close_Cursor (p_cursor_name IN SYS_REFCURSOR)
AS
BEGIN
  IF p_cursor_name%ISOPEN THEN
    CLOSE p_cursor_name;
  END IF;
END; 

Please note how I formatted the code and do it the same way.
For this, read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code, use code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Regards
Michel

[Updated on: Fri, 06 November 2009 12:03]

Report message to a moderator

Re: Creating a procedure to close cursors [message #430008 is a reply to message #429976] Fri, 06 November 2009 15:33 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
I do not understand. Cursors do not have names.

Please show us how you created and named the cursor you want to close.

Kevin
Re: Creating a procedure to close cursors [message #430012 is a reply to message #430008] Fri, 06 November 2009 16:20 Go to previous messageGo to next message
Faith007
Messages: 23
Registered: November 2009
Junior Member
  
CURSOR c_replacement_tag_range IS
        SELECT start_tag, end_tag
        FROM replacement_tag_range;

OPEN c_replacement_tag_range;

[Updated on: Fri, 06 November 2009 16:22]

Report message to a moderator

Re: Creating a procedure to close cursors [message #430017 is a reply to message #429976] Fri, 06 November 2009 18:08 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Thankyou. So, that answers your question.

You are creating a cursor variable, and you can indeed pass that into another procedure to close it. In fact the example provided by Michel does it for you.

Good luck, Kevin
Re: Creating a procedure to close cursors [message #430039 is a reply to message #430012] Sat, 07 November 2009 04:48 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Faith007 wrote on Fri, 06 November 2009 23:20
  
CURSOR c_replacement_tag_range IS
        SELECT start_tag, end_tag
        FROM replacement_tag_range;

OPEN c_replacement_tag_range;


If you use this code-template, you should close the cursor in the same code-block where you open it.
Closing cursors from other places only make sense for ref-cursors. (Which is in line with what Michel already told you: you can only use references to cursors)
Re: Creating a procedure to close cursors [message #430051 is a reply to message #429976] Sat, 07 November 2009 10:52 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Thanks, I missed that.

Kevin
Re: Creating a procedure to close cursors [message #430214 is a reply to message #430039] Mon, 09 November 2009 09:02 Go to previous messageGo to next message
Faith007
Messages: 23
Registered: November 2009
Junior Member
Frank wrote on Sat, 07 November 2009 04:48

Closing cursors from other places only make sense for ref-cursors.


I don't agree there. It makes sense for packaged cursors as well. In fact, this is the method that Feuerstein teaches in his "Oracle PL/SQL Programming", 4th edition. I wanted to take the technique one step further and write a general purpose "close cursor" procedure, but it looks like that can't be done without using cursor variables. But using cursor variables would defeat the very purpose of using packaged cursors, which is to have a single cursor available to every subprogram that may need it. If I used a cursor variable, it would require me to write the query in every subprogram that needed to open the cursor, rather than having it in one location.



Re: Creating a procedure to close cursors [message #430223 is a reply to message #430214] Mon, 09 November 2009 10:28 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Cursor variables are ref cursors, aren't they?

Regards
Michel
Re: Creating a procedure to close cursors [message #430226 is a reply to message #429976] Mon, 09 November 2009 10:33 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes, REFCURSOR = CURSOR VARIABLE.

Though I suppose there may be an academic difference (row vs. tuple / table vs. entity / refcursor vs. cursor variable), these are in the common tounge two names for the same thing.

Kevin
Re: Creating a procedure to close cursors [message #430227 is a reply to message #430223] Mon, 09 November 2009 10:34 Go to previous messageGo to next message
Faith007
Messages: 23
Registered: November 2009
Junior Member
Michel Cadot wrote on Mon, 09 November 2009 10:28
Cursor variables are ref cursors, aren't they?


Yes, but they're not packaged cursors.
Re: Creating a procedure to close cursors [message #430228 is a reply to message #429976] Mon, 09 November 2009 10:45 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Yes that is true too.

If you are asking: can you close the packaged cursor you gave as an example, from somewhere other than the piece of code in which you opened it, the answer is no. This is a question of scoping rules. If you wish to close this from somewhere else, you must use a refcursor (eg. cursor variable).

In order for you to close the cursor somewhere else, you need to be able to pass the cursor to that place. How would you do that for a packaged cursor as you have shown? To pass anything around you need a variable. Thus you need a variable for your cursor (aka. a cursor variable). That means your cursor must be created as a refcursor so that its scope can be global.

Good luck, Kevin
Re: Creating a procedure to close cursors [message #430229 is a reply to message #430227] Mon, 09 November 2009 10:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Faith007 wrote on Mon, 09 November 2009 17:34
Michel Cadot wrote on Mon, 09 November 2009 10:28
Cursor variables are ref cursors, aren't they?


Yes, but they're not packaged cursors.

But you do not pass package cursor as parameter unless you make a ref cursor on it.

Regards
Michel
Re: Creating a procedure to close cursors [message #430231 is a reply to message #430228] Mon, 09 November 2009 11:06 Go to previous messageGo to next message
Faith007
Messages: 23
Registered: November 2009
Junior Member
Kevin Meade wrote on Mon, 09 November 2009 10:45

If you are asking: can you close the packaged cursor you gave as an example, from somewhere other than the piece of code in which you opened it, the answer is no. This is a question of scoping rules. If you wish to close this from somewhere else, you must use a refcursor (eg. cursor variable).


On the contrary Kevin, the answer is Yes.
It is indeed a question of scoping rules, and the scoping rules permit it. A packaged cursor is visible to all subprograms in the package. You do NOT need a cursor variable to close it.



Kevin Meade wrote on Mon, 09 November 2009 10:45

In order for you to close the cursor somewhere else, you need to be able to pass the cursor to that place.


No you don't. By definition, the scope of a packaged cursor is the entire package. That's what makes you able to open the cursor in the subprogram in the first place, even though it's not declared in the subprogram.


[Updated on: Mon, 09 November 2009 11:16] by Moderator

Report message to a moderator

Re: Creating a procedure to close cursors [message #430232 is a reply to message #430229] Mon, 09 November 2009 11:12 Go to previous messageGo to next message
Faith007
Messages: 23
Registered: November 2009
Junior Member
Michel Cadot wrote on Mon, 09 November 2009 10:45

But you do not pass package cursor as parameter unless you make a ref cursor on it.


I know that Michel. But as I stated previously, using a ref cursor would defeat the purpose of using the packaged cursor in the first place. I don't want to hard-code the cursor query into every subprogram that needs to use the cursor -- that's precisely why I'm using a packaged cursor.
Conclusion:
I can't use cursor variables.
I can't make the generic "close cursor" procedure that I wanted.
It'll have to be closed right in the subprogram that opened it.
Thanks for your responses.


[Updated on: Mon, 09 November 2009 11:13]

Report message to a moderator

Re: Creating a procedure to close cursors [message #430233 is a reply to message #430232] Mon, 09 November 2009 11:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
using a ref cursor would defeat the purpose of using the packaged cursor in the first place

Which is?

Quote:
I don't want to hard-code the cursor query into every subprogram that needs to use the cursor -- that's precisely why I'm using a packaged cursor.

Sorry but how do you declare a packaged cursor (not a ref cursor one) without a hard-coded query?

Regards
Michel
Re: Creating a procedure to close cursors [message #430238 is a reply to message #430214] Mon, 09 November 2009 12:53 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
Faith007 wrote on Mon, 09 November 2009 16:02
Frank wrote on Sat, 07 November 2009 04:48

Closing cursors from other places only make sense for ref-cursors.


I don't agree there. It makes sense for packaged cursors as well. In fact, this is the method that Feuerstein teaches in his "Oracle PL/SQL Programming", 4th edition. I wanted to take the technique one step further and write a general purpose "close cursor" procedure, but it looks like that can't be done without using cursor variables. But using cursor variables would defeat the very purpose of using packaged cursors, which is to have a single cursor available to every subprogram that may need it. If I used a cursor variable, it would require me to write the query in every subprogram that needed to open the cursor, rather than having it in one location.




This only goes for globally defined cursors; that is cursors that are not defined in any of the packaged procedures.
Since the number of global cursors typically is low, this means that having a general procedure to close them all sounds like overkill.
Also, in my humble opinion, you should know when to close which cursor, instead of trying to close a bunch that might be open.
Re: Creating a procedure to close cursors [message #430244 is a reply to message #430233] Mon, 09 November 2009 14:29 Go to previous messageGo to next message
Faith007
Messages: 23
Registered: November 2009
Junior Member
Michel Cadot wrote on Mon, 09 November 2009 11:20
Quote:
using a ref cursor would defeat the purpose of using the packaged cursor in the first place

Which is?

As I already stated in a prior post:
"But using cursor variables would defeat the very purpose of using packaged cursors, which is to have a single cursor available to every subprogram that may need it."


Quote:
I don't want to hard-code the cursor query into every subprogram that needs to use the cursor -- that's precisely why I'm using a packaged cursor.

Sorry but how do you declare a packaged cursor (not a ref cursor one) without a hard-coded query?


You don't! You hard code it ONCE, not in "every subprogram that needs to use the cursor", as you'd have to do if you used cursor variables.
Re: Creating a procedure to close cursors [message #430246 is a reply to message #430244] Mon, 09 November 2009 14:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
as you'd have to do if you used cursor variables.

No more that with packaged cursor if you don't want to.

You should post an example of what you think you can do with a package cursor and can't with a ref cursor.

Regards
Michel
Re: Creating a procedure to close cursors [message #430247 is a reply to message #430238] Mon, 09 November 2009 14:38 Go to previous messageGo to next message
Faith007
Messages: 23
Registered: November 2009
Junior Member
Frank wrote on Mon, 09 November 2009 12:53

This only goes for globally defined cursors; that is cursors that are not defined in any of the packaged procedures.


Right Frank. That's what "packaged cursor" means!


Frank wrote on Mon, 09 November 2009 12:53

Since the number of global cursors typically is low, this means that having a general procedure to close them all sounds like overkill.
Also, in my humble opinion, you should know when to close which cursor, instead of trying to close a bunch that might be open.


I don't want a general procedure to close ALL of them at once. Look at my first post again. I want a procedure that closes the one cursor that is passed to that procedure!


Re: Creating a procedure to close cursors [message #430248 is a reply to message #430247] Mon, 09 November 2009 14:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
I want a procedure that closes the one cursor that is passed to that procedure!

And the only way, as already been said, is to use ref cursor.
Back to the beginning.

Regards
Michel
Re: Creating a procedure to close cursors [message #430249 is a reply to message #430248] Mon, 09 November 2009 14:42 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
...or a bunch of "if then elsif" (or a "case").

Regards
Michel

[Updated on: Mon, 09 November 2009 14:43]

Report message to a moderator

Re: Creating a procedure to close cursors [message #430252 is a reply to message #430246] Mon, 09 November 2009 14:52 Go to previous messageGo to next message
Faith007
Messages: 23
Registered: November 2009
Junior Member
Michel Cadot wrote on Mon, 09 November 2009 14:38
Quote:
as you'd have to do if you used cursor variables.

No more that with packaged cursor if you don't want to.


Yes, it IS more than you need to do with packaged cursors.

Michel Cadot wrote on Mon, 09 November 2009 14:38

You should post an example of what you think you can do with a package cursor and can't with a ref cursor.


With a packaged cursor, I declare my cursor ONCE in the package body:

create or replace PACKAGE BODY  "GNT" AS
 
  CURSOR c_replacement_tags IS
        SELECT tag
        FROM replacement_tags;
  
  -- And I can open it in this function.  
  FUNCTION  Find_Tag_Repetition 
  RETURN NUMBER
  AS
  
  -- Declare variables here.

  BEGIN
      OPEN c_replacement_tag_range;
      -- Rest of the function here.  
     
  END Find_Tag_Repetition;

END GNT;


Now I can open the same cursor in any other functions or procedures in this package without declaring it within them.

Whereas, if I wanted to pass a cursor variable from one of these functions to my proposed "Close Cursor" procedure, I'd have to create the cursor variable within each subprogram, which requires me to code the cursor's Query in each subprogram. E.g.

DECLARE
 cursor_type   SYS_REFCURSOR;

 v_tag_cursor  cursor_type;
 OPEN v_tag_cursor FOR SELECT tag FROM replacement_tags;
 
 -- Code to process the cursor results here.
 
 -- Close the cursor.
    Close_cursor (v_tag_cursor);
 


As you can see, I no longer have the ability to reduce code redundancy by placing the cursor query in one place ONLY.

[Updated on: Mon, 09 November 2009 15:05]

Report message to a moderator

Re: Creating a procedure to close cursors [message #430253 is a reply to message #429976] Mon, 09 November 2009 15:06 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Also to vindicate myself. I noted that packaged cursors are constrained by scoping rules. We all have actually agreed with this. You can only close a packaged cursor within the code in which they were opened.

With that in mind, a bit more clarity of what this mean is in order.

A packaged cursor can only be closed within the scope from which it was defined. This is an absolute. We know this to be so because when the cursor goes out of scope, the cursor will be closed by oracle for you if you failed to close it yourself.

The issue with global packaged cursors is that they are session scoped which essentially means they do not go out of scope until the session ends. This fact has lead many of us to the familiar TOO MANY OPEN CURSORS exception. Because most of us use third party connection pool managers, sessions never really end. This in turn means that cursors not closed remain open until the connection pool is recycled or the database is bounced and thus the opportunity for the error. It is really a question of sloppy coding or unique situations arising from runtime errors.

So, here is some actual code that shows scoping in action.

SQL> create or replace package pktemp1 is
  2  
  3     cursor c1 is select * from dual;
  4  
  5     procedure openc1;
  6  
  7  end;
  8  /

Package created.

SQL> 
SQL> create or replace package body pktemp1 is
  2  
  3     procedure openc1 is begin open c1; end;
  4  
  5  end;
  6  /

Package body created.

SQL> 
SQL> create or replace package pktemp2 is
  2  
  3     procedure closec1;
  4  
  5  end;
  6  /

Package created.

SQL> 
SQL> create or replace package body pktemp2 is
  2  
  3     procedure closec1 is begin close pktemp1.c1; end;
  4  
  5  end;
  6  /

Package body created.

SQL> 
SQL> 
SQL> exec pktemp1.openc1;

PL/SQL procedure successfully completed.

SQL> 
SQL> exec pktemp2.closec1;

PL/SQL procedure successfully completed.


Notice that indeed the cursor opened in one package was closed in another. This only works because the cursor was created as a session global cursor which is done by coding the cursor in a package specification and then opening it somewhere.

To that end, if you really wanted it, you can use dynamic sql to create a generic session global cursor closer. Notice that I have included proof the the cursor was closed by trying to reopen it. It opens again but a second open raises the exception already open. From this we know the execute immediate did indeed close the cursor.

SQL> exec pktemp1.openc1;

PL/SQL procedure successfully completed.

SQL> declare
  2     cursorname_v varchar2(30) := 'pktemp1.c1';
  3  begin
  4     execute immediate 'begin close '||cursorname_v||'; end;';
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> exec pktemp1.openc1;

PL/SQL procedure successfully completed.

SQL> exec pktemp1.openc1;
BEGIN pktemp1.openc1; END;

*
ERROR at line 1:
ORA-06511: PL/SQL: cursor already open
ORA-06512: at "KM21378.PKTEMP1", line 3
ORA-06512: at "KM21378.PKTEMP1", line 3
ORA-06512: at line 1


Now I have to ask why? What is the need that causes you to chase this kind of solution?

Kevin
Re: Creating a procedure to close cursors [message #430254 is a reply to message #430249] Mon, 09 November 2009 15:11 Go to previous messageGo to next message
Faith007
Messages: 23
Registered: November 2009
Junior Member
Michel Cadot wrote on Mon, 09 November 2009 14:42
...or a bunch of "if then elsif" (or a "case").


Do you mean passing a number to my proposed procedure and then executing the appropriate CLOSE statement (with a hardcoded cursor name) based on that number?
Re: Creating a procedure to close cursors [message #430256 is a reply to message #430254] Mon, 09 November 2009 15:27 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
nope. Did not see your proposal.

I would never code either. I am not recommending either.

But forced into a choice I would not use a number and hardcoded names. I would make the calling code know the name of what it wants to close. Given that, I'd use an execute immediate as shown so that I would not have to revisit the code every time some developer got it in his/her head to do this.

In for a penny in for a pound. Kevin
Re: Creating a procedure to close cursors [message #430258 is a reply to message #430253] Mon, 09 November 2009 15:48 Go to previous messageGo to next message
Faith007
Messages: 23
Registered: November 2009
Junior Member
Kevin Meade wrote on Mon, 09 November 2009 15:06
You can only close a packaged cursor within the code in which they were opened.


Doesn't the example that you just gave below contradict this?
You opened a cursor with one package and closed it with another!

Kevin Meade wrote on Mon, 09 November 2009 15:06

... when the cursor goes out of scope, the cursor will be closed by oracle for you if you failed to close it yourself.


This behavior is undocumented and can NOT be relied upon.
See http://www.gennick.com/open_cursors.html
especially the link "PL/SQL Cursor Handling Explained."
This should clarify why I consider explicit cursor closing to be so important, especially in exception handlers.


Kevin Meade wrote on Mon, 09 November 2009 15:06

Now I have to ask why? What is the need that causes you to chase this kind of solution?


I think you essentially answered the question yourself when you wrote:
Kevin Meade wrote on Mon, 09 November 2009 15:06

The issue with global packaged cursors is that they are session scoped which essentially means they do not go out of scope until the session ends. ... This in turn means that cursors not closed remain open until the connection pool is recycled or the database is bounced and thus the opportunity for the error. It is really a question of sloppy coding or unique situations arising from runtime errors.


I have to make certain that all of my packaged cursors are closed before I exit any subprogram that opens them, since going out of scope will NOT close them. It would be "sloppy coding" not to explicitly close them. Thus, I wanted to develop a quick and easy way to do this, rather than to hardcode in multiple places in my subprograms (including all Exception Handlers) the code for checking if the cursor is open, and, if so, closing it. That's why I thought of a generic cursor closer that could simply be called.
Your Dynamic SQL idea might just do the trick. I could put that within my Cursor closer and pass the cursor name to it. I'll try it.

Re: Creating a procedure to close cursors [message #430264 is a reply to message #429976] Mon, 09 November 2009 17:31 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
Excellent, it will work for you fine.

As to scope, you are mis-interepting what scope means. The example provided shows that the scope of your cursor is global session scope. Thus for the cursor to go out of scope, the session must end. The scope is the session. Perhaps this is picky but it is none-the-less true, a cursor will be closed when it goes out of scope (documented or not this is the current behavior). Consider this:

SQL> create or replace package pktemp1 is
  2  
  3     cursor c1 is select * from dual;
  4  
  5     procedure openc1;
  6  
  7  end;
  8  /

Package created.

SQL> 
SQL> 
SQL> create or replace package body pktemp1 is
  2  
  3     procedure openc1 is begin open c1; end;
  4  
  5  end;
  6  /

Package body created.

SQL> 
SQL> 
SQL> create or replace package pktemp2 is
  2  
  3     procedure closec1;
  4  
  5  end;
  6  /

Package created.

SQL> 
SQL> 
SQL> create or replace package body pktemp2 is
  2  
  3     procedure closec1 is begin close pktemp1.c1; end;
  4  
  5  end;
  6  /

Package body created.

SQL> 
SQL> 
SQL> exec pktemp1.openc1;

PL/SQL procedure successfully completed.

SQL> 
SQL> connect ...
Connected.
SQL> 
SQL> exec pktemp2.closec1;
BEGIN pktemp2.closec1; END;

*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at "KM21378.PKTEMP2", line 3
ORA-06512: at line 1


Know that reconnecting in SQLPLUS, ends the current session and starts a new one. Notice further that the cursor opened in the first session is no longer valid. Was the cursor actually closed? Sooner or later it will be, otherwise there is a memory leak and Oracle would need to fix it. It behaves closed and I can open cursor again now if I care to which I would not be able to do if it was not closed.

The scope of the cursor was session scope. Thus you can close it anywhere within the session. This means that opening the cursor in one package and closing it in another is in not contradictory but in fact demonstrates the meaning of "a cursor can only be closed within the scope in which it was openend". People generally tend to equate code compoents with scope and for the most part though this is technically incorrect, it is practically descriptive. But when dealing with session global objects, it shows the flaw in that thinking.

As for objects going out of scope, I never stated that it was good practice not to close cursor, only that the behavior of plsql for cursors, and indeed for all objects in general is that when an object goes out of scope it is "deinstantiated" and various coding examples will bear this out. You can build as many as you like to test the question.

I would never suggest that this behavior be reliead upon. Indeed, I believe firmly that it is the responsibility of those who explicitly create things, to explicitly end them, cursors included.

But it is good that you pointed this out.  Noting that this is not a "documented behavior" does have value.


I recommend everyone follow the link you provided, for two reasons: 1) it does provide some insight into the question of cursors closing when going out of scope, 2) but more importantly, it shows how the thought process of figuring things out can work. Inventing these kinds of tests are the stuff that good plsql and oracle people should be able to do. Indeed, it is one of the reasons why ORAFAQ exists; to promote better thinking processes.

Good luck with the dynamic sql. Kevin
Re: Creating a procedure to close cursors [message #430306 is a reply to message #430252] Tue, 10 November 2009 00:22 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
Whereas, if I wanted to pass a cursor variable from one of these functions to my proposed "Close Cursor" procedure, I'd have to create the cursor variable within each subprogram, which requires me to code the cursor's Query in each subprogram. E.g.

You can use the same thing with ref cursor (using your (invalid) code):
create or replace PACKAGE BODY  "GNT" AS
 
  c_replacement_tags sys_refcursor;  -- could be a constrained ref cursor type
  
  -- And I can open it in this function.  
  FUNCTION  Find_Tag_Repetition 
  RETURN NUMBER
  AS
  
  -- Declare variables here.

  BEGIN
      OPEN c_replacement_tags for 
        SELECT tag FROM replacement_tags;
      -- Rest of the function here.  
     
  END Find_Tag_Repetition;

END GNT;

The advantage of this method is that you can directly pass it to a generic close procedure.

Quote:
Do you mean passing a number to my proposed procedure and then executing the appropriate CLOSE statement (with a hardcoded cursor name) based on that number?

Yes, for instance.

Regards
Michel
Re: Creating a procedure to close cursors [message #430468 is a reply to message #430264] Tue, 10 November 2009 08:58 Go to previous messageGo to next message
Faith007
Messages: 23
Registered: November 2009
Junior Member
Kevin wrote:
Quote:
As to scope, you are mis-interepting what scope means. The example provided shows that the scope of your cursor is global session scope. Thus for the cursor to go out of scope, the session must end. The scope is the session. Perhaps this is picky but it is none-the-less true ...


No, I understood the point of your prior post completely. I understood that the scope of the cursor was global; therefore, you were able to close it through another package.

Kevin wrote:
Quote:
... a cursor will be closed when it goes out of scope (documented or not this is the current behavior). Consider this:


This is what I disagreed with because it is a generalization. The example you gave does indeed show a cursor going out of scope when the session ends, but that does not prove the generalized statement "a cursor will be closed when it goes out of scope. " As gennick.com states, there are specific instances where a locally declared cursor will NOT go out of scope when the local subprogram ends. Thus, since it is not consistent behavior, you can not make a general statement about cursors ALWAYS closing when they go out of scope. Furthermore, this lack of consistency means the best strategy is not to rely on it. Thus, when using packaged cursors, to make sure that a cursor is closed once the subprogram that opened it ends, you should close it both in the execution section and the exception handlers. My original post was simply an attempt to find a streamlined way of doing this by eliminating as much code redundancy as possible. Thanks for your post.

[Updated on: Tue, 10 November 2009 09:39]

Report message to a moderator

Re: Creating a procedure to close cursors [message #430475 is a reply to message #430306] Tue, 10 November 2009 09:56 Go to previous messageGo to next message
Faith007
Messages: 23
Registered: November 2009
Junior Member
Michel Cadot wrote on Tue, 10 November 2009 00:22

You can use the same thing with ref cursor (using your (invalid) code):
create or replace PACKAGE BODY  "GNT" AS
 
  c_replacement_tags sys_refcursor;  -- could be a constrained ref cursor type
  
  -- And I can open it in this function.  
  FUNCTION  Find_Tag_Repetition 
  RETURN NUMBER
  AS
  
  -- Declare variables here.

  BEGIN
      OPEN c_replacement_tags for 
        SELECT tag FROM replacement_tags;
      -- Rest of the function here.  
     
  END Find_Tag_Repetition;

END GNT;

The advantage of this method is that you can directly pass it to a generic close procedure.


But the disadvantage is that my cursor query is defined within the function rather than globally in the package. Thus, any other function that needs to use this cursor would also have to contain the same query. e.g.,

FUNCTION  Find_Tag_Row_Error 
  RETURN NUMBER
  AS
  
  -- Declare variables here.

  BEGIN
      OPEN c_replacement_tags for 
        SELECT tag FROM replacement_tags;
      -- Rest of the function here.  
     
  END Find_Tag_Row_Error;


... and the same for the other functions that need this cursor.
This redundancy of SQL for accessing the database is not good practice. The SQL query should appear in only one location in an application. That's the reason the cursor is packaged in the first place!
Thanks for your post.
Re: Creating a procedure to close cursors [message #430476 is a reply to message #430475] Tue, 10 November 2009 10:06 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Quote:
This redundancy of SQL for accessing the database is not good practice. The SQL query should appear in only one location in an application. That's the reason the cursor is packaged in the first place!


The obvious solution to this is to have a single function for each query that you use that will return a ref cursor opened for that query.

This retains all the advantages of using Michel's approach, while also allowing you a single point of definition for each cursor.
Re: Creating a procedure to close cursors [message #430479 is a reply to message #430476] Tue, 10 November 2009 10:14 Go to previous messageGo to next message
Faith007
Messages: 23
Registered: November 2009
Junior Member
JRowbottom wrote on Tue, 10 November 2009 10:06

The obvious solution to this is to have a single function for each query that you use that will return a ref cursor opened for that query.


Please write the code for what you mean so I can make sure I understand you correctly. Thanks.
Re: Creating a procedure to close cursors [message #430484 is a reply to message #430479] Tue, 10 November 2009 11:01 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Faith007 wrote on Tue, 10 November 2009 17:14
JRowbottom wrote on Tue, 10 November 2009 10:06

The obvious solution to this is to have a single function for each query that you use that will return a ref cursor opened for that query.


Please write the code for what you mean so I can make sure I understand you correctly. Thanks.

This was exactly what I meant, where do you see in my example that the query should be in many places? It is in a single one, the open function.
I repeat there is nothing you can do with "package cursor" you can't do with "ref cursor" but the opposite is wrong.

Regards
Michel

Re: Creating a procedure to close cursors [message #430491 is a reply to message #430484] Tue, 10 November 2009 12:49 Go to previous messageGo to next message
Faith007
Messages: 23
Registered: November 2009
Junior Member
Michel Cadot wrote on Tue, 10 November 2009 11:01
... where do you see in my example that the query should be in many places? It is in a single one, the open function.


In YOUR example, the query is open in one function. But, as I already stated, in MY APPLICATION, I want multiple functions to be able to access a single cursor, thus I want the cursor declaration to be made once at the package level.


Quote:
I repeat there is nothing you can do with "package cursor" you can't do with "ref cursor" ...


What about declaring the cursor once for the entire application so that it can be referenced from multiple subprograms, as I described above?

[Updated on: Tue, 10 November 2009 12:53]

Report message to a moderator

Re: Creating a procedure to close cursors [message #430499 is a reply to message #430491] Tue, 10 November 2009 13:37 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
In YOUR example, the query is open in one function. But, as I already stated, in MY APPLICATION, I want multiple functions to be able to access a single cursor, thus I want the cursor declaration to be made once at the package level.

Once again the "declaration" is ONLY ONCE IN THE OPEN FUNCTION, in any other place you use the ref cursor that is YOU CAN USE IT IN MULTIPLE SUBPROGRAMS.

Regards
Michel


Re: Creating a procedure to close cursors [message #430505 is a reply to message #430499] Tue, 10 November 2009 14:04 Go to previous messageGo to next message
Faith007
Messages: 23
Registered: November 2009
Junior Member
Michel Cadot wrote on Tue, 10 November 2009 13:37

Once again the "declaration" is ONLY ONCE IN THE OPEN FUNCTION, in any other place you use the ref cursor that is YOU CAN USE IT IN MULTIPLE SUBPROGRAMS.


Michel:
I did not know that you were talking about a separate OPEN function. In a prior post, in explaining your solution, you took my code and modified it. YOU wrote:
create or replace PACKAGE BODY  "GNT" AS
 
  c_replacement_tags sys_refcursor;  -- could be a constrained ref cursor type
  
  -- And I can open it in this function.  
  FUNCTION  Find_Tag_Repetition 
  RETURN NUMBER
  AS
  
  -- Declare variables here.

  BEGIN
      OPEN c_replacement_tags for 
        SELECT tag FROM replacement_tags;
      -- Rest of the function here.  
     
  END Find_Tag_Repetition;

END GNT;


Note that this is a "Find_Tag_Repetition" function. After it opens the cursor, its purpose is to perform a particular algorithm. That's why I wrote "Rest of the function here" after the OPEN statement.
If I understand you correctly, you are now saying that you meant this to be an OPEN cursor function. Is that correct?
If so, you should have named it "Open_Replacement_Tag_Cursor" to make your meaning clear.
Re: Creating a procedure to close cursors [message #430561 is a reply to message #430505] Wed, 11 November 2009 01:15 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
its purpose is to perform a particular algorithm

No! Its purpose is just to open the cursor.

Quote:
If I understand you correctly, you are now saying that you meant this to be an OPEN cursor function. Is that correct?

Correct!

Quote:
If so, you should have named it "Open_Replacement_Tag_Cursor" to make your meaning clear.

I surely use a more appropriate name in the real world but, as I said, I just copied your code (which was invalid so who care about the name, I didn't even notice what it was). Note that the comment on the function is "-- And I can open it in this function."
But I see now why you didn't understand my answers, sorry.

An advice: if you have or will have to maintain code in your professional life, start still now to not take care about names (or comments) but just code, as names (or comments) are mostly likely misleading or wrong after a couple of years of maintenance.

Regards
Michel


Re: Creating a procedure to close cursors [message #430845 is a reply to message #430561] Thu, 12 November 2009 08:54 Go to previous messageGo to next message
Faith007
Messages: 23
Registered: November 2009
Junior Member
Michel Cadot wrote on Wed, 11 November 2009 01:15
Quote:
its purpose is to perform a particular algorithm

No! Its purpose is just to open the cursor.


No Michel -- I sent you the code. The question was about MY APPLICATION! Therefore, one of the parameters of the question was that I was trying to execute a function called Find_Tag_Repetition. And that function performed, as its first step, an open of the cursor. If YOU wanted to change how that function worked, then you should have specifically said that you wanted to make a SEPARATE OPEN CURSOR function that is called by the Find_Tag_Repetition function. Simply taking my Find_Tag_Repetition function and writing your cursor variable code did not change the core functionality of the function, which was to find tag repetitions. You can't unilaterally decide to make MY algorithm disappear!

Michel Cadot wrote on Wed, 11 November 2009 01:15

... I just copied your code (which was invalid so who care about the name, I didn't even notice what it was).


What do you mean by "which was invalid"? There's nothing whatsoever invalid about my code. In fact, it is a simple stub designed to show how I wanted to use this function. Here it is exactly as I wrote it originally:
 -- And I can open it in this function.  
  FUNCTION  Find_Tag_Repetition 
  RETURN NUMBER
  AS
  
  -- Declare variables here.

  BEGIN
      OPEN c_replacement_tag_range;
      -- Rest of the function here.  
     
  END Find_Tag_Repetition;



What's invalid about this?


Michel Cadot wrote on Wed, 11 November 2009 01:15

Note that the comment on the function is "-- And I can open it in this function."


Yes, I opened the cursor in my function. So? What's your point?
Does that mean that the core functionality of my Find_Tag_Repetition function is "invalid"? Does that mean that you can make the algorithm that I intended for this function simply disappear?


Michel Cadot wrote on Wed, 11 November 2009 01:15

An advice: if you have or will have to maintain code in your professional life, start still now to not take care about names (or comments) but just code, as names (or comments) are mostly likely misleading or wrong after a couple of years of maintenance.


That's extremely POOR advice Michel!
Properly named subprograms are EXTREMELY IMPORTANT. Clear, detailed comments are EXTREMELY IMPORTANT. They should tell you exactly how the application is supposed to function.
If you ever find subprogram names or comments that are misleading, then they were written by bad programmers and should be immediately corrected!
Michel, I would strongly recommend that you read Feuerstein's "Oracle PL/SQL Programming, 4th Edition", which is an O'Reilly book. He describes the importance of proper naming conventions and commenting in considerable detail.
Thanks for your post.


[Updated on: Thu, 12 November 2009 09:11]

Report message to a moderator

Re: Creating a procedure to close cursors [message #430846 is a reply to message #430845] Thu, 12 November 2009 09:01 Go to previous messageGo to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
What's invalid about this?

It just does not compile.

Quote:
That's extremely POOR advice Michel!
Properly named subprograms are EXTREMELY IMPORTANT.

What does DBA_TAB_PRIVS give?
Important or not in the real life it is what it is, so you have to make with what it is and not with what should be in ideal life.

Regards
Michel

[Updated on: Thu, 12 November 2009 09:02]

Report message to a moderator

Previous Topic: Materialized view across network
Next Topic: exchange partition for moving data into history table
Goto Forum:
  


Current Time: Sun Dec 04 12:39:50 CST 2016

Total time taken to generate the page: 0.06637 seconds