11 g client SQL*loader: insert datetime is not working

Question

I have the following data

85566186;DATACONTRACT;1;0;-0.0235;0.6361;0.4448;59;None;N;dnn_auc_0.650_acc_0.670_sensitivity_0.670_specificity_0.640.h5;0.1;;2020-01-26 00:00:00;2020-01-27 19:26:54;18

I need to uploda that into the following oracle table:

CREATE TABLE DATAFOUNDATION.SHAPELY_SCORES_2
  ( 
    A                    INTEGER
  , B          VARCHAR2(1000)
  , C           NUMBER
  , D            VARCHAR2(1000)
  , E                 NUMBER
  , F              NUMBER
  , G          NUMBER
  , H    INTEGER 
  , I         VARCHAR2(4000)
  , J       VARCHAR2(16)  
  , K                 VARCHAR2(1000)
  , L             NUMBER
  , M         VARCHAR2(64)
  , N           DATE
  , O             DATE
  , P            INTEGER
  )
 ;

I using the follwing control file:

load data
INFILE 'shaply_test.txt'
INTO TABLE DATAFOUNDATION.SHAPELY_SCORES_2
INSERT 
FIELDS TERMINATED BY ';' 
TRAILING NULLCOLS
(
A INTEGER(8) nullif A= BLANKS,
B nullif B = BLANKS,
C DOUBLE  nullif C= BLANKS,
D nullif D = BLANKS,
E INTEGER nullif E= BLANKS,
F FLOAT nullif F= BLANKS,
G FLOAT nullif G = BLANKS,
H FLOAT nullif H= BLANKS,
I nullif I= BLANKS,
J nullif J= BLANKS,
K nullif K = BLANKS,
L FLOAT nullif L= BLANKS,
M nullif M= BLANKS,
N TIMESTAMP 'YYYY-MM-DD hh24:mi:ss' nullif N = BLANKS,
O TIMESTAMP 'YYYY-MM-DD hh24:mi:ss' nullif O = BLANKS,
P INTEGER(8) nullif P = BLANKS)

But I am getting the following error (sorry its in German but the message is simple to translate)

SQL*Loader: Release 11.2.0.1.0 - Production on Fr Feb 7 19:14:54 2020

Spaltenname Position Läng Term Eing Datentyp ------------------------------ ---------- ----- ---- ---- --------------------- A FIRST 8 INTEGER
NULL wenn A = BLANKS B NEXT * ; CHARACTER
NULL wenn B = BLANKS C NEXT 8 DOUBLE
NULL wenn C = BLANKS D NEXT * ; CHARACTER
NULL wenn D = BLANKS E NEXT 4 INTEGER
NULL wenn E = BLANKS F NEXT 4 FLOAT
NULL wenn F = BLANKS G NEXT 4 FLOAT
NULL wenn G = BLANKS H NEXT 4 FLOAT
NULL wenn H = BLANKS I NEXT * ; CHARACTER
NULL wenn I = BLANKS J NEXT * ; CHARACTER
NULL wenn J = BLANKS K NEXT * ; CHARACTER
NULL wenn K = BLANKS L NEXT 4 FLOAT
NULL wenn L = BLANKS M NEXT * ; CHARACTER
NULL wenn M = BLANKS N NEXT * ; DATETIME YYYY-MM-DD hh24:mi:ss
NULL wenn N = BLANKS O NEXT * ; DATETIME YYYY-MM-DD hh24:mi:ss
NULL wenn O = BLANKS P NEXT 8 INTEGER
NULL wenn P = BLANKS

Satz 1: Abgelehnt - Fehler in Tabelle DATAFOUNDATION.SHAPELY_SCORES_2, Spalte N. ORA-01841: (Volles) Jahr muss zwischen -4713 und +9999 liegen und darf nicht 0 sein

Tabelle DATAFOUNDATION.SHAPELY_SCORES_2: 0 Zeilen erfolgreich geladen. 1 Zeile aufgrund von Datenfehlern nicht geladen. 0 Zeilen nicht geladen, da alle WHEN-Klauseln fehlerhaft waren. 0 Zeilen nicht geladen, da alle Felder NULL waren.

Zugewiesener Bereich für Bind-Array: 134912 Bytes (64 Zeilen) Byte in Lese-Puffer: 1048576

Gesamtzahl der übersprungenen logischen Datensätze: 0 Gesamtzahl der gelesenen logischen Datensätze: 1 Gesamtzahl der abgelehnten logischen Datensätze: 1 Gesamtzahl der zurückgewiesenen logischen Datensätze: 0

Lauf begonnen am Fr Feb 07 19:14:54 2020 Lauf beendet am Fr Feb 07 19:14:54 2020

Abgelaufene Zeit: 00:00:00.16 CPU-Zeit: 00:00:00.04

Can anyone please tell me what I am doing wrong here?

It would be better if you posted information that matches. Column names are A, B, C, ..., while error mentions something else. I suggest you shorten the table for sample purposes and leave only column(s) that matter. Edit the question and post new information that makes sense. — Littlefoot
i suspected that there was something wrong with the data types in some column. the individual values are read incorrectly and the date can then no longer be converted. try char instead of numeric data types. the conversion should actually work automatically. you can then try to replace the data types later — hotfix
thanks @hotfix for the hint, it was indeed due to the negative decimal digits which I solved by reading them as DECIMAL EXTERNAL. — wasif

wasif wasif · Accepted Answer · 2020-02-08T19:04:07

I found the solution. There were two problems in my code. 1st is I was using FLOAT/DOUBLE but DECIMAL EXTERNAL was required (link)

2nd problem was with the format of decimal (always a catch with regional settings). I used the following line added for each decimal value column:

 "REPLACE(:column_name, '.', ',')"

e.g., C DECIMAL EXTERNAL nullif C = BLANKS "REPLACE(:C, '.', ',')",

:).

11 g client SQL*loader: insert datetime is not working

1 Answers