[cx-oracle-users] Re: cx_Oracle Python utility

[Anthony T. <an...@co...>]

I hope you don't mind me sending this to the mailing list for cx_Oracle
for their enlightenment as well.

The method you are using is not supported. It could be, but there is a
simpler method that works. :-) I just added another test to the suite
which demonstrates how this is done (which will be released with the
next version).

The snippet assumes that you have the table TestStrings from the
cx_Oracle test suite created and populated along with the following
package:

create or replace package cx_Oracle.pkg_TestOutCursors as
                                                                                
  type udt_RefCursor is ref cursor;
                                                                                
  procedure TestOutCursor (
    a_MaxIntValue               number,
    a_Cursor                    out udt_RefCursor
  );
                                                                                
end;
/
                                                                                
create or replace package body cx_Oracle.pkg_TestOutCursors as
                                                                                
  procedure TestOutCursor (
    a_MaxIntValue               number,
    a_Cursor                    out udt_RefCursor
  ) is
  begin
    open a_Cursor for
      select
        IntCol,
        StringCol
      from TestStrings
      where IntCol <= a_MaxIntValue
      order by IntCol;
  end;
                                                                                
end;
/

Then the following snippet of Python code does the trick:

import cx_Oracle
                                                                                
connection = cx_Oracle.connect("cx_Oracle/pw@dsn")
cursor = connection.cursor()
refcursor = connection.cursor()
cursor.callproc("pkg_TestOutCursors.TestOutCursor", (2, refcursor))
print "Desc:", refcursor.description
print "Results:", refcursor.fetchall()

In essence, instead of calling

refcursorvar = cursor.var(cx_Oracle.CURSOR)
refcursor = refcursorvar.getvalue()

simply use

refcursor = connection.cursor()

and bind the cursor directly.

Make sense?

On Mon, 2003-08-25 at 10:28, Schedler, Kurt wrote:
> Hi Anthony,
> 
> I'm working on a Python module which uses cx_Oracle for connecting to
> our Oracle 9i database.  I'm working on calling stored procedures
> which return cursors via an OUT parameter.  I'm having problems
> creating refcursor variables with cx_Oracle:
> 
> >>> import cx_Oracle as ora
> >>> ora.version
> '3.1'
> >>> con = ora.connect("###/######@####")
> >>> cur = con.cursor()
> >>> refcur = cur.var(ora.CURSOR)
> Traceback (most recent call last):
>   File "<interactive input>", line 1, in ?
> NotSupportedError: Variable_TypeByPythonType(): unhandled data type
> >>> 
> 
> Is this a bug or am I doing something wrong?
> 
> Thanks for your help!
> 
> Kurt.
-- 
Anthony Tuininga
an...@co...
 
Computronix
Distinctive Software. Real People.
Suite 200, 10216 - 124 Street NW
Edmonton, AB, Canada  T5N 4A3
Phone:	(780) 454-3700
Fax:	(780) 454-3838
http://www.computronix.com