Skipping the header while reading the CSV file through Oracle external Table

Using the external table we are reading the CSV file which contains header. I am using SKIP 1 as a command to skip the header. When we have a single file it is working fine and able to file using external table.

But getting the error “ORA-30653: reject limit reached” while reading multiple files. because skip 1 will skip only the first file header, not other files.

How to skip all the files header while reading multiple files

Answer

“ORA-30653: reject limit reached” while reading multiple files

You can specify the number of rows allowed to be rejected before throwing an error, or you can specify UNLIMITED. I guess you specified a number, but your csv file contains rows that the database can’t parse appropriately based on your definition.

If you want to ignore all malformed rows, you can simply change the limit to UNLIMITED:

alter table users_load reject limit unlimited;

create table example:

CREATE TABLE foo_load (
    employee_number CHAR(5)
) ORGANIZATION EXTERNAL (
    TYPE ORACLE_LOADER
    DEFAULT DIRECTORY ext_tab_dir
    ACCESS PARAMETERS (
        ...
    )
    LOCATION ('foo.txt')
)
REJECT LIMIT UNLIMITED; --Use limit, not limited

A complete exhaustive example of how to create external table can be read Here