cx_Oracle + array parameter

[Ian Clark]

lukasz.f24 at gmail.com wrote:
> Hello,
> 
> I'm trying to pass array as an argument into PL/SQL procedure.
> According to cursor manual (http://cx-oracle.sourceforge.net/html/
> cursorobj.html) arrayvar() should be use to do it. I've created my
> array type in PL/SQL:
> 
> CREATE OR REPLACE TYPE cx_array_string is table of varchar2(200);
> 
> and simple procedure:
> 
> CREATE OR REPLACE PROCEDURE text(ret IN cx_array_string) IS
> BEGIN
>     null;
> END text;
> 
> My python code:
> 
> p_array = curs.arrayvar(cx_Oracle.STRING, ['1','3'])
> curs.execute('BEGIN text( :1 ); end;', [p_array] )
> 
> And it gives me back an error:
> cx_Oracle.DatabaseError: ORA-06550: line 1, column 7:
> PLS-00306: wrong number or types of arguments in call to 'TEXT'
> ORA-06550: line 1, column 7:
> PL/SQL: Statement ignored
> 
> It's the same when i try to use callproc() instead of execute(). I've
> searched whole internet with no luck. Could anyone please give me a
> working example python + pl/sql how to pass string array form py to
> oracle procedure, please.
> 
> Thank you!

First off I've never used cxOracle or done any PL/SQL from python, but 
it looks like you're passing a list of a list to text().

 > p_array = curs.arrayvar(cx_Oracle.STRING, ['1','3'])
 > curs.execute('BEGIN text( :1 ); end;', [p_array] )

p_array appears to be some sort of cxOracle array, but when you pass it 
to curs.execute you wrap it in a new list: [p_array]. Try removing the 
parens and see what happens.

Ian