如何使用Python cx_Oracle读取Oracle自定义类型COMPARISON_TYPE的字段值

核心原因 #

你直接访问scan_info无法取到字段，是因为scan_info是cx_Oracle的绑定变量对象，并非存储实际数据的RECORD实例。

操作步骤 #

1. 调用callfunc执行存储过程后，先通过绑定变量的getvalue()方法提取实际的RECORD实例
2. 实例的字段名与Oracle定义的COMPARISON_TYPE字段名一致，默认大写，可直接通过属性访问

完整代码示例 #

try:
    with cx_Oracle.connect(
            config.username,
            config.password,
            config.dsn,
            encoding=config.encoding) as connection:
        with connection.cursor() as cursor:
            comparison_type = connection.gettype("DBMS_COMPARISON.COMPARISON_TYPE")
            scan_info = cursor.var(comparison_type)

            result = cursor.callfunc("dbms_comparison.compare", bool, [comparison_name, scan_info, None, None, True])
            
            # 提取RECORD实例
            scan_record = scan_info.getvalue()
            
            # 读取各字段值
            scan_id = scan_record.SCAN_ID
            loc_rows_merged = scan_record.LOC_ROWS_MERGED
            rmt_rows_merged = scan_record.RMT_ROWS_MERGED
            loc_rows_deleted = scan_record.LOC_ROWS_DELETED
            rmt_rows_deleted = scan_record.RMT_ROWS_DELETED
            
            # 可按需使用上述字段值
            print(f"scan_id: {scan_id}, loc_rows_merged: {loc_rows_merged}")
except cx_Oracle.Error as error:
    print(error)

额外说明 #

如果不确定字段名的拼写，可以执行print(dir(scan_record))查看实例的所有可用属性，过滤掉双下划线开头的内置属性后，剩余的就是COMPARISON_TYPE的字段名。

内容的提问来源于stack exchange，提问作者Alain d'Espaignet