ORA-01722: "invalid number" error while inserting data into table

Question

The scenario given to me as follows. Create employees with following columns;

·         EmpId
·         EmpName
·         EmpEmail
·         EmpDate

In OLTP I need to enter one record at a time and following validations are to be done at backend;

·         EmpId should have a sequence generator
·         No numeric values allowed in EmpName
·         EmpDate should be in format mm/dd/yyyy
·         Email Id should have following validations
o   Should contain ‘@’ sign and it should be only one
o   Should end with ‘.com’
o   Email id should have more than 3 characters

for this i've created a table and sequence like this

create table emp_316599
  (EmpId number(6),
  EmpName varchar2(15)CONSTRAINT chk_EmpName 
     check(regexp_like(EmpName,'![0-9]+')),
  EmpEmail varchar2(15) CONSTRAINT chk_EmpEmail
    check((length(EmpEmail)>3) and
      substr(EmpEmail,-4)='.com' and
            instr(EmpEmail,'@',1,2)=0),
  EmpDate varchar2(15) 
  CONSTRAINT chek_EmpDate
    check(EmpDate=to_char(EmpDate,'mm-dd-yyyy')));

  Table created.       

 create sequence Emp_SEQ
 increment by 1
 start with 1
 maxvalue 1000
 cache 20
sequence created.

Here comes the problem. When i am trying to insert using the below command it is giving me the error.

insert into emp_316599 values(Emp_SEQ.NEXTVAL,'&EmpName','&EmpEmail','&EmpDate');

ORA-01722 invalid number

Please help me out.

Work environment as follows DB:Oracle 11g, tool:plsql developer

Thanks Kumar

Unrelated but important nonetheless: Why are you storing a `DATE` in a varchar column? That is calling for trouble. — , Mar 12 '13 at 08:45
Additionally: the check constraint using a `to_char()` on a varchar column to check a date format is totally useless. I wouldn't be suprised if that - sorry for the word - stupid definition of a date column gives you the error during the implicit conversion that is going on there. Try defining empdate as `DATE` and make sure you provide a properly formatted date. And show us the content of your variables. — , Mar 12 '13 at 08:55
As per your suggestion i've changed the datatype to date and check constraint too.EmpDate date CONSTRAINT chek_EmpDate check(EmpDate=to_date(EmpDate,'mm-dd-yyyy')); but when i am trying to insert it is throwing me invalid month. my input as follows kumar,raj@gmail.com,24-11-2011 and my secong input is kumar,raj@gmail.com,11-24-2011 but am facing the issue with bot the inputs. help me out — Kumar, Mar 12 '13 at 09:28
You don't need a check constraint on a DATE column to verify it's a date. The data type already does that for you. Remove it. And additionally: it is also totally useless to convert a date to a date using `to_date()` (and being subject to implicit data type conversion). — , Mar 12 '13 at 09:31
Yes i agree that. but my requirement is that EmpDate format should be mm-dd-yyyy.bur data datatype format is dd-mm-yyyy right? how can i validate that column? — Kumar, Mar 12 '13 at 09:38
A `DATE` column does ***NOT*** have a format. Consequently there is no need to check that "format". The format is applied by the client application when *displaying* the date (in your case SQL*Plus). — , Mar 12 '13 at 11:38

score 0 · Answer 1 · answered Mar 12 '13 at 08:32

0

I get a similar error and I fixed using this define before execute the update:

set define off;

answered Mar 12 '13 at 08:32

Andrea Girardi

4,337
13
69
98

Hi, i tried to do as you suggested but am getting error ---missing or invalid option.ORA-00922 – Kumar Mar 12 '13 at 08:38
did you take a look to that: http://stackoverflow.com/questions/12549029/sql-error-ora-01722-invalid-number ? – Andrea Girardi Mar 12 '13 at 08:42
Yes, i looked into that. But i am not getting where am doing a mistake – Kumar Mar 12 '13 at 09:27

ORA-01722: "invalid number" error while inserting data into table

1 Answers1