Re: SQLLDR - char to Date

"John Henriksen" <john.henriksen_at_usa.net> wrote in message news:rLab5.159$hG1.62004_at_news.uswest.net...
> I can't seem to get the correct control file format for SQLLDR for my date
> column.
>
> The table is:
> Column Name Null? Type
> ------------------------------ -------- ----
> TYP_ID NOT NULL NUMBER(6) <- defaults on load or
> insert
> INS_ID NOT NULL NUMBER(10)
> NAME NOT NULL CHAR(50)
> STATUS NOT NULL CHAR(4)
> CREATE_BY NOT NULL CHAR(8)
> CREATE_TS NOT NULL DATE
>
> An example input record:
> RAR-AV-PYMT-AM 444730400CARDRPBELMNT1998-01-28-09.45.24
>
> The load control file:
> load data
> into table fdr_element
> (name char(30),
> ins_id char(10),
> status char(4),
> create_by char(8),
> create_ts date(19) "YYYY-MM-DD-HH24.MM.SS")
>
> With the above I get: ORA-01810: format code appears twice
>
> And I have tried all of the following with the indicated results.
>
> create_ts date(19) 'YYYY-MM-DD-HH24.MM.SS'
> ORA-01810: format code appears twice
>
> create_ts date(19) "to_date(:create_ts,'YYYY-MM-DD-HH24.MM.SS')"
> ORA-00907: missing right parenthesis
>
> create_ts char(19) "to_date(:create_ts,'YYYY-MM-DD-HH24.MM.SS')"
> ORA-01810: format code appears twice
>
> create_ts position(53:71) "to_date(:create_ts,'YYYY-MM-DD-HH24.MM.SS')"
> ORA-01810: format code appears twice
>
> create_ts position(53:71) 'YYYY-MM-DD-HH24.MM.SS'
> SQL*Loader-308: Optional SQL string of column CREATE_TS must be in
> double
> quotes
>
> create_ts char(19) 'YYYY-MM-DD-HH24.MM.SS'
> SQL*Loader-308: Optional SQL string of column CREATE_TS must be in
> double
> quotes
>
> Is there a correct syntax?

I can't explain your "missing right parenthesis" error, but the "format appears twice" might be because you keep specifying 'MM' more than once. What you want to do is this for the date format:

YYYY-MM-DD-HH24.MI.SS 'MM' means two digit month, 'MI' means minutes. (You kept doing HH24.MM.SS, not HH24.MI.SS.).

I would try this:
create_ts char(19) "to_date(:create_ts,'YYYY-MM-DD-HH24.MI.SS')"

Make the data a character value in your file and perform the correct TO_DATE format on it. (Part of your problem might also have been that you were doing a TO_DATE on something that was already a date to begin with.)

-Matt Received on Wed Jul 12 2000 - 00:00:00 CEST