cx_Oracle + array parameter

Discussion in 'Python' started by lukasz.f24@gmail.com, Dec 3, 2007.

  1. Guest

    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, 2007
    #1
    1. Advertising

  2. Ian Clark Guest

    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
    Ian Clark, Dec 3, 2007
    #2
    1. Advertising

  3. Guest

    On 3 Gru, 19:07, Ian Clark <> wrote:
    > 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, 2007
    #3
  4. Guest

    On 3 Gru, 19:07, Ian Clark <> wrote:
    > 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, 2007
    #4
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Glenn Stauffer

    cx_Oracle & prepared statements

    Glenn Stauffer, Aug 16, 2003, in forum: Python
    Replies:
    0
    Views:
    822
    Glenn Stauffer
    Aug 16, 2003
  2. GrayGeek

    import cx_Oracle

    GrayGeek, Nov 1, 2003, in forum: Python
    Replies:
    12
    Views:
    1,090
    GrayGeek
    Nov 7, 2003
  3. Benson, John
    Replies:
    1
    Views:
    2,206
    Aurelio Martin
    Jan 28, 2004
  4. Pieter Claerhout
    Replies:
    0
    Views:
    594
    Pieter Claerhout
    Jan 28, 2004
  5. About cx_Oracle..

    , Mar 1, 2004, in forum: Python
    Replies:
    0
    Views:
    330
Loading...

Share This Page