cx_Oracle如何将复合/复杂数组传递给PLSQL过程?
问题描述:
从这guide我能够将简单数据类型的关联数组(如cx_Oracle.NUMBER
)传递给PL/SQL过程。cx_Oracle如何将复合/复杂数组传递给PLSQL过程?
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.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/[email protected]') 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_HOME和等重新安装cx_Oracle设置为我的12C客户端后,我能够得到进一步,但仍然在append
操作中出现错误。
import cx_Oracle conn = cx_Oracle.connect('username/[email protected]') # 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)
答
这在cx_Oracle 5.3及更高版本中受支持。你必须使用支持这种事情的“对象”语法。
import cx_Oracle
conn = cx_Oracle.Connection("cx_Oracle/[email protected]/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])
在[发行说明5.3(https://cx-oracle.readthedocs.io/en/latest/releasenotes.html)它说,'增加了对创建,修改和有约束力的PL/SQL记录支持和集合(可在Oracle 12.1中获得)。“您是否知道这是您所指的注释,以及这是否意味着它不适用于11g? –
是的。这在Oracle 11g中不受支持。 –
'conn.gettype('TEST.R_FOO')'正在抛出一个'DatabaseError:ORA-04043:对象test.r_foo不存在'。看来,cx_Oracle在包中没有找到任何类型;而它可以在包外找到一种类型。你介意看看我更新的问题吗? –