ERRORS SQL LOADER

Hi have 100 records in my flat file while loading into staging table---- if any error even in single row then entire file be canceled HoW

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.

ERRORS (errors to allow)

Default: To see the default value for this parameter, invoke SQL*Loader without any parameters, as described in "Invoking SQL*Loader".

ERRORS specifies the maximum number of insert errors to allow. If the number of errors exceeds the value specified for ERRORS, then SQL*Loader terminates the load. To permit no errors at all, set ERRORS=0. To specify that all errors be allowed, use a very high number.

On a single-table load, SQL*Loader terminates the load when errors exceed this error limit. Any data inserted up that point, however, is committed.

SQL*Loader maintains the consistency of records across all tables. Therefore, multitable loads do not terminate immediately if errors exceed the error limit. When SQL*Loader encounters the maximum number of errors for a multitable load, it continues to load rows to ensure that valid rows previously loaded into tables are loaded into all tables and rejected rows are filtered out of all tables.

In all cases, SQL*Loader writes erroneous records to the bad file.

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.

SQLLDR CONTROL=sample.ctl, LOG=bar.log, BAD=baz.bad, DATA=etc.dat

USERID=scott/tiger, ERRORS=0, LOAD=2000, DISCARD=toss.dis,DISCARDMAX=5 ROWS=9999



OPTIONS (DIRECT=TRUE, SKIP=TRUE, ERRORS=0, rows=500000, )