Copying Data from a File into a CLOB in Oracle
The following copy_file_data_to_clob() procedure shows how to read text from a file and
store it in a CLOB:
CREATE PROCEDURE copy_file_data_to_clob(
p_clob_id INTEGER,
p_directory VARCHAR2,
p_file_name VARCHAR2
) AS
v_file UTL_FILE.FILE_TYPE;
v_chars_read INTEGER;
v_dest_clob CLOB;
v_amount INTEGER := 32767;
v_char_buffer VARCHAR2(32767);
BEGIN
-- insert an empty CLOB
INSERT INTO clob_content(
id, clob_column
) VALUES (
p_clob_id, EMPTY_CLOB()
);
-- get the LOB locator of the CLOB
SELECT clob_column
INTO v_dest_clob
FROM clob_content
WHERE id = p_clob_id
FOR UPDATE;
-- open the file for reading of text (up to v_amount characters per line)
v_file := UTL_FILE.FOPEN(p_directory, p_file_name, 'r', v_amount);
-- copy the data from the file into v_dest_clob one line at a time
LOOP
BEGIN
-- read a line from the file into v_char_buffer;
-- GET_LINE() does not copy the newline character into
-- v_char_buffer
UTL_FILE.GET_LINE(v_file, v_char_buffer);
v_chars_read := LENGTH(v_char_buffer);
-- append the line to v_dest_clob
DBMS_LOB.WRITEAPPEND(v_dest_clob, v_chars_read, v_char_buffer);
-- append a newline to v_dest_clob because v_char_buffer;
-- the ASCII value for newline is 10, so CHR(10) returns newline
DBMS_LOB.WRITEAPPEND(v_dest_clob, 1, CHR(10));
EXCEPTION
-- when there is no more data in the file then exit
WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP;
-- close the file
UTL_FILE.FCLOSE(v_file);
DBMS_OUTPUT.PUT_LINE('Copy successfully completed.');
END copy_file_data_to_clob;
/