[cx-oracle-users] Implicit Results interface in cx_Oracle

[Krishna M. IV <kri...@or...>]

Hello all,
   There is an interesting and useful feature, Implicit Results, added 
by Oracle in 12c. Planning to provide an interface in cx_Oracle for the 
same. Here is a brief description (links given for complete details) and 
proposed API changes. Let me know how it looks and suggestions you have!

thanks,
krishna

*Description*
     Implicit results are a way of returning cursors from a pl/sql block 
to the client, without specifying the OUT ref cursor parameters. The 
block or the procedure on the server opens the cursors and marks them 
for returning to the client, using dbms_sql.return_result procedure.
More here.
http://docs.oracle.com/database/121/LNPLS/dynamic.htm#LNPLS2174
https://docs.oracle.com/database/121/LNOCI/oci10new.htm#LNOCI73008


*cx_Oracle APIs/Classes

*_Cursor.implicitresultcount_
This read-only attribute specifies the number of different resultsets 
that have been returned from a pl/sql function or a procedure or an 
anonymous block. When no implicit results are returned, this value will 
be 0. This value is populated only after a call to  
callfunc()/callproc() or execute().


_Cursor.getnextimplicitresult()_
Fetch cursor for a unique resultset returned by a pl/sql function or 
procedure using dbms_sql.return_result. This function should be used 
when the Cursor. implicitresultcount is greater than 0. This routine 
should be called iteratively to get all the cursors returned by the 
PLSQL procedure.  Fetching of rows for each of these cursor objects can 
be done using fetchall/ fetchmany/ fetchone .
A Python  none object is returned when no implicit results exist.

Example

import cx_Oracle

sql = "declare \
c1 sys_refcursor; \
c2 sys_refcursor; \
begin \
open c1 for select city from locations where rownum < 4; \
dbms_sql.return_result(c1); \
open c2 for select first_name, last_name from employees where rownum < 4; \
dbms_sql.return_result(c2); \
end;"  #pl-sql block

conn = cx_Oracle.Connection("scott/tiger@inst1")
topcur = conn.cursor()
topcur.execute(sql);
print topcur.*/implicitresultcount/ *-----> Tells us how many such 
result sets are returned
impRSet = topcur./*getnextimplicitresult()*/ -------> To get each of 
those result sets
while impRSet:
     results = impRSet.fetchall()
     print results
     impRSet = topcur./*getnextimplicitresult()*/
topcur.close()
conn.close()

Note:
- The impRSet objects should not be closed by the application. They are 
implicitly closed by topcur.close(), the parent.
- Since we also have topcur.impicitresultcount, we can also do the above 
using a /for/ loop.

*Compatibility*
12c Oracle client and server.