CODEWITHSUNDEEP
oracle-databaseetldate-formattingsql-loadercontrolfile
That Robin
That Robin

Reputation: 85

How do I load millisecond accuracy dates with SQLLDR

I am trying to load date fields that have six decimal places using sqlldr. For example I have a date:
"2020-01-05-16.32.02.070000" in my csv import file.

In my control file for sqlldr I tried: LASTMODIFIED DATE "YYYY-MM-DD-HH24.MI.SS.FF6",

But the sqlldr throws the error: ORA-01821: date format not recognized

So I can change my format string to LASTMODIFIED DATE "YYYY-MM-DD-HH24.MI.SS",

The of course if fails to load because:

Record 43: Rejected - Error on table ORA_TIO_OWNER.REPORTER_STATUS, column LASTMODIFIED.
ORA-01830: format picture ends before converting entire input string

I can exclude the sub second accuracy from my data, then it imports just fine, but obviously that is sub-optimal. How do I get sqlldr to accept these data strings?

PS. I have checked with SQL Developer and see the target table is configured to have the same number of decimals.

Updated with loader control file

 load data
  2   infile 'REPORTER_JOURNAL.csv'
  3   into table ORA_TIO_OWNER.REPORTER_JOURNAL
  4   fields terminated by "," optionally enclosed by '"'
  5   ( CHRONO DATE "YYYY-MM-DD-HH24.MI.SS.FF", SERIAL INTEGER EXTERNAL, SERVERNAME CHAR, SERVERSERIAL INTEGER EXTERNAL, TEXT1 CHAR, TEXT10 CHAR, TEXT11 CHAR, TEXT12 CHAR, TEXT13 CHAR, TEXT14 CHAR, TEXT15 CHAR, TEXT16 CHAR, TEXT2 CHAR, TEXT3 CHAR, TEXT4 CHAR, TEXT5 CHAR, TEXT6 CHAR, TEXT7 CHAR, TEXT8 CHAR, TEXT9 CHAR, USERID INTEGER EXTERNAL )

That is non-functioning and giving ORA-01821: date format not recognized If I hack off the .FF it will load the cut down version of dates.

P.S. Don't worry about the differing table names. I am migrating a whole pile of data from DB2 to Oracle and there are many tables, but the problem is the same across them all

Upvotes: 0

Views: 1870

Answers (1)

pifor
pifor

Reputation: 7892

Try:

LASTMODIFIED timestamp "YYYY-MM-DD-HH24.MI.SS.FF"

I can run the following on 11.2.0.4 and 19c without errors:

$ cat ld.dat
"2020-01-05-16.32.02.070000"
$ cat ld.ctl
load data
infile 'ld.dat'
into table t 
fields terminated by "," optionally enclosed by '"'
(c timestamp "YYYY-MM-DD-HH24.MI.SS.FF")
$ sqlldr userid=system/oracle data=ld.dat control=ld.ctl

SQL*Loader: Release 11.2.0.4.0 - Production on Mon Jun 8 11:28:14 2020

Copyright (c) 1982, 2011, Oracle and/or its affiliates.  All rights reserved.

Commit point reached - logical record count 1
$ sqlplus system/oracle 
SQL> select * from t;

C
---------------------------------------------------------------------------
05-JAN-20 04.32.02.070000 PM

SQL> desc t;
 Name                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C                          TIMESTAMP(6) WITH LOCAL TIME
                             ZONE

Upvotes: 1

Related Questions