You know everything is Tim Halls fault, right? Including this blog post? Oh yes it is, he came up with the idea of OTN ODC Appreciation Day - a day where Oracle Bloggers all over the world show their appreciation of the Oracle Developer Community by blogging about some favorite Oracle feature.
Cursor variables - also known as REF CURSORs.
I'll not show so much syntax in this post, but rather discuss why I like it and use it often.
A cursor variable is a variable that points to a cursor. You can pass the variable around in your code, so for example one place takes care of building the query and opening the cursor, and then another place simply consumes the cursor and fetches from it. It may sound like no big deal, but it is quite useful when separating business logic code from UX representation code.
My first encounter with cursor variables was in 2000 when I learned PL/SQL by building a webshop with business logic in PL/SQL and UX in VBScript/ASP. The VBScript code used OO4O (Oracle Objects for OLE) to communicate with the database, and it was very easy to call a stored procedure or function with some input parameters and get an output cursor variable, which the VBScript then could fetch from and build the HTML.
I find cursor variables a great method for an the interface point between the API logic that produces the data to be presented and the UX code that presents the data. I have several reasons for this.
You can use static SQL in the OPEN FOR statement with all of the advantages that static SQL in PL/SQL offers - automatic binding of variables, cursor cache, etc.
A CURSOR( ) expression allows you to SELECT cursor variables you then can pass on and use elsewhere.
If need be (not often, but can happen) you can use a dynamically built string in OPEN FOR with bind variables in the USING clause very similar to EXECUTE IMMEDIATE.
If you have really special dynamic needs, you can even build the query with DBMS_SQL and then convert the parsed DBMS_SQL statement to a cursor variable.
The beauty is, that no matter which of these ways the producing code chooses, it does not matter to the consumer of the cursor variable. Once the consumer gets the variable, it can simply fetch data from it without knowing the internals of how the query was built.
Client code in many languages support cursor variables. For example the OO4O driver for VBScript I used had an OraDynaset object, which could be created by writing SQL directly in the VBScript, but could also (the smarter way) be an output REF CURSOR bind variable populated by procedure or function call. Once the OraDynaset object was populated with the cursor variable, the object had member methods for working with the cursor, fetching data, closing cursor, etc. Many languages/drivers support this.
Your UX application might retrieve data via AJAX REST calls, in which case the cursor variables again are handy, as you can use ORDS to REST enable a procedure with an OUT REF CURSOR parameter. Then ORDS will fetch the data from the cursor and send to the application as JSON.
Or the end-consumer might not be your application directly, but you need to send the data as XML or JSON somewhere via HTTP or FTP or mail attachment. In which case there is an XMLType constructor function that allows you to build XML from a cursor variable in one single, simple call. For JSON a little PL/SQL is needed, but APEX_JSON.write function has an overload that readily accepts a cursor variable and builds JSON from the fetched data.
All in all, the cursor variable in my opinion is often a good interface choice for the API functions in a #SmartDB scenario, where the database functions as an API "business logic service provider".
You can be as simple or as complex as needed in the implementation - the cursor variable can remain an invariant interface. Often strongly typed as an "interface contract" between producer and consumer, but you can use weakly typed for some use cases without having to invent a different data interface method.
The cursor variable I have been able to use over the years in several scenarios. And as the database evolves, new features come in new database versions - like the ability to transform DBMS_SQL to REF CURSOR or ORDS REST enabling. Cursor variables are stable and proven, yet keeps up with modern times - and can be used almost everywhere.
For me it has been happy years with cursor variables.