Error in External Tables
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: Error in External Tables

  1. #1
    Join Date
    Jan 2005
    Location
    Singapore
    Posts
    2

    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.

  2. #2
    Join Date
    May 2005
    Posts
    31
    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
    Experience is a hard teacher because she gives the test first, the lesson afterwards.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width