A couple of days ago I asked for some help regarding on how to load data using UTL_FILE ......I thought I post the solution in case anyone is wondering on how to go about it.... You will have to place your error conditions or other type of coditions as you wish./


DECLARE
TYPE list IS TABLE of VARCHAR2(2000) INDEX BY BINARY_INTEGER;
a_field list;
fh UTL_FILE.FILE_TYPE;

-- --------------------------
-- Var for file attributes
-- --------------------------

text_out VARCHAR2(2000);
file_exists BOOLEAN;
file_length NUMBER;
file_size NUMBER;

-- --------------------------
-- Var for buffer and loops
-- --------------------------

lv_line_buffer VARCHAR2(2000);
col_number NUMBER :=5;

-- --------------------------
-- Default N flag for
-- cc_invoice table.
-- --------------------------

lv_flag CHAR(1) := 'N';
BEGIN
-- --------------------
-- Get file attributes
-- --------------------

UTL_FILE.FGETATTR('DAT_DIR','external_invoice.txt' ,file_exists,file_length,file_size);

-- --------------------
-- Open File
-- --------------------

fh := UTL_FILE.FOPEN('DAT_DIR','external_invoice.txt','r ');

LOOP
BEGIN
-- -------------------------
-- Read file through loop.
-- -------------------------

UTL_FILE.get_line(fh,text_out);
lv_line_buffer := '"|'||text_out||'|"';

-- ------------------------------------------------
-- Get lines from file and assign them to a_file
-- with a pipe delimeter at the beginning of the
-- line and end in order to extract values during
-- the for loop.
-- ------------------------------------------------

FOR i in 1..col_number LOOP
a_field(i) := SUBSTR(lv_line_buffer, INSTR(lv_line_buffer, '|' ,1 ,i)+1,
(INSTR(lv_line_buffer,'|' ,1 ,i+1)- INSTR(lv_line_buffer, '|' ,1 ,i)-1));
END LOOP;

-- ------------------------
-- Load cc_invoice table.
-- ------------------------

BEGIN

INSERT INTO CC_INVOICE
VALUES(a_field(1),
a_field(2),
a_field(3),
a_field(4),
TO_DATE(a_field(5),'yymmdd'),
lv_flag);
END;


END;

END LOOP;


-- --------------------------
-- Close file
-- --------------------------
EXCEPTION
WHEN NO_DATA_FOUND THEN
UTL_FILE.FCLOSE(fh);
END;