write a CLOB to a file
 
Home

Resources
Library
How Can I?
Presentations
Links
Book Reviews
Downloads
User Groups


General
Contact Us
About Us
Services
Legal Notices & Terms of Use
Privacy Statement
Multiple Solutions Based On Built-In Packages
For a very long time we had only one way to write text to a file from inside the database with PL/SQL and that was through the use of the UTL_FILE package. UTL_FILE limped along with only a single text mode for read, write, and append until Database 10.1 when Oracle introduced the read, write, and append byte modes. But still we were stuck with a VARCHAR2 buffer only able to handle 32K at a time.

We now have a ways to write CLOB data directly to a file and this "How Can I" will show you two of them using DBMS_ADVISOR and DBMS_XSLPROCESSOR. First lets create a test table with a decent sized CLOB. Dropping the WHERE clause on my 11.1.0.7 database yields a CLOB of 77.5MB. The code used for this demo is based on 100,000 rows and yields a CLOB of about 5.5MB.
conn / as sysdba

CREATE TABLE uwclass.t (
textcol CLOB);

DECLARE
 c CLOB;

 CURSOR scur IS
 SELECT text
 FROM dba_source
 WHERE rownum < 100001;
BEGIN
  EXECUTE IMMEDIATE 'truncate table uwclass.t';

  FOR srec IN scur LOOP
   c := c || srec.text;
  END LOOP;

  INSERT INTO uwclass.t VALUES (c);
  COMMIT;
END;
/
Lets start with the traditional solution which is to use UTL_FILE. It isn't very pretty but it is functional.

We will start by creating a directory object, which is required for any real-world solution, and then test it out using the table created above.
CREATE OR REPLACE DIRECTORY ctemp AS 'c:\temp';
32767
DECLARE
 fhandle  utl_file.file_type;
 buf      CLOB;
 clobPart VARCHAR2(4096);
 offset   NUMBER := 1;
 clobLen  PLS_INTEGER;

 FUNCTION next_row(clob_in IN CLOB, off_in IN INTEGER)
 RETURN VARCHAR2 IS
 BEGIN
   RETURN dbms_lob.substr(clob_in, 1024, off_in);
 END next_row;
BEGIN
  fhandle := utl_file.fopen('CTEMP', 'testfile0.txt', 'W');

  SELECT textcol
  INTO buf
  FROM uwclass.t
  WHERE rownum = 1;

  cloblen := LENGTH(buf);

  LOOP
    EXIT WHEN offset >= clobLen;
    clobPart := next_row(buf, offset);
    utl_file.put_line(fhandle, clobPart, FALSE);

    offset := offset + 1024;
  END LOOP;
  utl_file.fflush(fhandle);
  utl_file.fclose(fhandle);
END;
/

DECLARE
*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at line 31

DECLARE
*
ERROR at line 1:
ORA-29285: file write error
ORA-06512: at "SYS.UTL_FILE", line 148
ORA-06512: at "SYS.UTL_FILE", line 889
ORA-06512: at line 26
The first solution I have found uses the built-in package DBMS_ADVISOR. The package contains a fully documented procedure, CREATE_FILE, that is used to write advisor results to the file system. The syntax is very simple:

dbms_advisor.create_file(
buffer   IN CLOB,
location IN VARCHAR2,  -- Oracle Directory Object Name
filename IN VARCHAR2); -- Name of file to write in directory
DECLARE
 buf CLOB;
BEGIN
  SELECT textcol
  INTO buf
  FROM uwclass.t
  WHERE rownum = 1;

  dbms_advisor.create_file(buf, 'CTEMP', 'testfile1.txt');
END;
/
When I tried this with 11.1.0.7 using a 21.5MB CLOB (168,000 rows from dba_source)  I generated the following:

ERROR at line 1:
ORA-00600: internal error code, arguments: [KGHALO2], [0x0], [], [], [], [], [], [], [], [], [], []
ORA-06512: at "SYS.PRVT_ADVISOR", line 49
ORA-06512: at "SYS.PRVT_ADVISOR", line 1105
ORA-06512: at "SYS.DBMS_ADVISOR", line 543
ORA-06512: at line 9

But running with 157,000 rows the 20.4MB CLOB was successful though, on Windows, there have been some locking problems where the file remained locked, yes I tried
COMMIT, until Oracle was shut down. This does not happen all of the time and does not seem to happen with small to medium sized CLOBs.

Thus this is not a perfect solution but should work for the vast majority of real-world CLOBs.

The second solution I found uses the built-in package DBMS_XSLPROCESSOR. The package contains a fully documented procedure, CLOB2FILE, and again the syntax is very simple:

dbms_xslprocessor.clob2file(
cl        IN CLOB,
flocation IN VARCHAR2,      -- file directory
fname     IN VARCHAR2,      -- file name
csid      IN NUMBER := 0);  -- character set id of the file

Use the same table and anonymous block, at the top of the page, to load rows exactly as done for the first demo.
DECLARE
 buf CLOB;
BEGIN
  SELECT textcol
  INTO buf
  FROM uwclass.t
  WHERE rownum = 1;

  dbms_xslprocessor.clob2file(buf, 'CTEMP', 'testfile2.txt');
END;
/
The output files created by the two methods do not have the same byte size nor do they take the same amount of time. The first generates a file that is larger than the second, looks like a standard Windows ASCII file when opened in notepad.

Using 100,000 rows from dba_source the following are averaged results following 5 runs. Each time after deleting the file previously created.
Package Name Procedure File Size (bytes) Run Time
UTL_FILE PUT_LINE 5,641,448 00:00:05.06
DBMS_ADVISOR CREATE_FILE 5,630,696 00:00:03.43
DBMS_XSLPROCESSOR CLOB2FILE 5,505,673 00:00:05.71
 
 
Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2013 Daniel A. Morgan All Rights Reserved