Thursday, July 11, 2013

12c - Implicit Result Sets...

Since version 7.2 of Oracle we've been able to return result sets from stored procedures to clients.  The way we've accomplished this in the past is via a REF CURSOR.  I've always liked this approach since it makes it so that the signature of a stored procedure/function makes it clear that a procedure/function a) actually returns a result set and b) can be done such that the 'shape' of the result set is known at compile time.

What that means is - the REF CURSOR would be a formal named parameter for the procedure or the return value of a function.  If you describe the procedure - you'll see it.  You'll know exactly how many result sets the procedure returns and you have the capability to know the number of columns, names of columns and datatypes if the developer chose to use strongly typed ref cursors.  In other words - you'll know what you are getting, the REF CURSORS are explicitly there, staring you in the face.

Other databases adopted an implicit approach.  If you describe their procedures - you will have no idea how many or what kind of result sets they return.  You have to run them and see what they decide to send back - and each time you run them, they could return a different result.  In other words, you had to read the code to see what results might be coming back to you.  I'm not a huge fan of this approach - it is much less self documenting, more error prone (in my opinion).

That said - this difference (explicit versus implicit result sets) can make migrating an application from these databases to Oracle difficult.  You have to change the inputs/outputs of your stored procedures - adding the ref cursors and you have to modify the client code.  You cannot just change the stored procedure, you have to change the client.  When using implicit result sets - the client code would look similar to this psuedo code:

execute stored procedure( param1, param2, .. paramN )
while more result-sets loop
   while more-data-from-that-result-set loop
       process data
   end while more-data-from-that-result-set
end while result-sets

while the code for explict result sets would resemble:

execute stored procedure
( param1, param2, ... paramN, 
  result-set1, result-set2, ... result-setN)

while more-data-in-result-set1 loop
    process data
end while more-data-in-result-set1
...
while more-data-in-result-setN loop
    process data
end while more-data-in-result-setN

