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

[Amaury F. d'A. <ama...@gm...>]

2015-02-03 12:36 GMT+01:00 Krishna Mohan 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!
>

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-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>


-- 
Amaury Forgeot d'Arc