Print CLOB content out as is in Oracle SQL script

Question

To start with here is the bigger picture of the task I'm trying to do. I need to create a xml file from the results of the particular SQL request and store it in a file on the client computer. For that I have a SQL script that does the DBMS_XMLGen with xslt, which I'm going to run from a command line with sqlplus and pipe the output into a file.

The problem I'm having now is that content of the XML code (stored in CLOB) has to be splitted into smaller chunks for DBMS_OUTPUT.PUT_LINE, and every chunk ends up with a new line character, breaking the structure of the XML code. I wonder if there's a way to print the content of a BLOB as is on the screen?

Here's the example of the SQL script:

SET SERVEROUTPUT ON FORMAT WRAPPED;
set feedback off
DECLARE
 v_ctx   DBMS_XMLGen.ctxHandle;
 v_xml   CLOB;
 v_xslt  CLOB;
 l_offset number := 1;
BEGIN
  v_ctx := DBMS_XMLGen.newContext('SELECT * FROM TABLE');

--  DBMS_XMLGen.setXSLT(v_ctx, v_xslt); --not relevant here

  v_xml := BMS_XMLGen(v_ctx);
  DBMS_XMLGen.closeContext(v_ctx);

  loop exit when l_offset > dbms_lob.getlength(v_xml);
   DBMS_OUTPUT.PUT_LINE (dbms_lob.substr( v_xml, 255, l_offset));
   l_offset := l_offset + 255;
  end loop;


EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(Substr(SQLERRM,1,255));
 raise;
END;
/

The output I'm getting is correct apart from the new line character after every 255 symbols. And I can't just remove the end of lines later, I need the XML to be readable

Any ideas?

Cheers, Leo

Answer 1

You can add a delimiter and print out 254 characters, then in notepad++ (in extended mode ~\r\n) replace this delimiter :

 loop exit when l_offset > dbms_lob.getlength(v_xml);
   DBMS_OUTPUT.PUT_LINE (dbms_lob.substr( v_xml, 254, l_offset) || '~');
   l_offset := l_offset + 255;
  end loop;

Answer 2

One possible approach is to do the following:

1. Create a database table with a single column and row of type CLOB.
2. On server, insert the produced XML into that table.

3. On client run the SQL*PLus script like this:

   SET WRAP OFF  
   SET HEADING OFF  
   SET ECHO OFF  
   SPOOL file_name.xml
   
   SELECT your\_clob\_column FROM your\_table;
   
   SPOOL OFF
   

That will dump your XML into file_name.xml After that, you will need to truncate you table by issuing:

TRUNCATE TABLE your\_table DROP STORAGE;

otherwise the table won't shrink even if you delete the line with CLOB.

Answer 3

Check APC's CLOB workaround for dbms_output on http://forums.oracle.com/forums/thread.jspa?threadID=306557

Answer 4

Why don't you use DBMS_OUTPUT.PUT instead of DBMS_OUTPUT.PUT_LINE? Give it a try. The trick is to add a newline character after printing your content. You can do it by calling DBMS_OUTPUT.NEW_LINE.