Errors (ORA-29913,ORA-30653,KUP-04036) When Loading Standard CSV File Into Oracle Using External Table (Doc ID 2591103.1)
Last updated on FEBRUARY 23, 2022
Applies to:
Symptoms
Loading external table fails with the following errors:
— ERROR at line 1:
— ORA-29913: error in executing ODCIEXTTABLEFETCH callout
— ORA-30653: reject limit reached
Log files reports:
Cause
To view full details, sign in with your My Oracle Support account.
Don’t have a My Oracle Support account? Click to get started!
In this Document
| Symptoms |
| Cause |
| Solution |
| References |
My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.
Oracle offers a comprehensive and fully integrated stack of cloud applications and platform services. For more information about Oracle (NYSE:ORCL), visit oracle.com. пїЅ Oracle | Contact and Chat | Support | Communities | Connect with us | 
| 
| 
| Legal Notices | Terms of Use
Annals of Oracle’s Improbable Errors
Welcome to Flavio Casetta’s official Oracle database application development related blog. No Mysql supporters were hurt in the making of this blog.
Friday, May 09, 2008
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:
Then suppose the content of file list.dat is the text in green color:
Line 2 contains just a newline character, so it will be considered an empty line.
When we attempt to access the table, we get:
In the log file generated (in the folder pointed to by directory FILE_LOCATION), the last lines are:
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):
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:
The last three lines in the log file this time contain:
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!
😀
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.
External Table UTF8
When trying to read records that contain UTF8 characters, my external table produces the following error:
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-30653: reject limit reached
ORA-06512: at «SYS.ORACLE_LOADER», line 14
ORA-06512: at line 1
29913. 00000 — «erro in executing %s callout»
*Cause: The execution of the specified callout caused an error.
*Action: Examine the error messages take appropriate action.
Error at Line: 3
Here is my external table definition:
CREATE TABLE «WRK_POSTS»
( «ID» NUMBER,
«POST_CONTENT» VARCHAR2(4000 CHAR)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY «MYSQL_DIR»
ACCESS PARAMETERS
( RECORDS DELIMITED BY NEWLINE
CHARACTERSET ‘UTF8’
STRING SIZES ARE IN CHARACTERS
NOBADFILE
NODISCARDFILE
NOLOGFILE
FIELDS TERMINATED BY 0X’5E5E’
LRTRIM
MISSING FIELD VALUES ARE NULL
(
ID CHAR,
POST_CONTENT CHAR
)
)
LOCATION
( ‘wrk_posts.txt’
)
)
;
The table works fine w/o multi-byte utf8 characters. My server NLS characterset is UTF8 as confirmed via:
select
value — UTF8
from v$nls_parameters where parameter=’NLS_CHARACTERSET’;
select
value — UTF8
from nls_database_parameters where parameter=’NLS_CHARACTERSET’;
The POST_CONTENT field has 4000 characters, but due to multi-byte — has more than 4000 bytes. I thought that specifying CHARACTERSET UTF8 and STRING SIZES ARE IN CHARACTERS would take care of this.
Any feedback is very much appreciated.
Comments
You need to check the NLS_LANG setting of your OS first.
You might find this article helpful,
NLS_LANG FAQ
Thanks for the reply yingkuan. Sorry for not including the NLS_LANG info.
Client
[windows registry]
AMERICAN_AMERICA.WE8MSWIN1252
Server
[rhel sqlplus]
SQL> HOST echo $NLS_LANG;
AMERICAN_AMERICA.UTF8
Note that doing the same SELECT from sqplus on the rhel server, (with the NLS_LANG explicitly declared in the env) produces the same error as on windows sqlplus/oracle developer.
Indeed I was familiar with the NLS_LANG_FAQ, however perhaps I am still missing something. I have also be referencing this thread:
I provided both the NLS_LANG info for both client / server OS.
After much research I am fairly confident that I am not screwing up something mundane within the syntax of the external table.
I am using SQL Developer and when I use a positional delimiter for the UTF8 field in question like so: POST_CONTENT POSITION(6:4000) CHAR(4000), I can see plenty of UTF8 characters, however I can be sure that the ACCESS DRIVER is not picking up the content as UTF8 b/c it is only grabbing the 4000 characters which correspond to what would otherwise be a single-byte character encoding set of content.
As described in the FAQ, I used: SQL> HOST echo $NLS_LANG; to find out the server NLS_LANG info -> however I think is this specific to SQLPLUS — and is different than the external table access driver? Is it possible that the access driver characterset is being driven by something else that is not multi-byte?
I explicitly asserted the characterset in the access parameters, but it does not work.
I must be missing something — please help! 
How to resolve ORA-29913 with external tables
ORA-29913 is one of the common error when working with external tables in Oracle database. We would be looking at various ways the error can be produced and what can be done to resolve the error
(1) External File not found or permission incorrect
When analyzing the table, you get a similar message:
SQL> execute sys.dbms_stats.gather_table_stats(‘SCOTT’,’EXAMPLE_EXTERNAL_TABLE’);
BEGIN sys.dbms_stats.gather_table_stats(‘SCOTT’,’EXAMPLE_EXTERNAL_TABLE’); END;
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file test1.dat in TEST_DIR not found
ORA-06512: at “SYS.DBMS_STATS”, line 7161
ORA-06512: at “SYS.DBMS_STATS”, line 7174
ORA-06512: at line 1
Or in general way
Reason
The external files have been moved from the correct location
Check the File Names associated with external table
Now checking at the OS level
$ cd /u01/oradata/external_files
$ ls test[1-2]/dat
No such file or directory
So files are not present at the correct location
Both the above queries can be combined as below also
Resolution:
Move back both the files to the original location
$ mv /u02/oradata/external_files/test2.dat /u01/oradata/external_files
$ mv /u02/oradata/external_files/test1.dat /u01/oradata/external_files
Now
Both the below statement will succeed
(2) Incorrect data format in external file
| ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEFETCH callout ORA-30653: reject limit reached ORA-06512: at “SYS.ORACLE_LOADER”, line 14 ORA-06512: at line 1 |
Reason
(i) The external file may be having empty lines
(ii) Check the TAB delimiter or the HEX delimiter
a. The TAB delimiter ‘\t’, used by Oracle, is represented by a HEX value ’09’
b. The HEX value in the datafile should match the HEX value specified in the
CREATE statement
c. The HEX specification in the CREATE statement should look like 0X’ ’
(iii) If the column added to the external table,same should be present in the external file
Resolution
Check the error in the log and correct the problem accordingly
(3) Error with Null column
| ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04043: table column not found in external source: |
Reason
The external file does have null values for the last column which is originally a number column. When we change the null to a number 0,then the query succeeds.
Resolution
To load a NULL value,we need to include ENCLOSED BY delimiter in the table definition.
| records delimited by newline fields terminated by ‘,’ optionally enclosed by ‘ ‘ (4) ORA-29913 can also occur if Oracle is not able to create the log files also.So check for the permission of the directories (5) External table directory error CREATE OR REPLACE DIRECTORY extdir AS ‘/u01/oradata/testdir’; Here are some good information about External Table,You should check out What are EXTERNAL TABLES in Oracle? (1)External tables are read only tables where the data is stored in flat files outside the database (2) You can user external table feature to access external files as if they are tables inside the database. (3) When you create an external table, you define its structure and location with in oracle. Basically you just store the metadata inside the oracle (4) When you query the table, oracle reads the external table and returns the results just as if the data had been stored with in the database. (5) The oracle server provides two major oracle driver to read the flat files
Related Articles Recommended CoursesHere is the nice Udemy Course for Oracle SQL detector |