Does sqlldr auto-commit after the job completes?

Question

sqlldr user/pwd@host skip=1 control=$CUR_CTL.final data=$fpath log=data.log rows=10000 direct=true errors=999

I'm uploading a csv file with only 8 rows. Does the above command run a commit only after 10000 rows or does it also commit at the end of the job?

Answer 1

Yes, it will commit at the end of the file. It would be rather unfit for purpose if it didn't, really

Technically, since you have direct=y then you're doing a direct path load, so the ROWS command line parameter affects how often SQL*Loader does a data save rather than a commit during a large load. The only real difference is in index usability though.

The ROWS parameter identifies the number of rows you want to read from the data file before a data save. The default is to read all rows and save data once at the end of the load.

If you had 10008 rows in your file, it would perform a data save after the first 10000 rows, and then again after the last eight, and it would only make the indexes usable once it had reached the end of the file.

Having the ROWS higher than the actual number of the rows in the data file will not cause data to be ignored.

You can also check the exit status code, which will hopefully tell you that all rows were loaded successfully, and the log file will show the number of inserted rows.