14

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.

Improve this question

4 Answers 4

Reset to default
12

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)"
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Still doesn't like it. Gives me this error: SQL*Loader-350: Syntax error at line 17. Expecting "," or ")", found "CURRENT_TIMESTAMP". LAST_UPDATED CURRENT_TIMESTAMP(3)
Can you try doing: LAST_UPDATED EXPRESSION "SELECT current_timestamp(3) from dual" OR LAST_UPDATED EXPRESSION "current_timestamp(3)"
Hehe, okay, that works great : LAST_UPDATED EXPRESSION "current_timestamp(3)", the select from dual option didn't for some reason. However, turns out that SYSDATE would have worked fine. I'll explain in an answer.
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
Improve this answer

1 Comment

SYSDATE returns a DATE data type (but that includes hours minutes and seconds). CURRENT_TIMESTAMP returns a TIMESTAMP data type which goes down to fractional seconds (download.oracle.com/docs/cd/B19306_01/server.102/b14200/…). Both these have internal representations (eg DATE is held as 7 bytes) and the client chooses how to display them.
0

If you want to use the table defined default you can use:

ROWDATE EXPRESSION "DEFAULT"
Improve this answer

Comments

-1

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

Improve this answer

1 Comment

This won't work - you're not changing the date at all and you're altering a different session.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.