cx_Oracle + array parameter

[lukasz.f24 at gmail.com]

On 3 Gru, 19:07, Ian Clark <icl... at mail.ewu.edu> wrote:
> lukasz.... 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

Hello,

Thanks for your reply. The secound parameter in curs.execute have to
be list. I passed only one parameter so it looks bizzare but this is
right.
Anyway i know why it was wrong. Problem is in the cx_array_string.
This type has to be INDEX BY BINARY_INTEGER !!!! I hope it will help
somebody in the future.