Home » RDBMS Server » Server Utilities » How to load data to Oracle Database using sql * loader? (Oracle 10g)
How to load data to Oracle Database using sql * loader? [message #385378] Mon, 09 February 2009 06:01 Go to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Dear All,

I have a flat file where in which the fiels are terminated by whitespace.Below is my control file for your kind
reference.But as mentioned in the below control file the line
fields are terminated by '' it means that spaces are there in
my flat file.But what i need to mention if the fields are
terminated by # or ||.What happened is my source data will be
getting loaded into a folder for day one the fields are
terminated by comma,day2 the fields are terminated by # and
day3 it is terminated by comma.IS there any option so that i can
mention in the control file all these three delimiters can be
mentioned.My concern is the data should load to the database
if the delimiter is anything.

OPTIONS (ROWS=1000, readsize=12582912, BINDSIZE=12582912, SILENT=FEEDBACK,DISCARDS)
LOAD DATA
infile 'C:\BULK\LOAD.TXT'
badfile 'C:\BULK\XYZ.BAD'
DISCARDFILE 'C:\BULK\XYZ.DSC'
INSERT INTO TABLE TEST
FIELDS TERMINATED BY ''
OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(a,         
b,       
c,       
d,
e,           
f,       
g,        
h,
i,         
j,           
k,   
l) 
            



Thanks and Regards,
Hammer.
Re: How to load data to Oracle Database using sql * loader? [message #385381 is a reply to message #385378] Mon, 09 February 2009 06:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
No, there is no way, but you can dynamically generates your control file from the script file that you use to launch sqlldr.

Regards
Michel
Re: How to load data to Oracle Database using sql * loader? [message #385389 is a reply to message #385381] Mon, 09 February 2009 06:23 Go to previous messageGo to next message
aviva4500
Messages: 122
Registered: July 2008
Location: bangalore
Senior Member
Dear Michel,
Thanks for your immediate reply is there any other way to achieve this and if there is chance can you give me a clue like a kind of shell or unix scripting.(This is my guess because i am new to shell and unix scripting)



Thanks and Regards,
Hammer.
Re: How to load data to Oracle Database using sql * loader? [message #385396 is a reply to message #385389] Mon, 09 February 2009 06:50 Go to previous message
Michel Cadot
Messages: 64152
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
As I said you can dynamically create your control file.
Something like:
cat >myctl.ctl <<EOF
OPTIONS (ROWS=1000, readsize=12582912, BINDSIZE=12582912, SILENT=FEEDBACK,DISCARDS)
LOAD DATA
infile '$1'
badfile 'C:\BULK\XYZ.BAD'
DISCARDFILE 'C:\BULK\XYZ.DSC'
INSERT INTO TABLE TEST
FIELDS TERMINATED BY '$2'
OPTIONALLY ENCLOSED BY '$3' TRAILING NULLCOLS
(a,         
b,       
c,       
d,
e,           
f,       
g,        
h,
i,         
j,           
k,   
l) 
EOF
sqlldr myuser/mypsw control=myctl.ctl ...

Regards
Michel
Previous Topic: Export -Import Speed up
Next Topic: Recover/Restore using Remote_Server backup file
Goto Forum:
  


Current Time: Sun Dec 11 00:27:50 CST 2016

Total time taken to generate the page: 0.08479 seconds