cx_Oracle, callfunc and varray

Dom dominic.giles at gmail.com
Fri Jan 9 20:24:48 CET 2015


Hi

I'm trying to return a simple array of numbers from a package using cx_oracle (5.1.2). I believe this is possible. I've not been able to find anything that suggest it isn't

create or replace TYPE NUMARRAY
-- Simple VArray of numbers
is VARRAY(3) OF NUMBER;
/

create or replace PACKAGE SIMPLEPACKAGE
AS
  FUNCTION DoSomethingSimple(
      cust_id INTEGER)
    RETURN numarray;
  FUNCTION DoSomethingSimpler(
      cust_id INTEGER)
    RETURN INTEGER;
END SIMPLEPACKAGE;
/

create or replace PACKAGE BODY SIMPLEPACKAGE
AS
FUNCTION DOSOMETHINGSIMPLE(
    cust_id INTEGER)
  RETURN numarray
AS
  simple_array numarray := numarray();
BEGIN
  simple_array.extend;
  simple_array(1) := cust_id;
  simple_array.extend;
  simple_array(2) := cust_id;
  simple_array.extend;
  simple_array(3) := cust_id;
  RETURN SIMPLE_ARRAY;
END DOSOMETHINGSIMPLE;
FUNCTION DOSOMETHINGSIMPLER(
    cust_id INTEGER)
  RETURN INTEGER
AS
BEGIN
  RETURN cust_id;
END DOSOMETHINGSIMPLER;
END SIMPLEPACKAGE;
/

The python (2.7) is very simple

import cx_Oracle

if __name__ == '__main__':
     with cx_Oracle.connect('soe', 'soe', 'oracle12c2/soe') as connection:
        try:
            cursor = connection.cursor();
            ArrayType = cursor.arrayvar(cx_Oracle.NUMBER,3)
            NumberType = cursor.var(cx_Oracle.NUMBER)
            cursor.callfunc("SIMPLEPACKAGE.DOSOMETHINGSIMPLER", NumberType, [99])
            cursor.callfunc("SIMPLEPACKAGE.DOSOMETHINGSIMPLE", ArrayType, [99])
        except cx_Oracle.DatabaseError as dberror:
            print dberror
        finally:
            cursor.close()

The call to return works just fine. The call to return the function gives the error

ORA-06550: line 1, column 13:
PLS-00382: expression is of wrong type
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Any ideas what I'm doing wrong?

Dom






More information about the Python-list mailing list