Error in executing odciexttablefetch callout ora 30653



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.

Читайте также:  Роблокс error code 277 что это

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.

Читайте также:  Pip install turtle error

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
The ORA-29913 error can also happen in external tables when you don’t grant read and write permissions to the directory:

CREATE OR REPLACE DIRECTORY extdir AS ‘/u01/oradata/testdir’;
GRANT READ ON DIRECTORY testdir TO ;
GRANT WRITE ON DIRECTORY testdir TO ;

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

  • Oracle_loader: This is used for the reading the flat files using the oracle loader technology. It basically allows to read the files which can be interpreted using sql loader technology
  • Oracle_datapump: This is used for both importing and exporting of data using platform independent format

Related Articles
Oracle External tables :Check out this post for information on usage of external table in oracle with example, how to create external table, how to use it
Oracle Create table :Tables are the basic unit of data storage in an Oracle Database.we covers how to use Oracle create table command to create table with foreign key /primary key
ORA-00936 missing expression :Learn troubleshooting ORA-00936 missing expression in oracle SQL.what are various solution, how we can avoid it, Oracle bugs for this errors
ORA-01017: invalid username/password; logon denied :Learn the possible resolution of ORA-01017 invalid username/password; logon denied. How to resolve it quickly without muc efforts
ORA-00001 unique constraint violated :Check out this post for the possible solution for oracle error ORA-00001 unique constraint violated. How to resolve and fix it
ORA-00911: invalid character :This post is for common causes of ORA-00911: invalid character in oracle with examples and resolution to help you complete the job
ORA-00900 : This post for the various solutions for ORA-00900 invalid sql statement.Reasons for the error.How to debug and resolve it quickly,
ORA-03113: end-of-file on communication channel :Check out method to resolve ORA-03113: end-of-file on communication channel. This error could happen in database startup, running program
Oracle documentation

Recommended Courses

Here is the nice Udemy Course for Oracle SQL
Oracle-Sql-Step-by-step : This course covers basic sql, joins, Creating Tables and modifying its structure, Create View, Union, Union -all and much other stuff. A great course and must-have course for SQL starter
The Complete Oracle SQL Certification Course : This is a good course for anybody who wants to be Job ready for SQL developer skills. A nice explained course
Oracle SQL Developer: Essentials, Tips and Tricks : Oracle Sql developer tool is being used by many developers. This course gives us tricks and lessons on how to effectively use it and become a productive sql developer
Oracle SQL Performance Tuning Masterclass 2020 : Performance tuning is one of the critical and most sought skills. This is a good course to learn about it and start doing sql performance tuning

Источник

Оцените статью
toolgir.ru
Adblock
detector