June 3, 2021
I got ” ORA-29400: data cartridge error ” error in Oracle database.
ORA-29400: data cartridge error
Details of error are as follows.
ORA-29400: data cartridge error Cause: An error has occurred in a data cartridge external procedure. This message will be followed by a second message giving more details about the data cartridge error. Action: See the data cartridge documentation for an explanation of the second error message.
When selecting from an external table , you get the following error:
SQL> select * from sys.empxt;
select * from sys.empxt
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file emp1.dat in EMP_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('SYS','EMPXT');
BEGIN sys.dbms_stats.gather_table_stats('SYS','EMPXT'); END;
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file emp1.dat in EMP_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:
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
data cartridge error
This ORA-29400 is related with the error has occurred in a data cartridge external procedure. This message will be
followed by a second message giving more details about the data cartridge error.
See the data cartridge documentation for an explanation of the second error message.
The flat files associated to the external table (emp1.dat, emp2.dat) do not exist in the OS directory pointed by the logical directory EMP_DIR.
Copy/move/recreate the flat file emp1.dat so that it exists in the OS directory pointed by the logical EMP_DIR directory.
SQL> select * from dba_directories ;OWNER DIRECTORY_NAME DIRECTORY_PATH ------ -------------- ----------------------- SYS EMP_DIR /oradata/external_files
$ mv /tmp/emp1.dat /oradata/external_files If the problem still persists: SQL> select * from sys.empxt; select * from sys.empxt * ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04040: file emp2.dat in EMP_DIR not found ORA-06512: at "SYS.ORACLE_LOADER", line 14 ORA-06512: at line 1 then be sure that all OS flat files associated to the external table exist in the OS directory pointed by the logical EMP_DIR directory: SQL> select * from dba_external_locations 2 where table_name='EMPXT';OWNER TABLE_NAME LOCATION DIRECTORY_OWNER DIRECTORY_NAME ----- ------------- -------- --------------- --------------- SYS EMPXT emp1.dat SYS EMP_DIR SYS EMPXT emp2.dat SYS EMP_DIR
$ mv /tmp/emp2.dat /oradata/external_files SQL> select * from sys.empxt;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ----- -------- ----- --------- ---- ------ ------ 7369 SMITH CLERK 7902 17-DEC-80 150 0 20 7499 ALLEN SALESMAN 7698 20-FEB-81 150 0 30 7521 WARD SALESMAN 7698 22-FEB-81 150 0 30 ...
SQL> execute sys.dbms_stats.gather_table_stats('SYS','EMPXT'); PL/SQL procedure successfully completed.
Do you want to learn Oracle Database for Beginners, then read the following articles.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )
1,754 views last month, 1 views today
June 3, 2021
I got ” ORA-29400: data cartridge error ” error in Oracle database.
ORA-29400: data cartridge error
Details of error are as follows.
ORA-29400: data cartridge error Cause: An error has occurred in a data cartridge external procedure. This message will be followed by a second message giving more details about the data cartridge error. Action: See the data cartridge documentation for an explanation of the second error message.
When selecting from an external table , you get the following error:
SQL> select * from sys.empxt;
select * from sys.empxt
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file emp1.dat in EMP_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('SYS','EMPXT');
BEGIN sys.dbms_stats.gather_table_stats('SYS','EMPXT'); END;
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file emp1.dat in EMP_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:
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
data cartridge error
This ORA-29400 is related with the error has occurred in a data cartridge external procedure. This message will be
followed by a second message giving more details about the data cartridge error.
See the data cartridge documentation for an explanation of the second error message.
The flat files associated to the external table (emp1.dat, emp2.dat) do not exist in the OS directory pointed by the logical directory EMP_DIR.
Copy/move/recreate the flat file emp1.dat so that it exists in the OS directory pointed by the logical EMP_DIR directory.
SQL> select * from dba_directories ;OWNER DIRECTORY_NAME DIRECTORY_PATH ------ -------------- ----------------------- SYS EMP_DIR /oradata/external_files
$ mv /tmp/emp1.dat /oradata/external_files If the problem still persists: SQL> select * from sys.empxt; select * from sys.empxt * ERROR at line 1: ORA-29913: error in executing ODCIEXTTABLEOPEN callout ORA-29400: data cartridge error KUP-04040: file emp2.dat in EMP_DIR not found ORA-06512: at "SYS.ORACLE_LOADER", line 14 ORA-06512: at line 1 then be sure that all OS flat files associated to the external table exist in the OS directory pointed by the logical EMP_DIR directory: SQL> select * from dba_external_locations 2 where table_name='EMPXT';OWNER TABLE_NAME LOCATION DIRECTORY_OWNER DIRECTORY_NAME ----- ------------- -------- --------------- --------------- SYS EMPXT emp1.dat SYS EMP_DIR SYS EMPXT emp2.dat SYS EMP_DIR
$ mv /tmp/emp2.dat /oradata/external_files SQL> select * from sys.empxt;EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ----- -------- ----- --------- ---- ------ ------ 7369 SMITH CLERK 7902 17-DEC-80 150 0 20 7499 ALLEN SALESMAN 7698 20-FEB-81 150 0 30 7521 WARD SALESMAN 7698 22-FEB-81 150 0 30 ...
SQL> execute sys.dbms_stats.gather_table_stats('SYS','EMPXT'); PL/SQL procedure successfully completed.
Do you want to learn Oracle Database for Beginners, then read the following articles.
Oracle Tutorial | Oracle Database Tutorials for Beginners ( Junior Oracle DBA )
1,650 views last month, 1 views today
Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.
The pair ORA-29913/ORA-29400 is a sort of catch-all exception embedding KUP-XXXXX error codes that further specify the type of problem encountered with the definition of an external table.
The type of errors encountered spans from syntax errors to missing files or privileges.
For instance, yesterday i got this one when i forgot to specify a keyword in the external table definition.
CREATE TABLE "IMP_BAD_BOXES"
( "TOTE_ID" NUMBER(8,0),
"DEPT" VARCHAR2(2 BYTE),
"CREATED" DATE
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "IMPORT_DIR"
ACCESS PARAMETERS
(LOGFILE 'BOXES.log'
FIELDS
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS (
CREATED POSITION(1:14) CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
TOTE_ID POSITION(15:22) CHAR,
DEPT POSITION(23:24) CHAR)
)
LOCATION
( 'BOXES.dat')
);select * from IMP_BAD_BOXES;
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "logfile": expecting one of: "column, ..."
KUP-01007: at line 1 column 1
I marked in red color the position in the statement that caused the run-time error.
Note indeed that when you create the table, no issues are reported, you won’t know if it works until you go live.
So, in the end, this verbose error message was to report that i forgot to specify the keyword RECORDS before LOGFILE.
If you look at the syntax diagram of the ACCESS PARAMETERS clause (ver. 10R1), you’ll notice that there are four distinct sub-clauses.
LOGFILE belongs to the record format sub-clause. This means that you cannot specify any keyword in this sub-clause if you haven’t specified the RECORDS keyword first.
Note also that from a syntax standpoint it is perfectly legitimate to write the RECORDS keyword alone, but if you do not add DELIMITED BY NEWLINE ( or some other specification) then the record terminator will remain undefined, resulting in the following run-time error:
CREATE TABLE "IMP_BAD_BOXES"
( "TOTE_ID" NUMBER(8,0),
"DEPT" VARCHAR2(2 BYTE),
"CREATED" DATE
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "IMPORT_DIR"
ACCESS PARAMETERS
(RECORDS
LOGFILE 'BOXES.log'
FIELDS
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS (
CREATED POSITION(1:14) CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
TOTE_ID POSITION(15:22) CHAR,
DEPT POSITION(23:24) CHAR)
)
LOCATION
( 'BOXES.dat')
);
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04038: internal error: unknown record type
So, my original statement must be rewritten as:
CREATE TABLE "IMP_BAD_BOXES"
( "TOTE_ID" NUMBER(8,0),
"DEPT" VARCHAR2(2 BYTE),
"CREATED" DATE
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "IMPORT_DIR"
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
LOGFILE 'BOXES.log'
FIELDS
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS (
CREATED POSITION(1:14) CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
TOTE_ID POSITION(15:22) CHAR,
DEPT POSITION(23:24) CHAR)
)
LOCATION
( 'BOXES.dat')
);
But how do i put the log file in directory other than IMPORT_DIR?
While the official documentation states that one can write a file location as directory:filename, in the reality it turns out that one must enclose the file name in single quotes, otherwise the following syntax error is returned:
CREATE TABLE "IMP_BAD_BOXES"
( "TOTE_ID" NUMBER(8,0),
"DEPT" VARCHAR2(2 BYTE),
"CREATED" DATE
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "LOG_DIR"
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
LOGFILE 'BOXES.log'
FIELDS
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS (
CREATED POSITION(1:14) CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
TOTE_ID POSITION(15:22) CHAR,
DEPT POSITION(23:24) CHAR)
)
LOCATION
( IMPORT_DIR:BOXES.dat)
);
ORA-00905: missing keyword
On the other hand, if you put the directory specifier inside the quotes too, you’ll get the following run-time error:
CREATE TABLE "IMP_BAD_BOXES"
( "TOTE_ID" NUMBER(8,0),
"DEPT" VARCHAR2(2 BYTE),
"CREATED" DATE
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "LOG_DIR"
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
LOGFILE 'BOXES.log'
FIELDS
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS (
CREATED POSITION(1:14) CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
TOTE_ID POSITION(15:22) CHAR,
DEPT POSITION(23:24) CHAR)
)
LOCATION
('IMPORT_DIR:BOXES.dat')
);
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file IMPORT_DIR:BOXES.dat in LOG_DIR not found
Finally, here is the correct syntax in blue color:
CREATE TABLE "IMP_BAD_BOXES"
( "TOTE_ID" NUMBER(8,0),
"DEPT" VARCHAR2(2 BYTE),
"CREATED" DATE
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "LOG_DIR"
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
LOGFILE 'BOXES.log'
FIELDS
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS (
CREATED POSITION(1:14) CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
TOTE_ID POSITION(15:22) CHAR,
DEPT POSITION(23:24) CHAR)
)
LOCATION
(IMPORT_DIR:'BOXES.dat')
);
It is up to you to decide whether you want to make the DEFAULT DIRECTORY the place where the source file is read from or the folder where the log files are written to.
Depending on the situation, you may need the appropriate READ and WRITE privileges on it.
As a last note, in case you wonder what would happen if you ALTER the table instead of dropping and re-creating it, you may want to know that it would perfectly possible to execute a statement like this:
ALTER TABLE IMP_BAD_BOXES
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
LOGFILE 'boxes.log');
however this statement will wipe out the previous definition of ACCESS PARAMETERS, because it doesn’t add just a LOGFILE, but replaces the ACCESS PARAMETERS as a whole.
So, don’t forget to include the whole sub-clause again if you plan to use ALTER TABLE, as follows:
ALTER TABLE IMP_BAD_BOXES
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
LOGFILE 'BOXES.log'
FIELDS
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS (
CREATED POSITION(1:14) CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
TOTE_ID POSITION(15:22) CHAR,
DEPT POSITION(23:24) CHAR)
);
Finally, let me provide a full-fledged example of external table where every log file goes to its own place:
ALTER TABLE IMP_BAD_BOXES
DEFAULT DIRECTORY IMPORT_DIR
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
LOGFILE LOG_DIR:'BOXES.log'
DISCARDFILE DISCARD_DIR:'DISCARDS.log'
BADFILE BAD_DIR:'BAD.log'
FIELDS
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS (
CREATED POSITION(1:14) CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
TOTE_ID POSITION(15:22) CHAR,
DEPT POSITION(23:24) CHAR)
)
LOCATION ('BOXES.dat');
As you can see i specified the DEFAULT DIRECTORY clause without embedding the directory object name in double quotes. You must use double quotes if the directory object name is case sensitive otherwise always use uppercase letters.
Do not use single quotes for the directory object name or you’ll get ORA-22929.
See message translations for ORA-29400 and search additional resources.
«data cartridge errorn%s»
*Cause: An error has occurred in a data cartridge external procedure.
This message will be followed by a second message giving
more details about the data cartridge error.
*Action: See the data cartridge documentation
for an explanation of the second error message.
Попробовать перевести
Поискать эту ошибку на форуме
Перечень ошибок взят из официального перечня ошибок Oracle версии 11R2.
Если не можете справиться — поищите эту ошибку на форуме по ссылке выше, многие ошибки мы уже разобрали.
Не получается — обязательно напишите, разберем, попробуем, пощупаем, понюхаем.
Главное — не паникуйте, но всегда, в первую очередь убедитесь, что бекап есть и сделайте бекап того, что сейчас имеете
ORA-20011 ORA-29913 and ORA-29400 with KUP-04040 Errors from DBMS_STATS.GATHER_STATS_JOB
<<Back to DB Administration Main Page
ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
DBMS_STATS: GATHER_STATS_JOB encountered errors. Check the trace file.
Errors in file /u01/diag/rdbms/orcl1d/ORCL1D/trace/ORCL1D_j000_24858.trc:
ORA-20011: Approximate NDV failed: ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file PSG_XVD.csv in DUMP_DATA not found
Tue Sep 10 22:05:06 2019
Cause
The primary cause of this issue is that an OS file for an «external table» existed at some point in time but does not now. However, the database still believes the OS file for the table exists since the dictionary information about the object has not been modified to reflect the change. When DBMS_STATS is run against the table in question, it makes a call out to the external table which fails because the object is not there.
Solution
Clean up the orphaned dictionary entries.
For Temporary Datapump External Table
check out the following document from oracle support and follow the steps accordingly.
Document 10327346.8 Bug 10327346 — DBMS_WORKLOAD_CAPTURE does not drop external tables (causing ORA-20011 from DBMS_STATS)
Document 336014.1 How To Cleanup Orphaned DataPump Jobs In DBA_DATAPUMP_JOBS ?
Other External Table
With cases where specific External tables (be they Demo Schema tables or other tables) are missing, the process for handling them is much the same and can be resolved by following the procedures below. For example, if the additional error is ‘error opening file ../demo/schema/log/ext_1v3.log’, then this indicates that there is a problem opening or locating the log file from the demo schema directory. The implication is that the demo tables have not been cleared up correctly:
Locate the files for these tables in their directory.
undefine owner
undefine table_pattern
select el.table_name, el.owner, dir.directory_path||’/’||dir.directory_name «path»
from dba_external_locations el
, dba_directories dir
where el.table_name like ‘%&&table_pattern%’
and el.owner like ‘%&&owner%’
and el.directory_owner = dir.owner
and el.directory_name = dir.directory_name
order by 1, 2;
It may be that the files still exist but they have just been renamed or re-located.- It may be that the directory path itself does not exists on the server.
If file has been renamed or re-located you can restore back the file to avoid the problem.
If the file has been removed or the directory itself has been deleted then follow either the following steps:
Lock the statistics on these tables by using the following command:
DBMS_STATS.LOCK_TABLE_STATS (‘HR’,’EMP’);
OR
Remove the dictionary object for the external table. DROP TABLE HR.EMP;
More on locking unlocking Stats read here
В моей базе данных есть внешняя таблица (my_ext_table), которая указывает на текстовый файл (my_text_file.txt) на жестком диске сервера базы данных. Когда я подключаюсь к серверу базы данных с помощью TOAD, я использую свое имя пользователя и пароль базы данных (my_db_account). Для доступа к текстовому файлу я использую совершенно другого пользователя (my_linux_os_account), который обращается к серверу базы данных с помощью служб терминалов.
Когда я пытаюсь выбрать данные из my_ext_table из TOAD, я получаю следующую ошибку:
[Error] Execution (25: 30):
ORA-29913: error in executing ODCIEXTTABLEFETCH callout ORA-29400: data cartridge error
KUP-04005: error while accessing file /my_server_dir/my_text_file.txt
В файле журнала есть следующие ошибки:
KUP-04017: OS message: Permission denied
KUP-04118: operation "open failed", location "SlfOpen1"
Я могу запросить таблицу при входе в службы терминалов как my_linux_os_account, и я использую sqlplus для входа как my_db_account.
Я хотел бы иметь возможность запрашивать таблицу с помощью TOAD.
Мой коллега посоветовал мне попробовать прокси-сервер от имени пользователя my_linux_os_account.
Можно ли это сделать из окна редактора TOAD? Какой инструмент мне следует использовать для прокси-сервера в качестве другого пользователя, чтобы получить доступ к данным с помощью оператора select?
В качестве альтернативы, можно ли изменить разрешения в текстовом файле, чтобы у моего пользователя базы данных были права на текстовый файл?
1 ответ
Лучший ответ
Чтобы запрос работал из TOAD, я изменил разрешения для my_text_file.txt:
chmod o=r my_text_file.txt
Always check out the original article at http://www.oraclequirks.com for latest comments, fixes and updates.
The pair ORA-29913/ORA-29400 is a sort of catch-all exception embedding KUP-XXXXX error codes that further specify the type of problem encountered with the definition of an external table.
The type of errors encountered spans from syntax errors to missing files or privileges.
For instance, yesterday i got this one when i forgot to specify a keyword in the external table definition.
CREATE TABLE "IMP_BAD_BOXES"
( "TOTE_ID" NUMBER(8,0),
"DEPT" VARCHAR2(2 BYTE),
"CREATED" DATE
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "IMPORT_DIR"
ACCESS PARAMETERS
(LOGFILE 'BOXES.log'
FIELDS
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS (
CREATED POSITION(1:14) CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
TOTE_ID POSITION(15:22) CHAR,
DEPT POSITION(23:24) CHAR)
)
LOCATION
( 'BOXES.dat')
);select * from IMP_BAD_BOXES;
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "logfile": expecting one of: "column, ..."
KUP-01007: at line 1 column 1
I marked in red color the position in the statement that caused the run-time error.
Note indeed that when you create the table, no issues are reported, you won’t know if it works until you go live.
So, in the end, this verbose error message was to report that i forgot to specify the keyword RECORDS before LOGFILE.
If you look at the syntax diagram of the ACCESS PARAMETERS clause (ver. 10R1), you’ll notice that there are four distinct sub-clauses.
LOGFILE belongs to the record format sub-clause. This means that you cannot specify any keyword in this sub-clause if you haven’t specified the RECORDS keyword first.
Note also that from a syntax standpoint it is perfectly legitimate to write the RECORDS keyword alone, but if you do not add DELIMITED BY NEWLINE ( or some other specification) then the record terminator will remain undefined, resulting in the following run-time error:
CREATE TABLE "IMP_BAD_BOXES"
( "TOTE_ID" NUMBER(8,0),
"DEPT" VARCHAR2(2 BYTE),
"CREATED" DATE
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "IMPORT_DIR"
ACCESS PARAMETERS
(RECORDS
LOGFILE 'BOXES.log'
FIELDS
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS (
CREATED POSITION(1:14) CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
TOTE_ID POSITION(15:22) CHAR,
DEPT POSITION(23:24) CHAR)
)
LOCATION
( 'BOXES.dat')
);
ORA-29913: error in executing ODCIEXTTABLEFETCH callout
ORA-29400: data cartridge error
KUP-04038: internal error: unknown record type
So, my original statement must be rewritten as:
CREATE TABLE "IMP_BAD_BOXES"
( "TOTE_ID" NUMBER(8,0),
"DEPT" VARCHAR2(2 BYTE),
"CREATED" DATE
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "IMPORT_DIR"
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
LOGFILE 'BOXES.log'
FIELDS
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS (
CREATED POSITION(1:14) CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
TOTE_ID POSITION(15:22) CHAR,
DEPT POSITION(23:24) CHAR)
)
LOCATION
( 'BOXES.dat')
);
But how do i put the log file in directory other than IMPORT_DIR?
While the official documentation states that one can write a file location as directory:filename, in the reality it turns out that one must enclose the file name in single quotes, otherwise the following syntax error is returned:
CREATE TABLE "IMP_BAD_BOXES"
( "TOTE_ID" NUMBER(8,0),
"DEPT" VARCHAR2(2 BYTE),
"CREATED" DATE
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "LOG_DIR"
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
LOGFILE 'BOXES.log'
FIELDS
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS (
CREATED POSITION(1:14) CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
TOTE_ID POSITION(15:22) CHAR,
DEPT POSITION(23:24) CHAR)
)
LOCATION
( IMPORT_DIR:BOXES.dat)
);
ORA-00905: missing keyword
On the other hand, if you put the directory specifier inside the quotes too, you’ll get the following run-time error:
CREATE TABLE "IMP_BAD_BOXES"
( "TOTE_ID" NUMBER(8,0),
"DEPT" VARCHAR2(2 BYTE),
"CREATED" DATE
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "LOG_DIR"
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
LOGFILE 'BOXES.log'
FIELDS
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS (
CREATED POSITION(1:14) CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
TOTE_ID POSITION(15:22) CHAR,
DEPT POSITION(23:24) CHAR)
)
LOCATION
('IMPORT_DIR:BOXES.dat')
);
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file IMPORT_DIR:BOXES.dat in LOG_DIR not found
Finally, here is the correct syntax in blue color:
CREATE TABLE "IMP_BAD_BOXES"
( "TOTE_ID" NUMBER(8,0),
"DEPT" VARCHAR2(2 BYTE),
"CREATED" DATE
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY "LOG_DIR"
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
LOGFILE 'BOXES.log'
FIELDS
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS (
CREATED POSITION(1:14) CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
TOTE_ID POSITION(15:22) CHAR,
DEPT POSITION(23:24) CHAR)
)
LOCATION
(IMPORT_DIR:'BOXES.dat')
);
It is up to you to decide whether you want to make the DEFAULT DIRECTORY the place where the source file is read from or the folder where the log files are written to.
Depending on the situation, you may need the appropriate READ and WRITE privileges on it.
As a last note, in case you wonder what would happen if you ALTER the table instead of dropping and re-creating it, you may want to know that it would perfectly possible to execute a statement like this:
ALTER TABLE IMP_BAD_BOXES
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
LOGFILE 'boxes.log');
however this statement will wipe out the previous definition of ACCESS PARAMETERS, because it doesn’t add just a LOGFILE, but replaces the ACCESS PARAMETERS as a whole.
So, don’t forget to include the whole sub-clause again if you plan to use ALTER TABLE, as follows:
ALTER TABLE IMP_BAD_BOXES
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
LOGFILE 'BOXES.log'
FIELDS
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS (
CREATED POSITION(1:14) CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
TOTE_ID POSITION(15:22) CHAR,
DEPT POSITION(23:24) CHAR)
);
Finally, let me provide a full-fledged example of external table where every log file goes to its own place:
ALTER TABLE IMP_BAD_BOXES
DEFAULT DIRECTORY IMPORT_DIR
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
LOGFILE LOG_DIR:'BOXES.log'
DISCARDFILE DISCARD_DIR:'DISCARDS.log'
BADFILE BAD_DIR:'BAD.log'
FIELDS
MISSING FIELD VALUES ARE NULL
REJECT ROWS WITH ALL NULL FIELDS (
CREATED POSITION(1:14) CHAR DATE_FORMAT DATE MASK "YYYYMMDDHH24MISS",
TOTE_ID POSITION(15:22) CHAR,
DEPT POSITION(23:24) CHAR)
)
LOCATION ('BOXES.dat');
As you can see i specified the DEFAULT DIRECTORY clause without embedding the directory object name in double quotes. You must use double quotes if the directory object name is case sensitive otherwise always use uppercase letters.
Do not use single quotes for the directory object name or you’ll get ORA-22929.
See message translations for ORA-29400 and search additional resources.
«data cartridge errorn%s»
*Cause: An error has occurred in a data cartridge external procedure.
This message will be followed by a second message giving
more details about the data cartridge error.
*Action: See the data cartridge documentation
for an explanation of the second error message.
Попробовать перевести
Поискать эту ошибку на форуме
Перечень ошибок взят из официального перечня ошибок Oracle версии 11R2.
Если не можете справиться — поищите эту ошибку на форуме по ссылке выше, многие ошибки мы уже разобрали.
Не получается — обязательно напишите, разберем, попробуем, пощупаем, понюхаем.
Главное — не паникуйте, но всегда, в первую очередь убедитесь, что бекап есть и сделайте бекап того, что сейчас имеете
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?