CLOB String litteral too long

Question

I'm facing an issue here, and wondering if there's an easy solution, I'm using the PL/SQL code below. the goal is to iterate one xml node, and update a table with the content of all which is between the opening and closing of this node. The function works well for that.

Issue is that I want to process a huge XML string, more than 150K lines, and 6M characters. and I get the 'string litteral too long' error, even if a CLOB is supposed to handle huge content. So I'm wondering if there's an easy way to process this anyway, or if this is another type of restriction can be modified elsewhere ?

DECLARE
v_XML_INPUT CLOB := '<xmlString>';
v_COUNT                 NUMBER;
v_TOTAL_COUNT           NUMBER;
v_XML_SECTION           CLOB;
v_TEMPLATE_FULL_NAME    VARCHAR2(128);
BEGIN

SELECT REGEXP_COUNT(v_XML_INPUT, '<templates>', 1) INTO v_TOTAL_COUNT FROM DUAL;
DBMS_OUTPUT.PUT_LINE(v_TOTAL_COUNT);
v_COUNT := 1;

WHILE v_COUNT <= v_TOTAL_COUNT
LOOP

    SELECT REGEXP_SUBSTR(v_XML_INPUT, '(\<templates\W)(.+?)(\</templates\W)',1,v_COUNT,'n') INTO v_XML_SECTION FROM DUAL;
    
    SELECT
    xt.TEMPLATE_FULL_NAME
    INTO v_TEMPLATE_FULL_NAME
    FROM XMLTABLE(
        '/templates'
        PASSING XMLType(v_XML_SECTION)
        COLUMNS TEMPLATE_FULL_NAME VARCHAR2(128) PATH 'templateFullName'
    ) xt;
    DBMS_OUTPUT.PUT_LINE(v_TEMPLATE_FULL_NAME);
    
    UPDATE 
        TEMPLATES
    SET 
        LAST_UPDATE = SYSDATE,
        XML_TEMPLATE_SOURCE = v_XML_SECTION
    WHERE TEMPLATE_FULL_NAME = v_TEMPLATE_FULL_NAME;
    
    v_COUNT := v_COUNT +1;

END LOOP;
COMMIT;
END;

Thanks in advance for your insight :-)

Answer 1

The problem is that the XML that you are pasting is too large to be stored in a VARCHAR2. When you are pasting it into the PL/SQL code, it is stored in a VARCHAR2 before being converted to a CLOB.

Instead of doing this (notice how the XML is all in one block):

DECLARE
    v_XML_INPUT   CLOB := '<xml>
    <subelement>
        <subelement2>123</subelement2>
    </subelement>
</xml>';
BEGIN
    NULL;
END;
/

Try splitting the lines of the XML into separate VARCHAR2 blocks with a text editor (very easy with Visual Studio Code), then concatenate them together when assigning to the CLOB:

DECLARE
    v_XML_INPUT   CLOB
        :=    '<xml>'
           || '    <subelement>'
           || '        <subelement2>123</subelement2>'
           || '    </subelement>'
           || '</xml>';
BEGIN
    NULL;
END;
/