CREATE OR REPLACE PROCEDURE process_large_xml1(
  p_xml_data  IN  CLOB,          
  p_out_value OUT VARCHAR2       
) AS
BEGIN

  BEGIN
    FOR rec IN (
      SELECT name_value, description_value
      FROM XMLTABLE('/root/items/item'
                     PASSING XMLTYPE(p_xml_data)
                     COLUMNS 
                       name_value VARCHAR2(100) PATH 'name',
                       description_value VARCHAR2(200) PATH 'description')
    ) LOOP
      DBMS_OUTPUT.PUT_LINE('Item Name: ' || rec.name_value);
      DBMS_OUTPUT.PUT_LINE('Item Description: ' || rec.description_value);

    END LOOP;
    
    p_out_value := 'XML data processed successfully';
  
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      p_out_value := 'No data found in XML for the specified path';
      DBMS_OUTPUT.PUT_LINE('No data found in XML');
    WHEN OTHERS THEN
      p_out_value := 'Error occurred';
      DBMS_OUTPUT.PUT_LINE('Error while processing XML: ' || SQLERRM);
  END;
END process_large_xml1;
/

SQL> DECLARE
  2    l_output_value VARCHAR2(100);
  3  l_xml_data clob := '
  4
  5  <root>
  6      <items>
  7          <item>
  8              <name>Item 1</name>
  9              <description>This is a detailed d
...
344          </item>
345          <!-- Repeat as needed to reach 5000 characters -->
346      </items>
347  </root>
348  ';
349
350  BEGIN
351
352    process_large_xml1(l_xml_data, l_output_value);
353
354    DBMS_OUTPUT.PUT_LINE (l_output_value);
355
356  END;
357  /
l_xml_data clob := '
                   *
ERROR at line 3:
ORA-06550: line 3, column 20:
PLS-00172: string literal too long

DECLARE

  l_output_value VARCHAR2(100);
  l_xml_data clob;

BEGIN

  l_xml_data := to_clob('<first 4000 characters>');
  l_xml_data := l_xml_data || '<next 4000 characters>');

<repeat until you have everything>

  process_large_xml1(l_xml_data, l_output_value);
  
  DBMS_OUTPUT.PUT_LINE (l_output_value);

END;
/