我'm trying to run a simple external table program using oracle 11g on Linux VM. The problem is that I can't查询.txt文件中的任何数据 .
这是我的代码:
CONN / as sysdba;
CREATE OR REPLACE DIRECTORY DIR1 AS 'home/oracle/TEMP/X/';
GRANT READ, WRITE ON DIRECTORY DIR1 TO user;
CONN user/password;
CREATE TABLE gerada
(
field1 INT,
field2 Varchar2(20)
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY DIR1
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ';'
MISSING FIELD VALUES ARE NULL
)
LOCATION ('registros.txt')
)
REJECT LIMIT UNLIMITED;
--Error starts here.
SELECT * FROM gerada;
DROP TABLE gerada;
DROP DIRECTORY DIR1;
这是错误消息:
第1行的错误:ORA-29913:执行ODCIEXTTABLEOPEN标注时出错ORA-29400:数据盒式磁带错误错误打开文件home / oracle / TEMP / X / GERADA_3375.log
这就是registros.txt的样子:
1234;hello world;
我已经检查了我对DIR1的权限,并且我有读/写权限 .
有任何想法吗?
1 回答
ORA-29913和ORA-29400意味着您无法访问目录和/或文件 .
仔细查看CREATE DIRECTORY命令,看起来您正在使用的路径可能格式错误 . 尝试在路径的开头添加正斜杠,并在创建目录时删除路径末尾的斜杠 - 例如
CREATE OR REPLACE DIRECTORY DIR1 AS '/home/oracle/TEMP/X';
.分享和享受 .