cx-oracle-users

[cx-oracle-users] Can't figure out how to use Cursor.arrayvar

[L. D. B. <dan...@gp...>]

Can someone please explain how to work with arrays?  Specifically,  
how do I pass an array into a stored procedure/stored function, and  
how do I retrieve an array from a stored procedure/stored function?

Example:

create or replace type foo is varray(20) of varchar2(30);

create or replace function getFoo return foo
IS
     l_data foo := foo();
BEGIN
     l_data.EXTEND;
     l_data(1) := 'Some text';
     return l_data;
END getFoo;


c = cx_Oracle.connect('foo/bar@baz')
cursor = c.cursor()

cursor.callfunc('getFoo', [cx_Oracle.STRING, 20])

Traceback (most recent call last):
   File "<stdin>", line 1, in ?
cx_Oracle.DatabaseError: ORA-06550: line 1, column 13:
PLS-00382: expression is of wrong type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

If the stored function returns STRING, or NUMBER, everything is fine,  
but I need to make this work with arrays.

Thanks much for your help,

L. Daniel Burr

Re: [cx-oracle-users] Can't figure out how to use Cursor.arrayvar

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

Ah, you've been bitten by the annoying difference between PL/SQL
arrays and SQL arrays. :-) cx_Oracle currently only supports PL/SQL
arrays. I have plans to support the other variety but work has been
getting in the way recently. So, you need to define in your PL/SQL
package something of the form:

type udt_StringArray is table of varchar2(30) index by binary_integer;

And then use this type instead of the other type you defined in SQL.
At that point, the Python code you used should work just fine.

Hope that helps.

On 6/24/05, L. Daniel Burr <dan...@gp...> wrote:
> Can someone please explain how to work with arrays?  Specifically,
> how do I pass an array into a stored procedure/stored function, and
> how do I retrieve an array from a stored procedure/stored function?
>=20
> Example:
>=20
> create or replace type foo is varray(20) of varchar2(30);
>=20
> create or replace function getFoo return foo
> IS
>      l_data foo :=3D foo();
> BEGIN
>      l_data.EXTEND;
>      l_data(1) :=3D 'Some text';
>      return l_data;
> END getFoo;
>=20
>=20
> c =3D cx_Oracle.connect('foo/bar@baz')
> cursor =3D c.cursor()
>=20
> cursor.callfunc('getFoo', [cx_Oracle.STRING, 20])
>=20
> Traceback (most recent call last):
>    File "<stdin>", line 1, in ?
> cx_Oracle.DatabaseError: ORA-06550: line 1, column 13:
> PLS-00382: expression is of wrong type
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
>=20
> If the stored function returns STRING, or NUMBER, everything is fine,
> but I need to make this work with arrays.
>=20
> Thanks much for your help,
>=20
> L. Daniel Burr
>=20
>=20
> -------------------------------------------------------
> SF.Net email is sponsored by: Discover Easy Linux Migration Strategies
> from IBM. Find simple to follow Roadmaps, straightforward articles,
> informative Webcasts and more! Get everything you need to get up to
> speed, fast. http://ads.osdn.com/?ad_id=3D7477&alloc_id=3D16492&op=3Dclic=
k
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>