CODEWITHSUNDEEP
xmloracle-database
John Zabroski
John Zabroski

Reputation: 2359

How can I print large XML string generated by Oracle?

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;

Upvotes: 2

Views: 3475

Answers (1)

Alex Poole
Alex Poole

Reputation: 191265

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.

Upvotes: 2

Related Questions