cx_oracle使用参数,cxuOracle：使用PL/SQL记录作为存储过程的参数

我试图将PL/SQL记录参数传递给存储过程。我使用的是cx®Oracle 6.0b1。从cxu-Oracle文档中&＃xff0c;我相信以下代码是正确的&＃xff0c;但是&＃xff0c;它总是导致错误PLS-00306: wrong number or types of arguments in call to &＃39;CREATE_VENDOR&＃39;

我的代码&＃xff1a;import cx_Oracle

ID_MAX_LEN &＃61; 29

def get_obj_of_type(type_name, connection):

type_name &＃61; type_name.replace(&＃39;.&＃39;, &＃39;_&＃39;)[:ID_MAX_LEN]

typeObj &＃61; connection.gettype(type_name)

return typeObj.newobject()

def main():

dsn &＃61; {...}

connection &＃61; cx_Oracle.connect(dsn[&＃39;user&＃39;], dsn[&＃39;password&＃39;], dsn&＃61;dsn[&＃39;oracle_dsn&＃39;])

cursor &＃61; connection.cursor()

try:

rec &＃61; get_obj_of_type(&＃39;AP_VENDOR_PUB_PKG.R_VENDOR_REC_TYPE&＃39;, connection)

rec.VENDOR_NAME &＃61; &＃39;Test Vendor&＃39;

P_API_VERSION &＃61; 1.0

P_INIT_MSG_LIST &＃61; "T"

P_COMMIT &＃61; "T"

P_VALIDATION_LEVEL &＃61; 1

X_RETURN_STATUS &＃61; cursor.var(cx_Oracle.STRING)

X_MSG_COUNT &＃61; cursor.var(cx_Oracle.NUMBER)

X_MSG_DATA &＃61; cursor.var(cx_Oracle.STRING)

P_VENDOR_REC &＃61; rec

X_VENDOR_ID &＃61; cursor.var(cx_Oracle.NUMBER)

X_PARTY_ID &＃61; cursor.var(cx_Oracle.NUMBER)

result &＃61; cursor.callproc(&＃39;AP_VENDOR_PUB_PKG.CREATE_VENDOR&＃39;,

parameters&＃61;[P_API_VERSION,

P_INIT_MSG_LIST,

P_COMMIT,

P_VALIDATION_LEVEL,

X_RETURN_STATUS,

X_MSG_COUNT,

X_MSG_DATA,

P_VENDOR_REC,

X_VENDOR_ID,

X_PARTY_ID])

finally:

cursor.close()

connection.close()

main()

此存储过程是Oracle r12电子商务套件的一部分。如果您有权访问该产品的数据库实例&＃xff0c;则此存储过程应该可用。在

安东尼要求的记录定义&＃xff1a;

^{pr2}$

编辑&＃xff1a;按照安东尼的建议&＃xff0c;我试着用我自己创作的唱片&＃xff1a;create or replace package XX_SUPPLIERS_PKG is

TYPE rec_test IS RECORD(p1 varchar2(255), p2 varchar2(255));

procedure test_record(p_rec in rec_test,

x_vendor out SYS_REFCURSOR);

[...]

end XX_SUPPLIERS_PKG;

当我尝试使用此存储过程并在cx\U Oracle中进行记录时&＃xff0c;我得到&＃xff1a;cx_Oracle.DatabaseError: ORA-04043: object XX_SUPPLIERS_PKG_REC_TEST does not exist

我检查了oraclesqldeveloper的DBA_TYPES视图&＃xff0c;没有看到任何以“XX”或“REC”开头的类型

以下是产生上述错误的代码&＃xff1a;import cx_Oracle

def main():

connection &＃61; cx_Oracle.connect(...)

cursor &＃61; connection.cursor()

try:

rec &＃61; connection.gettype(&＃39;XX_SUPPLIERS_PKG_REC_TEST&＃39;)

rec.P1 &＃61; "test"

rec.P2 &＃61; "test"

out_cursor &＃61; connection.cursor()

result &＃61; cursor.callproc(&＃39;XX_SUPPLIERS_PKG.TEST_RECORD&＃39;,

parameters&＃61;[rec, out_cursor])

finally:

cursor.close()

connection.close()

main()

另一个注意事项&＃xff1a;我尝试使用在DBA_TYPES视图中可见的PL/SQL记录类型。这会导致类似的错误&＃xff1a;cx_Oracle.DatabaseError: ORA-04043: object PL/SQL RECORD does not exist