1
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?

mauryat
  • 1,610
  • 5
  • 29
  • 53

1 Answers1

4

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.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • I have two consecutive commands in my bash script: sqlldr followed by sqlplus command to run a stored procedure. It appears that the sqlldr didn't fully finish committing to a table while the stored procedure tried accessing that very table. Any idea what could have gone wrong? – mauryat Jan 07 '14 at 15:50
  • @user640378 - I assume you didn't run `sqlldr` in the background. You can't see a partial commit, and with the scenario you described there would only be one commit. Was some data missing to the procedure but visible later? You'll need to explain what you saw in more detail I think. Did `sqlldr` maybe exit without processong the whole file, perhaps from hitting the reject limit? – Alex Poole Jan 07 '14 at 22:20
  • I created a new question with all the details: http://stackoverflow.com/q/21027330/640378 – mauryat Jan 09 '14 at 17:40