We help IT Professionals succeed at work.

Write clob data into a text file using utl_file if more than 32 Kb

soujii
soujii asked
on
18,541 Views
1 Endorsement
Last Modified: 2013-12-19
Hi,
When I am trying to write clob data more than 32 KB into a text file using utl_file, I am facing issues.
I ma getting an error:
Error: ORA-29285: file write error
ORA-06512: at line 28

Can you please help.



declare
   t_out_file   UTL_FILE.file_type;
   t_buffer     VARCHAR2(32767);
   t_amount     BINARY_INTEGER := 1000;
   t_pos        INTEGER := 1;
   t_clob_len   INTEGER;
   P_DATA CLOB;
   P_DIR  VARCHAR2(100) := 'D:/oracle/test';
   P_FILE VARCHAR2(100) := 'test1.csv'; 
BEGIN
   select clb_col into P_DATA from tab1 where rownum = 1;
   t_clob_len := DBMS_LOB.GetLength(p_data);
   t_out_file := UTL_FILE.fOpen(p_dir,p_file, 'W', 32767);
  
   dbms_output.put_line(' length : '||t_clob_len);
   
   WHILE t_pos < t_clob_len LOOP
   
      dbms_output.put_line(' t_pos before : '||t_pos);
      DBMS_LOB.Read(p_data, t_amount, t_pos, t_buffer);
      UTL_FILE.Put(t_out_file, t_buffer);
      UTL_FILE.fflush(t_out_file);
      dbms_output.put_line(' t_pos after : '||t_pos);
      t_pos := t_pos + t_amount;
	  
   END LOOP;
   dbms_output.put_line('completed writing');
   UTL_FILE.fClose(t_out_file);
EXCEPTION
   WHEN OTHERS THEN
      IF(UTL_FILE.Is_Open(t_out_file))THEN
         UTL_FILE.fClose(t_out_file);
      END IF;
      RAISE;
END;

Open in new window

Comment
Watch Question

CERTIFIED EXPERT
Top Expert 2008

Commented:
Write in 32K chunks:
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;
/

CERTIFIED EXPERT
Top Expert 2008

Commented:
also DBMS_LOB package can do the job.
Delete the section "convert the data to rawtohex format"
because the example is constructed for BLOBs:

How to save a BLOB to a file on disk in PL/SQL
From: Thomas Kyte

Use DBMS_LOB to read from the BLOB

You will need to create an external procedure to take binary data and write it to the operating system, the external procedure can be written in C. If it was CLOB data, you can use UTL_FILE to write it to the OS but UTL_FILE does not support the binary in a BLOB.

There are articles on MetaLink explaining how to do and it has a C program ready for compiling and the External Procedure stuff, i'd advise a visit.

Especially, look for Note:70110.1, Subject: WRITING BLOB/CLOB/BFILE CONTENTS TO A FILE USING EXTERNAL PROCEDURES

Here is the Oracle code cut and pasted from it. The outputstring procedure is the oracle procedure interface to the External procedure.
-------------------------------------

DECLARE
 i1    BLOB;
 len   NUMBER;
 my_vr RAW(10000);
 i2    NUMBER;
 i3    NUMBER := 10000;
BEGIN
  -- get the blob locator
  SELECT c2
  INTO i1
  FROM lob_tab
  WHERE c1 = 2;

  -- find the length of the blob column
  len := dbms_lob.getlength(i1);
  dbms_output.put_line('Column Length: ' || TO_CHAR(len));

  -- Read 10000 bytes at a time
  i2 := 1;
  IF len < 10000 THEN
    -- If the col length is < 10000
    dbms_lob.read(i1,len,i2,my_vr);

    outputstring('p:\bfiles\ravi.bmp',
    rawtohex(my_vr),'wb',2*len);  

    -- You have to convert the data to rawtohex format.
    -- Directly sending the buffer
    -- data will not work
    -- That is the reason why we are sending the length as
    -- the double the size of the data read

    dbms_output.put_line('Read ' || to_char(len) || 'Bytes');
  ELSE
    -- If the col length is > 10000
    dbms_lob.read(i1,i3,i2,my_vr);

    outputstring('p:\bfiles\ravi.bmp',
    rawtohex(my_vr),'wb',2*i3);

    dbms_output.put_line('Read ' || TO_CHAR(i3) || ' Bytes ');
  END IF;

  i2 := i2 + 10000;

  WHILE (i2 < len )
  LOOP
    -- loop till entire data is fetched
    dbms_lob.read(i1,i3,i2,my_vr);

    dbms_output.put_line('Read ' || TO_CHAR(i3+i2-1) ||
    ' Bytes ');

    outputstring('p:\bfiles\ravi.bmp',
    rawtohex(my_vr),'ab',2*i3);

    i2 := i2 + 10000 ;
  END LOOP;
END;
/

Author

Commented:
THanks! will check the solution

Author

Commented:
Hi,
I tried with your 1st solution.It's giving the sam error.
But, I have modified my code by keeping utl_file.put_line instead of utl_ftp.ptu.It started working if it is more than 32 KB


CERTIFIED EXPERT
Top Expert 2008
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.