0

I have setup a simple Oracle external table test.

The database we're using is 11g. I have followed the following steps :

First I created a directory :

create or replace directory  INTF_DIR AS '/orabin/hrtst/TEST/' ;

This is the external table definition:

CREATE TABLE XXHCM_LU_EXT
  (
    LOOKUP_TYPE  VARCHAR2(200 BYTE) ,
    LOOKUP_CODE  VARCHAR2(200 BYTE) ,
    MEANING      VARCHAR2(200 BYTE) ,
    ENABLED_FLAG VARCHAR2(10 BYTE)
  )
  ORGANIZATION EXTERNAL
  (
    TYPE ORACLE_LOADER 
    DEFAULT DIRECTORY INTF_DIR 
    ACCESS PARAMETERS 
    ( RECORDS DELIMITED BY NEWLINE SKIP 1 
    BADFILE INTF_DIR:'lookup_code.bad' 
    LOGFILE INTF_DIR:'Lookup_code.log' 
    NODISCARDFILE FIELDS TERMINATED BY '|' OPTIONALLY ENCLOSED BY '"' MISSING FIELD VALUES ARE NULL REJECT ROWS
WITH ALL NULL FIELDS ) LOCATION ( INTF_DIR: 'Lookup_code.csv' )
  )
  REJECT LIMIT UNLIMITED

But when i am executing the **select * frm XXHCM_LU_EXT;**

I am getting the following error :

  ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
error opening file /orabin/hrtst/TEST/Lookup_code.log

Also, I checked for the permission on directory and the files :

/orabin/hrtst/TEST$  ls -l Lookup_code.csv
-rwxrwxrwx 1 xxhcmuser dba 259 Feb 29 15:37 Lookup_code.csv

/orabin/hrtst/TEST$  ls -l /orabin/hrtst/TEST/
total 8
-rwxrwxrwx 1 xxhcmuser dba 246 Feb 29 15:38 Control.ctl
-rwxrwxrwx 1 xxhcmuser dba 259 Feb 29 15:37 Lookup_code.csv
-rwxrwxrwx 1 xxhcmuser dba   0 Feb 25 15:08 Lookup_code.log

Still i am not being able to access the log file.

divya.trehan573
  • 454
  • 1
  • 12
  • 28
  • What are the ownership/permissions on the TEST directory, and hrtest and orabin above it? Can you try to read and create files manually as the Oracle user? – Alex Poole Feb 29 '16 at 19:53
  • @AlexPoole-I tried creating files in the directry and i was able to do that. is that what you are asking ? – divya.trehan573 Mar 01 '16 at 06:06
  • @divya.trehan573 did you grant READ and WRITE privileges to the db user who owns the external table? – Marco Baldelli Mar 01 '16 at 15:26
  • @MarcoBaldelli- I have created it in apps schema. DB user will be the username trough which i am loggin into winscp ? – divya.trehan573 Mar 01 '16 at 17:42
  • @divya.trehan573 No, DB user is the owner of the external table. For example if your external table is DBUSER.XXHCM_LU_EXT then you will need to run `GRANT READ, WRITE on INTF_DIR to DBUSER`. – Marco Baldelli Mar 02 '16 at 17:53

0 Answers0