CODEWITHSUNDEEP
sqloracle-database
null
null

Reputation: 747

ORA-01858: a non-numeric character was found where a numeric was expected? Even when the values are numbers?

This is the table

    CREATE TABLE Employee
    (EmpID number(5) primary key,
    SIN Number(9) Not null,
    LastName Varchar2(25) Not null,
    FirstName Varchar2(25),
    Street Varchar2(30),
    City Varchar2(25),
    Province Char(2),
    PostalCode Varchar2(7),
    JobCode Number(4) Not null,
    Foreign Key(JobCode) REFERENCES Job,
    IncomeTax Char(1),
    BirthDate Date,
    HireDate Date,
    JobCodeDate Date)
    TABLESPACE users;

This is the line I am trying to insert, there is only three numeric values and all of them are numbers as far as I can see.

INSERT INTO Employee VALUES(93284,3249028,'fnwei','wefw','213jnijni','32n4','AB','s8j 900',3000,'N','24-Aug-86','07-Jul-03','07-Jul-03');

ERROR at line 1:
ORA-01858: a non-numeric character was found where a numeric was expected

Upvotes: 3

Views: 2569

Answers (3)

CJBS
CJBS

Reputation: 15685

There's possibly a discrepancy between the order of fields as laid out in the INSERT statement, and the order that Oracle is expecting them. I suggest trying again using the full syntax of the INSERT (i.e. specify field names when doing the INSERT). That way, it's absolutely clear the value to field correlation being made.

So something like this:

INSERT 
INTO Employee (EmpID, SIN, LastName, FirstName, Street, City, Province, PostalCode, JobCode, IncomeTax, BirthDate, HireDate, JobCodeDate)
VALUES(111, 111,'DEF','ABC','111 111 111.','4535','CC','S6H 1X7',3000,'N','1111-08-24','1211-07-07','1213-07-07');

Upvotes: 1

Kevin Bowersox
Kevin Bowersox

Reputation: 94459

I believe the issue is with the date columns try using this syntax to_date('07-Jul-03','DD-MON-YY'):

INSERT INTO Employee 
VALUES(1111 ,1111,'ABC','DEC','ABC DEC','ABC DEC','BC','BCC 1X7',3000,'N',to_date('01-01-01', 'DD-MON-YY'),to_date('01-JAN-03','DD-MON-YY'),to_date('07-JAN-03','DD-MON-YY'));

SQL-Fiddle: http://sqlfiddle.com/#!4/0e9df/2

Upvotes: 1

null
null

Reputation: 747

alter SESSION set NLS_DATE_FORMAT = 'DD-Mon-YY';

I just had to type this in so that sql will execute the date format in my insert query's correctly

Upvotes: 1

Related Questions