xmltype.getClobVal : ORA-06502: PL/SQL: numeric or value error

Question

My goal is to create an xml file through Oracle 10g, I use the library dbms_xmldom to achieve this. When I try to retrieve the clob created in the XMLType via getClobVal function, the server displays :

ORA-06502: PL / SQL: numeric or value error

My Program:

DECLARE
   l_xmltype XMLTYPE;
   l_domdoc dbms_xmldom.DOMDocument;
   l_root_node dbms_xmldom.DOMNode;
   l_reunion_element dbms_xmldom.DOMElement;
   l_reunions_node dbms_xmldom.DOMNode;
   l_r_element dbms_xmldom.DOMElement;
   l_r_node dbms_xmldom.DOMNode;
   l_reunion_element dbms_xmldom.DOMElement;
   l_reunion_node dbms_xmldom.DOMNode;
   l_course_element dbms_xmldom.DOMElement;
   l_course_node dbms_xmldom.DOMNode;
   l_eng_element  DBMS_XMLDOM.DOMELEMENT;
   l_eng_node DBMS_XMLDOM.DOMNODE;
   l_tp_element  DBMS_XMLDOM.DOMELEMENT;
   l_tp_node DBMS_XMLDOM.DOMNODE;

BEGIN
   -- Create an empty XML document
   l_domdoc := dbms_xmldom.newDomDocument;

   -- Create a root node
   l_root_node := dbms_xmldom.makeNode(l_domdoc);

   -- Create a new node Departments and add it to the root node
   --l_reunion_element := 
   l_reunions_node := dbms_xmldom.appendChild(l_root_node,dbms_xmldom.makeNode(dbms_xmldom.createElement(l_domdoc, 'donnees_reference_externes' )));
    -- réunion 
   FOR re IN ( select rc.identifiant, nvl(rc.date_report, RC.DATE_REUNION) date_reunion from reunion_course rc where date_reunion = '24/01/2014' )
   LOOP

        l_r_element := dbms_xmldom.createElement(l_domdoc, 'grep' );
      dbms_xmldom.setAttribute(l_r_element, 'ID', re.identifiant);
      dbms_xmldom.setAttribute(l_r_element, 'Date', re.date_reunion);
      l_r_node := dbms_xmldom.appendChild(l_reunions_node,dbms_xmldom.makeNode(l_r_element));

      -- course de la réunion selectionnée
      FOR rc in ( select * from course where id_reunion_course = re.identifiant ) 
      LOOP
      l_course_element := DBMS_XMLDOM.CREATEELEMENT(l_domdoc, 'ep');
      DBMS_XMLDOM.SETATTRIBUTE(l_course_element, 'ID', rc.identifiant);
      DBMS_XMLDOM.SETATTRIBUTE(l_course_element,'Prix', rc.nom_prix);
      l_course_node := DBMS_XMLDOM.APPENDCHILD(l_r_node, DBMS_XMLDOM.MAKENODE(l_course_element));

        FOR rg in ( select SIT.NUMERO_PMU, CH.NOM nom_cheval from situation_engagement_course sit, cheval ch where SIT.ID_CHEVAL = ch.identifiant and sit.id_course = rc.identifiant and SIT.CODE_ETAT = 'PARPR' ) 
        LOOP
            l_eng_element := DBMS_XMLDOM.CREATEELEMENT(l_domdoc, 'ap');
            DBMS_XMLDOM.SETATTRIBUTE(l_eng_element, 'NomCheval', rg.nom_cheval);
            DBMS_XMLDOM.SETATTRIBUTE(l_eng_element,'NPMU', rg.numero_pmu);
             l_eng_node := DBMS_XMLDOM.APPENDCHILD(l_course_node, DBMS_XMLDOM.MAKENODE(l_eng_element));

        END LOOP;  

      l_tp_element := DBMS_XMLDOM.CREATEELEMENT(l_domdoc, 'ofr');
      DBMS_XMLDOM.SETATTRIBUTE(l_tp_element, 'av_pg', rc.code_type_evenement_pari);
      DBMS_XMLDOM.SETATTRIBUTE(l_tp_element,'epba_ofr', 1);
      l_tp_node := DBMS_XMLDOM.APPENDCHILD(l_course_node, DBMS_XMLDOM.MAKENODE(l_tp_element));

      END LOOP;

   END LOOP;

   l_xmltype := dbms_xmldom.getXmlType(l_domdoc);
   dbms_xmldom.freeDocument(l_domdoc);

   dbms_output.put_line(l_xmltype.getClobVal);   -------------->   in this line ERROR Displays : ORA-06502: PL/SQL: numeric or value error ** 
END;

---

Thanks.

Answer 1

l_xmltype.getClobVal ()

(I am not sure that this will be enough)

If you call getClobVal() programmatically, you must explicitly free such a temporary CLOB value when finished with it. You can do this by calling PL/SQL method DBMS_LOB.freeTemporary()

Answer 2

There are limitations to dbms_output.put_line that cause issues when processing large data : namely, clobs larger than 32k do fail to be printed this way, with error ORA-06502.

If you don’t actually need to print the clob, just comment out that line and you should be fine.

Else, have a look at this StackOverflow post, that provides a solution based on a procedure that iterates through the clob and prints out one smaller chunk at a time.