Wednesday, January 6, 2010

Oracle: literal does not match format string

Which occurs when we define Date type column and passing strings in different form.
I wanted to overcome this issue when develop oracle DB script.
 There are two solutions available.
  • Use to_date function
Eg: - to_date(datestring, formatstring)
to_date('2010-01-05 14:05:29','yyyy-mm-dd hh24:mi:ss' )
  • Or alter the evironment Date format
Eg:- alter system/session set nls_date_format='yyyy-mm-dd hh24:mi:ss' scope=spfile;
But this is not good option. System level alter affects all other databases and session level alter also user need to do everytime.

So, create an trigger in your DB script. So it will be executed always when you start your database.
eg: create or replace trigger DATE_TRIGGER
    after logon on database
   begin
      execute immediate 'alter session set  nls_date_format=''yyyy-mm-dd hh24:mi:ss''';
   end;