0

I had created an external table in Oracle 11g.

It throws the error of file not found sometimes. The file is present in the oracle external table directory with all the permissions provided to the folder/file.

The thing that is odd is that the error does not appear every time. The select on the external table works most of the time. But sometimes, this error pops up. It depends upon Oracle's mood. :P

Already done:

  • I have provided all the grants to the directory.
  • I have provided chmod 777 (rwx) access to the files.
  • I archive the metadata files after each run (*.log, *.dsc and *.bad).

Error:

ORA-29913: error in executing ODCIEXTTABLEOPEN callout

ORA-29400: data cartridge error

KUP-04040: file DIM_OWNING_CENTER_FEED.dat in XFB_EXT_TAB_DATA not found

Table script:

CREATE TABLE SCHEMA_NAME.DIM_OWNING_CENTER_EXT
  (
    S_OWNING_CENTER_ID VARCHAR2 (50),
    OWNING_CENTER      VARCHAR2 (52),
    ED_START_DATE      DATE
  )
  ORGANIZATION EXTERNAL
  (
    TYPE ORACLE_LOADER 
    DEFAULT DIRECTORY XFB_EXT_TAB_DATA 
    ACCESS PARAMETERS (
      RECORDS DELIMITED BY NEWLINE
      SKIP 2
      LOAD WHEN (
        S_OWNING_CENTER_ID != 'FILETRAILER'
      )
      FIELDS TERMINATED BY '|^'
      MISSING FIELD VALUES ARE NULL (
        S_OWNING_CENTER_ID CHAR (50), 
        OWNING_CENTER CHAR (52), 
        ED_START_DATE DATE 'DD-MON-YYYY HH24:MI:SS'
      )
    ) 
    LOCATION ('DIM_OWNING_CENTER_FEED.dat')
  )
  PARALLEL 8 REJECT LIMIT UNLIMITED;
Community
  • 1
  • 1
Chakraborty
  • 123
  • 1
  • 2
  • 8
  • 1
    Are you using a standalone database or RAC? If it's RAC does the file exist in the filesystem directory on every node? – Alex Poole Jan 14 '19 at 10:38
  • RAC DB. The thing is it works most of the time. But sometimes it throws the above error. This error occurs mostly when my package is called from a unix script. – Chakraborty Jan 16 '19 at 05:42
  • Right, so does the file exist in the filesystem on both nodes? If not then whether it works or errors will depend on which instance your script happens to connect to. – Alex Poole Jan 16 '19 at 07:13

1 Answers1

1

It depends upon Oracle's mood.

No, it depends which instance you happen to connect to, and whether the DIM_OWNING_CENTER_FEED.dat file exists on the node that instance is running on. If you only created it on one of the nodes then it will sometimes work and sometimes error. You can check which instance you're on, both when it works and when it doesn't, by querying v$instance.

If you only want to maintain the file (and deal with the logs etc.) on one node, for some reason, then you'll have to modify your script to connect to that specific instance. But then if that instance is down for some reason - patching or whatever - it will fail anyway. That's kind of the point of RAC, of course.

An alternative is to have both nodes present the same physical file, for instance by NFS-mounting the same actual filesystem/path to wherever the XFB_EXT_TAB_DATA directory object points to, on both nodes. Or you could use a preprocessor script to copy the file from some shared location at runtime, but that has its own complications.

Alex Poole
  • 183,384
  • 11
  • 179
  • 318
  • Thans Alex for providing the details. I am checking with my DBA team, and this seems to be the issue. Hopefully it will be resolved now. Thanks for the help. – Chakraborty Jan 16 '19 at 11:19