CODEWITHSUNDEEP
sqloracle-databaseplsqloracle-apexplsqldeveloper
Edisson Gabriel López
Edisson Gabriel López

Reputation: 77

PL/SQL Return XML data in table - expression is of wrong type

I'm trying to return an XML in table format that I still can't get back as if it were a select * from TABLE ( cast( gcg_tempdata_mft () as GCG_TableMFT ) ); or something like that and I don't know what I'm doing wrong with a function, but when saving the changes I get this error. I'm basing it on this Function return sys_refcursor call from sql with specific columns

    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 code:

 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;

enter image description here

Thank you, regards.

Upvotes: 0

Views: 228

Answers (1)

MT0
MT0

Reputation: 167962

A RECORD is a PL/SQL only data type. If you want to return a collection to use in an SQL statement then you want to use an OBJECT (defined in the SQL scope) data type, as the linked question does.

CREATE TYPE GCG_RecordMFT is OBJECT(         
        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)
);

Then you can simplify the function by making it PIPELINED and a few other minor changes:

CREATE 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 PIPELINED
IS
  -- Not sure why this was a BLOB when you are dealing with text data.
  l_report CLOB;
BEGIN
  -- Do something to populate l_report.
  l_report := EMPTY_CLOB() || '<DATA_DS><MFT>
      <INVENTORYORGANIZATIONNAME>AAA</INVENTORYORGANIZATIONNAME>
    </MFT></DATA_DS>';

  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   xmltable(
             '/DATA_DS/MFT'
              passing XMLTYPE( l_report )
              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
    PIPE ROW(
      GCG_RecordMFT( 
        i.INVENTORY_ORGANIZATION_NAME,
        '2022-07-25T12:35:00.000+00:00',
        'M_ES40',
        'AC_DU_EXP',
        'OPERATION_NAME',
        'fas',
        'fafafs',
        'sadadad',
        'asdasdada'
        -- 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;
END gcg_tempdata_mft;
/

Then:

SELECT *
FROM   TABLE(gcg_tempdata_mft('A', 'B', 'C', 'D', 'E', 'F'))

Outputs:

INVENTORY_ORGANIZATION_NAME SCHEDULED_DATE WORK_ORDER OPERATIONS_CODE OPERATION_NAME MATERIAL_NAME MATERIAL_DESCRIPTION PLANNED_USAGE_QUANTITY PRIMARY_UOMNAME
AAA 2022-07-25T12:35:00.000+00:00 M_ES40 AC_DU_EXP OPERATION_NAME fas fafafs sadadad asdasdada

fiddle

Upvotes: 1

Related Questions