Write a CLOB to File in Oracle

Question

my student_t1 table consists of a blob column called image. I wrote a base64 function that correctly converts the BLOB into a CLOB. Now, I need to write this CLOB to my IO_DIR on my server /u01/app/oracle/io_dir.

When the following PL/SQL code is executed, the CLOB gets written to the directory as a text file. I believe the entire CLOB is not outputted though because when I try to decode the image is corrupted (only the upper portion of the image comes in). I ran it on a small image (11k) and it worked fine. I also monitored that l_pos is increasing correctly so it seems like it is reading each chunk of the CLOB (32kB) What am I doing wrong?

I am using Oracle 11g Express Edition (XE) and SQL Developer. Here is the error and the code

create or replace PROCEDURE c2F
 IS
   p_filename       VARCHAR2(100);
   p_dir            VARCHAR2(100) := 'IO_DIR';
   c_amount         CONSTANT BINARY_INTEGER := 32767;
   l_buffer         VARCHAR2(32767);
   l_chr10          PLS_INTEGER;
   l_clobLen        PLS_INTEGER;
   l_fHandler       sys.UTL_FILE.FILE_TYPE;
   l_pos            PLS_INTEGER    := 1;
   v_blob           BLOB;
   p_clob           CLOB;

 BEGIN
  dbms_output.put_line('Start Time: ' || TO_CHAR(sysdate,('YYYY/MM/DD hh24:Mi:ssss')));

  FOR i IN
    (SELECT student_no,
            image v_blob,
            encdec_base64.encode_base64(image) p_clob 
    FROM student_t1
    WHERE student_no =200601022
  ) loop

   IF (dbms_lob.isopen(i.p_clob) = 0) THEN
      dbms_lob.open(i.p_clob,dbms_lob.lob_readonly);
    END IF;
   l_pos      := 1;
   p_filename := i.student_no || '.txt';
   l_clobLen  := DBMS_LOB.GETLENGTH(i.p_clob);
   l_fHandler := sys.UTL_FILE.FOPEN(p_dir, p_fileName,'WB',c_amount);
   l_buffer := DBMS_LOB.SUBSTR(i.p_clob, c_amount, l_pos);

  WHILE l_pos < l_clobLen LOOP
     l_buffer := DBMS_LOB.SUBSTR(i.p_clob, c_amount, l_pos);

     EXIT WHEN l_buffer IS NULL;
     UTL_FILE.put_raw(l_fHandler,utl_raw.cast_to_raw(l_buffer));
     l_pos := l_pos + LEAST(LENGTH(l_buffer)+1,c_amount);
     UTL_FILE.FFLUSH(l_fHandler);
     END LOOP;
  END LOOP;
   sys.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;
dbms_output.put_line('End Time: ' || TO_CHAR(sysdate,('YYYY/MM/DD hh24:Mi:ssss')));
 END; 

Answer 1

create or replace PROCEDURE CONVERT_CLOB_2_FILE(
    p_fileName IN VARCHAR2,
    p_dir      IN VARCHAR2,
    p_clob     IN CLOB )
AS
  v_lob_image_id NUMBER;
  v_clob CLOB := p_clob;
  v_buffer RAW (32767);
  c_buffer VARCHAR2 (32767);
  v_buffer_size BINARY_INTEGER;
  v_amount BINARY_INTEGER;
  v_pos       NUMBER (38) := 1;
  v_clob_size INTEGER;
  v_out_file UTL_FILE.file_type;
