SQL Loader : Generate new log file each time

Question

I am using SQL Loader to import data from CSV file to a database table, and it works fine. Each time I run the command sqlldr, the log file is overridden with new infos. Is there a way to add a timestamp or something to keep the archive of those log files instead of overriding the same file and without passing the value in the command params.

.par file :

CONTROL=D:\projects\ctl\control.ctl
LOG=D:\projects\log\TRACK_MIGRATION.log
DATA=D:\projects\data\CUSTOMERS_SITES.csv

Want something like : TRACK_MIGRATION_20221229_113917.log

Wrap the call to sqlldr in a script renaming the log file after execution? — p3consulting, Dec 29 '22 at 16:32
That actually what I started doing, cause I think there is no native way to do it in sql loader. Thanks — SWEEPY, Dec 29 '22 at 16:38

score 2 · Accepted Answer · answered Dec 29 '22 at 20:57

After a long search, found no native way to automate naming the SQL Loader log file by adding a timestamp or something.

As mentioned above, I suggest wrapping the command line in a script that will take the responsibility of renaming your log file after it gets generated, or using the log= command line param which is a bit frustrating if you are running your command often.

For my case I found that our client use VBScript for such things in the server, so I decided using it to wrap my command line.

'Running SQL Loader command line
Dim oShell
Set oShell = WScript.CreateObject("WScript.Shell")
oShell.run "sqlldr userid=" & DBLogin & "/" & DBPwd & "@" & DBName & " parfile=parfile.par"
Set oShell = Nothing

'Renaming the log file (TRACK_MIGRATION_YYYYMMDD_HHMMSS.log)
Dim FSO
Dim todayDate
todayDate = Year(Now) & Month(Now) & Day(Now) & "_" & Hour(Now) & Minute(Now) & Second(Now)
Set FSO = CreateObject("Scripting.FileSystemObject")
FSO.MoveFile "TRACK_MIGRATION.log", "TRACK_MIGRATION_" & todayDate & ".log"

score 1 · Answer 2 · answered Dec 29 '22 at 17:17

1

You must modify the startup script to remove the log parameter from the parameter file and add the log parameter to the command line.

.par file

CONTROL=D:\projects\ctl\control.ctl
DATA=D:\projects\data\CUSTOMERS_SITES.csv

command line

sqlldr user/password@DB parfile=parfile.par log=log_%date%_%time:~0,2%%time:~3,2%%time:~6,2%.log

log file

29.12.2022  20:06             1 262 log_29.12.2022_200657.log
29.12.2022  20:12             1 262 log_29.12.2022_201224.log

answered Dec 29 '22 at 17:17

Dmitry Demin

2,006
2
15
18

Yes, it will work, but as I mentioned, it is not the way that I want to use, because each time I need to specify the log param – SWEEPY Dec 29 '22 at 20:13

SQL Loader : Generate new log file each time

2 Answers2