cx-oracle-users

[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.

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

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

[Shai B. <sh...@pl...>]

Hi Krishna,

This looks very interesting. Just one comment on the suggested API:

On Tuesday 03 February 2015 13:36:10 Krishna Mohan IV wrote:
> 
> 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.
> 

I would consider adding, from the get-go, an iterator for such cursors, as the 
primary interface. Something like:

	for imprset in topcur.implicitresults():
		results = imprset.fetchall()
		print results

Note that, according to the docs, the result-sets can be fetched in parallel, 
so something like this should also work:

	result_sets = list(topcur.implicitresults())
	results = zip(*result_sets)

Have fun,
	Shai.

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

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

Shai,
   thanks for the inputs! We can as well make topcur.implicitresults() 
return a tuple that can be used in the same way you outlined here. That 
simplifies the implementation and I guess there is no extra benefit that 
an iterator provides the users in this case.

thanks,
krishna

On 2/4/2015 1:19 AM, Shai Berger wrote:
> Hi Krishna,
>
> This looks very interesting. Just one comment on the suggested API:
>
> On Tuesday 03 February 2015 13:36:10 Krishna Mohan IV wrote:
>> 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.
>>
> I would consider adding, from the get-go, an iterator for such cursors, as the
> primary interface. Something like:
>
> 	for imprset in topcur.implicitresults():
> 		results = imprset.fetchall()
> 		print results
>
> Note that, according to the docs, the result-sets can be fetched in parallel,
> so something like this should also work:
>
> 	result_sets = list(topcur.implicitresults())
> 	results = zip(*result_sets)
>
> Have fun,
> 	Shai.
>
> ------------------------------------------------------------------------------
> 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

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

[Shai B. <sh...@pl...>]

Hi Krishna,

I just happened to notice that PEP-249 defines a `nextset()` operation for 
multiple result sets. I suspect this is not exactly suitable for Oracle 
implicit result sets, but you should consider either using it or explaining in 
docs why you don't. It is currently not implemented by cx_Oracle, as far as 
I'm aware.

Shai.

On Friday 20 February 2015 08:15:46 Krishna Mohan IV wrote:
> Shai,
>    thanks for the inputs! We can as well make topcur.implicitresults()
> return a tuple that can be used in the same way you outlined here. That
> simplifies the implementation and I guess there is no extra benefit that
> an iterator provides the users in this case.
> 
> thanks,
> krishna
> 
> On 2/4/2015 1:19 AM, Shai Berger wrote:
> > Hi Krishna,
> > 
> > This looks very interesting. Just one comment on the suggested API:
> > 
> > On Tuesday 03 February 2015 13:36:10 Krishna Mohan IV wrote:
> >> 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.
> > 
> > I would consider adding, from the get-go, an iterator for such cursors,
> > as the
> > 
> > primary interface. Something like:
> > 	for imprset in topcur.implicitresults():
> > 		results = imprset.fetchall()
> > 		print results
> > 
> > Note that, according to the docs, the result-sets can be fetched in
> > parallel,
> > 
> > so something like this should also work:
> > 	result_sets = list(topcur.implicitresults())
> > 	results = zip(*result_sets)
> > 
> > Have fun,
> > 
> > 	Shai.
> > 
> > -------------------------------------------------------------------------
> > ----- 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
> 
> ---------------------------------------------------------------------------
> --- Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
> from Actuate! Instantly Supercharge Your Business Reports and Dashboards
> with Interactivity, Sharing, Native Excel Exports, App Integration & more
> Get technology previously reserved for billion-dollar corporations, FREE
> http://pubads.g.doubleclick.net/gampad/clk?id=190641631&iu=/4140/ostg.clktr
> k _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

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

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

Shai,
   looks like nextset() is implemented by some db drivers to expose the 
resultsets from stored procedures too. But there is a restriction there 
to exhaust or discard all the rows of the current resultset before 
moving to the next. Our proposed API allows applications to fetch from 
the implicit cursors as per their need and does not impose that restriction.
   Sure, I understand we should document it clearly.

thanks,
krishna

On 3/1/2015 8:11 AM, Shai Berger wrote:
> Hi Krishna,
>
> I just happened to notice that PEP-249 defines a `nextset()` operation for
> multiple result sets. I suspect this is not exactly suitable for Oracle
> implicit result sets, but you should consider either using it or explaining in
> docs why you don't. It is currently not implemented by cx_Oracle, as far as
> I'm aware.
>
> Shai.
>
> On Friday 20 February 2015 08:15:46 Krishna Mohan IV wrote:
>> Shai,
>>     thanks for the inputs! We can as well make topcur.implicitresults()
>> return a tuple that can be used in the same way you outlined here. That
>> simplifies the implementation and I guess there is no extra benefit that
>> an iterator provides the users in this case.
>>
>> thanks,
>> krishna
>>
>> On 2/4/2015 1:19 AM, Shai Berger wrote:
>>> Hi Krishna,
>>>
>>> This looks very interesting. Just one comment on the suggested API:
>>>
>>> On Tuesday 03 February 2015 13:36:10 Krishna Mohan IV wrote:
>>>> 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.
>>> I would consider adding, from the get-go, an iterator for such cursors,
>>> as the
>>>
>>> primary interface. Something like:
>>> 	for imprset in topcur.implicitresults():
>>> 		results = imprset.fetchall()
>>> 		print results
>>>
>>> Note that, according to the docs, the result-sets can be fetched in
>>> parallel,
>>>
>>> so something like this should also work:
>>> 	result_sets = list(topcur.implicitresults())
>>> 	results = zip(*result_sets)
>>>
>>> Have fun,
>>>
>>> 	Shai.
>>>
>>> -------------------------------------------------------------------------
>>> ----- 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
>> ---------------------------------------------------------------------------
>> --- Download BIRT iHub F-Type - The Free Enterprise-Grade BIRT Server
>> from Actuate! Instantly Supercharge Your Business Reports and Dashboards
>> with Interactivity, Sharing, Native Excel Exports, App Integration & more
>> Get technology previously reserved for billion-dollar corporations, FREE
>> http://pubads.g.doubleclick.net/gampad/clk?id=190641631&iu=/4140/ostg.clktr
>> k _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> ------------------------------------------------------------------------------
> 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