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

PRO+

Content

Find more PRO+ content and other member only offers, here.

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.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Please create a username to comment.

-ADS BY GOOGLE

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchFinancialApplications

Close