外部表访问报错

[gaoflydog]

SQL> connect / as sysdba;
Connected.
SQL> show user
USER is "SYS"
SQL> create directory ext_dir_case3 as '/oradata/external_table';

Directory created.


SQL> grant read,write on directory ext_dir_case3 to scott;

Grant succeeded.

SQL> connect scott/tiger;
Connected.
SQL> show user;
USER is "SCOTT"
SQL> create table ext_case3
2 (ename varchar2(10),job varchar2(20),sal number)
3 organization external
4 (type oracle_loader
5 default directory ext_dir_case3
6 access parameters
7 (records delimited by newline characterset zhs16gbk
8 logfile 'ext_dir_case3':'ext_case3.log'
9 badfile 'ext_case3.bad'
10 fields terminated by ","
11 reject rows with all null fields
12 (ename,job,sal)
13 )location ('ext_case3.dat')
14 )reject limit unlimited;

Table created.

SQL> select * from ext_case3;
select * from ext_case3
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04080: directory object ext_dir_case3 not found


原因找到了：
logfile 'EXT_TEST_CASE3':'ext_case3.log'
单引号内大写就ok了

[gaoflydog]

[oracle@fleety external_table]$ pwd /oradata/external_table [oracle@fleety external_table]$ ls ext_case2.dat ext_case3.dat [oracle@fleety external_table]$ more ext_case3.dat tom,saler,4000 jack,manager,5000 lucy,operater,3000 lily,farmer,2300 [oracle@fleety external_table]$

[gaoflydog]

建立目录，并且授权给了scott用户，然后scott用户创建外部表，显示创建成功，但是数据没有导入，select查询时报错。

[gaoflydog]

SQL> desc ext_case3; Name Null? Type ----------------------------------------- -------- ---------------------------- ENAME VARCHAR2(10) JOB VARCHAR2(20) SAL NUMBER SQL>