Re: Implicit Results interface in cx_Oracle

2015-02-03 12:36 GMT+01:00 Krishna Mohan IV <[email protected]>:

>  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!
>

That's a good idea!
For the implementation, I suggest that you start with a subclass of
cx_Oracle.Cursor.
I'm sure these new features can be implemented with Python code.

Then when all details are sorted out, it will be easy enough to convert to
C code.


> 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.
>
>
>
> ------------------------------------------------------------------------------
> Dive into the World of Parallel Programming. The Go Parallel Website,
> sponsored by Intel and developed in partnership with Slashdot Media, is
> your
> hub for all things parallel software development, from weekly thought
> leadership blogs to news, videos, case studies, tutorials and more. Take a
> look and join the conversation now. http://goparallel.sourceforge.net/
> _______________________________________________
> cx-oracle-users mailing list
> cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>


-- 
Amaury Forgeot d'Arc

------------------------------------------------------------------------------
Dive into the World of Parallel Programming. The Go Parallel Website,
sponsored by Intel and developed in partnership with Slashdot Media, is your
hub for all things parallel software development, from weekly thought
leadership blogs to news, videos, case studies, tutorials and more. Take a
look and join the conversation now. http://goparallel.sourceforge.net/

_______________________________________________
cx-oracle-users mailing list
cx-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users