DBMS_OUTPUT.PUT_LINE(<xmltype>.getClobVal) returns PLSQL Numeric Value error

Paulzip

Use XMLSerialize to output the XML as CLOB directly via the query.

https://docs.oracle.com/cd/B19306_01/server.102/b14200/functions227.htm

Forget DBMS_OUTPUT here. Wrong tool for the job.

1 person found this helpful

Solomon Yakobson

DBMS_OUTPUT.PUT_LINE accepts VARCHAR2. And VARCHAR2 in PL/SQL is limited to 32767 bytes. As a result, you can't use DBMS_OUTPUT.PUT_LINE for CLOBs exceeding 32767 bytes.

SY.

Nimish Garg

you can use following function to print clob (copied from Ronalds Blog)

procedure print_clob( p_clob in clob ) is
  v_offset number := 1;
  v_chunk_size number := 10000;
  begin
  loop
  exit when v_offset > dbms_lob.getlength(p_clob);
  dbms_output.put_line( dbms_lob.substr( p_clob, v_chunk_size, v_offset ) );
  v_offset := v_offset + v_chunk_size;
  end loop;
  end print_clob;

1 person found this helpful

BluShadow

Correction, that code doesn't "print" anything. It puts fragments of clob data into the DBMS_OUTPUT buffer, and worse than that, it's chopping up things and inserting newline characters in places that could be half way through element names, or half way through data, thus potentially causing issues with the XML content.

The correct way is to pass the data back to a client tool that can process and render the content correctly.

1 person found this helpful

Marwim

You can "SPOOL" a CLOB

But as said above, dbms_output is usually the wrong tool.

Regards

Marcus

User_BPLEL

Is there a source for good examples on XMLSerialize? I didn't find very informative ones on the Oracle Documentation . May be something for beginners in XML ?

Thanks

-Madhuri

User_BPLEL

Thanks to all for the prompt guidance. I will pursue the options suggested and get back to this forum as necessary.

-Madhuri

Paulzip

XMLSerialize takes either a valid XML document (a whole bit of XML) or XML content (part of an XML document, which as it wasn't a full document would fail XML doc validation) and serializes it (makes it into a stream of text) which by default is a CLOB.

So, say you had a table called data, with an XMLType column called MY_XML, you can query it as text very simply using....

select XMLSerialize(document MY_XML as clob)

from data

Much better than DBMS_OUTPUT, as anything that can run a query can read the XML as text!

In 11g onwards there are some extra options like having the ability to beautiful the serialization by indentation :

select XMLSerialize(document MY_XML as clob indent size = 2)

from data

Hope this helps.

User_BPLEL

Actually, my data is in a non-XML format ( a regular Oracle table structure) and I'm looking to extract it into XML format.

BluShadow

I think you better make up your mind.

Your opening question states:

I'm using XMLTYPE to display a SQL query results in XML Format

So... is it XML or is it non-XML.... and therefore what on Earth is the issue?

Paulzip

3046139 wrote:

Actually, my data is in a non-XML format ( a regular Oracle table structure) and I'm looking to extract it into XML format.

That's not what you said originally!!!!

Here's one way to output a table's data as XMLType in a standard form :

select DBMS_XMLGEN.GetXMLType('select * from MyTable') from dual

or (if you want output as a CLOB)

select DBMS_XMLGEN.GetXML('select * from MyTable') from dual

Please post accurate information in your posts in future.

User_BPLEL

Hi Paul, I'm sorry for the confusion. Blame it on my ignorance of XML. My requirement is to retrieve data from a table but have it output in XML format. The data stored in the table is non-XML. I came across a few examples which suggested using code as below

PROCEDURE XXXX

l_refcursor SYS_REFCURSOR;

l_xmltype XMLTYPE;

BEGIN

OPEN l_refcursor FOR select ...

l_xmltype := XMLTYPE(l_refcursor);

DBMS_OUTPUT.PUT_LINE(l_xmltype.getClobVal);

END;

User_BPLEL

The code works as long as I limit the data to about 30 rows. But when I try to retrieve the entire table contents, I get the Numeric value error. I have over 4000 rows in the table.

Marwim

3046139 wrote:

The code works as long as I limit the data to about 30 rows. But when I try to retrieve the entire table contents, I get the Numeric value error. I have over 4000 rows in the table.

As told above dbms_output.put_line is limited to VARCHAR2. When you need to spool more characters you have to use a mechanism that separates the content into chunks. I linked an example in my first reply.

Nevertheless spooling XML/CLOB via dbms_output should be considered as second best solution.

unknown-7404

I'm using XMLTYPE to display a SQL query results in XML Format. But the DBMS_OUTPUT.PUT_LINE errors for no of rows greater than 29. We have over 4000 rows of data that need to be displayed in XML Format. Which would be the best way to do this?

Just use Oracle's free Sql Developer, query the data, then save the result to a file.

select dbms_xmlgen.getxml('select * from emp_language') from dual

<?xml version="1.0"?>

<ROW>

</ROW>

<ROW>

</ROW>

<ROW>

</ROW>

<ROW>

That is just a few rows of thousands.

I just executed the query - double-clicked on the result - and then used the 'pencil' icon to open the clob in a text editor.

ALL ROWS were there - in XML format. See the DBMS_XMLGEN package for how to customize the tags if you want.

SQL & PL/SQL

DBMS_OUTPUT.PUT_LINE(<xmltype>.getClobVal) returns PLSQL Numeric Value error

Comments

Post Details