BEGIN
  --dbms_output.put_line('Start Time: ' || TO_CHAR(sysdate,('YYYY/MM/DD hh24:Mi:ssss')));
  v_pos       := 1;
  v_clob_size := DBMS_LOB.GETLENGTH (v_clob);
  --IF (v_clob_size  < 32767) THEN
  -- v_buffer_size := v_clob_size;
  -- ELSE
  -- v_buffer_size := 32767;
  --END IF;
  v_buffer_size              := 32767;
  v_amount                   := v_buffer_size;
  IF (dbms_lob.isopen(v_clob) = 0) THEN
    dbms_lob.open(v_clob,dbms_lob.lob_readonly);
  END IF;
  v_out_file     := UTL_FILE.fopen (p_dir,p_fileName , 'WB', max_linesize => 32767);
  WHILE v_amount >= v_buffer_size
  LOOP
    DBMS_LOB.read (v_clob, v_amount, v_pos, c_buffer);
    --c_buffer := DBMS_LOB.SUBSTR(v_clob, v_amount, v_pos);
    v_buffer := UTL_RAW.CAST_TO_RAW(c_buffer);
    v_pos    := v_pos + v_amount;
    UTL_FILE.put_raw (v_out_file, v_buffer, TRUE);
    UTL_FILE.fflush (v_out_file);
  END LOOP;
  UTL_FILE.fflush (v_out_file);
  UTL_FILE.fclose (v_out_file);
  IF ( dbms_lob.isopen(v_clob) = 1 ) THEN
    dbms_lob.close(v_clob);
  END IF;
  --dbms_output.put_line('End Time: ' || TO_CHAR(sysdate,('YYYY/MM/DD hh24:Mi:ssss')));
EXCEPTION
WHEN OTHERS THEN
  IF ( dbms_lob.isopen(v_clob) = 1 ) THEN
    dbms_lob.close(v_clob);
  END IF;
  RAISE;
END;

Answer 2

Try using a smaller buffer size than 32767, in particular, one with size no more than 8191.

As far as I can tell, DBMS_LOB.SUBSTR does not always behave itself if the buffer size exceeds 8191 characters. If you ask for it to read (say), 10000 characters, it might only read 8191 from the LOB, but it will return to you a 10000-character long string where the first 8191 characters were what it read from the LOB and the other 1809 were what happened to be there before (e.g. from the last call to DBMS_LOB.SUBSTR). If the buffer size is 8191 characters or smaller, this problem doesn't occur and DBMS_LOB.SUBSTR returns the number of characters you asked for.

I cannot fathom why this function would do this. It seems very odd. I can only conclude that it is a bug in the database. Whether it is specific to Oracle 11g, or XE, I can't say.

The Oracle documentation for DBMS_LOB.SUBSTR includes the following under Usage Notes, which doesn't explain the problem but does include a mention of the number 8191:

• DBMS_LOB.SUBSTR will return 8191 or more characters based on the characters stored in the LOBs. If all characters are not returned as a consequence of the character byte size exceeding the available buffer, the user should either call DBMS_LOB.SUBSTR with a new offset to read the remaining characters, or call the subprogram on loop until all the data is extracted.

Finally, my copy of Oracle 11g XE is using a single-byte character set. The number 8191 may change if you are using a multi-byte character set.

Answer 3

First of all you need a BLOB type variable for dbms_lob.open, so use dbms_lob.open(i.v_clob) instead, and in my opinion, don't convert to a CLOB.

It seems this file (i.v_clob or v_clob) must be closed somewhere below, maybe as in the following :

  ....
  WHILE l_pos < l_clobLen LOOP
     l_buffer := DBMS_LOB.SUBSTR(i.p_clob, c_amount, l_pos);

     EXIT WHEN l_buffer IS NULL;
     .....
     UTL_FILE.FFLUSH(l_fHandler);
     dbms_lob.close(i.v_clob); 
     v_clob := i.v_clob;
     END LOOP;
  END LOOP;
   sys.UTL_FILE.FCLOSE(l_fHandler);

 EXCEPTION
  WHEN OTHERS THEN
   IF UTL_FILE.IS_OPEN(l_fHandler) THEN
   UTL_FILE.FCLOSE(l_fHandler);
   dbms_lob.close(v_clob); 
   END IF;
  .....