sqlloader script

[Roshan]

Hi Erman,

Environment details:
Red Hat Enterprise Linux Server release 7.2 (Maipo)
Oracle Database 12c

1. The ctlfile attached loads data from '/archicom/data/toprocess/outputDATA*.dat'
MOB_DATA.ctl

2. outputDATA*.dat is created by concatenating all outputDATA*.dat to one outputDATA_${d}_${n}.dat which is used in the ctl file(please find script below)
convertDATA.sh

3. Finally, the script below will run the MOB_DATA.ctl file using sqlloader and respective log and bad files will be created.
MOB_DATA.sh

I want to modify the script so that instead of concatenating all outputDATA*.dat to one outputDATA_${d}_${n}.dat file, individual dat files will be loaded using script in (1) and (3) and respective log files created.

Can you please advise a way I can proceed? If I insert a for loop in the ctl file, how will individual log files be created?

Regards,

Roshan

Regards,

[ErmanArslansOracleBlog]

If I understand you correctly, you want sqlloader to load  multiple .dat files instead of a single concataneted one. If this is what you are looking for, leave that dat files as is and write a wrapper script to execute sqlloader in a loop by giving the .dat files  as arguments one by one it.
The script can list the files, take them into an array and executing sqlloader command for each one of them.
This can be achieved by a bash script.

I mean, this can be achieved with a single ctl file.. (If the data in dat files are going into the same table and if they all have the same format  you only need 1 control file. ), so above method is okay.

You can also easily load all the dat files in a directory using a one-liner like below;

here is a windows loop example:
C:\erman>for %f in ( *.dat ) do sqlldr system/manager data.ctl data=%f

[Roshan]

Hi Erman,

I have implemented the for loop. For the logfiles, by default it will create a single logfile by default. Each time the dat file will be loaded, the default script will be renamed(date appended to it)

for i in `ls /archicom/data/chrg/sms*.unl`
do
d=$(date "+%d%m%Y-%H%M")
echo $i

mv /home/archicom/MOB_IN_SMS.log $i${d}.log

what is the difference between ` and ' ?

Regards,
Roshan

[ErmanArslansOracleBlog]

when you use put some command between the character -> ` , it is executed on the fly and its output is returned.

In your case, the for loop goes through the filenames which are the output of ls command.

The single quotes -> ' however is different. Enclosing characters in single quotes is used for preserving the literal value of each character within the quotes.

[Roshan]

thanks for support :)