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

Thank you, regards.

CodePudding user response：

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:

fiddle