Re: [cx-oracle-users] callfunc of a PL/SQL function returning REF CURSOR does not work

[Anthony T. <ant...@gm...>]

The only reason ref cursors weren't supported as return values from
functions is simply because I never use such techniques and so never
found the need for adding such support. :-) I have now done that. In
the meantime, you can either change the function into a procedure with
an out parameter or you can run the following code instead....

result = connection.cursor()
statement.execute("begin :result := my_pack.my_func_cursor(:arg); end;",
        result = result, arg = 5)
for row in result:
    print "Row:", row

Not as pretty but it works. :-) Hope that helps.

On 8/9/06, Steinmann Daniel <Dan...@co...> wrote:
> Hello, I would like to call a PL/SQL function which returns a REF CURSOR
> (see attached PL/SQL code) with following Python statements
>
>   connection = cx_Oracle.connect('test/test')
>   statement = connection.cursor()
>   result = statement.callfunc("my_pack.my_func_cursor",
> cx_Oracle.CURSOR, (5))
>   for row in result:
>
>       print "Row:", row
>
> This aborts like this:
>
>   Traceback (most recent call last):
>     File "./x.py", line 12, in ?
>       statement.callfunc("my_pack.my_func_cursor", cx_Oracle.CURSOR,
> (5))
>   cx_Oracle.NotSupportedError: Variable_TypeByPythonType(): unhandled
> data type
>
> I guess this happens because the function Variable_TypeByPythonType in
> Variable.c does not handle the CURSOR type. The REF CURSOR type is
> supported as in out parameter of a stored procedure (see
> http://permalink.gmane.org/gmane.comp.python.db/1413 )
>
> Is there any reason why REF CURSOR is not supported as return value of a
> stored function?
>
> Thanks,
> Daniel
>
> P.S.: I'm using cx_Oracle-4.2, Oracle 10.2, Python 2.4.3, Solaris 2.9.
>
> ---
>
> CREATE OR REPLACE PACKAGE my_pack
> IS
>    TYPE my_ref_cursor IS REF CURSOR;
>    FUNCTION my_func_cursor(some_number IN NUMBER) RETURN my_ref_cursor;
> END;
> /
> show errors
>
> CREATE OR REPLACE PACKAGE BODY my_pack
> IS
>    FUNCTION my_func_cursor(some_number IN NUMBER) RETURN my_ref_cursor
>    IS
>       result my_ref_cursor;
>    BEGIN
>       OPEN result FOR
>          SELECT some_number AS col1,
>                 some_number * 10 AS col2
>            FROM DUAL;
>       RETURN result;
>    END;
> END;
> /
> show errors
>
> -------------------------------------------------------------------------
> Using Tomcat but need to do more? Need to support web services, security?
> Get stuff done quickly with pre-integrated technology to make your job easier
> Download IBM WebSphere Application Server v.1.0.1 based on Apache Geronimo
> http://sel.as-us.falkag.net/sel?cmd=lnk&kid=120709&bid=263057&dat=121642
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>