如何使用 Oracle Sql Loader 使用当前时间戳填充时间戳字段
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1233254/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to populate a timestamp field with current timestamp using Oracle Sql Loader
提问by Sen
I'm reading a pipe delimited file with SQL Loader and want to populate a LAST_UPDATED field in the table I am populating. My Control File looks like this:
我正在使用 SQL Loader 读取管道分隔文件，并希望在我正在填充的表中填充 LAST_UPDATED 字段。我的控制文件如下所示：
LOAD DATA
INFILE SampleFile.dat
REPLACE
INTO TABLE contact
FIELDS TERMINATED BY '|'
OPTIONALLY ENCLOSED BY '"'
(
ID,
FIRST_NAME,
LAST_NAME,
EMAIL,
DEPARTMENT_ID,
LAST_UPDATED SYSTIMESTAMP
)
For the LAST_UPDATED field I've tried SYSTIMESTAMP and CURRENT_TIMESTAMP and neither work. SYSDATE however works fine but doesn't give me the time of day.
对于 LAST_UPDATED 字段，我尝试了 SYSTIMESTAMP 和 CURRENT_TIMESTAMP ，但都不起作用。然而，SYSDATE 工作正常，但没有给我一天中的时间。
I am brand new to SQL Loader so I really know very little about what it is or isn't capable of. Thanks.
我是 SQL Loader 的新手，所以我对它的功能知之甚少。谢谢。
采纳答案by RC.
Have you tried the following:
您是否尝试过以下操作：
CURRENT_TIMESTAMP [ (precision) ]
CURRENT_TIMESTAMP [（精度）]
select current_timestamp(3) from dual; CURRENT_TIMESTAMP(3) ----------------------------- 10-JUL-04 19.11.12.686 +01:00
To do this in SQLLDR, you will need to use EXPRESSION in the CTL file so that SQLLDR knows to treat the call as SQL.
要在 SQLLDR 中执行此操作，您需要在 CTL 文件中使用 EXPRESSION，以便 SQLLDR 知道将调用视为 SQL。
Replace:
代替：
LAST_UPDATED SYSTIMESTAMP
with:
和：
LAST_UPDATED EXPRESSION "current_timestamp(3)"
回答by Sen
I accepted RC's answer because ultimately he answered what I was asking but my unfamiliarity with some of Oracle's tools led me to make this more difficult than it needed to be.
我接受了 RC 的回答，因为最终他回答了我的问题，但我对 Oracle 的一些工具的不熟悉导致我使这变得比需要的更困难。
I was trying to get SQL*Loader to record a timestamp instead of just a date. When I used SYSDATE, and then did a select on the table it was only listing the the date (05-AUG-09).
我试图让 SQL*Loader 记录时间戳，而不仅仅是日期。当我使用 SYSDATE，然后在表上进行选择时，它只列出日期 (05-AUG-09)。
Then, I tried RC's method (in the comments) and it worked. However, still, when I did a select on the table I got the same date format. Then it occurred to me it could just be truncating the remainder for display purposes. So then I did a:
然后，我尝试了 RC 的方法（在评论中）并且它起作用了。然而，当我在桌子上做一个选择时，我得到了相同的日期格式。然后我想到它可能只是为了显示目的而截断其余部分。然后我做了一个：
select TO_CHAR(LAST_UPDATED,'MMDDYYYY:HH24:MI:SS') from contact;
And it then displayed everything. Then I went back to the control file and changed it back to SYSDATE and ran the same query and sure enough, the HH:MI:SS was there and accurate.
然后它显示了所有内容。然后我回到控制文件并将其改回 SYSDATE 并运行相同的查询，果然，HH:MI:SS 就在那里并且准确无误。
This is all being done in SqlDeveloper. I don't know why it defaults to this behavior. Also what threw me off are the following two statements in sqldeveloper.
这一切都在 SqlDeveloper 中完成。我不知道为什么它默认为这种行为。同样让我失望的是 sqldeveloper 中的以下两个语句。
SELECT CURRENT_TIMESTAMP FROM DUAL; //returns a full date and time
SELECT SYSDATE FROM DUAL; // returns only a date
回答by vas
In Sql Developer run: ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
在 Sql Developer 中运行：ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
and then check it with SELECT SYSDATE FROM DUAL
然后用 SELECT SYSDATE FROM DUAL 检查它
