ORA-29913 and ORA-30653 error selecting an external table

database-designoracleoracle-sql-developer

I created an external table using a .tbl file and now Im trying to select all rows from that table, using:

select * from users_load;

But Im getting this error:

ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached

Do you know why this error is happening? This table have millions of rows, I dont know if can be because of this…

Best Answer

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 .tbl file contains rows that the database can't parse appropriately based on your definition.

enter image description here

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
Related Question