CODEWITHSUNDEEP
oracle-databaseoracle11gruntime-errorexternal-tables
Chakraborty
Chakraborty

Reputation: 133

External table throwing error (sometimes)

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:

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;

Upvotes: 0

Views: 650

Answers (1)

Alex Poole
Alex Poole

Reputation: 191235

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.

Upvotes: 1

Related Questions