CODEWITHSUNDEEP
sqloracle-databaseora-01722
Nayeem Badshah
Nayeem Badshah

Reputation: 1

Could not Insert Row: Reason of The Error is Invalid Number

I created this table for which I am trying to insert a record:

    CREATE TABLE EMPLOYEE(
    Emp_No NUMBER(4),
    Ename VARCHAR2(20) NOT NULL,
    Sex CHAR(1) NOT NULL,
    Job VARCHAR2(20) NOT NULL,
    Mgr_No NUMBER(4) REFERENCES EMPLOYEE(Emp_No),
    DOJ DATE NOT NULL,
    Sal NUMBER(8,2) NOT NULL,
    Comm NUMBER(6,2),
    Dept_No NUMBER(2),
    CONSTRAINTS PK_103 PRIMARY KEY(Emp_no),
    CONSTRAINTS CHK_100 CHECK(Sex LIKE 'M'+'F')
    );

However, when I do the insert:

    INSERT INTO EMPLOYEE (Emp_No, Ename, Sex, Job, Mgr_no, DOJ, Sal, Comm, Dept_No) 
      VALUES (100, 'Satish', "M", 'Sr.Developer', 9, '25/FEB/2019',  18000, 5, 10);

I am getting the following error:

ERROR at line 1: ORA-01722: invalid number

Could someone point out what I am doing wrong? Thanks

Upvotes: 0

Views: 167

Answers (2)

Mladen S
Mladen S

Reputation: 93

you have wrong constraint chk_100.

Table must be create like this:

CREATE TABLE EMPLOYEE
( Emp_No NUMBER(4), 
  Ename VARCHAR2(20) NOT NULL, 
  Sex CHAR(1) NOT NULL, 
  Job VARCHAR2(20) NOT NULL, 
  Mgr_No NUMBER(4) REFERENCES EMPLOYEE(Emp_No), 
  DOJ DATE NOT NULL, 
  Sal NUMBER(8,2) NOT NULL, 
  Comm NUMBER(6,2), 
  Dept_No NUMBER(2), 
  CONSTRAINTS PK_103 PRIMARY KEY(Emp_no),
  CONSTRAINTS CHK_100 CHECK(Sex in ('M', 'F'))
  --CONSTRAINTS CHK_100 CHECK(Sex LIKE 'M'+'F')   -- this constraint is not correct

);

Whey you insert first row it will fail if you put any value in Mgr_No because table is empty and reference doesn't exists.

So first insert must be something like this:

INSERT INTO EMPLOYEE5 
        (Emp_No, Ename,   Sex,  Job,          Mgr_no, DOJ,     Sal,   Comm, Dept_No) 
VALUES (100,   'Satish', 'M', 'Sr.Developer', null,      sysdate, 18000, 5,    10);

and than second insert:

INSERT INTO EMPLOYEE5 
        (Emp_No, Ename,   Sex,  Job,          Mgr_no, DOJ,     Sal,   Comm, Dept_No) 
VALUES (101,   'Satish', 'M', 'Sr.Developer - other', 100,      sysdate, 18000, 5,    10);

Upvotes: 2

hotfix
hotfix

Reputation: 3396

you constaint CHK_100 is wrong + is not defined for strings. if you want to check it value = 'M' or value = 'F'

then you should define it like:

CONSTRAINTS CHK_100 CHECK(Sex in ('M','F'))

Upvotes: 1

Related Questions