This content has been marked as final. Show 6 replies
923315 wrote:Please post to the correct forum
i have one issue in the loader in a unix script.
i am using sql loader which will insert more than 1000 record from a .txt file into a table.
but i want to add one functionality that if more than 500 hundred error occurs then the unix script will get aborted.
for example if first 10 record are inserted then for 501 error my script has to stopr executing and no data will be saved in the database.
means alredy inserted 10 rows will get deleted.
is this possible with sql loader?
i am thinking that i will first all the record get inserted into the table.
then i will read the .bad file
if the line is greater than 500 then i will apply the rollback statement.
pls let me kinow how to do?
many thanks in advance..
Pl post details of OS and database versions.
Pl see if the ERRORS parameter can help
Yes, there is the ROWS sqlldr parameter that controls the commit frequency. For example, If you use the SQL*Loader parameter ROWS=1000, we asking sql*loader to commit after every thousand rows are loaded. You may also be able to try ROWS=9999_high_values to get SQL*Loader to commit at the end of the job, but beware of potential undo log (rollback segment) issues, such as the ORA-01555 Snapshot Too Old error.
The default commit is after 28 records. Youc an increase it to any number.
Use ERRORS=N in your sqlldr command line or parameter file, where N is an arbitrary large number. I don't know if there is any upper limit for this parameter. As you've allready found ot, the default is 50.
You can use Error=501 and rows = high number so whenever it will hit an maximum error limit your sqlldr job will quit and it will rollback.
Many thanks for your useful information..
now i just want to confirm that what happen
suppose loader load 10 row in database .
then continiously 501 rows it get error.
so in this case
whether my loader will stop executing?
what about the first 10 rows which was ionserted in the table? where they get committed or rollbacked?
please let me know above two answer. i have read that loader is auto commit. so i am confusing in the above situation?
Thanks in advance..
Q)whether my loader will stop executing?
Ans) Yes it will quit.
Q)what about the first 10 rows which was ionserted in the table? where they get committed or rollbacked?
Ans) The default auto commit is after every 28 rows. But you can change this default behavior by ROWS parameter. Like if you give very high number in ROWS(for ex ROWS=999999), now the commit will happen after 999999 not before that. and if your job quits after 500 errors , it should roll back. You can give this a shot.
Per my understanding please look into this one...
OPTIONS (SKIP=1, LOAD=0,ERRORS=5) LOAD DATA INFILE 'd:\test.txt' INSERT INTO TABLE emp_info2 FIELDS TERMINATED BY "," TRAILING NULLCOLS ( empno, ename, description )