Re: External table without directory write access

On Jun 13, 6:14 pm, matthias.h..._at_gmail.com wrote:
> Hello,
>
> Is it possible to create an external table without write access on the
> underlying directory object ?
>
> I tried this:
>
> CREATE TABLE matthias_ext
> (
> SMILES VARCHAR2(1000 BYTE)
> )
> ORGANIZATION EXTERNAL
> ( TYPE ORACLE_LOADER
> DEFAULT DIRECTORY SMILES_DIR
> ACCESS PARAMETERS
> ( RECORDS DELIMITED BY newline
> FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'
> (
> smiles CHAR(1000)
> )
> )
> LOCATION (SMILES_DIR:'smiles.txt')
> )
> REJECT LIMIT UNLIMITED
> NOPARALLEL
> NOMONITORING;
>
> But when I query the table, it still requires write access on the
> directory object SMILES_DIR. I have read permissions on SMILES_DIR,
> but no write access.
>
> Any idea ? Couldn't find much in the documentation.
>
> Matthias

The reason it needs write access is that you didn't say where to log errors and what to do with rejected and/or discarded rows. By default, Oracle creates a .log file, and can create a .bad file or a .dis file if it hits a bad row or discards a row, in default directory, and it needs write access to do that. You should add NOLOGFILE NOBADFILE NODISCARDFILE to the record format info clause of ACCESS PARAMETERS to prevent Oracle from attempting to create any auxiliary files and thus remove the need for write access to the default directory:

CREATE TABLE matthias_ext
(
  SMILES VARCHAR2(1000 BYTE)
)
ORGANIZATION EXTERNAL
  ( TYPE ORACLE_LOADER

     DEFAULT DIRECTORY SMILES_DIR
     ACCESS PARAMETERS
     ( RECORDS DELIMITED BY newline

       NOBADFILE
       NOLOGFILE
       NODISCARDFILE

  FIELDS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"'   (
   smiles CHAR(1000)
  )
  )

     LOCATION (SMILES_DIR:'smiles.txt')
  )
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING; NOBADFILE NODISCARDFILE may be an overkill in your case, but NOLOGFILE is essential as Oracle always creates it unless explicitly told not to.

Hth,

   Vladimir M. Zakharychev
   N-Networks, makers of Dynamic PSP(tm)    http://www.dynamicpsp.com Received on Wed Jun 13 2007 - 11:54:55 CDT