Oracle 10G external table error

Question

I have created the following external table on Oracle 10G.

 connect system/password as SYSDBA
 create or replace directory ext_tab as 'C:\Suman\External_Tables';
 CREATE TABLE emp_ext_3( 
    empno NUMBER(4), first_name CHAR(20), last_name CHAR(20), dob     CHAR(10))
    ORGANIZATION EXTERNAL( 
    TYPE ORACLE_LOADER DEFAULT DIRECTORY ext_tab
    ACCESS PARAMETERS
    ( 
    RECORDS DELIMITED BY NEWLINE 
    NOBADFILE
    NOLOGFILE
    SKIP 1    
    FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'  LRTRIM  
    MISSING FIELD VALUES ARE NULL  
    REJECT ROWS WITH ALL NULL FIELDS 
    (empno INTEGER EXTERNAL (4),
    first_name CHAR(20),
    last_name CHAR(20),
    dob CHAR(10) DATE_FORMAT DATE MASK "dd/mm/yyyy") 
    ) 
    LOCATION ('employee1.dat')
    )
    PARALLEL 
    REJECT LIMIT 0;

Now If I try to execute select command, I am getting following error.

SQL> select * from "SYSTEM"."EMP_EXT_3";
select * from "SYSTEM"."EMP_EXT_3"
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file employee1.dat in EXT_TAB not found
ORA-06512: at "SYS.ORACLE_LOADER", line 19

But I have the file "employee1.dat" in 'C:\Suman\External_Tables'. Can someone please help me on why I am getting this error?

In Windows happens: Could you verify that filename is not employee1.dat.txt and windows is hiding the extension? — Florin Ghita, Feb 18 '13 at 08:18
As a note: Only directory need to be created with user sys/system. External table and queries on it should be done with a regular user. — Florin Ghita, Feb 18 '13 at 08:23
I have verified. The file name is employee1.dat and not employee1.dat.txt — user2032118, Feb 18 '13 at 08:38
"As a note: Only directory need to be created with user sys/system. External table and queries on it should be done with a regular user." Can you elaborate more on this? what do you mean by regular user? — user2032118, Feb 18 '13 at 08:38
sys and system are for administration purposes only. create an user, create a tablespace and use those for applications/tests/homeworks :) — Florin Ghita, Feb 18 '13 at 08:44
If you connected as `system/password as SYSDBA`, then the table would be created in SYS, not SYSTEM. Yet the table also appears to exist in SYSTEM, or else you wouldn't have got that error message. Something doesn't add up here. Either the script is not exactly what was ran, or the tables exists in two different schemas. — Jon Heller, Feb 19 '13 at 06:36

score 0 · Accepted Answer · answered Feb 18 '13 at 08:42

0

The Oracle server is looking for a file in the following location: 'C:\Suman\External_Tables'. That directory is on the Oracle server machine, not your local Windows client machine.

answered Feb 18 '13 at 08:42

Rene

10,391
5
33
46

I have installed Oracle Server in my local machine. So that should not be a problem right? – user2032118 Feb 18 '13 at 08:55
@user2032118 - possibly; Oracle needs to be able to access the file at O/S level, it's irrelevant whether you can access it yourself. That's part of the security model around [directories](http://docs.oracle.com/cd/B19306_01/server.102/b14215/et_concepts.htm#i1009094). – Alex Poole Feb 18 '13 at 10:05

Oracle 10G external table error

1 Answers1