| SQL LOADER (sqlldr) [message #295139] |
Mon, 21 January 2008 07:53  |
talk2neyo
Messages: 4
Registered: January 2008
|
Junior Member |
|
|
I have this problem using sqlldr with multiple files
controlfile:
LOAD DATA
INFILE "mnt20080112_10100*.unl"
append
INTO TABLE rec_bill
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(id,name,amt,time)
ERROR message received after execution:
SQL*Loader-500: Unable to open file (mnt20080112_10100*.unl)
SQL*Loader-553: file not found
SQL*Loader-509: System error: No such file or directory
the problem is that sqlldr does not recognise the wildcard (*)in the file name mnt20080112_10100*.unl
and even when it is specified thus mnt20080112_10100\*.unl
it still gives same error.
can anyone help on how to make sqlldr process multiple files at once .
|
|
|
|
|
|
| Re: SQL LOADER (sqlldr) [message #295142 is a reply to message #295139] |
Mon, 21 January 2008 08:07  |
 |
Michel Cadot
Messages: 68729
Registered: March 2007
Location: Saint-Maur, France, https...
|
Senior Member
Account Moderator |
|
|
1/ As you don't post in the first forum I think you had a look at the list. Don't you think: "Server Utilities: Datapump, Import, Export, SQL*Loader..." is a more appropriate one?
2/ Execute the process for each file or create a pipe in which you enter the files using cat or use multiple inffile statements.
Regards
Michel
[Updated on: Mon, 21 January 2008 08:07] Report message to a moderator |
|
|
|
| Re: SQL LOADER (sqlldr) [message #295155 is a reply to message #295141] |
Mon, 21 January 2008 08:36 |
talk2neyo
Messages: 4
Registered: January 2008
|
Junior Member |
|
|
Hello Vamsi
that was a nice one
LOAD DATA
INFILE file1.dat
INFILE file2.dat
INFILE file3.dat
APPEND
INTO TABLE emp
( empno POSITION(1:4) INTEGER EXTERNAL,
ename POSITION(6:15) CHAR,
deptno POSITION(17:18) CHAR,
mgr POSITION(20:23) INTEGER EXTERNAL
but the issue is if i have like thousands of this file then i have to specify multiple INFILE file1.dat in their thousands for each file name
I actually thought about using a loop script but there still seem to be a problem , check it in my next reply.
|
|
|
|
| Re: SQL LOADER (sqlldr) [message #295158 is a reply to message #295142] |
Mon, 21 January 2008 08:44 |
talk2neyo
Messages: 4
Registered: January 2008
|
Junior Member |
|
|
Hello Michel , i agree in using a pipe to process each file , see below which is still a problem
s1="_101"
s2="_102"
h='_'
dt=`date "+%Y%m%d"`
g=`expr $dt - 4`
callmnr1="mnr"$g$s1
callmnr2="mnr"$g$s2
for i in 0001 0002 0003 0004 0005 0006 0007 0008
do
t1=$callmnr1$i
for b in 0001 0002 0003 0004 0005 0006 0007 0008 0009 0010 0011 0012 0013
do
j=$t1$h$b.unl
echo $j
LOAD DATA
INFILE $j
append
INTO TABLE mnr_bill
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(id,name,amt,time)
done
done
sqlldr does not understand ( INFILE $j )
|
|
|
|
|
|
|
|
| Re: SQL LOADER (sqlldr) [message #295176 is a reply to message #295170] |
Mon, 21 January 2008 10:55 |
talk2neyo
Messages: 4
Registered: January 2008
|
Junior Member |
|
|
Hi all , thanks for your support,i probably assume and did give some menial informations or either i didnt express myself well in some areas, however below is the final solution and it worked very fine.
THE LOOP SCRIPT | with sqlldr calling
s1="_101"
s2="_102"
h='_'
dt=`date "+%Y%m%d"`
g=`expr $dt - 9`
callmnr1="mnr"$g$s1
callmnr2="mnr"$g$s2
for i in 0001 0002 0003 0004 0005 0006 0007 0008
do
t1=$callmnr1$i
for b in 0001 0002 0003 0004 0005 0006 0007 0008 0009 0010 0011 0012
do
j=$t1$h$b.unl
echo $j
sqlldr userid='admin/admin' data=$j control=load.ctl log=mnrlog.log
done
done
THE CONTROL FILE :
LOAD DATA
INFILE '/home/oracle/MNR/mnr20080112_3010001_0035.unl'
append
INTO TABLE mnr_bill
FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"'
TRAILING NULLCOLS
(id,name,amt,time)
the sqlldr process each file from ( data = $j ) and ignores the file specified in the control file.
Thanks once again.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
