Using Execute Immediate to insert the data by querying the XML whose size > 4 MB

46 views Asked by At

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;
0

There are 0 answers