ODC Appreciation Day: Cursor Variables

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.

So what should I pick this year of the multitude of possible favorites? Something brand new? No, I think I'll write about something that for almost a couple decades has been a trusted friend when I develop - and still can keep up without getting outdated:

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.

On the "producer" side, the query for the cursor variable can be built in different ways:

• 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.
  
• A cursor variable can be strongly typed, where both producer and consumer knows at compile-time the columns and datatypes of the cursor. Or for the more dynamic use cases it can be weakly typed allowing the columns and datatypes to be determined at run-time (either with a purpose-built weak ref cursor type or the built-in SYS_REFCURSOR.)

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.

Using the cursor variable is then possible in many ways too:

• 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.
  
• Consumer of the cursor variable might also be PL/SQL. Either for further processing, or in cases where the UX is created with PL/SQL (from old mod_plsql to modern APEX applications.)
  
• 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 front-end can (and very often will) change technology, but there is a great chance that the new technology somehow can consume the cursor variable. For example the above-mentioned webshop code could be transformed from VBScript to C# and still call the same API functions - ODP.NET supports cursor variables equally well as OO4O. And with HTML5 and JQuery the API functions could be called via REST with very little change (just publish the function via ORDS.)

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.

#ThanksODC