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
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'));
Thanks, we just encountered this. Have you reported it to the SQL Developer team?
ReplyDelete