10 Replies Latest reply on Mar 14, 2018 12:57 PM by BluShadow

    XMLTYPE.getClobVal - ORA-06512

    Dairy Land

      I'm running into ORA-06512 issue when attempting to output a XML data.  Can you please help?

       

       

      create table test2 (

      pk number,

      year varchar2(4),

      entity varchar2(100),

      state varchar2(10),

      country varchar2(50)

      );

       

       

      insert into test2

      select rownum, to_char(sysdate, 'YYYY'), 'E'||rownum, 'S'||(rownum+2)*2, 'USA'

      from dual

      connect by rownum<=500;

       

       

      select * from test2;

       

       

      declare

         l_xmltype xmltype;

         v_clob clob;

      begin

          dbms_output.enable(1000000);

          l_xmltype := dbms_xmlgen.getxmltype('SELECT * from test2' );

       

          dbms_output.put_line(l_xmltype.getclobval);

      end;

      /

       

      Thanks

       

      Message was edited by: User528456-OC

        • 1. Re: XMLTYPE.getClobVal - ORA-06512
          John Thorton

          User528456-OC wrote:

           

          I'm running into ORA-06512 issue when attempting to output a XML data. Can you please help?

           

           

          create table test2 (

          pk number,

          year varchar2(4),

          entity varchar2(100),

          state varchar2(10),

          country varchar2(50)

          );

           

           

          insert into test2

          select rownum, to_char(sysdate, 'YYYY'), 'E'||rownum, 'S'||(rownum+2)*2, 'USA'

          from dual

          connect by rownum<=500;

           

           

          select * from test2;

           

           

          declare

          l_xmltype xmltype;

          v_clob clob;

          begin

          dbms_output.enable(1000000);

          l_xmltype := dbms_xmlgen.getxmltype('SELECT * from test2' );

           

          dbms_output.put_line(l_xmltype.getclobval);

          end;

          /

           

          Thanks

           

          Message was edited by: User528456-OC

          DBMS_OUTPUT does not work with CLOB

          It wants VARCHAR2

          • 2. Re: XMLTYPE.getClobVal - ORA-06512
            Dairy Land

            varchar2 works for smaller size data.

            • 3. Re: XMLTYPE.getClobVal - ORA-06512
              John Thorton

              User528456-OC wrote:

               

              varchar2 works for smaller size data.

              The sun rises in the East.

               

              Functions have datatype limitations that must be respected; otherwise error gets thrown.

              • 4. Re: XMLTYPE.getClobVal - ORA-06512
                Dairy Land

                I guess that is why I use clob. That example works perfectly when you limit the number of rows to a smaller size, say 50. But my situation does not guarantee that small a size. I'm looking for a fix in cases like that.

                 

                Here is another approach.

                DECLARE

                   l_refcursor SYS_REFCURSOR;

                   l_xmltype XMLTYPE;

                 

                 

                BEGIN

                   OPEN l_refcursor FOR SELECT * from test2;

                   l_xmltype := XMLTYPE(l_refcursor);

                   dbms_output.put_line(l_xmltype.getClobVal);

                END;

                /

                • 5. Re: XMLTYPE.getClobVal - ORA-06512
                  L. Fernigrini

                  As John mentioned, the dbms_output.put_line procedure is not able to "put" a CLOB:

                   

                  https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_output.htm#i1000105

                   

                  Syntax

                  DBMS_OUTPUT.PUT_LINE ( item IN VARCHAR2); 

                  so,  you will have to break the CLOB into chunks that can be put by the "put_line" procedure.

                   

                  By the way, dbms_output is not right way to return information unless you are just doing some ad-hoc debugging

                  • 6. Re: XMLTYPE.getClobVal - ORA-06512
                    Manik

                    If this is something for checking the output instead of using dbms_output you can very well use the oracle supplied package to move your clob content into file with simple instruction in your program like this :

                     

                    declare
                                 l_xmltype   xmltype;
                                 v_clob      clob;
                    begin
                                 l_xmltype   := dbms_xmlgen.getxmltype ( 'SELECT * from test2' );
                                 DBMS_XSLPROCESSOR.clob2file (l_xmltype.getclobval,'ORACLE_FILE_DIR','checkxml.txt',0 );
                    end;
                    

                     

                    And the file generated (checkxml.txt) in that particular oracle directory would have the output of your clob.

                    Checked in Oracle 11gr2.

                     

                    For more information on clob2file:

                     

                    https://docs.oracle.com/cd/B19306_01/appdev.102/b14258/d_xslpro.htm#i1007362

                     

                    CLOB2FILE Procedure

                    This procedure writes content of a CLOB into a file.

                    Syntax

                    DBMS_XSLPROCESSOR.CLOB2FILE( cl          IN  CLOB; flocation   IN  VARCHAR2, fname       IN  VARCHAR2, csid        IN  NUMBER:=0); 

                     

                    Cheers,

                    Manik.

                    • 7. Re: XMLTYPE.getClobVal - ORA-06512
                      Gaz in Oz

                      You are running into more than just "ORA-06512":

                      SQL> ed

                      Wrote file afiedt.buf

                       

                        1  declare

                        2     l_xmltype xmltype;

                        3     v_clob clob;

                        4  begin

                        5     dbms_output.enable(1000000);

                        6     l_xmltype := dbms_xmlgen.getxmltype('SELECT * from test2');

                        7     dbms_output.put_line(l_xmltype.getclobval);

                        8* end;

                      SQL> /

                      declare

                      *

                      ERROR at line 1:

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

                      ORA-06512: at line 7

                       

                      SQL>

                      F:\>oerr ORA-06502

                      06502, 00000, "PL/SQL: numeric or value error%s"

                      // *Cause: An arithmetic, numeric, string, conversion, or constraint error

                      //         occurred. For example, this error occurs if an attempt is made to

                      //         assign the value NULL to a variable declared NOT NULL, or if an

                      //         attempt is made to assign an integer larger than 99 to a variable

                      //         declared NUMBER(2).

                      // *Action: Change the data, how it is manipulated, or how it is declared so

                      //          that values do not violate constraints.

                       

                      Note the phrase "conversion".

                      As dbms_output.put_line expects VARCHAR2 data, then the CLOB is trying to be converted to VARCHAR2.

                      The DBMS_OUTPUT.PUT_LINE() length limit is 32767 bytes. You are trying to squeeze 60527 bytes into it. That'll never work.

                      • 8. Re: XMLTYPE.getClobVal - ORA-06512
                        Paulzip

                        DBMS_Output.Put_line works on varchar2s, you are trying to pass a clob.  You can write your own routine for achieving this though...

                         

                        create or replace procedure PutLineClob(pClob CLOB) is

                          MAX_LINE_LENGTH constant integer := 32767; -- 32k in 10gR2

                          MAX_BUFFER_SIZE constant integer := 1000000;

                          vPos integer := 1;

                          vLen integer;

                        begin

                          vLen := nvl(DBMS_LOB.GetLength(pClob), 0); -- Null CLOB = null length

                          loop

                            exit when (vPos > vLen) or (vLen > MAX_BUFFER_SIZE);

                            DBMS_OUTPUT.Put_Line(DBMS_LOB.Substr(pClob, MAX_LINE_LENGTH, vPos));

                            vPos := vPos + MAX_LINE_LENGTH;

                          end loop;

                        end;

                         

                        As you are simply getting the XML as a clob, you may as well use dbms_xmlgen.getxml when you call this routine.  If you need the XMLType, you should be using XMLSerialize, not getClobVal - which is deprecated.

                         

                        declare

                          v_clob clob;

                        begin

                          dbms_output.enable(1000000);

                          v_clob := dbms_xmlgen.getxml('SELECT * from test2');

                          PutLineClob(v_clob);

                        end;

                        • 9. Re: XMLTYPE.getClobVal - ORA-06512
                          Manik

                          Again if its just to check the output :

                           

                          select dbms_xmlgen.getxml ( 'select * from test2' ) xml from dual;
                          

                           

                          Cheers,

                          Manik.

                          • 10. Re: XMLTYPE.getClobVal - ORA-06512
                            BluShadow

                            And while you're at it, take a read of the community document: PL/SQL 101 - DBMS_OUTPUT

                             

                            To learn when you should and shouldn't use dbms_output.