Inserting the data from text file to Oracle using UTL_FILE --inserting into table using the UTL_FILE package

Inserting the data from text file to Oracle using UTL_FILE

This works only on Unix/Linux Systems and 'utl_file_dir=*' should be included init init.ora , and then system is to be Shutdown and reboot.

Assuming

text file name is  ---- o.text
the above file is present in  '/datadump'  filesystem


declare
  in_file   UTL_FILE.FILE_TYPE;
  linebuf   VARCHAR2(80);
  ph varchar2(7);
  ne varchar2(15);
 BEGIN
 BEGIN

  in_file := UTL_FILE.FOPEN('/datadump','o.txt','R');
  LOOP
         UTL_FILE.GET_LINE(in_file, linebuf);

     ph  := substr(linebuf,11,6);
     ne :=  substr(linebuf,36,10);

    insert into  dummy_table values(ph,ne);
    commit;

  END LOOP;
EXCEPTION
    WHEN UTL_FILE.INVALID_FILEHANDLE THEN
            DBMS_OUTPUT.PUT_LINE('Invalid File Handle');
            UTL_FILE.FCLOSE_ALL;
    WHEN UTL_FILE.INVALID_MODE THEN
            UTL_FILE.FCLOSE_ALL;
            DBMS_OUTPUT.PUT_LINE('Invalid Mode');
    WHEN UTL_FILE.INTERNAL_ERROR THEN
            UTL_FILE.FCLOSE_ALL;
            DBMS_OUTPUT.PUT_LINE('Internal Error');
    WHEN UTL_FILE.INVALID_OPERATION THEN
            UTL_FILE.FCLOSE_ALL;
            DBMS_OUTPUT.PUT_LINE('Invalid Operation');
    WHEN UTL_FILE.INVALID_PATH THEN
            UTL_FILE.FCLOSE_ALL;
            DBMS_OUTPUT.PUT_LINE('Invalid Path');
    WHEN UTL_FILE.READ_ERROR THEN
            UTL_FILE.FCLOSE_ALL;
            DBMS_OUTPUT.PUT_LINE('Read Error');
    WHEN UTL_FILE.WRITE_ERROR THEN
            UTL_FILE.FCLOSE_ALL;
            DBMS_OUTPUT.PUT_LINE('Write Error');
    WHEN NO_DATA_FOUND THEN
            UTL_FILE.FCLOSE_ALL;
            DBMS_OUTPUT.PUT_LINE('No Data Found');
    WHEN VALUE_ERROR THEN
            UTL_FILE.FCLOSE_ALL;
            DBMS_OUTPUT.PUT_LINE('Value Error');
    WHEN OTHERS THEN
            UTL_FILE.FCLOSE_ALL;
            DBMS_OUTPUT.PUT_LINE('Error!');

END;
end;
/