I'm trying to export the dml from sql developer(v 17.4) but the date column is not coming as expected.
While inserting the record i used SYSDATE for date field but i think implicitly the sql developer is converting that SYSDATE to to_date(). Here's the Query I'm using to insert-
Insert into table_name (INT_OBJ_ID,INT_OBJ_NM,CREATE_DT,CREATE_USER_ID,UPDATE_DT,UPDATE_USER_ID) values (2016,'RDS_BV_RETURN_MAIL_INFO',SYSDATE,'User01',SYSDATE,'User01');
But in DML Export i'm getting this -
Insert into table_name (INT_OBJ_ID,INT_OBJ_NM,CREATE_DT,CREATE_USER_ID,UPDATE_DT,UPDATE_USER_ID) values (2016,'RDS_BV_RETURN_MAIL_INFO',to_date('09-AUG-23','DD-MON-RR'),'User01',to_date('09-AUG-23','DD-MON-RR'),'User01');
Expexted Export : Insert into table_name (INT_OBJ_ID,INT_OBJ_NM,CREATE_DT,CREATE_USER_ID,UPDATE_DT,UPDATE_USER_ID) values (2016,'RDS_BV_RETURN_MAIL_INFO',SYSDATE,'User01',SYSDATE ,'User01');
Does anyone know how can we resolve this?
[My Export wizard screenshots(incase it helps)](https://i.sstatic.net/C0Lja.png)
I tried to find the setting to default implicit conversion but couldn't find any. Even Tried to give SYSDATE in NLS but it expects valid date formate.
Export extract what it found in that column. It didn't find SYSDATE
(as function name), but value returned by SYSDATE
function at the moment of insert and it was - apparently, for that row - 9th of August 2023.
Export (actually, people who programmed it) chose to use to_date
function with appropriate format mask: to_date('09-AUG-23', 'DD-MON-RR')
.
Though, I'd rather see something different, such as
to_date('09.08.2023', 'dd.mm.yyyy')
. You certainly wouldn't be able to insert "AUG" into my database which speaks Croatian - of course, not without additional settings (altering the session to set format model and language).
I don't think you can get SYSDATE
in export. But, what you can do is to omit date column(s) from export: in "Specify Data" step, don't include all columns (*
) - click the pencil button and uncheck date column(s). Then, on target side, either set column's default value to sysdate
, e.g.
alter table target_table modify date_column default sysdate;
or populate it manually after insert, e.g.
update target_table set date_column = sysdate where date_column is null;