1

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

Leo
  • 11
  • 1
  • 1
  • 2
  • Had eventually to write a java program to do what I needed. Thanks for all the help guys! – Leo Nov 10 '09 at 22:51

4 Answers4

2

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;
KAD
  • 10,972
  • 4
  • 31
  • 73
2

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.

Felypp Oliveira
  • 2,147
  • 1
  • 17
  • 17
1

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.

Sergey Stadnik
  • 3,100
  • 8
  • 27
  • 31
  • +1 In the code supplied by the OP, replace usage of dbms_output with insert statements into the suggested output table. You might want to add a second column "line_number" to that table and fill it from a sequence, in order to be able to select the lines in the correct order. – Juergen Hartelt Oct 17 '09 at 17:55
  • erm, of course that only makes sense, if you actually produce more than one line :) – Juergen Hartelt Oct 17 '09 at 17:56
  • you can't select from CLOB column... anyway I've given up on this approach – Leo Nov 10 '09 at 22:47
0

Check APC's CLOB workaround for dbms_output on https://community.oracle.com/tech/developers/discussion/308557/ora-20000-oru-10027-buffer-overflow-limit-of-1000000-bytes

Community
  • 1
  • 1
rene707
  • 1