How to populate a timestamp field with current timestamp using Oracle Sql Loader

Question

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.

Answer 1

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)"