PL/SQL函数返回XML转表类型报错：表达式类型错误

问题背景 #

需要实现一个PL/SQL函数将XML数据转换为自定义表类型GCG_TableMFT返回，期望通过select * from TABLE ( cast( gcg_tempdata_mft () as GCG_TableMFT ) );调用，但保存函数时出现“expression is of wrong type”错误。

自定义类型代码：

type GCG_RecordMFT is record(         
    INVENTORY_ORGANIZATION_NAME VARCHAR2(200 CHAR),
    SCHEDULED_DATE              VARCHAR2(80 CHAR),--timestamp,
    WORK_ORDER                  VARCHAR2(80 CHAR),
    OPERATIONS_CODE             VARCHAR2(80 char),
    OPERATION_NAME              VARCHAR2(80 CHAR),
    MATERIAL_NAME               VARCHAR2(80 CHAR),
    MATERIAL_DESCRIPTION        VARCHAR2(150 CHAR),
    PLANNED_USAGE_QUANTITY      VARCHAR2(80 CHAR),--NUMBER(15,10),
    PRIMARY_UOMNAME             VARCHAR2(30 CHAR)
    );

type GCG_TableMFT is table of GCG_RecordMFT;

函数代码：

function gcg_tempdata_mft (   
        p_user                      in varchar2,
        p_password                  in varchar2,
        p_InventoryOrganizationName in varchar2,
        p_WorkOrder                 in varchar2,
        p_ScheduledStartDate        in varchar2,
        p_ScheduledEndDate          in varchar2
    )
    return GCG_TableMFT is
        l_envelope CLOB;
        l_response XMLTYPE;
        l_report_clob clob;
        l_report_blob blob;
        l_xml xmltype;
        l_ref_cur sys_refcursor;
        l_rec GCG_TableMFT := GCG_TableMFT();
    BEGIN

    FOR i in (SELECT
        INVENTORY_ORGANIZATION_NAME,
        --cast(to_timestamp_tz(SCHEDULED_DATE,'YYYY-MM-DD"T"HH24:MI:SS.FFTZH:TZM') as timestamp) SCHEDULED_DATE,
        SCHEDULED_DATE,
        WORK_ORDER,
        OPERATIONS_CODE,
        OPERATION_NAME,
        MATERIAL_NAME,
        MATERIAL_DESCRIPTION,
        PLANNED_USAGE_QUANTITY,
        PRIMARY_UOMNAME
    FROM  (
    select
        XMLTYPE( l_report_blob,3) as xml
    from dual
    ) xml_table,  
    xmltable(
            '/DATA_DS/MFT'
            passing xml_table.xml
            columns
            INVENTORY_ORGANIZATION_NAME VARCHAR2(200 CHAR)  PATH '/MFT/INVENTORYORGANIZATIONNAME',
            SCHEDULED_DATE              VARCHAR2(80 CHAR)   PATH '/MFT/SCHEDULEDDATE',
            WORK_ORDER                  VARCHAR2(80 CHAR)   PATH '/MFT/WORKORDER',
            OPERATIONS_CODE             VARCHAR2(80)        PATH '/MFT/CODE',
            OPERATION_NAME              VARCHAR2(80 CHAR)   PATH '/MFT/OPERATION',
            MATERIAL_NAME               VARCHAR2(80 CHAR)   PATH '/MFT/MATERIALNAME',
            MATERIAL_DESCRIPTION        VARCHAR2(150 CHAR)  PATH '/MFT/MATERIALDESCRIPTION',
            PLANNED_USAGE_QUANTITY      VARCHAR2(80 CHAR)   PATH '/MFT/REQUIREDQUANTITY',
            PRIMARY_UOMNAME             VARCHAR2(30 CHAR)   PATH '/MFT/PRIMARYUOMCODE'
        )) 
        loop
            dbms_output.put_line(i.INVENTORY_ORGANIZATION_NAME);
            l_rec.extend;
            l_rec(i) :=  GCG_RecordMFT( 
                'SILENCIO',
                '2022-07-25T12:35:00.000+00:00',
                'M_ES40',
                'AC_DU_EXP',
                'OPERATION_NAME',
                'fas',
                'fafafs',
                'sadadad',
                'asdasdada'
            -- i.INVENTORY_ORGANIZATION_NAME,
            -- i.SCHEDULED_DATE,
            -- i.WORK_ORDER,
            -- i.OPERATIONS_CODE,
            -- i.OPERATION_NAME,
            -- i.MATERIAL_NAME,
            -- i.MATERIAL_DESCRIPTION,
            -- i.PLANNED_USAGE_QUANTITY,
            -- i.PRIMARY_UOMNAME    
            );
        END LOOP;

        RETURN l_rec;
    END gcg_tempdata_mft;

错误原因分析 #

1. 类型作用域不兼容：你定义的GCG_RecordMFT（RECORD类型）和GCG_TableMFT是PL/SQL本地类型，只能在PL/SQL块内部使用，无法在SQL语句的TABLE()函数中直接引用，SQL层仅支持数据库级别的OBJECT和TABLE类型。
2. 表类型赋值索引错误：循环中使用l_rec(i)赋值，这里的i是循环行记录，不是表类型的索引，会导致类型不匹配。
3. XML解析路径冗余：XMLTABLE的PATH中写了/MFT/xxx，但已经指定了根路径/DATA_DS/MFT，冗余的路径会导致无法正确解析节点。

