Direct Path Load of TimeStamp Data With SQL*LDR

Question

The SQL-LDR documentation states that you need to do a convetional Path Load:

When you want to apply SQL functions to data fields. SQL functions are not available during a direct path load

I have TimeStamp data stored in a CSV file that I'm loading with SQL-LDR by describing the fields as such:

STARTTIME       "To_TimeStamp(:STARTTIME,'YYYY-MM-DD HH24:MI:SS.FF6')",
COMPLETIONTIME  "To_TimeStamp(:COMPLETIONTIME,'YYYY-MM-DD HH24:MI:SS.FF6')"

So my question is: Can you load timestamp data without a function, or is it the case that you can not do a Direct Path Load when Loading TimeStamp data?

Answer 1

From this OTN Forum thread:

you just need to set the environment variable NLS_TIMESTAMP_FORMAT to tell SQL*Loader what format to expect the timestamp to be in:

set NLS_TIMESTAMP_FORMAT=YYYY-MM-DD HH24:MI:SS.FF

..and remove the reference to the to_timestamp function completely from the controlfile.

Answer 2

Here is an example of someone successfully direct loading timestamp data:
Loading Data (Part 4): sqlldr (direct, skip_index_maintainance)

Answer 3

As a side note most of us have discontinued using “sql loader” for the more advance version “External Tables” assuming you’re on a newer version of Oracle.