I'm running into ORA-06512 issue when attempting to output a XML data. Can you please help?
create table test2 (
insert into test2
select rownum, to_char(sysdate, 'YYYY'), 'E'||rownum, 'S'||(rownum+2)*2, 'USA'
connect by rownum<=500;
select * from test2;
l_xmltype := dbms_xmlgen.getxmltype('SELECT * from test2' );
Message was edited by: User528456-OC
DBMS_OUTPUT does not work with CLOB
It wants VARCHAR2
varchar2 works for smaller size data.
varchar2 works for smaller size data.
The sun rises in the East.
Functions have datatype limitations that must be respected; otherwise error gets thrown.
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.
OPEN l_refcursor FOR SELECT * from test2;
l_xmltype := XMLTYPE(l_refcursor);
As John mentioned, the dbms_output.put_line procedure is not able to "put" a CLOB:
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
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:
This procedure writes content of a
CLOBinto a file.
DBMS_XSLPROCESSOR.CLOB2FILE( cl IN CLOB; flocation IN VARCHAR2, fname IN VARCHAR2, csid IN NUMBER:=0);
You are running into more than just "ORA-06512":
Wrote file afiedt.buf
2 l_xmltype xmltype;
3 v_clob clob;
6 l_xmltype := dbms_xmlgen.getxmltype('SELECT * from test2');
ERROR at line 1:
ORA-06502: PL/SQL: numeric or value error
ORA-06512: at line 7
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.
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 := nvl(DBMS_LOB.GetLength(pClob), 0); -- Null CLOB = null length
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;
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.
v_clob := dbms_xmlgen.getxml('SELECT * from test2');
Again if its just to check the output :
select dbms_xmlgen.getxml ( 'select * from test2' ) xml from dual;