python - cx_Oracle 如何将复合/复杂数组传递给 PLSQL 过程？

Question

从此guide我能够将简单数据类型的关联数组(例如 cx_Oracle.NUMBER)传递给 PL/SQL 过程。

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;
/

调用它:

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

<小时/>

但是，我想调用以下过程 foo ，它采用复杂类型。

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;
/

我尝试过各种方法，例如:

# 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')])

<小时/>

以下失败:

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

看起来您可以在包外部创建一个类型并引用它。

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

引用它:

t = conn.gettype('T_FOO')

但是，您不允许在包外部创建关联数组的记录。我可以用对象替换 RECORD，但我想不出任何可以替换关联数组的东西，这是 cx_Oracle 可以传入或传出的唯一集合类型。

<小时/>

完整代码:

PL/SQL:

-- returns 12.1.0.2.0SELECT VERSION FROM v$instance;CREATE OR REPLACE PACKAGE testIS    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 testIS    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_Oracleprint cx_Oracle.version  # 5.3print 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 successfullyfor row in result:    print row# Raises DatabaseError: ORA-04043: object TEST.R_FOO does not existconn.gettype('TEST.R_FOO')# Raises DatabaseError: ORA-04043: object TEST.T_COMPLEX does not existconn.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', [])

<小时/>

在将 $ORACLE_HOME 等设置为我的 12c 客户端重新安装 cx_Oracle 后，我能够得到一些进一步的信息，但在 append 操作中仍然遇到错误。

import cx_Oracleconn = cx_Oracle.connect('username/password@sid')# This no longer raises an errorrecordTypeObj = conn.gettype('TEST.R_FOO')tableTypeObj = conn.gettype('TEST.T_COMPLEX')rec = recordTypeObj.newobject()tab = tableTypeObj.newobject()# This works finerec.ID = 1rec.NAME = "foo"# This fails with # cx_Oracle.NotSupportedError: Object_ConvertFromPython(): unhandled data type 250tab.append(rec)

Answer 1

cx_Oracle 5.3 及更高版本支持此功能。您必须使用支持此类事物的“对象”语法。

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])