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.

Donal Fellows
  • 133,037
  • 18
  • 149
  • 215
Sen
  • 327
  • 1
  • 3
  • 11

4 Answers4

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)"
RC.
  • 27,409
  • 9
  • 73
  • 93
  • 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) – Sen Aug 05 '09 at 13:34
  • Can you try doing: LAST_UPDATED EXPRESSION "SELECT current_timestamp(3) from dual" OR LAST_UPDATED EXPRESSION "current_timestamp(3)" – RC. Aug 05 '09 at 13:47
  • 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. – Sen Aug 05 '09 at 14:06
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
Sen
  • 327
  • 1
  • 3
  • 11
  • 4
    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 (http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#i53219). Both these have internal representations (eg DATE is held as 7 bytes) and the client chooses how to display them. – Gary Myers Aug 05 '09 at 22:57
0

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

ROWDATE EXPRESSION "DEFAULT"
Antonio Bardazzi
  • 2,996
  • 1
  • 23
  • 20
-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

vas
  • 1
  • This won't work - you're not changing the date at all and you're altering a _different_ session. – Ben Nov 26 '14 at 18:04