Home » SQL & PL/SQL » SQL & PL/SQL » Multiple files loading using SQL Loader
icon10.gif  Multiple files loading using SQL Loader [message #226502] Sat, 24 March 2007 04:48 Go to next message
prjbst
Messages: 7
Registered: March 2007
Junior Member
HEllo experts
I have a smal issue with the sqlloader. i have 10 files.sqlloader should

load data one file after another(after completing the first file it should

go to the next file and soon...) after completing all the files it chould

come out...
Along with it I want the file name also to be store as an value in a column.
I am using Windows XP as an OS
Re: Multiple files loading using SQL Loader [message #226521 is a reply to message #226502] Sat, 24 March 2007 09:26 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
In my opinion, this post/problem has nothing to do with SQL or PL/SQL or even SQL*Loader.
The problem is how to write an OS shell script to meet the OP requirements.
What has been left unstated is how this yet unwritten script is supposed to determine which 10 files out of all the files on this system are the ones to be loaded & into which table the data is supposed to reside.

Re: Multiple files loading using SQL Loader [message #226522 is a reply to message #226502] Sat, 24 March 2007 09:29 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Are files supposed to be loaded into the same table?

http://download-east.oracle.com/docs/cd/B10501_01/server.920/a96652/ch05.htm#1004998
To load filename into database, use this method.
http://www.orafaq.com/forum/t/12234/0/
If all the files are in same directory, just script it.
Re: Multiple files loading using SQL Loader [message #226603 is a reply to message #226521] Sun, 25 March 2007 17:32 Go to previous messageGo to next message
prjbst
Messages: 7
Registered: March 2007
Junior Member
Hi,
Thanks for replying, We are having the case where all the files will reside in a common directory and will have same naming convention and format. Here the name of the file wld be like x1, x2...
Can u plz suggest or tell me the code for script in windows
Re: Multiple files loading using SQL Loader [message #226604 is a reply to message #226522] Sun, 25 March 2007 17:38 Go to previous messageGo to next message
prjbst
Messages: 7
Registered: March 2007
Junior Member
Hi Mahesh,
Thanks for replying, Can u plz suggest me somethin similar for windows also,
bcoz i m having exactly similar problem wat u have explained in ur reply
Re: Multiple files loading using SQL Loader [message #226605 is a reply to message #226502] Sun, 25 March 2007 17:39 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
IMO, Windoze does not have a native usable scripting language.
I'd do this task in PERL, but I suspect this won't be your 1st choice.
It is not clear whether or not the filenames will always be the same. If so just hardcode & be done with this nonsense.
If file names will be variable, consideration should be given to hiring somebody who can do more than just spell SQL.
Re: Multiple files loading using SQL Loader [message #226606 is a reply to message #226605] Sun, 25 March 2007 17:44 Go to previous messageGo to next message
prjbst
Messages: 7
Registered: March 2007
Junior Member
Hi,
Since file name is being variable we need a script but not in perl, as it is not in our requirement, n i dont hav any problem in sql. Since SQL was never a problem question is of script but not in perl as it is not in clients requirement
Re: Multiple files loading using SQL Loader [message #226608 is a reply to message #226606] Sun, 25 March 2007 17:47 Go to previous messageGo to next message
prjbst
Messages: 7
Registered: March 2007
Junior Member
wld VB scripting work in this case ?
Re: Multiple files loading using SQL Loader [message #226843 is a reply to message #226608] Mon, 26 March 2007 14:25 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
>> wld VB scripting work in this case ?
Might be.
There are native windows scripting methods available.
Only it is not much favoured by many folks including myself.
I am not into windows scripting. But can give a try when i get some time.
Meanwhile, you can use the shell scripts from the provided examples against in a windows environment using emulators like CygWIN or MKSToolkit (I always use MKSToolkint and never ever bothered about windows scripting Smile)
Re: Multiple files loading using SQL Loader [message #227297 is a reply to message #226843] Tue, 27 March 2007 22:47 Go to previous messageGo to next message
prjbst
Messages: 7
Registered: March 2007
Junior Member
Hi I need to write a batch program for dynamic creation of control file but I am getting the error as : "The file is being accessed by another process". I am writing this batch script in windows ..

@echo off
cd c:\checking
for %%f in (*.txt) do (

cd c:\checking
For /F "tokens=1* delims=." %%a in ("%%f") Do ( echo a value is %%a
cd c:\checking

:loop
if '%%a' == '' Goto continue
echo load data > member.ctl
echo infile '%%a' >> member.ctl
echo insert into table checking >> member.ctl
echo fields terminated by ',' >>member.ctl
echo trailing nullcols >>member.ctl
echo ( >> member.ctl
echo name, >>member.ctl
echo Marks, >> member.ctl
echo file_id constants '%%a' >> member.ctl
echo ) >> member.ctl

:end
shift
)
:continue


)
cd \


This is the script I am using in windows
Re: Multiple files loading using SQL Loader [message #227299 is a reply to message #226502] Tue, 27 March 2007 22:53 Go to previous messageGo to next message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
> I am getting the error as : "The file is being accessed by another process". I am writing this batch script in windows
IMO, this problem is not appropriate here & should be in some MicroSoft forum.
Re: Multiple files loading using SQL Loader [message #227562 is a reply to message #226502] Wed, 28 March 2007 11:43 Go to previous messageGo to next message
prjbst
Messages: 7
Registered: March 2007
Junior Member
Thanxs for all of ur efforts, I got the resolution in windows
Re: Multiple files loading using SQL Loader [message #227564 is a reply to message #227297] Wed, 28 March 2007 11:53 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
I appreciate the effort you put into this Smile
All did is just a little clean up in your script.
You were almost there.
I did a workaround. May someone having batch scripting knowledge could fix it and make code look better~

C:\ora>somebat.bat
About to load file 1.txt
this is the controlfile
load data
infile '1.txt'
insert into table checking
fields terminated by ','
trailing nullcols
(
name,
Marks,
file_id constant '1.txt'
)
sqlldr userid=user/pass control=member.ctl
About to load file readme.txt
this is the controlfile
load data
infile 'readme.txt'
insert into table checking
fields terminated by ','
trailing nullcols
(
name,
Marks,
file_id constant 'readme.txt'
)
sqlldr userid=user/pass control=member.ctl
C:\ora>
C:\ora>
C:\ora>
C:\ora>more somebat.bat
@echo off
for %%f in (*.txt) do (
        for /F %%a in ("%%f") do (
                                    echo load data > member.ctl
                                    echo infile '%%a' >> member.ctl
                                    echo insert into table checking >> member.ctl
                                    echo fields terminated by ','   >>member.ctl
                                    echo trailing nullcols >>member.ctl
                                    echo ( >> member.ctl
                                    echo name, >>member.ctl
                                    echo Marks, >>member.ctl
                                    echo file_id constant '%%a'  >>member.ctl
                                    rem
                                    rem The problem was echoing ) into member.ctl.
                                    rem I was not able to make it work. As a workaround, i did this
                                    type closebr >>member.ctl
                                    rem the file closebr has only one entry which is ")".
                                    echo About to load file %%a
                                    echo this is the controlfile
                                    more member.ctl
                                    rem
                                    rem remove this echo on next line while loadind
                                    rem
                                    echo sqlldr userid=user/pass control=member.ctl
                                 )

        )


C:\ora>
Re: Multiple files loading using SQL Loader [message #227565 is a reply to message #227564] Wed, 28 March 2007 11:53 Go to previous message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
Ah,
a few minutes late~
Previous Topic: How to get either of two records?
Next Topic: Please help me in Dynamic Row to Column in Oracle 8i
Goto Forum:
  


Current Time: Sat Dec 10 14:26:50 CST 2016

Total time taken to generate the page: 0.04377 seconds