Problem loading default date column in sqlldr

Shell Programming and Scripting
1

Hi All,

My control file looks like:

LOAD DATA
APPEND
INTO TABLE MK9210_PROD_DL.MK9210_PROD_EG_6
FIELDS TERMINATED BY '|'
TRAILING NULLCOLS
(
STUDY CHAR
,PATIENT INTEGER EXTERNAL
,CPEVENT CHAR
,NAMEG CHAR
,REFIDEG CHAR
,POSEG CHAR
,METHEG CHAR
,EGDAT CHAR
,EGTIM CHAR
,COMEG CHAR
,COM1EG CHAR
,COM2EG CHAR
,COM3EG CHAR
,COM4EG CHAR
,BYRLD INTEGER EXTERNAL
,SEXLD CHAR
,EGCLSIG CHAR
,CATEG CHAR
,TESTEG CHAR
,EVALEG CHAR
,CORESEG CHAR
,CSCDEG CHAR
,CORSNEG INTEGER EXTERNAL
,CORSUEG CHAR
,DATLDFL CHAR
,EGFND CHAR
,EGFNDTP CHAR
, Blind_Break CONSTANT 'YES'
,Created_Timestamp DATE "nvl( to_date(:Created_Timestamp,'DD-MON-YYYY HH24:MI:SS
'), to_date('21-MAY-2014 14:45:46','DD-MON-YYYY HH24:MI:SS'))")

But The error message that is thrown is :ORA-01821: date format not recognized

2

check the loader log

3

At first glance,

  • Should there be a leading colon before the Created_Timestamp reference?
  • Should the ') to end the date format description be all on the same line?

What do you get in the log?

4

Since this question has zero to do with shell scripting and everything to do with the use of an Oracle utility, you'd be better served on a forum dedicated to use of said utility. Fortunately, there is such a forum at
https://community.oracle.com/community/developer/english/oracle\_database/export\_import\_sql\_loader\_%26\_external_tables/content

1 Like
5

I guess the issue is 'new line character' at date format definition in the last but one line