Hello,

here is the setup: I'm importing a text file using SQLLoader into an
Oracle 9i database. As the file contains date values with fractional
seconds I am forced to use the following definition in the control file:

daterecorded TIMESTAMP "YYYY-MM-DD HH24:MM:SS.FF"

note that the column in the database is defined as DATE not as TIMESTAMP.

now when I import this into my table, SQL*Loader completes successfully.

But when I try to access this column via JDBC using getTimestamp() the
driver throws an IllegalArgumentException (in
oracle.jdbc.driver.DateTimeCommonAccessor.zoneOffs et())

When I use direct=false for SQL*Loader JDBC access works fine.

I tried the JDBC driver for Oracle9, 10gR1 and 10gR2, no difference.

Does anyone have an idea how I could keep using direct=true but convince
SQL*Loader to write the date column correctly? Maybe a better definition
in the control file? (I have tried several ways with trim() and substr()
but to no avail, the above definition is the only one I could get to work)

To be honest this is more out of personal curiosity then a real project
need, as I could live with direct=false without problems.

Cheers
Thomas


--
It's not a RootKit - it's a Sony

Thomas Kellerer wrote:

If the table column is defined as a date but the character represtation
of the date is a timestamp truncate off the fractional seconds using a
substr function. From my notes where I did something like this:

FLD2 NEXT * , O(") CHARACTER
SQL string for column :
"to_date(substr(:fld2,1,19),'YYYY.MM.DD.HH24.MI.SS ')

Worked for me.

HTH -- Mark D Powell --

On 19.07.2006 15:36 Mark D Powell wrote:
For me too
I never tried to use to_date() I always declared the column as DATE and
then tried the substr() thing. Bu this works fine.

Thanks a lot!

Thomas

--
It's not a RootKit - it's a Sony