Re: Problem with SQLLoader Date columns and JDBC access

Thomas Kellerer wrote:
> 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.zoneOffset())
>
> 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

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 -- Received on Wed Jul 19 2006 - 08:36:08 CDT