如何将数组对象传递给Oracle存储过程？

Question

怎么传递数组对象呢？

我通过 Python 使用 Oracledb 库连接了 Oracle 数据库。

#数据库连接

connection = oracledb.connect(user="hr", password=userpwd,dsn="dbhost.example.com/orclpdb") 

#cursor object
cursor = connection.cursor()

#声明一个输出变量

 out_values = cursor.var(int)

在这里，我需要把值传递给 Oracle 的存储过程。

array_values = ['u','U','G']
  cursor.callproc('schema.pkg.get_prc',[array_values ,9713979, '2', '2', 'N'])

我需要把上面的 array_values 传递给数组对象。怎么传递这个数组对象呢？

"错误信息": "ORA-06550: l: 调用 'get_prc' 时参数数量或类型错误"

下面是存储过程的内容：

 **schema.pkg.get_prc**(pt_enrol_data IN  tb_enrol_matching,
  p_enrol_log_id IN  NUMBER,
  p_columns IN  VARCHAR2,
  p_dynamic_columns IN  VARCHAR2)
  
  CREATE OR REPLACE TYPE schema.tb_enrol_matching
  IS OBJECT
  
  Email  VARCHAR2(4000),
  Forename  VARCHAR2(4000),
  Lastname VARCHAR2(4000),);

我不知道怎么传递数组对象并调用存储过程，问题出在哪里，我相信会有人帮忙。谢谢！

Answer 1

你数据库中这一列的数据类型不是数组，而是对象。所以你不需要从Python传递一个数组；你需要传递一个对象。

可以参考“7.12. 使用输入类型处理器更改绑定数据类型”的文档，里面有个例子：

# A standard Python object
class Building:

    def __init__(self, build_id, description, num_floors, date_built):
        self.building_id = build_id
        self.description = description
        self.num_floors = num_floors
        self.date_built = date_built

building = Building(1, "Skyscraper 1", 5, datetime.date(2001, 5, 24))

# Get Python representation of the Oracle user defined type UDT_BUILDING obj_type = con.gettype("UDT_BUILDING")

# convert a Python Building object to the Oracle user defined type
# UDT_BUILDING def building_in_converter(value):
    obj = obj_type.newobject()
    obj.BUILDINGID = value.building_id
    obj.DESCRIPTION = value.description
    obj.NUMFLOORS = value.num_floors
    obj.DATEBUILT = value.date_built
    return obj

def input_type_handler(cursor, value, num_elements):
    if isinstance(value, Building):
        return cursor.var(obj_type, arraysize=num_elements,
                          inconverter=building_in_converter)


# With the input type handler, the bound Python object is converted
# to the required Oracle object before being inserted cur.inputtypehandler = input_type_handler cur.execute("insert into
myTable values (:1, :2)", (1, building))

把Building改成和TB_ENROL_MATCHING的结构一致：

class EnrolMatching:
    def __init__(self, email, forename, lastname):
        self.email = email
        self.forename = forename
        self.lastname = lastname


ENROL_MATCHING_TYPE = connection.gettype("TB_ENROL_MATCHING")


def enrol_matching_in_converter(value):
    obj = ENROL_MATCHING_TYPE.newobject()
    obj.EMAIL = value.email
    obj.FORENAME = value.forename
    obj.LASTNAME = value.lastname
    return obj

def input_type_handler(cursor, value, num_elements):
    if isinstance(value, EnrolMatching):
        return cursor.var(obj_type, arraysize=num_elements,
                          inconverter=enrol_matching_in_converter)


cursor.inputtypehandler = input_type_handler
cursor.callproc(
    "schema.pkg.get_prc",
    (EnrolMatching("u", "U", "G"), 9713979, "2", "N"),
)

注意：这个代码没有经过测试，因为我没有你的表或过程来进行测试，但你应该能大致明白怎么实现。

如果你想把代码简化到最基本的样子，可以这样写：

ENROL_MATCHING_TYPE = connection.gettype("TB_ENROL_MATCHING")
enrol_matching = ENROL_MATCHING_TYPE.newobject()
enrol_matching.EMAIL = "u"
enrol_matching.FORENAME = "U"
enrol_matching.LASTNAME = "G"
cursor.callproc("schema.pkg.get_prc", (enrol_matching, 9713979, "2", "N"))

注意2：同样没有经过测试，原因和上面一样。