Home » RDBMS Server » Server Utilities » SQL LOADER (sqlldr)
SQL LOADER (sqlldr) [message #295139] Mon, 21 January 2008 07:53 Go to next message
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 #295141 is a reply to message #295139] Mon, 21 January 2008 08:03 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
Check this.

By
Vamsi
Re: SQL LOADER (sqlldr) [message #295142 is a reply to message #295139] Mon, 21 January 2008 08:07 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
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 Go to previous messageGo to next message
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 Go to previous messageGo to next message
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 #295162 is a reply to message #295158] Mon, 21 January 2008 09:40 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
please read and follow OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format. Use the "Preview Message" button.

Quote:
sqlldr does not understand ( INFILE $j )

I don't see any sqlldr statement in your script.
Where is the call?

Quote:
i agree in using a pipe to process each file ,

This is not what you posted, isn't it?

Regards
Michel
Re: SQL LOADER (sqlldr) [message #295170 is a reply to message #295155] Mon, 21 January 2008 10:17 Go to previous messageGo to next message
vamsi kasina
Messages: 2107
Registered: October 2003
Location: Riyadh, Saudi Arabia
Senior Member
May be you can put all the files in one folder. (I think the folder should contain only these files).
Then you can use INFILE *

By
Vamsi
Re: SQL LOADER (sqlldr) [message #295176 is a reply to message #295170] Mon, 21 January 2008 10:55 Go to previous message
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.
Previous Topic: Using a condition in SQL Loader for comma delimited file
Next Topic: sqlldr
Goto Forum:
  


Current Time: Sun Dec 04 20:21:39 CST 2016

Total time taken to generate the page: 0.10062 seconds