Idea Summary

While downloading IG in Excel XLSX format sometimes we get AJAX error which is traced to wwv_flow_xlsx line 1469 which states 

L_XML := L_XML || '<tableColumn id="' || L_IDX || '" name="' || SUBSTR( SYS.DBMS_XMLGEN.CONVERT( P_COLUMNS( L_IDX ).COLUMN_NAME ), 1 , 255 ) || ‘"/>’;

Problem: columns that contain long names via concatenation exceeds 32k or large number of columns.

Use Case
Sometimes huge number of columns are unavoidable. Industry computers, laboratory equipment etc., use specialized software. Case is laboratory HPLC and plasma mass spectrophotometer. It measures all elements on chemical periodic table of elements + isotopes. With columns such as ID for sample number, date, label & notes it adds to 820 columns. Column names are for example iron, 54FE and two more columns 54FE_HE and 54FE_HEHE where He and HEHe depicts type of gas used (helium and some other helium isotope). The equipment spits out CSV with all those 820 columns. I have devised a way for lab techs to split that CSV into 3 XLSX and via APEX loader load it into database table. There they do all their work, calculations and other sci/fi & CSI stuff but when it comes to export it from Interactive grid, AJAX error. I have managed to set them 3 different IG reports so they download 3 excel files and combine them in the end into 1 XLSX but even thou those gals and guys are genii in their laboratory field, they are sharp as marble when it comes to IT.

Preferred Solution (Optional)
Don't be mad at me for looking into your proprietary code but I just want to help.

This solution can work for both column names and column data as well since its all text in XLSX (XML) so it doesn't matter if the data type is varchar2 or date or number.

Procedure ADD_TABLE in package wwv_flow_xlsx starts at line 1386 and ends in 1485.

But here is the part where the columns are added.

1465     L_IDX := P_COLUMNS.FIRST;
1466 
1467     WHILE L_IDX IS NOT NULL LOOP
1468 
1469         L_XML := L_XML || '<tableColumn id="' || L_IDX || '" name="' || SUBSTR( SYS.DBMS_XMLGEN.CONVERT( P_COLUMNS( L_IDX ).COLUMN_NAME ), 1 , 255 ) || '"/>';
1470 
1471         L_IDX := P_COLUMNS.NEXT( L_IDX );
1472     END LOOP;

Suggestion is to make chunks and add to L_XML. You will know better how to loop and save.

1467     WHILE L_IDX IS NOT NULL LOOP
1468         -- Temporary variable to store each chunk of XML data
1469         DECLARE
1470             L_XML_PART VARCHAR2(32767);  -- Temporary chunk to hold the XML for a row
1471         BEGIN
1472             -- Initialize the chunk variable for this iteration
1473             L_XML_PART := '<tableColumn id="' || L_IDX || '" name="' || 
1474                            SUBSTR(SYS.DBMS_XMLGEN.CONVERT(P_COLUMNS(L_IDX).COLUMN_NAME), 1, 255) || '"/>';
1475             
1476             -- Check if concatenating this chunk would exceed 32k for L_XML
1477             IF LENGTH(L_XML) + LENGTH(L_XML_PART) > 32767 THEN
1478                 -- If the limit is exceeded, process the current chunk and start a new one
1479                 -- You can either store this part in a temporary table, write it to a file, or process further
1480                 -- For example, storing the current chunk temporarily:
1481                 -- INSERT INTO TEMP_XML_TABLE (xml_part) VALUES (L_XML);
1482                 
1483                 -- Clear the current L_XML and start adding the new chunk
1484                 L_XML := L_XML_PART;
1485             ELSE
1486                 -- Otherwise, safely concatenate the chunk to the main L_XML
1487                 L_XML := L_XML || L_XML_PART;
1488             END IF;
1489             
1490             -- Move to the next column index
1491             L_IDX := P_COLUMNS.NEXT(L_IDX);
1492         END;
1493     END LOOP;