OraFAQ Forum: Server Utilities » How to load multiple files into the database using sql*loader?

Home » RDBMS Server » Server Utilities » How to load multiple files into the database using sql*loader? (Oracle 10g)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

How to load multiple files into the database using sql*loader? [message #385816]

Wed, 11 February 2009 03:32

aviva4500
Messages: 122
Registered: July 2008
Location: bangalore

Senior Member

Dear All,
I have small clarification I don't know whether it is possible i have a folder which has 100 datafiles where each datafile is named as 11/2/2009.txt,10/2/2009.txt.... and so on.Where all the datafiles are stored in a particular folder so that each file in the folder should get append to the table on datewise , my concern is whether we need to mention all the datafiles names in the control file or is there any method to load the data by use of sql*loader.

Thanks and Regards,
Hammer

Report message to a moderator

Re: How to load multiple files into the database using sql*loader? [message #385823 is a reply to message #385816]

Wed, 11 February 2009 04:02

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Loop on all files in your OS script file.

Regards
Michel

Report message to a moderator

Re: How to load multiple files into the database using sql*loader? [message #385824 is a reply to message #385823]

Wed, 11 February 2009 04:05

aviva4500
Messages: 122
Registered: July 2008
Location: bangalore

Senior Member

Dear Michel,
Thanks for your immediate reply.Can you give me clue that how to loop all the files.Can you explain a bit on OS script file.

Thanks And Regards,
Hammer

Report message to a moderator

Re: How to load multiple files into the database using sql*loader? [message #385825 is a reply to message #385824]

Wed, 11 February 2009 04:13

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

It depends on your OS and shell.

Regards
Michel

Report message to a moderator

Re: How to load multiple files into the database using sql*loader? [message #385826 is a reply to message #385825]

Wed, 11 February 2009 04:29

aviva4500
Messages: 122
Registered: July 2008
Location: bangalore

Senior Member

Dear michel,
You made my doubt clear..I understood from you that it depends upon OS.The server in which the oracle ressides is on windows any sample script can you show with example so that i can mke an incisive analysis.

Thanks And Regards,
Hammer.

Report message to a moderator

Re: How to load multiple files into the database using sql*loader? [message #385828 is a reply to message #385826]

Wed, 11 February 2009 04:42

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

They are examples in Windows forum.
You can do something like:

for /f %f in ('dir/b') do sqlldr control=myctl data=%f

Regards
Michel

Report message to a moderator

Re: How to load multiple files into the database using sql*loader? [message #386614 is a reply to message #385828]

Mon, 16 February 2009 04:31

aviva4500
Messages: 122
Registered: July 2008
Location: bangalore

Senior Member

Dear Michel,

Thanks for your reply.Can you give me a precise example.Please correct me if i am wrong, does shell scripts syntax vary for different operating system. I have gone through the windows froum but i am not good in shell scripting to dig it up. Can you give me the example syntax to invoke the Oracle Loader into the shell scripting. I don't know whether this is possible, If i have 10 files named 01-JAN-2009.txt,02-JAN-2009.txt....inside a single folder.Inside the control file can i use (like *.txt) so that it will load all the files.

Thanka and Regards,
Hammer.

Report message to a moderator

Re: How to load multiple files into the database using sql*loader? [message #386626 is a reply to message #386614]

Mon, 16 February 2009 04:45

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

What I posted IS a precise example.

Regards
Michel

Report message to a moderator

Re: How to load multiple files into the database using sql*loader? [message #386649 is a reply to message #386626]

Mon, 16 February 2009 07:01

aviva4500
Messages: 122
Registered: July 2008
Location: bangalore

Senior Member

Dear Michel,
Yes Michel I agree but I would like you provide me any links for this part like windows forum link etc...Your input values a lot for me.

Thanks in Advance.

Thanks and Regards,
Hammer.

Report message to a moderator

Re: How to load multiple files into the database using sql*loader? [message #386652 is a reply to message #386649]

Mon, 16 February 2009 07:29

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Sorry that Google is broken for you.

Regards
Michel

Report message to a moderator

Re: How to load multiple files into the database using sql*loader? [message #386766 is a reply to message #386652]

Mon, 16 February 2009 23:36

aviva4500
Messages: 122
Registered: July 2008
Location: bangalore

Senior Member

Dear Michel,
Google is not broken for me.In Command Prompt when I issue DIR c:\load\*.txt it displays all the text files corresponding to that folder.

C:\>DIR C:\LOAD\*.txt
 Volume in drive C has no label.
 Volume Serial Number is EC8F-43E0

 Directory of C:\LOAD

01/16/2009  04:54 PM         3,852,881 10-JAN-2009.txt
01/16/2009  04:54 PM         3,852,881 load2.txt
01/16/2009  04:54 PM         3,852,881 load3.txt
               3 File(s)     11,558,643 bytes
               0 Dir(s)  13,988,012,032 bytes free

So in command prompt whether i need to issue the below statement.

c:\dir c:\load\*.txt do sqlldr test/test control=c:\controlfiles\myctl.ctl log=forall.log

Here i have one more clarification what needs to be mention in the infile i am confused in this part.

Thnaks and Regards,
Hammer

Report message to a moderator

Re: How to load multiple files into the database using sql*loader? [message #386786 is a reply to message #386766]

Tue, 17 February 2009 00:34

Littlefoot
Messages: 21808
Registered: June 2005
Location: Croatia, Europe

Senior Member
Account Moderator

Michel's FOR statement should be part of a batch (.BAT) file.

Report message to a moderator

Re: How to load multiple files into the database using sql*loader? [message #386878 is a reply to message #386786]

Tue, 17 February 2009 03:33

aviva4500
Messages: 122
Registered: July 2008
Location: bangalore

Senior Member

Dear LittleFoot,
Sorry if i am wrong, like the below scripts it looks like.

@echo off
for /f %f in ('dir c:\load\*.txt') do sqlldr test/test control=c:\controlfiles\load.ctl log=test.log

Thanks and Regards,
Hammer

[Updated on: Tue, 17 February 2009 03:35]

Report message to a moderator

Re: How to load multiple files into the database using sql*loader? [message #386900 is a reply to message #386878]

Tue, 17 February 2009 04:06

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Merge what I posted and what you posted and you have your solution.

Regards
Michel

Report message to a moderator

Re: How to load multiple files into the database using sql*loader? [message #386917 is a reply to message #386900]

Tue, 17 February 2009 04:50

aviva4500
Messages: 122
Registered: July 2008
Location: bangalore

Senior Member

Hi Michel,
When i issue the command dir /c in command prompt it dispalys all the files like .txt files,.log files and so on.From your post i came to know that since it is windows operating system we need to make use of the batch files to run the task.

correct me if i am wrong, below is my batch file

@echo off
for /f %f in ('dir c:\load\*.txt') do (sqlldr test/test control=c:\controlfiles\load.ctl log=test.log)
eof

So in the above batch file 'dir c:\load\*.txt' this part will give the results of all the file names corresponding to the particular folder and each .txt filename is assigned to the variable %f.But in control file what needs to be mentioned in the infile parameter.

Thanks and Regards,
Hammer

[Updated on: Tue, 17 February 2009 04:54]

Report message to a moderator

Re: How to load multiple files into the database using sql*loader? [message #386920 is a reply to message #386917]

Tue, 17 February 2009 04:55

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

In my post I used:
1/ /b option in dir
2/ %f variable in sqlldr

Regards
Michel

Report message to a moderator

Re: How to load multiple files into the database using sql*loader? [message #386925 is a reply to message #386920]

Tue, 17 February 2009 05:16

aviva4500
Messages: 122
Registered: July 2008
Location: bangalore

Senior Member

Dear Michel,

Below is the .bat file i have changed.

@echo off
for /f %f in ('dir c:\load\*.txt') do sqlldr test/test control=c:\controlfiles\load.ctl log=test.log data=%f
eof

My control file looks like below whether i need to mention %f in the infile parameter?

Can you give me a clue.

OPTIONS (ROWS=1000, readsize=12582912, BINDSIZE=12582912, SILENT=FEEDBACK,DISCARDS)
LOAD DATA
infile 'C:\BULK\LOAD1.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)

Thanks and Regards,
Hammer

[Updated on: Tue, 17 February 2009 05:18]

Report message to a moderator

Re: How to load multiple files into the database using sql*loader? [message #386927 is a reply to message #386925]

Tue, 17 February 2009 05:20

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

My control file looks like below whether i need to mention %f in the infile parameter?

No, you need to remove infile from the control file.

Regards
Michel

Report message to a moderator

Re: How to load multiple files into the database using sql*loader? [message #386948 is a reply to message #386927]

Tue, 17 February 2009 06:05

aviva4500
Messages: 122
Registered: July 2008
Location: bangalore

Senior Member

Dear Michel,
I think i am near to the results.Below is my batch file.

@echo off
for /f %f in ('dir c:\load\*.txt') do sqlldr test/test control=c:\controlfiles\load.ctl log=test.log data=%f
eof

I named the batch file as test.bat in c drive.

My control file looks like below,

OPTIONS (ROWS=1000, readsize=12582912, BINDSIZE=12582912, SILENT=FEEDBACK,DISCARDS)
LOAD DATA
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)

when i issue the below command it fetches the results saying that the file exist.

C:\>dir c:\load\*.txt
 Volume in drive C has no label.
 Volume Serial Number is EC8F-43E0

 Directory of c:\load

01/16/2009  04:54 PM         3,852,881 10-JAN-2009.txt
01/16/2009  04:54 PM         3,852,881 load2.txt
01/16/2009  04:54 PM         3,852,881 load3.txt
               3 File(s)     11,558,643 bytes
               0 Dir(s)  13,715,406,848 bytes free

C:\>

But when i run the batch file test.bat in command prompt it says that the file not found.

C:\>dir c:\load\*.txt
Volume in drive C has no label.
Volume Serial Number is EC8F-43E0

Directory of c:\load

01/16/2009 04:54 PM 3,852,881 10-JAN-2009.txt
01/16/2009 04:54 PM 3,852,881 load2.txt
01/16/2009 04:54 PM 3,852,881 load3.txt
3 File(s) 11,558,643 bytes
0 Dir(s) 13,715,406,848 bytes free

C:\>test.bat
File Not Found

SQL*Loader: Release 10.1.0.2.0 - Production on Tue Feb 17 17:32:54 2009

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

SQL*Loader-500: Unable to open file (f.dat)
SQL*Loader-553: file not found
SQL*Loader-509: System error: The system cannot find the file specified.
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

SQL*Loader: Release 10.1.0.2.0 - Production on Tue Feb 17 17:32:54 2009

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

SQL*Loader-500: Unable to open file (f.dat)
SQL*Loader-553: file not found
SQL*Loader-509: System error: The system cannot find the file specified.
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

SQL*Loader: Release 10.1.0.2.0 - Production on Tue Feb 17 17:32:54 2009

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

SQL*Loader-500: Unable to open file (f.dat)
SQL*Loader-553: file not found
SQL*Loader-509: System error: The system cannot find the file specified.
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.
'eof' is not recognized as an internal or external command,
operable program or batch file.

C:\>

Thanks and Regards,
Hammer

Report message to a moderator

Re: How to load multiple files into the database using sql*loader? [message #386953 is a reply to message #386948]

Tue, 17 February 2009 06:33

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Michel Cadot wrote on Tue, 17 February 2009 11:55

In my post I used:
1/ /b option in dir
2/ %f variable in sqlldr

Regards
Michel

If you use it in a batch file (not at command line) you have to use %%f

Try your script with "echo" instead of sqlldr to debug and get the correct syntax.

Regards
Michel

[Updated on: Tue, 17 February 2009 06:35]

Report message to a moderator

Re: How to load multiple files into the database using sql*loader? [message #386962 is a reply to message #386953]

Tue, 17 February 2009 07:05

aviva4500
Messages: 122
Registered: July 2008
Location: bangalore

Senior Member

Dear Michel,
Sorry that i have posted the wrong .bat file i apoligise for my mistake,actually this is my batch file

@echo off
for /f %%f in ('dir c:\load\*.txt') do (sqlldr test/test control=c:\controlfiles\load.ctl log=c:controlfiles\test.log data=%%f)

But still the same message whether in data=(i need to specify the root path of the datafiles ?).

Thanks and Regards,
Hammer

Report message to a moderator

Re: How to load multiple files into the database using sql*loader? [message #386964 is a reply to message #386962]

Tue, 17 February 2009 07:18

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Quote:

i need to specify the root path of the datafiles ?

Yes.

Regards
Michel

Report message to a moderator

Re: How to load multiple files into the database using sql*loader? [message #386969 is a reply to message #386964]

Tue, 17 February 2009 07:30

aviva4500
Messages: 122
Registered: July 2008
Location: bangalore

Senior Member

Dear Michel,

Something like this i need to mention in the batch file.

@echo off
for /f %%f in ('dir c:\load\*.txt') do (sqlldr test/test control=c:\controlfiles\myctl.ctl log=c:\controlfiles\test.log data=c:\load\%%f)

Thanks and Regards,
Hammer

[Updated on: Tue, 17 February 2009 07:32]

Report message to a moderator

Re: How to load multiple files into the database using sql*loader? [message #386986 is a reply to message #386969]

Tue, 17 February 2009 08:19

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Something like this, yes.

Regards
Michel

Report message to a moderator

Re: How to load multiple files into the database using sql*loader? [message #387158 is a reply to message #386986]

Wed, 18 February 2009 01:39

aviva4500
Messages: 122
Registered: July 2008
Location: bangalore

Senior Member

Dear Michel,
I tried yesterday at my home, but still the same that unable to find the file.Can i know what have missed in the syntax i have posted above.

Thanks and Regards,
Hammer

Report message to a moderator

Re: How to load multiple files into the database using sql*loader? [message #387162 is a reply to message #387158]

Wed, 18 February 2009 01:52

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

I can't see what you have on your screen and I don't know what you want for what you have on disk.
Note: don't explain it, show it.
Do as I said:

Quote:

Try your script with "echo" instead of sqlldr to debug and get the correct syntax.

Regards
Michel

Report message to a moderator

Re: How to load multiple files into the database using sql*loader? [message #387188 is a reply to message #387162]

Wed, 18 February 2009 03:51

aviva4500
Messages: 122
Registered: July 2008
Location: bangalore

Senior Member

Dear Michel,
I made the below test in command prompt,I used %f directly in the command prompt, for the test purpose I executed directly in the command prompt.Below is the error which i got,this is for your kind reference.

C:\Documents and Settings\Administrator>FOR /F %F IN ('DIR C:\LOAD\*.TXT') DO SQ
LLDR TEST/TEST CONTROL=C:\CONTROLFILES\MYCTL.CTL LOG=C:\CONTROLFILES\TEST.LOG DA
TA=C:\LOAD\%F

C:\Documents and Settings\Administrator>SQLLDR TEST/TEST CONTROL=C:\CONTROLFILES
\MYCTL.CTL LOG=C:\CONTROLFILES\TEST.LOG DATA=C:\LOAD\Volume

SQL*Loader: Release 10.1.0.2.0 - Production on Wed Feb 18 15:17:14 2009

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

SQL*Loader-500: Unable to open file (C:\LOAD\Volume.dat)
SQL*Loader-553: file not found
SQL*Loader-509: System error: The system cannot find the file specified.
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

C:\Documents and Settings\Administrator>SQLLDR TEST/TEST CONTROL=C:\CONTROLFILES
\MYCTL.CTL LOG=C:\CONTROLFILES\TEST.LOG DATA=C:\LOAD\Volume

SQL*Loader: Release 10.1.0.2.0 - Production on Wed Feb 18 15:17:14 2009

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

SQL*Loader-500: Unable to open file (C:\LOAD\Volume.dat)
SQL*Loader-553: file not found
SQL*Loader-509: System error: The system cannot find the file specified.
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

C:\Documents and Settings\Administrator>SQLLDR TEST/TEST CONTROL=C:\CONTROLFILES
\MYCTL.CTL LOG=C:\CONTROLFILES\TEST.LOG DATA=C:\LOAD\Directory

SQL*Loader: Release 10.1.0.2.0 - Production on Wed Feb 18 15:17:15 2009

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

SQL*Loader-500: Unable to open file (C:\LOAD\Directory.dat)
SQL*Loader-553: file not found
SQL*Loader-509: System error: The system cannot find the file specified.
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

C:\Documents and Settings\Administrator>SQLLDR TEST/TEST CONTROL=C:\CONTROLFILES
\MYCTL.CTL LOG=C:\CONTROLFILES\TEST.LOG DATA=C:\LOAD\01/16/2009

SQL*Loader: Release 10.1.0.2.0 - Production on Wed Feb 18 15:17:15 2009

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

SQL*Loader-500: Unable to open file (C:\LOAD\01/16/2009.dat)
SQL*Loader-553: file not found
SQL*Loader-509: System error: The system cannot find the file specified.
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

C:\Documents and Settings\Administrator>SQLLDR TEST/TEST CONTROL=C:\CONTROLFILES
\MYCTL.CTL LOG=C:\CONTROLFILES\TEST.LOG DATA=C:\LOAD\01/16/2009

SQL*Loader: Release 10.1.0.2.0 - Production on Wed Feb 18 15:17:15 2009

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

SQL*Loader-500: Unable to open file (C:\LOAD\01/16/2009.dat)
SQL*Loader-553: file not found
SQL*Loader-509: System error: The system cannot find the file specified.
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

C:\Documents and Settings\Administrator>SQLLDR TEST/TEST CONTROL=C:\CONTROLFILES
\MYCTL.CTL LOG=C:\CONTROLFILES\TEST.LOG DATA=C:\LOAD\2

SQL*Loader: Release 10.1.0.2.0 - Production on Wed Feb 18 15:17:15 2009

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

SQL*Loader-500: Unable to open file (C:\LOAD\2.dat)
SQL*Loader-553: file not found
SQL*Loader-509: System error: The system cannot find the file specified.
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

C:\Documents and Settings\Administrator>SQLLDR TEST/TEST CONTROL=C:\CONTROLFILES
\MYCTL.CTL LOG=C:\CONTROLFILES\TEST.LOG DATA=C:\LOAD\0

SQL*Loader: Release 10.1.0.2.0 - Production on Wed Feb 18 15:17:15 2009

Copyright (c) 1982, 2004, Oracle.  All rights reserved.

SQL*Loader-500: Unable to open file (C:\LOAD\0.dat)
SQL*Loader-553: file not found
SQL*Loader-509: System error: The system cannot find the file specified.
SQL*Loader-2026: the load was aborted because SQL Loader cannot continue.

C:\Documents and Settings\Administrator>

Thanks and Regards,
Hammer

[Updated on: Wed, 18 February 2009 03:55]

Report message to a moderator

Re: How to load multiple files into the database using sql*loader? [message #387201 is a reply to message #387188]

Wed, 18 February 2009 04:24

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Are you unable to read what already post several times:

Quote:

In my post I used:
1/ /b option in dir

Once again I don't have your directory and files SO I CAN'T SEE WHAT IS WRONG AND YOU DON'T FOLLOW WHAT I RECOMMEND PLEASE READ AND FOLLOW WHAT I SAID AND COME BACK WITH IT.

Regards
Michel

Report message to a moderator

Re: How to load multiple files into the database using sql*loader? [message #387214 is a reply to message #387201]

Wed, 18 February 2009 05:15

aviva4500
Messages: 122
Registered: July 2008
Location: bangalore

Senior Member

Dear Michel,

1. My text files are located in the folder named LOAD which has two text files with names load2.txt,load3.txt.

C:\Documents and Settings\Administrator>dir c:\load\*.txt
 Volume in drive C has no label.
 Volume Serial Number is EC8F-43E0

 Directory of c:\load

01/16/2009  04:54 PM         3,852,881 load2.txt
01/16/2009  04:54 PM         3,852,881 load3.txt
               2 File(s)      7,705,762 bytes
               0 Dir(s)  13,762,519,040 bytes free

C:\Documents and Settings\Administrator>

2. When I issue the command in the command prompt whether the particular file exists it was there which was show above.

3. Below is my control file which is named as myctl.ctl which is located in the c drive

C:\Documents and Settings\Administrator>dir c:\controlfiles\myctl.ctl
 Volume in drive C has no label.
 Volume Serial Number is EC8F-43E0

 Directory of c:\controlfiles

02/17/2009  04:53 PM               454 MYCTL.CTL
               1 File(s)            454 bytes
               0 Dir(s)  13,789,933,568 bytes free

C:\Documents and Settings\Administrator>

4. The content in the control file looks ike below.

OPTIONS (ROWS=1000, readsize=12582912, BINDSIZE=12582912, SILENT=FEEDBACK,DISCARDS)
LOAD DATA
badfile 'C:\BULK\XYZ.BAD'
DISCARDFILE 'C:\BULK\XYZ.DSC'
REPLACE
INTO TABLE PRODUCT
FIELDS TERMINATED BY ''
OPTIONALLY ENCLOSED BY '"' TRAILING NULLCOLS
(a,
b,
c,
d,
e,
f,
g..)

5. My data in the text files looks like below.

EX1021	2516800	0	0	0	1307	0	6R	LMV	LAX	432	22-Dec-07
EX1022	96800	0	0	0	1278	0	6R	LMV	ONT	432	22-Dec-07
EX1023	290400	0	0	0	1290	0	6R	HSV	MEX	432	22-Dec-07
EX1024	3194400	0	0	0	1307	0	6R	LAX	LMV	432	22-Dec-07
EX1025	580800	0	0	0	1553	0	6R	MEX	LAX	432	22-Dec-07

So the above description is for your kind reference,

I opened the command prompt and issued the below statement.

c:\Documents and Settings\Administrator\>for /f %f in ('dir/b') do sqlldr test/test control=c:\controlfiles\myctl.ctl log=c:\controlfiles\test.log data=c:\load\%f

But when i issue dir/b in the command prompt it displays all the files.

Microsoft Windows XP [Version 5.1.2600]
(C) Copyright 1985-2001 Microsoft Corp.

C:\Documents and Settings\Administrator>dir /b
.businessobjects
.WASRegistry
._cie.trace.xml.lck
afiedt.buf
BrioPlatformInstall.log
BrioPlatformInstall.log.0
BrioPlatformInstall.log.1
cogtrwin.ini
Contacts
CONTROL2.log
DDL_LIST.SQL
Desktop
DROP.SQL
Favorites
ismanager_workspace
is_install.rsp
LOAD1.log
LOADALL.LOG
LOADER.log
LOADTEST.LOG
My Documents
OUTPUT_TO_FLAT_FILE.TXT
portdef.props
sqlnet.log
Start Menu
test.ctl
test.dat
TEST.LOG
TEST1.LOG
TEST1.LOG;
test12.log
TEST123.LOG
Tracing


C:\Documents and Settings\Administrator>

Now i have changed to dir/b in the below statement.

c:\Documents and Settings\Administrator\>for /f %f in ('dir/b') do sqlldr test/test control=c:\controlfiles\myctl.ctl log=c:\controlfiles\test.log data=c:\load\%f

Could you correct me where i am missing.

Thanks and Regards,
Hammer

Report message to a moderator

Re: How to load multiple files into the database using sql*loader? [message #387218 is a reply to message #387214]

Wed, 18 February 2009 05:33

Michel Cadot
Messages: 68647
Registered: March 2007
Location: Nanterre, France, http://...

Senior Member
Account Moderator

Combine the 2 "dir" command.
JUST TRY IT.

Regards
Michel

Report message to a moderator

Re: How to load multiple files into the database using sql*loader? [message #387468 is a reply to message #387214]

Thu, 19 February 2009 05:55

Barbara Boehmer
Messages: 9091
Registered: November 2002
Location: California, USA

Senior Member

Run this from the operating system command line:

FOR /F %F IN ('DIR C:\LOAD\*.TXT/b') DO SQLLDR test/test CONTROL=C:\CONTROLFILES\MYCTL.CTL LOG=C:\CONTROLFILES\TEST.LOG DATA=C:\LOAD\%F

Or put this in a .bat file:

FOR /F %%F IN ('DIR C:\LOAD\*.TXT/b') DO SQLLDR test/test CONTROL=C:\CONTROLFILES\MYCTL.CTL LOG=C:\CONTROLFILES\TEST.LOG DATA=C:\LOAD\%%F

Report message to a moderator

Re: How to load multiple files into the database using sql*loader? [message #387578 is a reply to message #387468]

Thu, 19 February 2009 23:19

aviva4500
Messages: 122
Registered: July 2008
Location: bangalore

Senior Member

Dear Barbara Bohemer,
Always your post regarding sql*loader really rocks.Thanks a lot.....

Dear Michel,
I would like to thank Michel for his support.

Thanks and Regards,
Hammer

[Updated on: Thu, 19 February 2009 23:22]

Report message to a moderator

Previous Topic:	SQL LOADER
Next Topic:	trailing option in ctl file

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Apr 29 04:23:17 CDT 2024