Sunday, January 9, 2011

SQL Developer bugs when export the data into "insert statement" for datatype "DATE"

Symptoms:

SQL> Insert into dt (ID,CDATE,CTIMESTAMP)
2 values (1,to_timestamp('06-JAN-11 18:00:00','DD-MON-RR HH.MI.SS.FF AM'),
3 to_timestamp('06-JAN-11 06.00.00.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'))
;
values (1,to_timestamp('06-JAN-11 18:00:00','DD-MON-RR HH.MI.SS.FF AM'),
*
ERROR at line 2:
ORA-01849: hour must be between 1 and 12



Test Cases:

create table dt(id number, cdate date,ctimestamp timestamp);
insert into dt values(1,trunc(sysdate)+3/4,trunc(systimestamp)+3/4);
commit;
select * from dt;
commit;

REM INSERTING into table_export
Insert into dt (ID,CDATE,CTIMESTAMP)
values (1,to_timestamp('06-JAN-11 18:00:00','DD-MON-RR HH.MI.SS.FF AM'),
to_timestamp('06-JAN-11 06.00.00.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'));


SQL> Insert into dt (ID,CDATE,CTIMESTAMP)
2 values (1,to_timestamp('06-JAN-11 18:00:00','DD-MON-RR HH.MI.SS.FF AM'),
3 to_timestamp('06-JAN-11 06.00.00.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'))
;
values (1,to_timestamp('06-JAN-11 18:00:00','DD-MON-RR HH.MI.SS.FF AM'),
*
ERROR at line 2:
ORA-01849: hour must be between 1 and 12



Workaround:

Modify \sqldeveloper\bin\sqldeveloper.conf, add following line:
AddVMOption -Doracle.jdbc.mapDateToTimestamp=false

Below is my sqldeveloper.conf

IncludeConfFile ../../ide/bin/ide.conf

SetJavaHome ../../jdk

AddVMOption -Doracle.ide.util.AddinPolicyUtils.OVERRIDE_FLAG=true

AddVMOption -Dsun.java2d.ddoffscreen=false

AddVMOption -Dwindows.shell.font.languages=

AddVMOption -XX:MaxPermSize=256M

AddVMOption -Doracle.jdbc.mapDateToTimestamp=false


IncludeConfFile sqldeveloper-nondebug.conf



After this, correct export statement can be saved as insert statements:


REM INSERTING into table_export
Insert into "table_export" (ID,CDATE,CTIMESTAMP) values (1,to_date('06-JAN-11 18:00:00','DD-MON-RR HH24:MI:SS'),to_timestamp('06-JAN-11 06.00.00.000000000 PM','DD-MON-RR HH.MI.SS.FF AM'));