How To Write Data from a CLOB to a File

A HunBug Document
Document No.: 11070
Database Version: Oracle 9i
Last Updated: 01 Jan 2006
Author: HunBug
This solution takes data from a CLOB and writes it to a file using the UTL_FILE built-in package. As UTL_FILE has a limit of 32K, our procedure loops through the CLOB in 32K chunks.

The Procedure
CREATE OR REPLACE PROCEDURE dpr_clobToFile
( p_fileName       IN VARCHAR2,
  p_dir            IN VARCHAR2,
  p_clob           IN CLOB ) IS

  c_amount         CONSTANT BINARY_INTEGER := 32767;
  l_buffer         VARCHAR2(32767);
  l_chr10          PLS_INTEGER;
  l_clobLen        PLS_INTEGER;
  l_fHandler       UTL_FILE.FILE_TYPE;
  l_pos            PLS_INTEGER    := 1;

BEGIN

  l_clobLen  := DBMS_LOB.GETLENGTH(p_clob);
  l_fHandler := UTL_FILE.FOPEN(p_dir, p_fileName,'W',c_amount);

  WHILE l_pos < l_clobLen LOOP
    l_buffer := DBMS_LOB.SUBSTR(p_clob, c_amount, l_pos);     
    EXIT WHEN l_buffer IS NULL;
    l_chr10  := INSTR(l_buffer,CHR(10),-1);
    IF l_chr10 != 0 THEN
      l_buffer := SUBSTR(l_buffer,1,l_chr10-1);
    END IF;
    UTL_FILE.PUT_LINE(l_fHandler, l_buffer,TRUE);
    l_pos := l_pos + LEAST(LENGTH(l_buffer)+1,c_amount);
  END LOOP;

  UTL_FILE.FCLOSE(l_fHandler);

EXCEPTION
WHEN OTHERS THEN
  IF UTL_FILE.IS_OPEN(l_fHandler) THEN
    UTL_FILE.FCLOSE(l_fHandler);
  END IF;
  RAISE;

END;
/

How It Works
The procedure accepts 3 parameters.
1.    The filename to be output
2.    The directory for the output file
3.    The CLOB data

The directory should be an Oracle directory, as stored in the dba_directories system view.
Use the CREATE DIRECTORY built in to create a new directory if required.
CREATE DIRECTORY MYDIR AS 'C:\TEMP';
The procedure starts by getting the total length of the CLOB, for to be used by the loop, then creating the output file.

The main while loop, loops while our position in the CLOB is less than the length of the CLOB.
  WHILE l_pos < l_clobLen LOOP
    l_buffer := DBMS_LOB.SUBSTR(p_clob, c_amount, l_pos);     
    EXIT WHEN l_buffer IS NULL;
    l_chr10  := INSTR(l_buffer,CHR(10),-1);
    IF l_chr10 != 0 THEN
      l_buffer := SUBSTR(l_buffer,1,l_chr10-1);
    END IF;
    UTL_FILE.PUT_LINE(l_fHandler, l_buffer,TRUE);
    l_pos := l_pos + LEAST(LENGTH(l_buffer)+1,c_amount);
  END LOOP;
The buffer variable is populated with 32K (32767 bytes) of data. We then look for the last carriage return character (CHR(10)), due to a restriction with UTL_FILE, which requires each 32K to contain at least one carriage return.
The UTL_FILE.PUT_LINE procedure always adds a carriage return character, so if we find one, we move one place left it.
  l_buffer := SUBSTR(l_buffer,1,l_chr10-1);
If we don’t find a carriage return, one is added by the PUT_LINE anyway, allowing the procedure to complete without error. This does mean that the output file would not be exactly the same as the CLOB data.

Before the loop finishes our position within the CLOB is updated.
    l_pos := l_pos + LEAST(LENGTH(l_buffer)+1,c_amount);
The LEAST command allows us to add a character to our position, so we don’t start with the carriage return we omitted, whilst not omitting a character if we didn’t find a carriage return.

The procedure finally closes the output file when it’s finished, or closes it if an error has occurred, before raising the error.

Variations
This example works around the UTL_FILE carriage return limitation by adding one if one does not exist. If it would be preferred to not output the CLOB at all, if we can’t get it exact, the procedure can be modified to raise an error instead.
  WHILE l_pos < l_clobLen LOOP
    l_buffer := DBMS_LOB.SUBSTR(p_clob, c_amount, l_pos);     
    EXIT WHEN l_buffer IS NULL;
    l_chr10  := INSTR(l_buffer,CHR(10),-1);
    IF l_chr10 != 0 THEN
      l_buffer := SUBSTR(l_buffer,1,l_chr10-1);
    ELSE
      RAISE_APPLICATION_ERROR(-20101,'No carriage return found');
    END IF;
    UTL_FILE.PUT_LINE(l_fHandler, l_buffer,TRUE);
    l_pos := l_pos + LEAST(LENGTH(l_buffer)+1,c_amount);
  END LOOP;

Further Reading


AskTom: Tom comments on similar suggestions.
Create Directory: Oracle CREATE DIRECTORY documentation

HunBug Store








Comments are all individually read, none are automatically posted to the site.