Oracle external table with dba's directory

Question

I wanted to create an external table, but did not have the CREATE ANY DIRECTORY permission (and could not have it granted). Fair enough, I asked the DBAs to run the following:

CREATE OR REPLACE DIRECTORY ext_data_files AS '/data/ext_data_files';
GRANT ALL ON DIRECTORY ext_data_files TO MYAPPUSER;

They did, and the final object has the following script:

CREATE OR REPLACE DIRECTORY 
EXT_DATA_FILES AS 
'/data/ext_data_files';
GRANT READ, WRITE ON DIRECTORY SYS.EXT_DATA_FILES TO MYAPPUSER;

(I got that from asking a desc with Toad)

I was then hoping to use this directory to create my external table with the script as follows:

CREATE TABLE MYAPPUSER.MY_EXT_TABLE
(
  ID                VARCHAR2(100 BYTE),
  LOGIN             VARCHAR2(100 BYTE),
  CODE              VARCHAR2(100 BYTE),
  CREATED_AT        VARCHAR2(100 BYTE)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY SYS.EXT_DATA_FILES
     ACCESS PARAMETERS 
       ( RECORDS DELIMITED BY NEWLINE
    NOBADFILE
    NOLOGFILE
    FIELDS TERMINATED BY ';'
    MISSING FIELD VALUES ARE NULL
    ( ID, LOGIN, CODE, CREATED_AT) )
     LOCATION (SYS.EXT_DATA_FILES:'the_external_file.txt')
  )
REJECT LIMIT 0
PARALLEL ( DEGREE DEFAULT INSTANCES DEFAULT )
NOMONITORING;

but then when I SELECT * FROM MY_EXT_TABLE, the result is the infamous

ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-04040: file the_external_file.txt in EXT_DATA_FILES not found
ORA-06512: at "SYS.ORACLE_LOADER", line 19

(which has quite a few hits on google, but none seem related)

I'm confident of the syntax since this is the exact same script used in our DEV environment. Also, the permissions of all files and directories involved were checked and there is nothing lower than 775.

The only difference I have here from DEV (where it works) is that the directory EXT_DATA_FILES was not created by MYAPPUSER. I tried to create a synonym for it.. but had no effect.

Maybe worth mentioning, it is Oracle 10g we are talking about.

Am I missing something obvious? Is this allowed?

1. Why in sys? Couldn't the DBA's have created it in the correct schema? 2. Does you file actually exist in the directory? — Ben
– Ben, Commented Mar 21, 2012 at 12:30
Well, this directory should be used by more than one schema.. I thought it kind of made sense to be in SYS or somewhere common to all users. As for the files, yes it exists and as I mention "the permissions of all files and directories involved were checked". :/ — filippo
– filippo, Commented Mar 21, 2012 at 12:35
@Ben: Directories don't have schemas, they all belong to SYS (as of 11gR2) — Vincent Malgrat
– Vincent Malgrat, Commented Mar 21, 2012 at 12:43
Bahh. Shame on me. Found it. All files and permissions were there alright, one small issue thought. They moved the whole friggn' DB and didn't mount back some of less used FSs back after the migration (and as Murphy predicted, I picked one of these for my external table). From my app POV was all fine, but Oracle didn't have the FS to query. Sorry to bother you guys with this.. — filippo
– filippo, Commented Mar 21, 2012 at 15:00

APC · Accepted Answer · 2012-03-21 12:42:04Z

1

All directories are in fact owned by SYS. That's why there is no CREATE DIRECTORY privilege, only CREATE ANY DIRECTORY.

So try the command without prefixing the directory name with the SYS schema and see what happens.

answered Mar 21, 2012 at 12:42

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

filippo Over a year ago

Same result without the SYS.. It actually was the original script. I added the SYS to make it explicit in the question. Good to know that anyway. The error should be elsewhere.

filippo Over a year ago

Just found something interesting: In my dev env. if I do desc EXT_DATA_FILES it shows the description of the object. Now, in the prod env. if I do the same desc EXT_DATA_FILES it wont work, it will however show the description if I add the SYS. to it, e.g. desc SYS.EXT_DATA_FILES. Would this be a clue somehow? @VincentMalgrat this would also make sense to you? thanks!

Rene · Accepted Answer · 2012-03-21 12:46:06Z

0

The error message reads: "file the_external_file.txt in EXT_DATA_FILES not found"

Are you sure it's there?

answered Mar 21, 2012 at 12:46

Rene

10.6k5 gold badges37 silver badges48 bronze badges

Collectives™ on Stack Overflow

Oracle external table with dba's directory

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related