cx_Oracle How to Pass a Composite/Complex array to a PLSQL Procedure?

Question

From this guide I am able to pass an associative array of a simple data type (like cx_Oracle.NUMBER) to a PL/SQL procedure.

CREATE OR REPLACE PACKAGE test
IS
    TYPE t_ids IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    PROCEDURE foo(p_ids_i IN t_ids);
END;
/

To call it:

ids = cursor.arrayvar(cx_Oracle.NUMBER, [1,2,3])
cursor.callproc('test.foo', [ids])

---

However, I want to call the following procedure foo instead which takes a complex type instead.

CREATE OR REPLACE PACKAGE test
IS
    TYPE r_foo IS RECORD (id NUMBER, name VARCHAR2(10));
    TYPE t_complex IS TABLE OF r_foo INDEX BY PLS_INTEGER;
    PROCEDURE foo(p_ids_i IN t_complex);
END;
/

I've tried various things like:

# Raises NotSupportedError: Variable_TypeByPythonType(): unhandled data type
foos = cursor.arrayvar((cx_Oracle.NUMBER, cx_Oracle.STRING), [(1, 'foo'), (2, 'bar')])

# Raises NotSupportedError: Variable_MakeArray(): type does not support arrays
foos = cur.arrayvar(cx_Oracle.OBJECT, [(1, 'foo'), (2, 'bar')])

---

The following is failing:

# Raises DatabaseError: ORA-04043: object TEST.R_FOO does not exist
record_type = conn.gettype('TEST.R_FOO')

It looks like you can create a type outside of a package and reference that.

CREATE TYPE t_foo IS TABLE OF NUMBER; -- Not an Associative Array

To reference it:

t = conn.gettype('T_FOO')

However, you are not allowed to create a RECORD of an Associative Array outside of a package. I could replace the RECORD with an Object, but I can't think of anything to replace the Associative Array with, which is the only collection type that cx_Oracle can pass in or out.

---

Full code:

PL/SQL:

-- returns 12.1.0.2.0
SELECT VERSION FROM v$instance;

CREATE OR REPLACE PACKAGE test
IS
    TYPE r_foo IS RECORD (id NUMBER, name VARCHAR2(10));
    TYPE t_complex IS TABLE OF r_foo INDEX BY PLS_INTEGER;
    PROCEDURE foo(p_ids_i IN t_complex);
END;
/

CREATE OR REPLACE PACKAGE BODY test
IS
    PROCEDURE foo(p_ids_i IN t_complex)
    IS
    BEGIN
        FOR i IN p_ids_i.FIRST .. p_ids_i.LAST LOOP
            DBMS_OUTPUT.PUT_LINE(p_ids_i(i).id || ' ' || p_ids_i(i).name);
        END LOOP;
    END;
END;
/

-- The following works as expected.
DECLARE
    l_complex test.t_complex;
BEGIN
    l_complex(1).id := 1;
    l_complex(1).name := 'Matthew';

    l_complex(2).id := 2;
    l_complex(2).name := 'Moisen';

    test.foo(l_complex);
END;

Python:

import cx_Oracle

print cx_Oracle.version  # 5.3
print cx_Oracle.clientversion() # (12, 1, 0, 2, 0)

conn = cx_Oracle.connect('username/password@sid')
cur = conn.cursor()

result = cur.execute("SELECT * FROM user_source WHERE name = 'TEST' and type = 'PACKAGE'")

# This prints the package spec successfully
for row in result:
    print row

# Raises DatabaseError: ORA-04043: object TEST.R_FOO does not exist
conn.gettype('TEST.R_FOO')

# Raises DatabaseError: ORA-04043: object TEST.T_COMPLEX does not exist
conn.gettype('TEST.T_COMPLEX')

# This raises the appropriate exception saying I called the procedure
# incorrectly, demonstrating that I have access to it.
cur.callproc('TEST.FOO', [])

---

After reinstalling cx_Oracle with $ORACLE_HOME and etc set to my 12c client, I was able to get a bit futher, but still hit an error with the append operation.

import cx_Oracle

conn = cx_Oracle.connect('username/password@sid')

# This no longer raises an error
recordTypeObj = conn.gettype('TEST.R_FOO')
tableTypeObj = conn.gettype('TEST.T_COMPLEX')
rec = recordTypeObj.newobject()
tab = tableTypeObj.newobject()

# This works fine
rec.ID = 1
rec.NAME = "foo"

# This fails with 
# cx_Oracle.NotSupportedError: Object_ConvertFromPython(): unhandled data type 250
tab.append(rec)

Answer 1

This is supported in cx_Oracle 5.3 and higher. You have to use the "object" syntax which supports this sort of thing.

import cx_Oracle

conn = cx_Oracle.Connection("cx_Oracle/dev@localhost/orcl")
recordTypeObj = conn.gettype("TEST.R_FOO")
tableTypeObj = conn.gettype("TEST.T_COMPLEX")

tab = tableTypeObj.newobject()

rec = recordTypeObj.newobject()
rec.ID = 1
rec.NAME = "foo"
tab.append(rec)

rec = recordTypeObj.newobject()
rec.ID = 2
rec.NAME = "bar"
tab.append(rec)

cursor = conn.cursor()
cursor.callproc("test.foo", [tab])