Weak Ref Cursor Types: Do I ever need to declare my own? No!

This question just came in via Twitter DM, so I thought it could do with a blog answer.

Ref cursor types are the datatypes from which we declare cursor variables. A cursor variable is, well, just that: a variable pointing back to a cursor/result set.

Some really nice aspects of cursor variables:

• you can associate a query with a cursor variable at runtime (useful with both static and dynamic SQL); 
• you can pass the cursor variable as a parameter or function RETURN value. Specifically: you can pass a cursor variable back to a host language like Java for consumption.

Check out the cursor variable documentation. 

Explore this LiveSQL script on cursors, including multiple examples of cursor variables.

OK, to get to it, then:

Before you can declare a cursor variable, you need to have a ref cursor type defined. There are two, ahem, types of types: strong and weak. 

With a strong type, you include a RETURN clause that specifies the number and datatypes of expressions returned by the query associated with the cursor variable. 

With a weak type, there is no RETURN. You can associate a variable based on a weak type with any SELECT statement.

PL/SQL offers a pre-defined weak ref cursor type: SYS_REFCURSOR. And (now we are getting to the answer to the question in the title of this post) that's the only weak ref cursor type you'll ever need. 

Here's a package specification that shows both usages:

CREATE OR REPLACE PACKAGE refcursor_pkg 
IS 
   /* Use this "strong" REF CURSOR to declare cursor variables whose 
      queries return data from the endangered_species table. */ 
 
   TYPE endangered_species_t IS REF CURSOR 
      RETURN endangered_species%ROWTYPE; 
 
   FUNCTION filtered_species_cv (filter_in IN VARCHAR2) 
      RETURN endangered_species_t; 

   /* User-defined weak REF CURSOR type . BUT YOU SHOULD NOT DO THIS! */
 
   TYPE weak_t IS REF CURSOR;
  
   FUNCTION data_from_any_query_cv (query_in IN VARCHAR2) 
      RETURN weak_t; 
 
   /* INSTEAD, just use SYS_REFCURSOR! */ 

   FUNCTION data_from_any_query_cv2 (query_in IN VARCHAR2) 
      RETURN SYS_REFCURSOR; 
END refcursor_pkg;

Remember: every line of code you write needs to be tested and maintained. Every bit of code you manage to avoid writing gives you more time to test and maintain - and enhance - the code you have to write.

So don't declare you own weak REF CURSOR types!