Storing CLOB as .csv file

In order to help solve your request, I have listed below two procedures: 1) load_clob_data(); 2)write_ascii_da...

ta().

The first procedure load_clob_data uses the table below. This table has a number field and a clob field.

CREATE TABLE DISPLAY_CLOB (ID NUMBER, CLOB_FIELD CLOB);

The procedure load_clob_data() is used to insert data into the display_clob table.

create or replace procedure load_clob_data(p_id number, p_file_name clob)
as 
   l_clob clob;
   l_bfile bfile;
begin
   insert into DISPLAY_CLOB value (ID, CLOB_FIELD)
   values
    ( p_id, empty_clob())
   returning CLOB_FIELD into l_clob;
   l_bfile := bfilename('CLOB_DATA_DIR',p_file_name);
   dbms_lob.fileopen(l_bfile);
   dbms_lob.loadfromfile(l_clob, l_bfile,
                          dbms_lob.getlength( l_bfile));
   dbms_lob.fileclose(l_bfile);
end;
/

where clob_data_dir is the directory name for the location, on the server, where the .csv file is located.

Procudure write_ascii_data() when executed will write a file to the filesystem. The location of the file is determined by the value of the parameter UTL_FILE.

PROCEDURE WRITE_ASCII_DATA (P_DATA IN CLOB
                            ,P_DIR  IN VARCHAR2
                            ,P_FILE IN VARCHAR2)
 IS

   t_out_file   UTL_FILE.file_type;
   t_buffer     VARCHAR2(32767);
   t_amount     BINARY_INTEGER := 32767;
   t_pos        INTEGER := 1;
   t_clob_len   INTEGER;
BEGIN
   t_clob_len := DBMS_LOB.GetLength(p_data);
   t_out_file := UTL_FILE.fOpen(p_dir, p_file, 'W', 32767);

   WHILE t_pos < t_clob_len LOOP
      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);
      t_pos := t_pos + t_amount;
   END LOOP;

   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;