Oracle error sql

Question

I have to create a table and insert values into the tables. The tables is being created but it wont insert values. heres my code-

CREATE TABLE CUSTOMER(
CID         number(20) not null primary key,        
FNAME       varchar2(50) not null,                  
MIDDLE      varchar2(50) null,
LNAME       varchar2(50) not null,
STREET      varchar2(50) not null,
CITY        varchar2(50) not null,
STATE       char(2) not null,
ZIP         varchar2(5) not null,
PHONE       varchar2(20) not null,
BIRTH_YR    date);

INSERT INTO CUSTOMER VALUES(
'1','john','paul','connor','Broad st','newark','nj','07103','2018643110','1992');

I keep getting an error

[Error] Script lines: 12-14 ------------------------
ORA-01861: literal does not match format string
Script line 14, statement line 2, column 85

Can somebody help me out? What am i doing wrong?

RGO · Accepted Answer · 2012-11-06 03:12:52Z

3

You must change the table definition to something like below to allow insertion of year in the format you need:

CREATE TABLE CUSTOMER(
CID         number(20) not null primary key,        
FNAME       varchar2(50) not null,                  
MIDDLE      varchar2(50) null,
LNAME       varchar2(50) not null,
STREET      varchar2(50) not null,
CITY        varchar2(50) not null,
STATE       char(2) not null,
ZIP         varchar2(5) not null,
PHONE       varchar2(20) not null,
BIRTH_YR    char(4) not null);

Otherwise, you have to provide literal date values in the default format accepted by your Oracle database. You can use this query to find out what is the date format accepted by your system:

SELECT *
FROM nls_database_parameters
WHERE parameter LIKE '%DATE%'

For me, it returns:

NLS_DATE_FORMAT      |   DD-MON-RR
---------------------------------
NLS_DATE_LANGUAGE    |   AMERICAN

which means instead of '1992', I should insert values such as '01-Jan-1992' in the BIRTH_YR column:

INSERT INTO CUSTOMER
    VALUES('1','john','paul','connor','Broad st','newark','nj','07103','2018643110', '01-Jan-1992');

edited Nov 6, 2012 at 3:12

answered Nov 6, 2012 at 2:29

RGO

4,7774 gold badges30 silver badges41 bronze badges

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

3 Comments

ypercubeᵀᴹ Over a year ago

CHAR(4) for a year column? Why, why should anyone do that?

RGO Over a year ago

@ypercube In this case, it was suggested as a solution that works. If he is not going to do date calculations, what's wrong with it?

ypercubeᵀᴹ Over a year ago

One problem: He may end up with nonsense data, like a row with year = '19A5' And I would assume that one always wants to do calculations.

Vikdor · Accepted Answer · 2012-11-06 02:24:36Z

1

BIRTH_YR being a DATE field should be given in 'yyyy-MM-dd' format, '1992-01-01'.

answered Nov 6, 2012 at 2:24

Vikdor

24.2k10 gold badges66 silver badges86 bronze badges

Collectives™ on Stack Overflow

Oracle error sql

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related