SQL Loader

[tmp]

ctrl file
INTO TABLE UG_HISTORY
APPEND fields TERMINATED BY "#" optionally ENCLOSED BY '"' TRAILING NULLCOLS
(
UPDATEDT DATE 'YYYY-MM-DD HH24:MI:SS',
)

--------
dat file
>"2006-03-16 13:48:23.0"#
>"2006-03-16 13:50:37.0"#
>"2006-03-16 14:26:44.0"#
---------
LOG:
Record 1: Rejected - Error on table UG_HISTORY, column UPDATEDT.
ORA-01830: date format picture ends before converting entire input string
Record 2: Rejected - Error on table UG_HISTORY, column UPDATEDT.
ORA-01830: date format picture ends before converting entire input string

Record 3: Rejected - Error on table UG_HISTORY, column UPDATEDT.
ORA-01830: date format picture ends before converting entire input string

I do not understand in what problemma. help please

[davey23uk]

you have a .0 on the end of the data

[tmp]

aga
I understood, but whether it is possible to set in control file such type of date what understood .0 because a dat file is formed by the program

[tamilselvan]

aga
I understood, but whether it is possible to set in control file such type of date what understood .0 because a dat file is formed by the program

Can you correct the program?

Tamil

[KenEwald]

Try this...
UPDATEDT DATE 'YYYY-MM-DD HH24:MI:SS.S',

edit... nix that, it only works with to_char(). Is the .0 significant?

[ixion]

Try timestamp instead of date

UPDATEDT timestamp 'YYYY-MM-DD HH24:MI:SS.ff'

[tmp]

to correct the program this a superfluous cycle is necessary time will go away...
if UPDATEDT DATE 'YYYY-MM-DD HH24:MI:SS.S', then:
ORA-01821: date format not recognized

------
UPDATEDT timestamp 'YYYY-MM-DD HH24:MI:SS.ff'
this is work=)))))) thank you)))

[ixion]

Your welcome

[tmp]

yet question!
log:
Record 713: Rejected - Error on table UG_HISTORY, column TXTFILE.
second enclosure string not present
ctl file:
LOAD DATA

INFILE 'UG_HISTORY.dat'

INTO TABLE UG_HISTORY
APPEND fields TERMINATED BY "#" optionally ENCLOSED BY '№' TRAILING NULLCOLS
(
ID
TXTFILE CHAR(4000),
UPDATEDT timestamp 'YYYY-MM-DD HH24:MI:SS.ff',
)

bad file:
№7702№#№Toru and Naoko's college romance
might have been perfectly simple and predictable.
They might have been confronted
with
the ordinary issues of becoming
young adults in a large foreign city.
---------
but farther there must be a date, but SQL loader breaks((
alike from after transition on new line...
how to avoid it?

[ixion]

Your text file is missing the enclosure character...