python批量跑plsql_如何将复合/复杂阵列传递给PLSQL过程？

从这个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;

/

称之为：

^{pr2}$

但是，我想调用下面的过程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')

但是，不允许在包外部创建关联数组的记录。我可以用一个对象来替换记录，但是我想不出任何东西来替换关联数组，因为关联数组是cx峎Oracle唯一可以传入或传出的集合类型。在

完整代码：

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', [])

在将$Oracle\'u HOME和etc设置为12c客户机后，我重新安装了cxu-Oracle之后，我可以进行更进一步的操作，但仍然遇到了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)