从外部表中选择时出现 sqlplus 错误:ORA-29913:执行 ODCIEXTTABLEOPEN 标注时出错-数据库问题

sqlplus error on select from external table: ORA-29913: error in executing ODCIEXTTABLEOPEN callout(从外部表中选择时出现 sqlplus 错误:ORA-29913:执行 ODCIEXTTABLEOPEN 标注时出错)

本文介绍了从外部表中选择时出现 sqlplus 错误:ORA-29913:执行 ODCIEXTTABLEOPEN 标注时出错的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我设置了一个简单的 Oracle 外部表测试，我(与 DBA 和 Unix 管理员一起)无法开始工作.

I have setup a simple Oracle external table test that I (alongside a DBA and Unix admin) can't get to work.

以下内容基于 Oracle 的外部表概念.我们使用的数据库是 11g.

The following is based on Oracle's External Tables Concepts. The database we're using is 11g.

这是外部表定义:

drop table emp_load;

CREATE TABLE emp_load
    (employee_number      CHAR(5),
     employee_dob         DATE,
     employee_last_name   CHAR(20),
     employee_first_name  CHAR(15),
     employee_middle_name CHAR(15),
     employee_hire_date   DATE)
  ORGANIZATION EXTERNAL
    (TYPE ORACLE_LOADER
     DEFAULT DIRECTORY defaultdir
     ACCESS PARAMETERS
       (RECORDS DELIMITED BY NEWLINE
        FIELDS (employee_number      CHAR(2),
                employee_dob         CHAR(20),
                employee_last_name   CHAR(18),
                employee_first_name  CHAR(11),
                employee_middle_name CHAR(11),
                employee_hire_date   CHAR(10) date_format DATE mask "mm/dd/yyyy"
               )
       )
     LOCATION ('external_table_test.dat')
);

这是external_table_test.dat"的内容:

This is the contents of "external_table_test.dat":

56november, 15, 1980  baker             mary       alice      09/01/2004
87december, 20, 1970  roper             lisa       marie      01/01/1999

我可以毫无问题地运行创建emp_load"的脚本.我也可以很好地描述表格.当我尝试从 emp_load 中选择 *"时，出现以下错误:

I am able to run the script that creates "emp_load" with no issues. I can also describe the table fine. When I attempt "select * from emp_load", I get the following errors:

SQL> select * from emp_load;
select * from emp_load
              *
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
error opening file /defaultdir/EMP_LOAD_29305.log

编辑 1
oracle 对该目录具有读/写权限.

EDIT 1
oracle has read/write permissions on the directory.

编辑 2
通过使用以下外部表定义，我能够通过此错误:

EDIT 2
I was able to get passed this error by using the following external table definition:

CREATE TABLE emp_load
    (employee_number      CHAR(3),
     employee_last_name   CHAR(20),
     employee_middle_name CHAR(15),
     employee_first_name  CHAR(15)
     )
  ORGANIZATION EXTERNAL
    (TYPE ORACLE_LOADER
     DEFAULT DIRECTORY defaultdir
     ACCESS PARAMETERS
       (RECORDS DELIMITED BY NEWLINE
        BADFILE DHHSMAPSIS:'EMP.BAD'
        LOGFILE DHHSMAPSIS:'EMP.LOG'
        FIELDS TERMINATED BY ','
       )
    LOCATION ('external_table_test2.dat')
)
REJECT LIMIT UNLIMITED;

我的 .dat 文件看起来像这样...

My .dat file looks like this...

056,baker,beth,mary
057,smith,teddy,john

我必须在EMP.BAD"、EMP.LOG"和& 上设置权限"external_table_test2.dat" 到 777 以使其工作.oracle 用户不拥有这些文件，但与这些文件在同一组中.

I had to set the permissions on "EMP.BAD", "EMP.LOG" & "external_table_test2.dat" to 777 in order to get it to work. The oracle user doesn't own those files but is in the same group as the files are.

知道为什么当我将这些文件的权限设置为 770 时无法使其工作吗?同样，oracle 与这些文件在同一组中，所以我认为 770 对权限来说是可以的...