Tutorial :Print CLOB content out as is in Oracle SQL script


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


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;  


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:


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


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


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.

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »