cx-oracle-users

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

[Steinmann D. <Dan...@co...>]

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 =3D cx_Oracle.connect('test/test')
  statement =3D connection.cursor()
  result =3D statement.callfunc("my_pack.my_func_cursor",
cx_Oracle.CURSOR, (5))
  for row in result:

      print "Row:", row =20

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

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
>

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

[Steinmann D. <Dan...@co...>]

On Thursday, August 10, 2006 4:43 PM, Anthony Tuininga wrote:

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

Cool!

I checked http://cx-oracle.cvs.sourceforge.net/cx-oracle/cx_Oracle/ but
could not see any code change. I guess you do not keep the sources
there, right?

If it is not too much hassle for you: Can you send me a patch of this
change so I can try it on my machine?

> In the meantime, you can either change the function into a procedure
> with an out parameter or you can run the following code instead.... =20
>=20
> result =3D connection.cursor()
> statement.execute("begin :result :=3D my_pack.my_func_cursor(:arg);
>         end;", result =3D result, arg =3D 5)
> for row in result:
>     print "Row:", row
>=20
> Not as pretty but it works. :-) Hope that helps.

Nice idea. I will use this approach in the meantime.

Thanks for the quick response,
Daniel

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

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

On 8/10/06, Steinmann Daniel <Dan...@co...> wrote:
> On Thursday, August 10, 2006 4:43 PM, Anthony Tuininga wrote:
>
> > 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.
>
> Cool!
>
> I checked http://cx-oracle.cvs.sourceforge.net/cx-oracle/cx_Oracle/ but
> could not see any code change. I guess you do not keep the sources
> there, right?

Correct. Too much trouble usually for its worth.

> If it is not too much hassle for you: Can you send me a patch of this
> change so I can try it on my machine?

Sure. Attached.

> Thanks for the quick response,

You're welcome.

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

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

[Steinmann D. <Dan...@co...>]

On Thursday, August 10, 2006 5:51 PM, Anthony Tuininga wrote:

> On 8/10/06, Steinmann Daniel <Dan...@co...> wrote:

>> If it is not too much hassle for you: Can you send me a patch of this
>> change so I can try it on my machine?
>=20
> Sure. Attached.

Works perfectly!

Thanks a lot,
Daniel

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

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

You're welcome. Glad to hear it works well for you, too.

On 8/11/06, Steinmann Daniel <Dan...@co...> wrote:
> On Thursday, August 10, 2006 5:51 PM, Anthony Tuininga wrote:
>
> > On 8/10/06, Steinmann Daniel <Dan...@co...> wrote:
>
> >> If it is not too much hassle for you: Can you send me a patch of this
> >> change so I can try it on my machine?
> >
> > Sure. Attached.
>
> Works perfectly!
>
> Thanks a lot,
> Daniel
>
> -------------------------------------------------------------------------
> 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
>