Tuesday, 23 December 2014

Oracle 12c Implicit Result Sets in SQL Developer 4.1

Ever want to run a Stored Procedure or a PL/SQL anonymous block and just want to "print out" the results of a query ?  Even as a little bit of debug information?

In Oracle 11g you have to create a SQL*Plus REFCURSOR variable and then bind it within the anonymous block  or  pass it as an argument to a procedure/function. Run the code and then print the refcursor.

This requires a bit of know how in SQL*Plus and how it will work with your PL/SQL block or procedure.

In Oracle 12c a new feature called IMPLICIT RESULT SETS allows you to pass back a result set without parameters or external binds. When calling from SQL*Plus or SQL Developer Worksheet , this means we do not have to create REFCURSOR variables.

It was initially developed to help migrating from Sybase and SQL Server to Oracle. But its a nice feature which may help you in your day to day.

Explicit Result Sets in 11g

 Implicit Result Sets in 12c

In Oracle 12c the cursor is defined as variable not a parameter. This cursor variable can be "reused" to pass a cursor value into DBMS_SQL.RETURN_RESULT.  Clients which support Implicit Results Sets (like SQL*Plus and SQL Developer) will handles these implicitly for you.

There are pros and cons to this.
1) Not all clients/drivers support it.
2) Using Explicit parameters is very good practice as it denotes what the procedure is going to return.
3) Its MAGIC!

1) You do not need to know how to define, bind/assign or print SQL*Plus variables.
2) You do not have to change the procedure parameters. Especially useful if you want to return multiple result sets.
3) Its MAGIC!

Its handy in an anonymous block as well. No fuss!