I am currently unable to insert the data into a table by querying an XML whose file size is > 4 MB in PL/SQL. Please advice.
Code :
DECLARE
vTable VARCHAR2(400);
vExecSQL CLOB;
vInsColNames VARCHAR2(32767);
vSelColNames VARCHAR2(32767);
vXPath VARCHAR2(4000);
vInboundXML CLOB;
BEGIN
vTable := 'FUEL_TABLE';
vXPath = '/DataArea/FuelTableMaster/FUELRECORDS/FUELRECORD';
SELECT LISTAGG(column_name, ',') WITHIN GROUP (ORDER BY column_name) "ColNames"
INTO vInsColNames
FROM user_tab_columns WHERE table_name = vTable;
SELECT LISTAGG(DECODE(data_type,'DATE','TO_DATE(SUBSTR(i.'||column_name||',1,19),''YYYY-MM-DD
HH24:MI:SS'')',
'NUMBER','TO_NUMBER(i.'||column_name||')', 'i.'||column_name),',') WITHIN GROUP (ORDER BY column_name)
"ColNames1" INTO vSelColNames
FROM user_tab_columns WHERE table_name = vTable;
vExecSQL := 'INSERT INTO :1 (:2) SELECT :3 FROM XMLTABLE ('':4'' PASSING XMLTYPE('':5'') COLUMNS :6 )i;';
EXECUTE IMMEDIATE vExecSQL USING vTable,vInsColNames,vSelColNames,vXPath,vInboundXml,vInsColNames;
COMMIT;
END;