How can I print large XML string generated by Oracle?

Question

I need something way bigger than varchar2(2000) to be able to print my generated XML. Either recommend a better approach than dbms_output.put_line(x) or recommend a different data type to store the result and then print it. I am an Oracle newbie and full-time job is UI development.

declare
    v_ctx dbms_xmlgen.ctxHandle;
    x varchar2(2000); -- I need something bigger than varchar2(2000)
begin
    v_ctx := DBMS_XMLGen.newContext('
        select
            baz as "Baz"
        from schema.table_with_10000_rows');
    DBMS_XMLGen.setRowsetTag(v_ctx, 'Foo');
    DBMS_XMLGen.setRowTag(v_ctx, 'Bar');
    x := dbms_xmlgen.getxml(v_ctx);
    dbms_output.put_line(x);
end;

I've been there before. Casting to a `BLOB`/`CLOB` seems fine in principle, but in practice we hit all manner of issues at all layers of the stack: OCI segfaults, silent truncation, character set problems. I hope someone else has a good solution. — willglynn, Oct 05 '12 at 05:04

score 2 · Accepted Answer · edited May 23 '17 at 12:27

You need to use a CLOB if you have more than 4000 characters, and that's the data type that getxml returns. This question seems to be approaching the same issue, and shows a simple way to loop through and print in chunks, though it has a flaw - it should be using dbms_output.put() instead of put_line (edit: maybe with a terminal dbms_output.new_line after the loop; untested I'm afraid).

If you're doing this in SQL*Plus you can avoid dbms_output altogether using a client bind variable:

var x clob;

declare
    v_ctx dbms_xmlgen.ctxHandle;
begin
    v_ctx := DBMS_XMLGen.newContext('
        select table_name as "Baz"
        from all_tables');
    DBMS_XMLGen.setRowsetTag(v_ctx, 'Foo');
    DBMS_XMLGen.setRowTag(v_ctx, 'Bar');
    :x := dbms_xmlgen.getxml(v_ctx);
end;
/

print x

x is declared outside the PL/SQL block, and used withing it as a bind variable - note the colon in the assignment, :x := ....

X
--------------------------------------------------------------------------------
<?xml version="1.0"?>
<Foo>
 <Bar>
  <Baz>DUAL</Baz>
 </Bar>
 <Bar>
  <Baz>SYSTE

By default you'll probably only see the start of the XML; you'll need to set long <some big number> to see it all, and (as of 11g) you're restricted to 2GB. (I imagine you'll also want to set feedback and headings off, and maybe longchunk).

I'd question why you're printing it to screen like this though, rather than storing in a table. If the ultimate goal is to get it in a file, utl_file might be more appropriate; something like this maybe.

Just saw your answer. Will try today or tomorrow and likely mark as answer. Seems like it should work. Thanks. — John Zabroski, Oct 10 '12 at 14:36
@JohnZabroski - thinking about it, if you just used `dbms_output.put` in the question I linked to, you'd get no or incomplete output; I think you'd also need a `dbms_output.new_line` after the loop. You may also have buffer size issues. Are you doing this from SQL*Plus? Might have a different approach if so... — Alex Poole, Oct 10 '12 at 14:56
Am doing this from SQL Navigator. I am a UI developer. I once did DBA type stuff with SQL Server, but not Oracle. Took an Oracle class in college 5 or 6 years ago. — John Zabroski, Oct 19 '12 at 21:56
Sorry for the delay in marking this as an answer. What I determined is that I can access SQL*PLUS primitives for outputting information to a file, from SQL Navigator. That dramatically simplified the task of figuring out how to serialize things, and was the linchpin to the solution. — John Zabroski, Jan 03 '13 at 15:41

How can I print large XML string generated by Oracle?

1 Answers1