Re: Returning VArrays from callfunc

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 <[email protected]>
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-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
> 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-oracle-users-5NWGOfrQmneRv+LV9MX5uipxlwaOVQ5f@public.gmane.org
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users