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?

Answer 1

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');

• ypercubeTM: CHAR(4) for a year column? Why, why should anyone do that?
• RGO: @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?
• ypercubeTM: 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.

Answer 2

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