XMLType.getClobval:ORA-06502:PL/SQL:数值或值错误

Question

我的程序：

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;

谢了。

Answer 1

dbms_output.put_line有一些限制，在处理大数据时会导致问题：也就是说，大于32K的CLOB不能以这种方式打印，错误为ORA-06502。

如果您实际上并不需要打印clob，只需注释掉这一行，就可以了。

另外，请看这篇StackOverflow文章，它提供了一个基于遍历clob并每次打印出一个较小块的过程的解决方案。