Q
Problem solve Get help with specific problems with your technologies, process and projects.

Storing CLOB as .csv file

I need to fetch a CLOB field from a database and then move the fetched content to a .csv file. Can you please help?

I need to fetch a CLOB field from a database and then move the fetched content to a .csv file. Can you please help? I know how to generate a .csv file but I'm not sure about how to fetch data and how to store it.
Hi,

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;
This was last published in July 2006

Dig Deeper on Using Oracle PL-SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close