英文:
SYSDATE is implicitly is getting converted to to_date() in SQL developer DML Export
问题
我正在尝试从SQL Developer（版本17.4）导出DML，但日期列的结果不符合预期。
在插入记录时，我在日期字段中使用了SYSDATE，但我认为SQL Developer会将其隐式转换为to_date()。以下是我用于插入的查询语句：
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');
但在DML导出中，我得到的是：
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');
期望的导出结果是：
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');
有人知道如何解决这个问题吗？
英文:
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.stack.imgur.com/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.
答案1
得分: 0
导出提取在该列中找到的内容。它没有找到SYSDATE（作为函数名），但找到了插入时SYSDATE函数返回的值，显然是2023年8月9日对于那一行。
导出（实际上是编程人员）选择使用了to_date函数，并使用适当的格式掩码：to_date('09-AUG-23', 'DD-MON-RR')。
虽然，我更希望看到一些不同的东西，比如
to_date('09.08.2023', 'dd.mm.yyyy')。你肯定不能将"AUG"插入到我的使用克罗地亚语的数据库中 - 当然，不是没有额外的设置（更改会话以设置格式模型和语言）。
我不认为你可以在导出中获取SYSDATE。但是，你可以在导出中省略日期列：在“指定数据”步骤中，不包括所有列（*）- 点击铅笔按钮并取消选中日期列。然后，在目标端，要么将列的默认值设置为sysdate，例如：
alter table target_table modify date_column default sysdate;
要么在插入后手动填充它，例如：
update target_table set date_column = sysdate where date_column is null;
英文:
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;
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论