Ora 29913 ошибка при выполнении odciexttablefetch callout - Решение и исправление самых разных ошибок на TopOshibok.ru

Dealing with the oracle database error ORA-29913 but having no idea of why you are receiving this error? Don’t worry….this tutorial will surely help you out.

This post is completely dedicated to Oracle Database ORA-29913 Error, its causes, and of course ways to fix it.

Without wasting any more time… let’s dive into the post.

The use of External Tables in Oracle is becoming popular these days which has moreover has eliminated the need for an SQL loader for loading tables. However, you may come across many issues while using the external tables in your database one of which is described here in this post.

ORA-29913 is also one such common error which usually comes while working with external tables within the Oracle database.

When you perform the SELECT operation on the external table (the feature is a complement to existing SQL*Loader functionality. It enables you to access data in external sources as if it were in a table in the database) then you may face error those which are similar to the following:

“ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04063: unable to open log file EXTERN_17063.log
KUP-04040: file TEST.TXT in TEST_DIR not found
ORA-06512: at “SYS.ORACLE_LOADER”, line 19”

What Types Error Messages Appears When ORA- 22913 Error Occurs?

The ORA- 22913 error comes in various forms; let’s have a look over each of them:

ora 29913 error in executing odciexttableopen callout windows
ora-29913 error in executing odciexttableopen callout ora-29400 data cartridge error kup-04040
ora-29913 error in executing odciexttableopen callout impdp
ora-29913: error in executing odciexttablefetch callout ora-30653: reject limit reached
ora-29913: error in executing odcitabledescribe callout
ora-29913: error in executing odciexttablepopulate callout
ora-29913: error in executing odciexttablefetch callout ora-01722: invalid number
ora-29913: error in executing odciaggregateiterate callout

What Causes Oracle ORA-29913 Error?

The most common ORA-29913 error is caused when the Oracle fails to open the OS files that it needs for opening. These files may or may not include in the input files, output files, and the log and bad or discard files. The reason behind the occurrence of this error is that Oracle execution has not been granted permission for accessing the directory properly or the directory itself does not exist in the database or the files have not been granted the permissions.

You may also come across this error line that the Oracle fails to write the log file then this definitely means that there may be an issue with the directory or with the log files contained in the directory.

How To Fix Oracle ORA-29913 Error?

Fix 1# ORA-29913: error in executing ODCIEXTTABLEOPEN callout

Reason:

Your external file must have assigned with the null values in its last column. Originally it’s a number column. To run the query successfully, you need to change the null to value to number o.

How To Fix:

For loading up the NULL value, you have to include the delimiter ENCLOSED BY within your table definition.

records delimited by newline

fields terminated by ‘,’

optionally enclosed by ‘ ‘ <<<=====

missing field values are null

Fix 2# ORA-29913: error in executing ODCIEXTTABLEFETCH callout

Reason:

If you are receiving the ORA-29913: error in executing ODCIEXTTABLEFETCH callout then the reasons can be the following:

Incorrect or improper data format in the external file
Your external file has some empty lines
You need check the following things in the HEX delimiter and TAB delimiter
TAB delimiter ‘\t’ which is used by Oracle database, is denoted by the HEX value ’09’
HEX value present within the data file must match with the CREATE statement’s HEX value.
HEX specification present in the CREATE statement must be like 0X'<value&

gt;’ or 0x'<value>’

Suppose if a column is added in the external table then the same thing must need to be there within your external file.

How To Fix:

To fix ORA-29913: error in executing ODCIEXTTABLEFETCH callout at first you need to check the error within the log. After that fix the problem consequently.

Fix 3# Check The External Tables

So when you make use of the external tables then you need to perform certain checks that are mentioned below:

You need to check the statement carefully that you have used for creating the directory.
Check that the directories have been granted the necessary rights or permissions by the Oracle for performing reading and writing.
Need to perform special checks of ownership and permissions for the network directories.
Also, check that there is no problem associated with the permissions for the files that are in existence.

Some More Quick Fixes To Resolve Ora-29913

You can easily fix this problem by trying out some of the solutions that follow:

You need to build physical directories at all nodes of the cluster and need to copy the data files physically to all the locations.
When you present the storage of data from SAN or NAS then it gets presented in NFS. You also note that this is mandatory to get a separate license from the storage provider.
You can use the Cluster file system at the OS level which is visible from all the nodes. However, the solution to do so is a bit expensive as you need to purchase some additional licenses to do so.
When you are accessing the external tables then you need to connect to specific instances but you will be not able to take the advantage of this service.

Automated Solution To Fix Ora-29913 Error

If meanwhile performing the above fixes to resolve Ora-29913 error you get into some oracle database corruption issues. In that case, use the Oracle repair tool.

It is a powerful and comprehensive data recovery software that is specially designed for recovering corrupted or damaged Oracle database files and the tables and the contents in it that are exported from the external source.

This software scans and searches all the errors and damages that are induced in the Oracle database in the system and then shows a preview of recoverable database objects. This software is capable of recovering from both the database and backup files.

Hence the recovered data after going through the process is also gets stored in Transact-SQL script. The recovery tool is very easy to use an even the non-technical people can easily make use of it for recovering their data back from their corrupted and damaged Oracle database.