so, the structure of the client code must be modified in addition to the stored procedures signature (it's inputs and outputs).  

In order to ease migrations from implicit result set databases to Oracle, Oracle database 12c introduced support for implicit result sets - result sets that can be returned to a client but do not necessitate a formal named parameter.  The client code to process such a result set is identical now - the client code needs not change, the stored procedure signature need not change - the body of the procedure just needs to be implemented in PL/SQL.

We accomplish this magic in PL/SQL via two new API calls in the DBMS_SQL package - one for returning REF CURSORS and one for returning DBMS_SQL cursors.  They are:



PROCEDURE RETURN_RESULT(rc IN OUT SYS_REFCURSOR, to_client IN BOOLEAN DEFAULT TRUE); 
PROCEDURE RETURN_RESULT(rc IN OUT INTEGER, to_client IN BOOLEAN DEFAULT TRUE);

TO_CLIENT => true, return to client layer
TO_CLIENT=> false, return to invoker, immediate caller - could be another plsql routine


to demonstrate this, we can use SQL*Plus as the client and show an implicit result set being returned to the client and auto-magically printed (this will only work with a 12c SQL*Plus!! it is the first SQL*Plus to recognize that there might be result sets to print)


ops$tkyte%ORA12CR1> declare
  2      c1 sys_refcursor;
  3      c2 sys_refcursor;
  4  begin
  5      open c1 for select * from dept;
  6      dbms_sql.return_result(c1);
  7
  8      open c2 for select * from dual;
  9      dbms_sql.return_result(c2);
 10  end;
 11  /

PL/SQL procedure successfully completed.

ResultSet #1

    DEPTNO DNAME          LOC
---------- -------------- -------------
        10 ACCOUNTING     NEW YORK
        20 RESEARCH       DALLAS
        30 SALES          CHICAGO
        40 OPERATIONS     BOSTON

ResultSet #2

D
-
X

ops$tkyte%ORA12CR1>

as you can see - there were no inputs/outputs to this block of code - we implicitlly returned the two result sets using the new DBMS_SQL API and SQL*Plus used a method that would result the second set of psuedo code above to discover what results might be available and then print them out.

We can process these implicit result sets in PL/SQL if we want (they don't have to be returned to a client - the ref cursor/dbms_sql cursor can be implicitly returned to a PL/SQL function/procedure) using the two new API calls:

PROCEDURE GET_NEXT_RESULT(c IN INTEGER, rc OUT SYS_REFCURSOR);
PROCEDURE GET_NEXT_RESULT(c IN INTEGER, rc OUT INTEGER);

(see http://docs.oracle.com/cd/E16655_01/appdev.121/e17622/dynamic.htm#LNPLS2176 for a description and example of this API).

But in general, it'll be a client bit of code that processes these result sets and a client java program might resemble:

Connection conn = DriverManager.getConnection(jdbcURL, user, password);
try 
{
    Statement stmt = conn.createStatement (); 
    stmt.executeQuery ( “begin foo; end;” );
 
    while (stmt.getMoreResults())
    {
         ResultSet rs = stmt.getResultSet();
         System.out.println("ResultSet");
         while (rs.next())
         {   
               /* get results */
         }
     }
}

I see Tim Hall has written about this recently as well - you can see his writeup here: http://www.oracle-base.com/articles/12c/implicit-statement-results-12cr1.php



POST A COMMENT

7 Comments:

Blogger Parthiban Nagarajan said....

Hi Tom

//If you describe their procedures - you will have no idea how many or what kind of result sets they return//

As you mentioned here, this feature may bring in unnecessary headaches. Though it is recommended to be used only when migrating from other databases, there are chances that this feature could be misused. Also, during migration, it should not be a big issue in using explicit result sets, I believe.

By the way, thanks for the cool 12c articles.

Thanks and regards

Mon Jul 15, 12:03:00 AM EDT  

Blogger Michael O'Neill said....

Thank you for exposing some of the 12c nuggets. This particular "feature" seems to be a step backwards.

Sat Jul 20, 04:25:00 PM EDT  

Blogger Thomas Kyte said....

@Micheal,

hopefully it is used as a step forward :) for migrating from SQL Server to Oracle more rapidly - and then fixing all of the bad practices...

Sun Jul 21, 08:17:00 AM EDT  

Anonymous Oracle Connections said....

it is quite an informative post you've shared. appreciated !

Thu Aug 01, 05:12:00 AM EDT  

Blogger Rahim PK said....

Hi Tom,

I have the following code which is tested in 12c sqlplus. But I am getting the following error.

declare
src sys_refcursor;
begin
open src for select * from v$version;
dbms_sql.return_result(src,false);
end;
/

Error report -
ORA-29481: Implicit results cannot be returned to client.
ORA-06512: at "SYS.DBMS_SQL", line 2785
ORA-06512: at "SYS.DBMS_SQL", line 2779
ORA-06512: at line 9

Also version information is specified .

BANNER CON_ID
-------------------------------------------------------------------------------- ----------
Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production 0
PL/SQL Release 12.1.0.1.0 - Production 0
CORE 12.1.0.1.0 Production 0
TNS for Linux: Version 12.1.0.1.0 - Production 0
NLSRTL Version 12.1.0.1.0 - Production 0
Is any additional configuration to be made in my database to get the implicit result set in sqlplus ?

Tue Nov 04, 02:11:00 AM EST  

Blogger Thomas Kyte said....

@Rahim

what is your sqlplus version?

I don't believe it was really 12.1

29481, 00000, "Implicit results cannot be returned to client."
// *Cause: An attempt was made to return implicit results to an older version
// client that is not capable of receiving these implicit results.
// *Action: Upgrade the Oracle Database Client software.



I ran this in

SQL*Plus: Release 12.1.0.2.0 Production on Tue Nov 4 02:20:01 2014

Copyright (c) 1982, 2014, Oracle. All rights reserved.

Last Successful login time: Thu Oct 09 2014 06:24:40 -05:00

Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
With the Partitioning, OLAP, Advanced Analytics and Real Application Testing options

Tue Nov 04, 02:21:00 AM EST  

Blogger Rahim PK said....

Thanks a lot Tom. I got the result. Actually 11g & 12c was running on the same server and the path variable was pointed to the 11g version.

Tue Nov 04, 04:05:00 AM EST  

POST A COMMENT

<< Home