Oracle外部表 [英] Oracle external tables
问题描述
尽管我研究了Oracle论坛，但我仍在努力使用Oracle外部表.仍然没有成功.
I'm struggling with an Oracle external table, although I researched the Oracle forums. Still, no success.
假设我有一张简单的桌子
Let's suppose I have a simple table
DESCRIBE PRODUCTS
Name Null Type
------------------------------ -------- ---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
ID NOT NULL NUMBER
NAME VARCHAR2(30)
VALUE NUMBER(5,2)
DEP VARCHAR2(30)
COUNT NUMBER(3)
然后，我创建了一个oracle文件夹:
Then, I created an oracle folder:
CREATE OR REPLACE DIRECTORY ext_prod_dir AS 'c:\';
我将该表的内容保存在.lst文件中
I save the content of that table in a .lst file
spool c:\products.lst
select p.id || ';' || p.name || ';' || p.value || ';' || p.dep || ';' || p.count FROM products p;
spool off;
P.ID||';'||P.NAME||';'||P.VALUE||';'||P.DEP||';'||P.COUNT
----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1;Settlers of Catan;29,95;Toys;3
2;DVD Player;82,97;Electronics;2
3;Red Shirt;12,49;Clothes;3
4;Black Leather Couch;399,99;Furniture;5
5;Oak Cofee Table;223,99;Furniture;5
6;Technodrome;27,99;Toys;4
7;Oh Cereal;3,95;Foods;1
8;Game Console;299,95;Toys;2
9;Video Game;29,95;Toys;3
10;Lawn Chair;34,99;Furniture;11
11;Dog Toy Bone;34,99;Toys;9
12;Heated Blanket;27,95;Toys;8
13;Flux Capacitor;27,95;Toys;7
14;Chocolate Pie;3,14;Foods;7
然后我尝试创建外部表:
Then I tried to create the external table:
CREATE TABLE products_ext
(ID NUMBER,
NAME VARCHAR2(30),
VALUE NUMBER(5,2),
DEP VARCHAR2(30),
COUNT NUMBER(3))
ORGANIZATION EXTERNAL
(TYPE oracle_loader DEFAULT DIRECTORY ext_prod_dir
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ';'
MISSING FIELD VALUES ARE NULL
BADFILE ext_prod_dir:'products.bad_xt'
LOGFILE ext_prod_dir:'products.log_xt'
(ID CHAR(6),
NAME CHAR(30),
VALUE CHAR(8),
DEP CHAR(30),
COUNT CHAR(3)))
location ('products.lst')
) REJECT LIMIT UNLIMITED
到目前为止，一切都很好.然后，当我从外部表中选择数据时，我得到了:
So far so good. Then when I select data from the external table, I got:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encontered while parsing access parameters
KUP-01005: syntax error: found "badfile": expecting one of: "column (,reject"
KUP-01007: at line 4 column 7
我尝试了很多事情，但是我对此错误有所了解.我完成的最好的事情是我摆脱了错误，但是表是空的.如果有更多经验的人能指出我正确的方向，我将感激不尽.
I tried a huge amount of things, but I got variations on this error. Best thing I accomplished was that I got rid of error, but the table was empty. I would be very much indebted If someone with more experience can point me in the right direction.
推荐答案
BADFILE和LOGFILE不属于FIELDS子句.因此，将它们移到终止的字段"上方.
BADFILE and LOGFILE are not part of the FIELDS clause. So, move them above the FIELDS TERMINATED.
CREATE TABLE products_ext
(ID NUMBER,
NAME VARCHAR2(30),
VALUE NUMBER(5,2),
DEP VARCHAR2(30),
COUNT NUMBER(3))
ORGANIZATION EXTERNAL
(TYPE oracle_loader DEFAULT DIRECTORY ext_prod_dir
ACCESS PARAMETERS
(RECORDS DELIMITED BY NEWLINE
BADFILE ext_prod_dir:'products.bad_xt'
LOGFILE ext_prod_dir:'products.log_xt'
FIELDS TERMINATED BY ';'
MISSING FIELD VALUES ARE NULL
(ID CHAR(6),
NAME CHAR(30),
VALUE CHAR(8),
DEP CHAR(30),
COUNT CHAR(3)))
LOCATION ('products.lst')
) REJECT LIMIT UNLIMITED
此外，您说的是消除错误后，表格为空.您检查日志文件了吗?如果错误与VALUE列有关，则检查NLS_NUMERIC_CHARACTERS参数 查看v $ nls_parameters.
Also, you said when you got rid of the error, the table was empty. Did you check the logfile? If the error is with the VALUE column, then check NLS_NUMERIC_CHARACTERS parameter in view v$nls_parameters.
select * from v$nls_parameters;
检查小数点标记的确是逗号.如果没有，请更新此参数或在数据文件中更改它.
Check if the decimal marker is indeed a comma. If not either update this parameter or change it in the data file.
这篇关于Oracle外部表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