Steps to Fix Oracle Database ORA-29913 Error

Step 1: Search the initial screen of Oracle repair tool. with a pop-up window showing options to select or search corrupt Oracle databases in your computer.

Step 2: Click Scan File to initiate the scan process after selecting the oracle database. The recoverable database objects get listed in left-side pane.

Step 3: Click an object to see its preview.

Step 4: : Click Start Repair in the icon bar to start the repair process. A pop-up window is displayed which show the steps needed to perform further. Click next and continue.

Step 5: Give the user name, password and path of the blank database where you want to save the repaired database objects.

Step 6: Repairing and restoring various database objects after establishing a connection with blank oracle database.

Conclusion:

Oracle Database ORA-29913 Error is a very common error which any user can face while handling the external tables in the Oracle database. But now you all need not worry because you have the fixes to resolve this error easily without affecting the file integrity.

Jacob Martin is a technology enthusiast having experience of more than 4 years with great interest in database administration. He is expertise in related subjects like SQL database, Access, Oracle & others. Jacob has Master of Science (M.S) degree from the University of Dallas. He loves to write and provide solutions to people on database repair. Apart from this, he also loves to visit different countries in free time.

Источник

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

SQL> select * from Scott.example_external_table;
select * from example_external_table
*
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.ORACLE_LOADER", line 14
ORA-06512: at line 1

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

ORA-20011: Approximate NDV failed:

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file <file_name> in <directory_name> not found

Reason

The external files have been moved from the correct location

SQL> select * from dba_directories ;

OWNER DIRECTORY_NAME DIRECTORY_PATH
------ -------------- -----------------------
SYS TEST_DIR /u01/oradata/external_files

Check the File Names associated with external table

select * from dba_external_locations
where table_name='EXAMPLE_EXTERNAL_TABLE';OWNER TABLE_NAME LOCATION DIRECTORY_OWNER DIRECTORY_NAME
----- ------------- -------- --------------- ---------------
SYS EXAMPLE_EXTERNAL_TABLE test1.dat SYS TEST_DIR
SYS EXAMPLE_EXTERNAL_TABLE test2.dat SYS TEST_DIR

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

select TABLE_NAME, DIRECTORY_PATH||'/'||LOCATION file_name from DBA_EXTERNAL_LOCATIONS loc, dba_directories dir
where loc.DIRECTORY_OWNER=dir.OWNER
and loc.DIRECTORY_NAME=dir.DIRECTORY_NAME
and loc.OWNER='&1' and loc.TABLE_NAME='&2';

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

select * from Scott.example_external_table;
execute sys.dbms_stats.gather_table_stats('SCOTT','EXAMPLE_EXTERNAL_TABLE');

(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'<value&
gt;’ or 0x'<value>’
(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 ‘ ‘ <<<=====
missing field values are null

(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 <user name>;
GRANT WRITE ON DIRECTORY testdir TO <user name>;

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

Источник

I’m trying to execute this code in PL/SQL:

create or replace directory ext_tab_dir as 'C:/mydir';
GRANT READ,WRITE ON DIRECTORY ext_tab_dir TO PUBLIC;
DROP TABLE emp_load;
CREATE TABLE emp_load (v1 VARCHAR2(4000), 
                        v2 VARCHAR2(4000) 
                        )
   ORGANIZATION EXTERNAL (
     TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab_dir
     ACCESS PARAMETERS (
     RECORDS DELIMITED BY NEWLINE
     BADFILE ext_tab_dir:'bad.bad'
     LOGFILE ext_tab_dir:'log.log'
     FIELDS TERMINATED BY ','         
     )
     LOCATION ('testfile.csv')
   ); 
--   INSERT INTO tablename(v1,v2)
   SELECT * From emp_load

and then getting next errors:

ORA-29913: error in executing ODCIEXTTABLEOPEN callout    
ORA-29400: data cartridge error error opening file C:/mydir/log.log

I do get that it has to do something with permissions, but I’m the one who created that directory, so how do I grant priveleges to myself if it is set like this by default? Is there any way to perform that sort of operation from PL/SQL?

Источник

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

ORA-29913

(1) External File not found or permission incorrect

When analyzing the table, you get a similar message:

Or in general way

ORA-20011: Approximate NDV failed:

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file <file_name> in <directory_name> not found

Reason

The external files have been moved from the correct location

SQL> select * from dba_directories ;

OWNER DIRECTORY_NAME DIRECTORY_PATH
------ -------------- -----------------------
SYS TEST_DIR /u01/oradata/external_files

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

select TABLE_NAME, DIRECTORY_PATH||'/'||LOCATION file_name from DBA_EXTERNAL_LOCATIONS loc, dba_directories dir
where loc.DIRECTORY_OWNER=dir.OWNER
and loc.DIRECTORY_NAME=dir.DIRECTORY_NAME
and loc.OWNER='&1' and loc.TABLE_NAME='&2';

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

select * from Scott.example_external_table;
execute sys.dbms_stats.gather_table_stats('SCOTT','EXAMPLE_EXTERNAL_TABLE');

(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'<value&
gt;’ or 0x'<value>’
(iii) If the column added to the external table,same should be present in the external file