Home » RDBMS Server » Server Utilities » sqlldr-how to generate log file for all files loaded (Oracle 11.2.0.2.0)
sqlldr-how to generate log file for all files loaded [message #626187] Wed, 22 October 2014 09:10 Go to next message
f150
Messages: 13
Registered: September 2014
Junior Member
Hi,
I am running Unix shell script (ksh) which loads several files in Oracle db via sqlldr.
i created a loop so each file will be passed on to sqllldr and it will process it, now when I am done processing 1st file I can see the log for first file but it overwrites the log file for 2nd file. I tried to use (>>) append and took the data in another file but name of file wont appear in appended file, i could only get following info in appended file...
SQL*Loader: Release 11.2.0.3.0 - Production on Tue Oct 20 14:06:57 2014

Copyright (c) 1982, 2011, Oracle and/or its affiliates. All rights reserved.

logical records count xxxx commint point reached xxxx
logical records count xxxx commint point reached xxxx
logical records count xxxx commint point reached xxxx
logical records count xxxx commint point reached xxxx


while I want this info so I can track which file loaded and which did not loaded with failure reason...

Control File: controlfile.ctl
Data File: samplefilename.csv
Bad File: badfile.bad
Discard File: discard.dsc
(Allow 999 discards)

Total logical records skipped: 1
Total logical records read: 1260125
Total logical records rejected: 0
Total logical records discarded: 0

Run began on Tue Oct 20 14:06:57 2014
Run ended on Tue Oct 20 14:08:31 2014

Elapsed time was: 00:01:34.33
CPU time was: 00:00:13.12


thank you
Re: sqlldr-how to generate log file for all files loaded [message #626188 is a reply to message #626187] Wed, 22 October 2014 09:12 Go to previous messageGo to next message
joy_division
Messages: 4947
Registered: February 2005
Location: East Coast USA
Senior Member
If it's the same control file, why not just load all the files in a single load? You can put multiple INFILE parameters in your control file.
Re: sqlldr-how to generate log file for all files loaded [message #626189 is a reply to message #626187] Wed, 22 October 2014 09:15 Go to previous messageGo to next message
BlackSwan
Messages: 26607
Registered: January 2009
Location: SoCal
Senior Member
so what exactly do you expect from us?
You need to have the person who wrote this script to fix it to do what you desire.
Re: sqlldr-how to generate log file for all files loaded [message #626190 is a reply to message #626188] Wed, 22 October 2014 09:43 Go to previous messageGo to next message
f150
Messages: 13
Registered: September 2014
Junior Member
hi joy_division,
INFILE is assigned variable so it will pass the file one by one. would you suggest console output ?
Re: sqlldr-how to generate log file for all files loaded [message #626198 is a reply to message #626190] Wed, 22 October 2014 10:27 Go to previous messageGo to next message
Michel Cadot
Messages: 66653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Why don't you pass all the files to your script adding one INFILE statement in your (basic) control file (the one with no INFILE) to generate the current control file.

Re: sqlldr-how to generate log file for all files loaded [message #626215 is a reply to message #626198] Wed, 22 October 2014 20:25 Go to previous messageGo to next message
f150
Messages: 13
Registered: September 2014
Junior Member
Dear Michel, BlackSwan and joy divsion
any other way of doing it? because can not change the control file or any of infile statements as other team members had already validated the process. this is just one utility to add which will give log for all files processed instead of last one. it will help to track down which file failed with specific reason.

I appreciate your replies.
Re: sqlldr-how to generate log file for all files loaded [message #626216 is a reply to message #626215] Wed, 22 October 2014 20:34 Go to previous messageGo to next message
BlackSwan
Messages: 26607
Registered: January 2009
Location: SoCal
Senior Member
You told us what you can not change.
You need to tell us what you can change so we can focus on those items.

Please read and follow the forum guidelines, to enable us to help you:

http://www.orafaq.com/forum/t/88153/0/ and read http://www.orafaq.com/forum/t/174502/


post the whole content of the script file
Re: sqlldr-how to generate log file for all files loaded [message #626217 is a reply to message #626216] Wed, 22 October 2014 21:23 Go to previous messageGo to next message
f150
Messages: 13
Registered: September 2014
Junior Member
this is my shell script which calls sqlldr at line 11 and mastercontrol.ctl (line 40) is my control file, from which controlfile.ctl (line 40) is generated dynamically in loop (line 29).
so can not make change to control file.
logfile.log is over written every time loop executes, so it will hold the log for very last file only, I tried to append logfile.log in to processlog.log but it does not have the names of files processed and sqlldr errors (as mentioned in previous posts)

1 #!/bin/ksh
2
3
4  function(){
5
6
7
8
9  some scripts
10
11         //sqlldr path id/pw control=controlfile.ctl log=logfile.log>>processfile.log
12
13
14
15  }
16
17 function(){
18        some scripts 
19  }
20
21
22 function(){
23 some scripts
24  }
25
26
27
28
29  for file in path/path/path/*.xls ; do
30
31
32  loaderfilename=$file
33  controlnum=${loaderfilename##*/}
34
36  echo $controlnum
37 export controlipfile=$controlnum
38 tableNum=$(echo $controlipfile | sed 's/^[^-]*-\([^.]*\).*/\1/')
39 echo $tableNum
40 sed 's/fileName/'$controlipfile'/' mastercontrol.ctl > temp.ctl
41 cat temp.ctl | sed -e "s/ipfile/\"${controlipfile}\"/g"  > controlfile.ctl
42
43 some scripts logic related to function ()
44
45 done
Re: sqlldr-how to generate log file for all files loaded [message #626219 is a reply to message #626217] Wed, 22 October 2014 21:34 Go to previous messageGo to next message
BlackSwan
Messages: 26607
Registered: January 2009
Location: SoCal
Senior Member
#12 as below

cat logfile.log >> capture_file.log
Re: sqlldr-how to generate log file for all files loaded [message #626220 is a reply to message #626219] Wed, 22 October 2014 22:04 Go to previous messageGo to next message
f150
Messages: 13
Registered: September 2014
Junior Member
thank you a lot BlackSwan !!
you saved my night
it worked with 'cat'
Re: sqlldr-how to generate log file for all files loaded [message #626221 is a reply to message #626220] Wed, 22 October 2014 22:06 Go to previous messageGo to next message
BlackSwan
Messages: 26607
Registered: January 2009
Location: SoCal
Senior Member
just be sure at the start of the script to add the new line below

cp /dev/null capture_file.log
Re: sqlldr-how to generate log file for all files loaded [message #626222 is a reply to message #626221] Wed, 22 October 2014 22:08 Go to previous messageGo to next message
f150
Messages: 13
Registered: September 2014
Junior Member
add it at line 2 , so it will catch it from null bucket ?
Re: sqlldr-how to generate log file for all files loaded [message #626225 is a reply to message #626222] Thu, 23 October 2014 00:01 Go to previous message
Michel Cadot
Messages: 66653
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

This will just empty the file.

Previous Topic: SQL Loader Calling Stored Proc
Next Topic: Is there a way to import different database dump into a RAC database
Goto Forum:
  


Current Time: Tue Oct 22 14:19:13 CDT 2019