Visit Our UNIX and Linux User Community


Problem loading default date column in sqlldr


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting Problem loading default date column in sqlldr
# 1  
Old 05-21-2014
Problem loading default date column in sqlldr

Hi All,

My control file looks like:
Code:
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  
Old 05-21-2014
check the loader log
# 3  
Old 05-21-2014
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  
Old 05-21-2014
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/communi...tables/content
This User Gave Thanks to edstevens For This Post:
# 5  
Old 05-23-2014
I guess the issue is 'new line character' at date format definition in the last but one line

Previous Thread | Next Thread
Test Your Knowledge in Computers #443
Difficulty: Easy
FHM, a men's lifestyle magazine, asked 1,344 women if they preferred to hang out with a computer science expert or a footballer, and 1,023 (76.116%) stated they preferred geeks over athletes.
True or False?

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

Facing problem in the sqlldr & shell script

Guys i am facing two problems : (1) when i create the sql loader file the date format i m getting is this 28-DEC-11 12.03.14.107137 AM; for this i m using this script but unable to load the files trailing nullcols ( SERIALNO, AMOUNT, CLASS, MDN, VDATE "to_date(:TIMESTAMP, 'DD-MON-YY... (6 Replies)
Discussion started by: xal_kaushi
6 Replies

2. UNIX for Advanced & Expert Users

OpenBoot Loading Problem

I am using a SunBlade 100 system and accidently set a new alias for keyboard, now when it start it gives the following error, The selected input device has no read routine. Keyboard not present using ttya for input and output. Now i cannot execute any commands on the OBP and i just gets... (3 Replies)
Discussion started by: sumwhriblong
3 Replies

3. Shell Programming and Scripting

loading Binary files with SQLLDR

Hi, Can anyonr please tell how to load a binary file through SQLLDR. I have the structure of the binary file in a seperate lb file but I don't know how to feed it to the sqlldr. (0 Replies)
Discussion started by: snowline84
0 Replies

4. Linux

Problem in loading library

I am trying to run seagull tool. But when i start it following error shown |E|Unable to open library file error |A|STOP log Please help me how i can recover from it. (1 Reply)
Discussion started by: mansoorulhaq
1 Replies

5. UNIX for Dummies Questions & Answers

value too large for column : sqlldr

Hi, I am a newbie to Linux(Suse).I am facing a problem with 'sqlldr' utility while trying to upload data to Database tables.My backend is Oracle and is using the UTF8 encoding format.I am trying to load a datafile which contains some Western European Characters.While loading am getting an error... (1 Reply)
Discussion started by: DILEEP410
1 Replies

6. SuSE

sqlldr : charset conversion while loading ?

Hi, I am a newbie to Linux(Suse).I am facing a problem with 'sqlldr' utility while trying to upload data to Database tables.My backend is Oracle and is using the UTF8 encoding format.I am trying to load a datafile which contains some Western European Characters.While loading am getting an... (0 Replies)
Discussion started by: DILEEP410
0 Replies

7. Solaris

Application Loading problem

I install a software xchm for opening chm files. I install software and also its required libraries. But when i start it; no GUI appears. Application got halt. Process name also appears in process list when i run top command. Appliation continously using cpu but no GUI appears. Please tell me... (7 Replies)
Discussion started by: mansoorulhaq
7 Replies

8. UNIX for Dummies Questions & Answers

LPAR Loading Problem

I'm having a major problem trying to load a LPAR. I've got through the HMC and shutdown and then activated the LPAR and it only gets to the Operator Panel Value of 0517. Now from some research I've found that this is to do with loading the rootvg. ( I think ) On the actual terminal all I get is... (0 Replies)
Discussion started by: KeesH
0 Replies

9. Shell Programming and Scripting

Problem of Data Loading....

i am studying a script which is used for data loading. it has functions which deletes all the existing data before loading and then loads new fresh data. but i am stuck up at function Replace into table ( col 1,col 2....) Does this signify All Inserts. (1 Reply)
Discussion started by: ankitgupta
1 Replies

10. Windows & DOS: Issues & Discussions

loading problem in XP help!!!!!!!!!!!

have a azuse mother borad 1600Mhz ram 512 hd 40gb will not install windows want to finish but will not take it! Is this a hard ware problem or a virus i use av norton 2002 and nod32 think got a virus not shure xp will not load any more (2 Replies)
Discussion started by: amicrawler2000
2 Replies

Featured Tech Videos