cx_Oracle, callfunc and varray
Dom
dominic.giles at gmail.com
Fri Jan 9 14:24:48 EST 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