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?
In order to help solve your request, I have listed below two procedures: 1) load_clob_data(); 2)write_ascii_data().
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;