oracle giving PL/SQL: numeric or value error: character to number error

Question

My table is :

EmpNo   Ename   Job   MGR   HireDate    Sal
1        ABC    DPS   JOO   01-10-2002  2000
2        BCD    LPS   KOL   03-11-2005  2300
3        KOLD   MPS   BOR   13-03-2012  2400

I am trying to import above table from csv file into pre defined table in oracle database. Below is the code(pl sql code) to import above table.

Code :

Drop table emp_tbl
create table emp_tbl
( EmpNo Number,
EName varchar2(15),
Job Varchar2(15),
MGR Varchar2(30),
HireDate Date,
Sal Number
);


create or replace procedure read_file_proc
Is
  f_line varchar2(2000);
  f utl_file.file_type ;
  f_dir varchar2(250);
  fname varchar2(50);
  comma1 Number;
  comma2 Number;
  comma3 Number;
  comma4 Number;
  comma5 Number;
  f_empno emp_tbl.empno%type;
  f_ename emp_tbl.ename%type;
  f_job emp_tbl.job%type;
  f_mgr emp_tbl.MGR%type;
  f_hiredate emp_tbl.hiredate%type;
  f_sal emp_tbl.sal%type;
Begin
  dbms_output.put_line('At Begin');
  fname := 'Emp_tbl.csv';
  f := utl_file.fopen('UTL_FILE_DIR', fname, 'R');
  dbms_output.put_line('Before loop');
  loop 
    Begin
      Utl_file.get_line(f, f_line);
      --dbms_output.put_line(f_line);
    exception
      when no_data_found then
      exit;
    end;
    comma1 := instr(f_line,',', 1, 1);
    comma2 := instr(f_line,',', 1, 2);
    comma3 := instr(f_line,',', 1, 3);
    comma4 := instr(f_line,',', 1, 4);
    comma5 := instr(f_line,',', 1, 5);
    f_empno := to_number(substr(f_line, 1, comma1 -1 ));
    f_ename := substr(f_line, comma1 + 1, comma2 - comma1 - 1);
    f_job := substr(f_line, comma2 + 1, comma3 - comma2 - 1);
    f_mgr := substr(f_line, comma3 + 1, comma4 - comma3 - 1);
    f_hiredate := to_date(substr(f_line, comma4 + 1, comma5 - comma4 - 1), 'dd-mon,-yyyy');
    f_sal := to_number(substr(f_line, comma5 + 1));
    insert into emp_tbl values(f_empno, f_ename, f_job, f_mgr,  f_hiredate, f_sal);

  end loop;
  utl_file.fclose(f);
  --commit;
end read_file_proc;

But upon running in sql developer i am receiving below error :

ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at "SYSTEM.READ_FILE_PROC", line 36 ORA-06512: at line 2

But when i am entering values directly(by using insert query) for f_empno, f_hiredate, f_salary... it is working fine.

Can anybody please tell me Why i am getting above mentioned error.

Thanks in advance.

EDIT : As asked the first row in CSV file appears as :

1,Akon,TSP,Navi,01-10-2002,2000

Please make your code more readable by using a good layout. It will help you when debugging. See my changes in your code, as example. — wieseman
– wieseman, Commented Nov 26, 2016 at 19:31
The result for f_empno is a varchar2 (f_empno is the only number variable). Try the same but use empno as varchar2 by creating the table. Then you will see the value used during the insert. Before you make it a stored procedure run the procedure not as a stored procedure and check the values (with dbms_output.put_line) before using on the insert statement — wieseman
– wieseman, Commented Nov 26, 2016 at 19:47
@wieseman.... i have already declared f_empno as number in my table where i am importing the csv file data even while manipulating the substring i am using to_number function to convert the varchar into number...but still error come in.. — Dave
– Dave, Commented Nov 27, 2016 at 6:42

David דודו Markovitz · Accepted Answer · 2016-11-27 07:26:29Z

1

It seems the date format is wrong.

f_hiredate := to_date(substr(f_line, comma4 + 1, comma5 - comma4 - 1), 'dd-mon,-yyyy');

The right format is dd-mm-yyyy

P.s.
There are much better ways to do this.
If you have access to the server external table would be great.
From client side, SQL*Loader.
If you insist on Procedure - regular expression will serve you well.

edited Nov 27, 2016 at 7:26

answered Nov 26, 2016 at 20:43

David דודו Markovitz

45.2k7 gold badges75 silver badges97 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

David דודו Markovitz Over a year ago

@Dave,I need to see some data in order to determine the right format.

Dave · Accepted Answer · 2016-11-27 07:28:23Z

-1

thanks all for your response.

I have changed

f_hiredate := to_date(substr(f_line, comma4 + 1, comma5 - comma4 - 1), 'dd-mon,-yyyy');

to

f_hiredate := to_date(substr(f_line, comma4 + 1, comma5 - comma4 - 1), 'dd-mm-yyyy');

And actually my csv file was :

EmpNo,Ename,Job,MGR,HireDate,Sal
1,Akon,TSP,Navi,01-10-2002,2000
2,Anu,SSP,Man,03-11-2005,2300

The problem was the first row which contains the column names instead of data.

answered Nov 27, 2016 at 7:28

Dave

95 bronze badges

1 Comment

Dave Over a year ago

pl. justify the negative response.

Collectives™ on Stack Overflow

oracle giving PL/SQL: numeric or value error: character to number error

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related