Ora 29913 ошибка при выполнении odciexttablefetch callout

I am trying to use oracle external table feature to load flat file into database but encounter a error while execute SELECT statement on external table which i have created.
Below are the exact steps that i am following —

/user2/cbusdev01/smp43/domains/columbus/tools>mkdir data_1
/user2/cbusdev01/smp43/domains/columbus/tools>chmod 777 data_1

create a file inside the data_1 folder add_len.csv
Barbados_1000230,2461000230,2461000230,SSLN 00 0 00 138
Barbados_1000229,2461000229,2461000229,SSLN 00 0 00 139
Barbados_1000228,2461000228,2461000228,SSLN 00 0 03 14

login to sysdba —
grant create any directory to cbusdev01cm;
grant read, write on DIRECTORY data_1 to cbusdev01cm;

Now create a external table —

CREATE TABLE add_len_ext (
  country_code      VARCHAR2(20),
  sip               NUMBER,
  tn                NUMBER,
  len               VARCHAR2(30) 
)
ORGANIZATION EXTERNAL (
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY data_1
  ACCESS PARAMETERS (
    RECORDS DELIMITED BY NEWLINE
    BADFILE data_1:'bad.bad'
     LOGFILE data_1:'log.log'
    FIELDS TERMINATED BY ','
    MISSING FIELD VALUES ARE NULL
    (
      country_code      CHAR(50),
      sip               CHAR(50),
      tn                CHAR(50),
      len               CHAR(50)
    )
  )
  LOCATION (data_1:'add_len.csv')
)
PARALLEL 5
REJECT LIMIT UNLIMITED;

Table ADD_LEN_EXT created.

Then i fire the SELECT statement

select * from ADD_LEN_EXT;

And it gives me —

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
error opening file /user2/cbusdev01/smp43/domains/columbus/tools/log.log
29913. 00000 -  "error in executing %s callout"
*Cause:    The execution of the specified callout caused an error.
*Action:   Examine the error messages take appropriate action.

I am not getting this error !!
I have granted all the permission to concerns but still the error is coming.

I am using —

Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production
PL/SQL Release 11.2.0.3.0 - Production
"CORE 11.2.0.3.0 Production"
TNS for Linux: Version 11.2.0.3.0 - Production
NLSRTL Version 11.2.0.3.0 - Production

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:

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

gt;’ or 0x'<value>’

1. 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:

1. You need to build physical directories at all nodes of the cluster and need to copy the data files physically to all the locations.
2.  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.
3. 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.
4. 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.

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.

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?