Re: [cx-oracle-users] Returning VArrays from callfunc

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

Hi Dom,

Apologies for the delay in responding. I have been extremely busy at work
without any opportunity to do much with cx_Oracle and cx_Freeze. :-(
Fortunately, that looks to be coming to an end. :-)

cx_Oracle supports PL/SQL arrays (index by integer) but does not support
SQL objects (arrays) except on read from a SQL statement, not a call to a
PL/SQL function. This issue has come up a few times over the years but I
have not had the chance to work on it -- nor the need for it it at work,
unfortunately. Hopefully sometime soon. In the meantime, you can use PL/SQL
arrays.

Anthony

On Sun, Jan 18, 2015 at 11:44 AM, Dominic Giles <dom...@gm...>
wrote:

> Apologies if this is a repeat…. I checked the archives and couldn’t find
> it.
>
> I'm trying to determine if it’s possible to return a simple array of
> numbers from a package using cx_oracle (5.1.2). I believe this is possible.
> I've not been able to find anything that suggest it isn’t.
>
> create or replace TYPE NUMARRAY
> -- Simple VArray of numbers
> is VARRAY(3) OF NUMBER;
> /
>
> create or replace PACKAGE SIMPLEPACKAGE
> AS
>   FUNCTION DoSomethingSimple(
>       cust_id INTEGER)
>     RETURN numarray;
>   FUNCTION DoSomethingSimpler(
>       cust_id INTEGER)
>     RETURN INTEGER;
> END SIMPLEPACKAGE;
> /
>
> create or replace PACKAGE BODY SIMPLEPACKAGE
> AS
> FUNCTION DOSOMETHINGSIMPLE(
>     cust_id INTEGER)
>   RETURN numarray
> AS
>   simple_array numarray := numarray();
> BEGIN
>   simple_array.extend;
>   simple_array(1) := cust_id;
>   simple_array.extend;
>   simple_array(2) := cust_id;
>   simple_array.extend;
>   simple_array(3) := cust_id;
>   RETURN SIMPLE_ARRAY;
> END DOSOMETHINGSIMPLE;
> FUNCTION DOSOMETHINGSIMPLER(
>     cust_id INTEGER)
>   RETURN INTEGER
> AS
> BEGIN
>   RETURN cust_id;
> END DOSOMETHINGSIMPLER;
> END SIMPLEPACKAGE;
> /
>
> And the Python test code
>
> import cx_Oracle
>
> if __name__ == '__main__':
>      with cx_Oracle.connect('soe', 'soe', 'oracle12c2/soe') as connection:
>         try:
>             cursor = connection.cursor();
>             ArrayType = cursor.arrayvar(cx_Oracle.NUMBER,3)
>             NumberType = cursor.var(cx_Oracle.NUMBER)
>             cursor.callfunc("SIMPLEPACKAGE.DOSOMETHINGSIMPLER",
> NumberType, [99])
>             cursor.callfunc("SIMPLEPACKAGE.DOSOMETHINGSIMPLE", ArrayType,
> [99])
>         except cx_Oracle.DatabaseError as dberror:
>             print dberror
>         finally:
>             cursor.close()
>
> The call to return works just fine. The call to return the function gives
> the error
>
> ORA-06550: line 1, column 13:
> PLS-00382: expression is of wrong type
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
>
> Any ideas what I'm doing wrong?
>
> Thanks
>
> Dom
>
>
> ------------------------------------------------------------------------------
> New Year. New Location. New Benefits. New Data Center in Ashburn, VA.
> GigeNET is offering a free month of service with a new server in Ashburn.
> Choose from 2 high performing configs, both with 100TB of bandwidth.
> Higher redundancy.Lower latency.Increased capacity.Completely compliant.
> http://p.sf.net/sfu/gigenet
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>