troubleshooting Question

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

Avatar of soujii
soujiiFlag for India asked on
Oracle Database
5 Comments1 Solution18744 ViewsLast Modified:
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;
ASKER CERTIFIED SOLUTION
Join our community to see this answer!
Unlock 1 Answer and 5 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 5 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros