Inserting data in NEWEMP table using UTL_FILE but it's able to display only one data

Question

 declare
     ENAME      VARCHAR2(10);
      JOB       VARCHAR2(10);
     MGR       VARCHAR2(4);
     HIREDATE  varchar2(11);
     SAL       VARCHAR2(11);
      COMM      VARCHAR2(9);
     DEPTNO    number(2);
        EMPNO     NUMBER(4);
           namesfile UTL_FILE.FILE_TYPE;
  begin
      namesfile :=UTL_FILE.FOPEN('DIPRJDIR','empdata.txt','R');
      LOOP
          EMPNO:=0;
          ENAME :=0;
          JOB:=0;
          MGR:=0;
          HIREDATE:=0;
          SAL:=0;
          COMM :=0;
          DEPTNO:=0;
          UTL_FILE.GET_LINE(namesfile,EMPNO,4);
          dbms_output.put_line('EMPNO :' || EMPNO);
          UTL_FILE.GET_LINE(namesfile,ENAME,10);
          dbms_output.put_line('ENAME :' || ENAME);
          UTL_FILE.GET_LINE(namesfile,JOB,9);
          dbms_output.put_line('JOB :' || JOB);
          UTL_FILE.GET_LINE(namesfile,MGR,4);
          dbms_output.put_line('MGR :' || MGR);
          UTL_FILE.GET_LINE(namesfile,HIREDATE,11);
          dbms_output.put_line('HIREDATE :' || HIREDATE);
           UTL_FILE.GET_LINE(namesfile,SAL,11);
           dbms_output.put_line('SAL :' || SAL);
           UTL_FILE.GET_LINE(namesfile,COMM,9);
           dbms_output.put_line('COMM :' || COMM);
           UTL_FILE.GET_LINE(namesfile,DEPTNO,2);
           dbms_output.put_line('DEPTNO :' || DEPTNO);
       END LOOP;
    END;


    In the loop it display data only one time after than it give error

   SQL> /
    EMPNO :7839
    ENAME :KING
    JOB :PRESIDENT
    MGR :0000
    HIREDATE :17-nov-1981
    SAL : 005000.00
    COMM :000000.00
    DEPTNO :10
    declare


    ERROR at line 1:
    ORA-06502: PL/SQL: numeric or value error: character to number conversion error
    ORA-06512: at line 22

I had written the program but it is not giving me the proper output. It is able to display data for only one time. Can you expalain to me why it displays data only one time, after which it gives an error.

Answer 1

Your error means there is a problem with the ninth row of your text file. It doesn't contain the numeric value you are expecting.

Clearly the obvious solution is for you to open your file in a text editor and see what the problem is. We cannot do this for you.

Probably your extract process has injected some extra characters. This might be a linefeed character, it might be some spaces, perhaps your table has an additional column you're not handling, etc.

The easiest solution is to read the data into a string buffer and then convert it to the expected datatype within your program. This will give you something you can use in an error handling section; something like this:

    .... 
exception
     when VALUE_ERROR then
         dbms_output.put_line('expected numeric or date value, got '||lv_string);
         raise;

You might want to define your own exceptions for separate datatypes. It's all in the PL/SQL documentation. Find out more.

---

Incidentally, this is a very labourious and brittle way to handle data exchange. You really should try to persuade your data extractor to provide a file which has a whole record per line. Then you could use SQL Loader or external tables, which are much easier.