How To Load Data Using Functions CASE, CONCAT And TO_DATE To Avoid The Error ORA-1861 (Doc ID 785368.1)

Last updated on NOVEMBER 11, 2011

Applies to:

Oracle Server - Enterprise Edition - Version: 9.2.0.1 to 10.2.0.4 - Release: 9.2 to 10.2
Information in this document applies to any platform.

Goal

This note demonstrates how to load data with SQL*Loader (sqlldr) using the functions CASE, CONCAT and TO_DATE  to concatenate "0" at the beginning of a number column when is required to avoid  format data  problems.

sqlplus test/test

CREATE TABLE AHSTDN_TMP
(
   DNHSTD   VARCHAR2(5),
   DNPAT    NUMBER(9),
   DNPATY   VARCHAR2(1),
   DNTDT_YY NUMBER(9),
   DNTDT_MM NUMBER(9),
   DNTDT_DD NUMBER(9),
   DNTDT    DATE
);

Control file load3tSR.ctl:

LOAD data
infile *
BADFILE 'load3t.bad'
DISCARDFILE 'load3t.dsc'
APPEND
into table AHSTDN_TMP
fields terminated by "," optionally enclosed by '"'
trailing nullcols
(
   DNHSTD,
   DNPAT,
   DNPATY,
   DNTDT_YY,
   DNTDT_MM,
   DNTDT_DD,
   DNTDT "DECODE (:DNTDT_MM,10, TO_DATE (:DNTDT_YY||:DNTDT_MM||:DNTDT_DD, 'YYYY-MM-DD'), 11, TO_DATE (:DNTDT_YY||:DNTDT_MM||:DNTDT_DD, 'YYYY-MM-DD'), 12, TO_DATE (:DNTDT_YY||:DNTDT_MM||:DNTDT_DD, 'YYYY-MM-DD'), TO_DATE (:DNTDT_YY||'0'||:DNTDT_MM||:DNTDT_DD, 'YYYY-MM-DD'))"
)
BEGINDATA
,154612,,2001,10,31,,36845,6,,8060,60,7,0,"01",2,0,3,,,142,0.00,0,0,0,,"0","D",
"F",0,,"F",464,,,,,,,,,,"01","DN",,20011106,"DBLOCK",0,,0,0,20011031,,0.00
,154613,,2002,12,7,,24670,6,,28649,69,1,0,"01",2,0,3,,,142,0.00,0,0,0,,"0","D",
"F",0,,"F",432,,,,,,,,,,"01","DN",,20011210,"ELOPEZ",0,,0,0,20011207,,0.00
,154614,,2003,2,17,,24671,6,,28649,99999,1,0,"01",2,0,3,,,142,4.00,0,0,0,,"0",
"D","F",0,,"F",432,,,,,,,,,,"01","DN",,20011210,"ELOPEZ",0,,0,0,20011207,,0.00
,154615,,2003,2,7,,24671,6,,28649,99999,1,0,"01",2,0,3,,,142,4.00,0,0,0,,"0"
,"D","F",0,,"F",432,,,,,,,,,,"01","DN",,20011210,"ELOPEZ",0,,0,0,20011207,,0.00

 SQL*Loader command line:

#> sqlldr test/test CONTROL=load3tSR.ctl DIRECT=TRUE ERRORS=1000000

Obtained log file:

Insert option in effect for this table: APPEND
TRAILING NULLCOLS option in effect

Column Name Position Len Term Encl Datatype
------------------------------ ---------- ----- ---- ---- ---------------------
DNHSTD FIRST * , O(") CHARACTER
DNPAT NEXT * , O(") CHARACTER
DNPATY NEXT * , O(") CHARACTER
DNTDT_YY NEXT * , O(") CHARACTER
DNTDT_MM NEXT * , O(") CHARACTER
DNTDT_DD NEXT * , O(") CHARACTER
DNTDT NEXT * , O(") CHARACTER
SQL string for column : "DECODE(:DNTDT_MM,10,TO_DATE(:DNTDT_YY||:DNTDT_MM||:
DNTDT_DD,'YYYY-MM-DD'),11,TO_DATE(:DNTDT_YY||:DNTDT_MM||:DNTDT_DD,'YYYY-MM-DD'), 12,TO_DATE(:DNTDT_YY||:DNTDT_MM||:DNTDT_DD,'YYYY-MM-DD'),TO_DATE(:DNTDT_YY||'0'| |:DNTDT_MM||:DNTDT_DD,'YYYY-MM-DD'))"

Record 2: Rejected - Error on table AHSTDN_TMP.
ORA-00604: error occurred at recursive SQL level 1
ORA-01861: literal does not match format string

Record 4: Rejected - Error on table AHSTDN_TMP.
ORA-00604: error occurred at recursive SQL level 1
ORA-01861: literal does not match format string

Solution

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms