CURSOR c_replacement_tag_range IS
        SELECT start_tag, end_tag
        FROM replacement_tag_range;

OPEN c_replacement_tag_range;

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

Cursor variables are ref cursors, aren't they?

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.

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.

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

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.

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.

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?

as you'd have to do if you used cursor variables.

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.

I want a procedure that closes the one cursor that is passed to that procedure!

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.

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;

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);
 

...or a bunch of "if then elsif" (or a "case").

You can only close a packaged cursor within the code in which they were opened.

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

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

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.

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.

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;

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?

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:

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.

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;

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.

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.

... 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" ...

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.

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;

its purpose is to perform a particular algorithm

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.

Quote:

its purpose is to perform a particular algorithm

No! Its purpose is just to open the cursor.

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

 -- 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;

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

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.

What's invalid about this?

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