gpt4 book ai didi

python - 在Python中使用callproc调用包含记录集合的存储过程

转载 作者:行者123 更新时间:2023-12-01 08:44:05 24 4
gpt4 key购买 nike

我有一个 sql 存储过程:

PROCEDURE sp_add_object_tags(
pi_account_id IN  user_files.account_id%TYPE,
pi_object_id IN  object_metadata.object_id%TYPE,
pi_all_tags_identified IN  VARCHAR2,
pi_object_tag_tbl IN  object_tag_tbl,
po_error_code OUT NUMBER,
po_error_message OUT VARCHAR2 )

其中object_tag_tbl是object_tag_rec的集合

object_tag_rec 是具有以下字段(元素)的记录:

tag_type            VARCHAR2(1),
tag_category VARCHAR2(100),
tag_key VARCHAR2(250),
tag_value VARCHAR2(250),
created_by VARCHAR2(50)

当我使用 callproc 调用上述存储过程时

object_tag_rec = collections.namedtuple('object_tag_rec',['tag_type','tag_category','tag_key', 'tag_value', 'created_by'])
rec_params1=object_tag_rec("S","person","person","1","abc")
params = [rec_params1]
cur_var = cur.callproc('tejfilusr.pg_object_tagging.sp_add_object_tags', [1234, 5678, 'N', params, po_error_code, po_error_message])

它给了我一个错误“元素 0 值不受支持”。有没有办法在Python中做到这一点?

最佳答案

您不能直接使用collections.namedtuple作为Oracle对象类型。

我在 Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 上使用 Cx-Oracle-7.0.0 和 < strong>Python 3.6.4,它对我有用。

创建类型

CREATE OR REPLACE TYPE object_tag_rec AS OBJECT (
tag_type VARCHAR2(1),
tag_category VARCHAR2(100),
tag_key VARCHAR2(250),
tag_value VARCHAR2(250),
created_by VARCHAR2(50)
);
/

创建收藏

CREATE OR REPLACE TYPE object_tag_tbl is TABLE OF object_tag_rec;
/

创建过程

CREATE OR REPLACE procedure  sp_add_object_tags(
pi_account_id IN INT,
pi_object_id IN INT,
pi_all_tags_identified IN VARCHAR2,
pi_object_tag_tbl IN object_tag_tbl,
po_error_code OUT NUMBER,
po_error_message OUT VARCHAR2 )
AS
BEGIN

po_error_code := 0;
po_error_message := 'NO ERRORS';
END;
/

代码

import cx_Oracle
conn = cx_Oracle.connect('user/pwd@//localhost:1521/dbname')
cur = conn.cursor()

recordTypeObj = conn.gettype("HR.OBJECT_TAG_REC") #mind the cases
tableTypeObj = conn.gettype("HR.OBJECT_TAG_TBL")
params = tableTypeObj.newobject()

rec = recordTypeObj.newobject()
(rec.TAG_TYPE,rec.TAG_CATEGORY,rec.TAG_KEY,rec.TAG_VALUE,rec.CREATED_BY) = ("S","person","person","1","abc")
#mind the cases for attributes.

po_error_code = cur.var(cx_Oracle.NUMBER)
po_error_message = cur.var(cx_Oracle.STRING)

params.append(rec)
cur.callproc('dbms_output.enable')
cur.callproc('hr.sp_add_object_tags', [1234, 5678, 'N', params, po_error_code, po_error_message])

print (po_error_code.getvalue(),po_error_message.getvalue())

执行

$python pass_obj.py
0.0 NO ERRORS

关于python - 在Python中使用callproc调用包含记录集合的存储过程,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/53375917/

24 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com