DECLARE
input_file utl_file.file_type;
input_buffer varchar2(1000);
l_dir varchar2(50):='C:\PLSQL';
l_name varchar2(20):='input1.txt';
l_param_code varchar2(50);
l_param_subcode varchar2(10);
l_param_name varchar2(10);
BEGIN
BEGIN
input_file:=utl_file.fopen(l_dir,l_name,'R');
EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Error :'||sqlerrm);
END;
BEGIN
LOOP
utl_file.get_line(input_file,input_buffer);
IF input_buffer IS NULL THEN
EXIT;
END IF ;
l_param_code:=substr(input_buffer,1,instr(input_buffer,',',1,1)-1);
l_param_subcode:=substr(input_buffer,instr(input_buffer,',',1,1)+1,
instr(input_buffer,',',1,2)-instr(input_buffer,',',1,1)-1);
l_param_name:=substr(input_buffer,instr(input_buffer,',',1,2)+1);
END LOOP;
dbms_output.put_line(l_param_code||'-'||l_param_subcode||'-'||l_param_name);
EXCEPTION
WHEN others THEN
dbms_output.put_line('END OF FILE');
END;
utl_file.fclose(input_file);
exception
WHEN UTL_FILE.INVALID_PATH THEN
DBMS_OUTPUT.PUT_LINE('invalid_path');RAISE;
WHEN UTL_FILE.INVALID_MODE THEN
DBMS_OUTPUT.PUT_LINE('invalid_mode');RAISE;
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
DBMS_OUTPUT.PUT_LINE('invalid_filehandle');RAISE;
WHEN UTL_FILE.INVALID_OPERATION THEN
DBMS_OUTPUT.PUT_LINE('invalid_operation');RAISE;
WHEN UTL_FILE.READ_ERROR THEN
DBMS_OUTPUT.PUT_LINE('read_error');RAISE;
WHEN UTL_FILE.WRITE_ERROR THEN
DBMS_OUTPUT.PUT_LINE('write_error');RAISE;
WHEN UTL_FILE.INTERNAL_ERROR THEN
DBMS_OUTPUT.PUT_LINE('internal_error');RAISE;
end ;