423,851 Members | 1,043 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,851 IT Pros & Developers. It's quick & easy.

cx_Oracle + array parameter

P: n/a
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!
Dec 3 '07 #1
Share this Question
Share on Google+
3 Replies


P: n/a
lu********@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

Dec 3 '07 #2

P: n/a
On 3 Gru, 19:07, Ian Clark <icl...@mail.ewu.eduwrote:
lukasz....@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.
Dec 3 '07 #3

P: n/a
On 3 Gru, 19:07, Ian Clark <icl...@mail.ewu.eduwrote:
lukasz....@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 has 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.
Dec 3 '07 #4

This discussion thread is closed

Replies have been disabled for this discussion.