Search This Blog

Tuesday, November 3, 2009

SQL Loader limit number of rows

Based on a query from one of our reader here is an example to illustrate how to limit number of rows to be loaded in SQL Loader. This can be done using option LOAD


OPTIONS (SKIP=1, LOAD=10, ERRORS=5)
LOAD DATA infile c:/sv_test.dat
REPLACE INTO TABLE sv_test_sql_tbl
FIELDS TERMINATED BY "," optionally enclosed by '"'
trailing nullcols
(
item_number "trim(:item_number)"
, vendor_name "trim(:vendor_name)"
, vendor_site_name "trim(:vendor_site_name)"
, supplier_item "trim(:supplier_item)"
, process_flag Constant 'UNPROCESSED'
)


In the example above the total records to be loaded is limited to 10, error records is 5 and 1 record is skipped.

These options can also be given with sqlldr command as follows

sqlldr control='sv_test.ctl' data='sv_test.dat' load=10 errors=5 skip=1


Keywords: SQL*LOADER, ERRORS, SKIP, LOAD

2 Comments:

vvvv said...

Thanks Suresh...

Regards,
Venkat

vvvv said...

Hi Suresh,

I just want to know,

What are the validations of interfaces?

Can u please elaborate on this?

Thanks,
Venkat

Copyright (c) All rights reserved. Presented by Suresh Vaishya