Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
You may get this error when selecting from an
external table, type ORACLE_LOADER, and you have set the
REJECT LIMIT to some numerical value instead of the literal
UNLIMITED and,
for some reason to be investigated, oracle reached the limit while loading the rows from the file.
First of all you should open the log file associated with the external table, if any.
If the log file is not present because the
NOLOGFILE option was specified, it is advisable to redefine the table and specify a valid log file destination.
It's worth spending some more words on the significant differences that you may get with what is to be considered as a
rejected record (collected in the .bad file) or as a
discarded record (collected into the .dsc file), depending on whether certain keywords are specified or not in the external table definition.
In the end, what matters is that rejected records are those that affect the
REJECT LIMIT counter, whilst discarded records (
LOAD WHEN) do not affect it.
For instance, a classical problem of row rejection is represented by an
empty line of text.
If
REJECT LIMIT 0 is specified, then you must handle empty lines in some way, because they will cause rejections causing the failure of the operation. Let's look at the following example:
CREATE TABLE test_ext (
a VARCHAR2(10),
b VARCHAR2(10),
c VARCHAR2(10)
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY file_location
ACCESS PARAMETERS
(
RECORDS DELIMITED BY newline
LOAD WHEN ((1:1) != "#")
FIELDS TERMINATED BY ":"
(
a CHAR(10),
b CHAR(10),
c CHAR(10)
)
)
LOCATION ('list.dat')
)
REJECT LIMIT 0
/
Then suppose the content of file list.dat is the text in green color:
#
test:external:table:
Line 2 contains just a newline character, so it will be considered an empty line.
When we attempt to access the table, we get:
select * from test_ext;
ORA-29913: error in executing ODCIEXTTABLEFETCH callout ORA-30653: reject limit reached
In the log file generated (in the folder pointed to by directory FILE_LOCATION), the last lines are:
KUP-04102: record 1 discarded from file ...list.dat
KUP-04021: field formatting error for field A
KUP-04023: field start is after end of record
KUP-04101: record 2 rejected in file ...list.dat
The first message refers to the line beginning with the pound sign (#), that we deliberately discarded by means of the
LOAD WHEN clause. The next three lines refer to the empty line. In the end what really matters is that the record was rejected, thereby causing the entire operation to abort because of the reached reject limit.
Now, let's rebuild the table adding a special field definition clause (in
green color):
CREATE TABLE test_ext (
a VARCHAR2(10),
b VARCHAR2(10),
c VARCHAR2(10)
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY file_location
ACCESS PARAMETERS
(
RECORDS DELIMITED BY newline
LOAD WHEN ((1:1) != "#")
FIELDS TERMINATED BY ":"
MISSING FIELD VALUES ARE NULL
(
a CHAR(10),
b CHAR(10),
c CHAR(10)
)
)
LOCATION ('list.dat')
)
REJECT LIMIT 0
/
select * from test_ext;
A B C
---------- ---------- ----------
test external table
2 rows selected
If you look at the log file, this time there will be only the
KUP-04102 message at the bottom.
Now, you might say you don't want null values in your table: piece of cake, just add one more clause:
CREATE TABLE test_ext (
a VARCHAR2(10),
b VARCHAR2(10),
c VARCHAR2(10)
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY file_location
ACCESS PARAMETERS
(
RECORDS DELIMITED BY newline
LOAD WHEN ((1:1) != "#")
FIELDS TERMINATED BY ":"
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS
(
a CHAR(10),
b CHAR(10),
c CHAR(10)
)
)
LOCATION ('list.dat')
)
REJECT LIMIT 0
/
select * from test_ext;
A B C
---------- ---------- ----------
test external table
1 rows selected
The last three lines in the log file this time contain:
KUP-04102: record 1 discarded from file ...list.dat
KUP-04073: record ignored because all referenced fields are null for a record
KUP-04049: record 2 discarded from file ...list.dat
Finally it's interesting to note a linguistic quirk, which makes me so happy, as you know:
the log file says
record 2 discarded, but the clause we applied is
REJECT ROWS...
Indeed the discarded record will be found in the .dsc file, not in the .bad file.
I guess how easily one can be fooled by such terminology where discarded records must be explicitly rejected and rejected records are those automatically discarded... good grief!
:-D
On a subtler level, finally note that in the log file the record discarded owing to
REJECT ROWS clause is marked by code
KUP-04049, whereas the record discarded because it doesn't match the
LOAD WHEN condition is marked by code
KUP-04102.
See message translations for
ORA-30653 and
ORA-29913 and search additional resources.
