sqloracle-databaseplsqloracle-apexplsqldeveloper
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;
Thank you, regards.
Solution
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 |
- Math.Sin() gives incorrect value
- How to run my python script when the sunOS is start booting
- Express-session: not resetting cookie expiration on each request
- Getting a stack overflow exception when normalizing a vector
- Edit default summary function in R gives error for multiple variables
- What was a For loop? Why isn't it needed in R?
- How to use download button in shiny and save results in various formats (csv, texte, pdf, spss...)?
- Why are there two assignment operators, `<-` and `->` in R?
- lm()$assign: what is it?
- How to get the value of list(...) in R and S functions
- Design matrix for MLM from library(lme4) with fixed and random effects
- how to generate elements not included in my sample
- Create a matrix with gradually changing values without a for loop
- Emacs ESS and S-plus ( S+ ) 8.1 compatability
- How to lag date-index in a time-series in R?
- Nonlinear regression in R / S
- Calling R from S-Plus?