-
Error in External Tables
I am having an error while using external tables.
Create directory blah as '/tmp/'
create table external_emp (
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2))
Organization external
(type oracle_loader
default directory BLAH
access parameters (records delimited by newline
fields terminated by ',')
location ('tab.lst'))
reject limit 1000;
::/tmp>ls -ltr test.lst
-rwxr-xr-x 480 2005-06-17 15:55 test.lst
::/tmp>cat test.lst
7369,SMITH,CLERK,7902,17-DEC-80,800,20
7499,ALLEN,SALESMAN,7698,20-FEB-81,1600,300,30
7521,WARD,SALESMAN,7698,22-FEB-81,1250,500,30
7566,JONES,MANAGER,7839,02-APR-81,2975,,20
7654,MARTIN,SALESMAN,7698,28-SEP-81,1250,1400,30
7698,BLAKE,MANAGER,7839,01-MAY-81,2850,,30
7782,CLARK,MANAGER,7839,09-JUN-81,2450,,10
7788,SCOTT,ANALYST,7566,19-APR-87,3000,,20
7839,KING,PRESIDENT,,17-NOV-81,5000,,10
7844,TURNER,SALESMAN,7698,08-SEP-81,1500,0,30
7876,ADAMS,CLERK,7788,23-MAY-87,1100,,20
grant read, write on directory BLAH to misreport;
sqlplus misreport/misreport
SQL> select ENAME from misreport.external_emp;
select ENAME from misreport.external_emp
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file tab.lst in BLAH not found
ORA-06512: at "SYS.ORACLE_LOADER", line 14
ORA-06512: at line 1
SQL> desc misreport.external_emp
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
I have given grants and everything.
Could anyone pls tell me what might be the issue.
-
create table external_emp (
EMPNO NUMBER(4),
ENAME VARCHAR2(10),
JOB VARCHAR2(9),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2))
Organization external
(type oracle_loader
default directory BLAH
access parameters (records delimited by newline
fields terminated by ',')
location ('tab.lst'))
reject limit 1000;
ls -ltr test.lst
the file name in external table creation statement is mentioned as tab.lst where as file in Unix directory is called as test.lst