sql query ORA-01843

Question

I have the following DDL and DML statements :

create table emp_details (
ID number(2) constraint t_pk primary key,
F_Name varchar(10) not null,
L_Name varchar(10) not null,
DOB date,
Mob_no number(10),
City varchar(10),
PIN number(5),
Gender char(1),
Designation varchar(15),
Join_Date date,    
);

insert into emp_details values (01,'John','Wick','1990-07-05',9856482358,'Goa',403001,'M','SDE II', '2015-01-08');

then, I get the error of ORA-01843. So, what could be the problem?

Answer 1

It seems when you query with

select * from nls_session_parameters p where p.parameter = 'NLS_DATE_FORMAT';

you won't get YYYY-MM-DD or YYYY-DD-MM from your result of error ORA-01843.

This problem is due to inserting wrong-formatted value for date columns DOB and Join_date.

There may be two ways to prevent this error :

• Assume you get DD/MM/YYYY from above query, then use 05-07-1990 for DOB, and 08/01/2015 for Join_Date columns, respectively.

• Format your values as to_date('1990-07-05','YYYY-MM-DD') for DOB and to_date('2015-01-08','YYYY-MM-DD') for Join_Date

Answer 2

The easiest thing to do here is to use ANSI date literals instead of strings for the dates (using strings will depend on the value of NLS_DATE_FORMAT and you don't want to play around with that if you don't have to):

INSERT INTO emp_details
VALUES
  ( 01, 'John', 'Wick', DATE'1990-07-05', 9856482358
  , 'Goa', 403001, 'M', 'SDE II', DATE'2015-01-08');

I have to add that explicitly listing the columns into which you're inserting values is a good habit to have. Otherwise, your INSERT query will break if you or someone else adds a column from your table:

INSERT INTO emp_details
  ( id, f_name, l_name, dob, mob_no, city, pin, gender, designation, join_date )
VALUES
  ( 01, 'John', 'Wick', DATE'1990-07-05', 9856482358
  , 'Goa', 403001, 'M', 'SDE II', DATE'2015-01-08');

Last, another good practice is to use VARCHAR2 instead of VARCHAR when you're working with Oracle. Currently they work the same, but Oracle "reserves the right" to change VARCHAR to meet with the ANSI standard under which NULL values and the empty string won't be the same (with VARCHAR2 they will always be the same). IOW, the behavior of VARCHAR values in Oracle can change.