解决方案 #

步骤1：创建数据库级别的自定义类型 #

在SQL层（而非PL/SQL块内）创建OBJECT和TABLE类型，确保SQL语句可以识别：

CREATE OR REPLACE TYPE GCG_RecordMFT AS OBJECT (
    INVENTORY_ORGANIZATION_NAME VARCHAR2(200 CHAR),
    SCHEDULED_DATE              VARCHAR2(80 CHAR),
    WORK_ORDER                  VARCHAR2(80 CHAR),
    OPERATIONS_CODE             VARCHAR2(80 CHAR),
    OPERATION_NAME              VARCHAR2(80 CHAR),
    MATERIAL_NAME               VARCHAR2(80 CHAR),
    MATERIAL_DESCRIPTION        VARCHAR2(150 CHAR),
    PLANNED_USAGE_QUANTITY      VARCHAR2(80 CHAR),
    PRIMARY_UOMNAME             VARCHAR2(30 CHAR)
);
/

CREATE OR REPLACE TYPE GCG_TableMFT AS TABLE OF GCG_RecordMFT;
/

步骤2：修改函数代码适配类型并修正错误 #

CREATE OR REPLACE FUNCTION gcg_tempdata_mft (   
        p_user                      IN VARCHAR2,
        p_password                  IN VARCHAR2,
        p_InventoryOrganizationName IN VARCHAR2,
        p_WorkOrder                 IN VARCHAR2,
        p_ScheduledStartDate        IN VARCHAR2,
        p_ScheduledEndDate          IN VARCHAR2
    )
    RETURN GCG_TableMFT IS
        l_envelope CLOB;
        l_response XMLTYPE;
        l_report_clob CLOB;
        l_report_blob BLOB;
        l_xml XMLTYPE;
        l_ref_cur SYS_REFCURSOR;
        l_rec GCG_TableMFT := GCG_TableMFT();
    BEGIN
        -- 补充获取l_report_blob的逻辑（原代码未初始化该变量，需根据实际场景赋值）
        -- 例如：调用接口、读取文件或查询数据库获取XML的BLOB数据

        FOR i IN (SELECT
            INVENTORY_ORGANIZATION_NAME,
            SCHEDULED_DATE,
            WORK_ORDER,
            OPERATIONS_CODE,
            OPERATION_NAME,
            MATERIAL_NAME,
            MATERIAL_DESCRIPTION,
            PLANNED_USAGE_QUANTITY,
            PRIMARY_UOMNAME
        FROM  (
            SELECT XMLTYPE(l_report_blob, 3) AS xml FROM dual
        ) xml_table,  
        XMLTABLE(
                '/DATA_DS/MFT'
                PASSING xml_table.xml
                COLUMNS
                INVENTORY_ORGANIZATION_NAME VARCHAR2(200 CHAR)  PATH 'INVENTORYORGANIZATIONNAME',
                SCHEDULED_DATE              VARCHAR2(80 CHAR)   PATH 'SCHEDULEDDATE',
                WORK_ORDER                  VARCHAR2(80 CHAR)   PATH 'WORKORDER',
                OPERATIONS_CODE             VARCHAR2(80)        PATH 'CODE',
                OPERATION_NAME              VARCHAR2(80 CHAR)   PATH 'OPERATION',
                MATERIAL_NAME               VARCHAR2(80 CHAR)   PATH 'MATERIALNAME',
                MATERIAL_DESCRIPTION        VARCHAR2(150 CHAR)  PATH 'MATERIALDESCRIPTION',
                PLANNED_USAGE_QUANTITY      VARCHAR2(80 CHAR)   PATH 'REQUIREDQUANTITY',
                PRIMARY_UOMNAME             VARCHAR2(30 CHAR)   PATH 'PRIMARYUOMCODE'
            )) LOOP
            DBMS_OUTPUT.PUT_LINE(i.INVENTORY_ORGANIZATION_NAME);
            l_rec.EXTEND;
            -- 用表的COUNT值作为新元素的索引，替代循环变量i
            l_rec(l_rec.COUNT) := GCG_RecordMFT( 
                i.INVENTORY_ORGANIZATION_NAME,
                i.SCHEDULED_DATE,
                i.WORK_ORDER,
                i.OPERATIONS_CODE,
                i.OPERATION_NAME,
                i.MATERIAL_NAME,
                i.MATERIAL_DESCRIPTION,
                i.PLANNED_USAGE_QUANTITY,
                i.PRIMARY_UOMNAME    
            );
        END LOOP;

        RETURN l_rec;
    END gcg_tempdata_mft;
/

步骤3：正确调用函数 #

现在可以直接调用函数，无需额外CAST：

SELECT * FROM TABLE(gcg_tempdata_mft('你的用户名', '你的密码', '组织名称', '工单编号', '开始日期', '结束日期'));

内容的提问来源于stack exchange，提问作者Edisson Gabriel López