在python中使用callproc调用包含记录集合的存储过程 [英] Call stored procedure containing collection of records using callproc in python

问题描述

我在sql中有一个存储过程:

I am having a stored procedure in 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是OF_object_tag_rec的集合

where object_tag_tbl is collection OF object_tag_rec

object_tag_rec是RECORD，具有以下字段(元素):

object_tag_rec is RECORD with below fields(elements):

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

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

When I am calling the above stored procedure using 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中做到这一点吗?

it gives me an error "element 0 value is unsupported". Is there any way to do this in Python?

推荐答案

您不能直接将 collections.namedtuple 用作Oracle对象类型.

You may not directly use collections.namedtuple as Oracle object type.

我在 Oracle Database 12c企业版12.2.0.1.0 中使用 Cx-Oracle-7.0.0 和 Python 3.6.4 ，它对我有用.

I tried the below on Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 with Cx-Oracle-7.0.0 and Python 3.6.4 and it works for me.

创建类型

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中使用callproc调用包含记录集合的存储过程的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！