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:
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.
I am brand new to SQL Loader so I really know very little about what it is or isn't capable of. Thanks.
Have you tried the following:
CURRENT_TIMESTAMP [ (precision) ]
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.
Replace:
LAST_UPDATED SYSTIMESTAMP
with:
LAST_UPDATED EXPRESSION "current_timestamp(3)"
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.
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).
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:
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.
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.
SELECT CURRENT_TIMESTAMP FROM DUAL; //returns a full date and time
SELECT SYSDATE FROM DUAL; // returns only a date
In Sql Developer run:
ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'
and then check it with
SELECT SYSDATE FROM DUAL