.txt file inserting to table with utl_file

—-örnek ‘a.txt’ dosyası içeriği;—-

ABCD-1020 80
DCKF-5010 20
———————————-
drop procedure file_read;
drop table test;

create table test (
text VARCHAR2(200),
text_name VARCHAR2(200),
create_date date default sysdate);

CREATE OR REPLACE PROCEDURE file_read(file_name VARCHAR2) IS
vSFile utl_file.file_type;
vNewLine VARCHAR2(200);
BEGIN
vSFile := utl_file.fopen(‘ORHAN_DIR’, file_name,’r’);

IF utl_file.is_open(vSFile) THEN
LOOP
BEGIN
utl_file.get_line(vSFile, vNewLine);

IF vNewLine IS NULL THEN
EXIT;
END IF;

INSERT INTO test
(text, text_name, create_date)
VALUES
(vNewLine, file_name,sysdate);
EXCEPTION
WHEN NO_DATA_FOUND THEN
EXIT;
END;
END LOOP;
COMMIT;
END IF;
utl_file.fclose(vSFile);
EXCEPTION
WHEN utl_file.invalid_mode THEN
RAISE_APPLICATION_ERROR (-20051, ‘Invalid Mode Parameter’);
WHEN utl_file.invalid_path THEN
RAISE_APPLICATION_ERROR (-20052, ‘Invalid File Location’);
WHEN utl_file.invalid_filehandle THEN
RAISE_APPLICATION_ERROR (-20053, ‘Invalid Filehandle’);
WHEN utl_file.invalid_operation THEN
RAISE_APPLICATION_ERROR (-20054, ‘Invalid Operation’);
WHEN utl_file.read_error THEN
RAISE_APPLICATION_ERROR (-20055, ‘Read Error’);
WHEN utl_file.internal_error THEN
RAISE_APPLICATION_ERROR (-20057, ‘Internal Error’);
WHEN utl_file.charsetmismatch THEN
RAISE_APPLICATION_ERROR (-20058, ‘Opened With FOPEN_NCHAR
But Later I/O Inconsistent’);
WHEN utl_file.file_open THEN
RAISE_APPLICATION_ERROR (-20059, ‘File Already Opened’);
WHEN utl_file.invalid_maxlinesize THEN
RAISE_APPLICATION_ERROR(-20060,’Line Size Exceeds 32K’);
WHEN utl_file.invalid_filename THEN
RAISE_APPLICATION_ERROR (-20061, ‘Invalid File Name’);
WHEN utl_file.access_denied THEN
RAISE_APPLICATION_ERROR (-20062, ‘File Access Denied By’);
WHEN utl_file.invalid_offset THEN
RAISE_APPLICATION_ERROR (-20063,’FSEEK Param Less Than 0′);
WHEN others THEN
RAISE_APPLICATION_ERROR (-20099, ‘Unknown UTL_FILE Error’);
END file_read;
/
————————————————–