ORA-29913:执行ODCIEXTTABLEOPEN标注时出错 [英] ORA-29913: error in executing ODCIEXTTABLEOPEN callout

问题描述

我正在使用hr模式创建外部表，但出现错误

I am creating external table using hr schema but i get errors

"ORA-29913:执行ODCIEXTTABLEOPEN标注ORA-29400时出错: 数据盒带错误KUP-00554:解析访问时遇到错误 参数KUP-01005:语法错误:发现缺失":期望以下之一: 列，(" KUP-01007:第4行第3列 29913.00000-执行％s标注时出错" *原因:执行指定的标注会导致错误. *措施:检查错误消息采取适当的措施."

"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 "missing": expecting one of: "column, (" KUP-01007: at line 4 column 3 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."

----------------我的代码-------------------

----------------My Code-------------------

create directory ex_tab as 'C:\My Works\External Table';


create table strecords (
st_id number(4),
st_name varchar(10),
schl_name varchar(5),
st_city varchar(15),
st_year number(4)
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
 DEFAULT DIRECTORY ex_tab
 ACCESS PARAMETERS
 (
  RECORDS DELIMITED BY newline
  FIELDS TERMINATED BY ',' 
  REJECT ROWS WITH ALL NULL FIELDS
  MISSING FIELDS VALUES ARE NULL
  (
    st_id number(4),
    st_name char(10),
    schl_name char(5),
    st_city char(15),
    st_year number(4)
  )
 )
 LOCATION ('strecords.txt')
);

desc strecords;

select * from strecords;

这是我的代码，请检查并检查.

推荐答案

您在这里遇到了几个问题.引起您问题的直接原因是

You have several issues here. The immediate one causing your problem is that you have the clauses in the wrong order, but you also have MISSING FIELDS instead of MISSING FIELD:

...
 ACCESS PARAMETERS
 (
  RECORDS DELIMITED BY newline
  FIELDS TERMINATED BY ',' 
  MISSING FIELD VALUES ARE NULL
  REJECT ROWS WITH ALL NULL FIELDS
  (
...

然后您的

Then your field list contents have invalid data types for that part of the statement; you can just omit that entirely in this case as those match the table column definition.

如果未指定任何字段列表，则假定数据文件中的字段与外部表中的字段具有相同的顺序.

If no field list is specified, then the fields in the data file are assumed to be in the same order as the fields in the external table.

因此您可以将其简化为:

So you can simplify it to:

create table strecords (
st_id number(4),
st_name varchar(10),
schl_name varchar(5),
st_city varchar(15),
st_year number(4)
)
ORGANIZATION EXTERNAL
(TYPE oracle_loader
 DEFAULT DIRECTORY ex_tab
 ACCESS PARAMETERS
 (
  RECORDS DELIMITED BY newline
  FIELDS TERMINATED BY ',' 
  MISSING FIELD VALUES ARE NULL
  REJECT ROWS WITH ALL NULL FIELDS
 )
 LOCATION ('strecords.txt')
);

这篇关于ORA-29913:执行ODCIEXTTABLEOPEN标注时出错的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！