Sunday, December 20, 2015

ORA-29913: error in executing ODCIEXTTABLEOPEN callout;ORA-29400;KUP-00554;KUP-01005

Drop the table (if exists)
BEGIN
EXECUTE IMMEDIATE 'DROP TABLE EXT_TEST PURGE';
EXCEPTION WHEN OTHERS THEN IF SQLCODE != -942 THEN RAISE; END IF;
END;
Verify this does not already exist before running this
Create the "Directory" Object
BEGIN
CREATE OR REPLACE DIRECTORY FULL_SCHEMA_BACKUPS as '/mnt/dbbkup/Databkup/FULL_SCHEMA_BACKUPS'
END;
grant read,write on directory FULL_SCHEMA_BACKUPS to TEST_SUH;
Create the "External" table
SQL> CREATE TABLE EXT_TEST
  2  (
  3     TEST_Client VARCHAR2(30 BYTE)
  4     ,REQUEST_ID VARCHAR2(20 BYTE)
  5     ,Measure VARCHAR2(200 BYTE)
  6     ,FirstDate VARCHAR2(25 BYTE)
  7     ,LastDate VARCHAR2(25 BYTE)
  8     ,CHANGED_FIELD VARCHAR2(10 BYTE)
  9     ,OLD_VALUE VARCHAR2(100 BYTE)
 10     ,NEW_VALUE VARCHAR2(100 BYTE)
 11  )
 12  ORGANIZATION EXTERNAL
 13  (
 14    TYPE ORACLE_LOADER
 15    DEFAULT DIRECTORY FULL_SCHEMA_BACKUPS
   16  ACCESS PARAMETERS
  17   (
 18     SKIP 1
 19      FIELDS TERMINATED BY '|'
 20      RECORDS DELIMITED BY '\r\n'
 21      MISSING FIELD VALUES ARE NULL
  22     NOBADFILE
   23    NODISCARDFILE
   24    NOLOGFILE
 25    )
 26    LOCATION
  27   (
 28      'EXT_TEST.txt'
  ) 29
) 30
 31  REJECT LIMIT 0;

Table created.

SQL> select * from EXT_TEST;
select * from EXT_TEST
*
ERROR at line 1:
ORA-29913: error in executing ODCIEXTTABLEOPEN callout
ORA-29400: data cartridge error
KUP-00554: error encountered while parsing access parameters
KUP-01005: syntax error: found "skip": expecting one of: "column, fields,
records"
KUP-01007: at line 1 column 1

Replaced my code as below and it worked. I am able to query from EXT_TEST table.

CREATE TABLE EXT_TEST
(
                HEDIS_Client VARCHAR2(30 BYTE)
                ,REQUEST_ID VARCHAR2(20 BYTE)
                ,Measure VARCHAR2(200 BYTE)
                ,FirstDate VARCHAR2(25 BYTE)
                ,LastDate VARCHAR2(25 BYTE)
                ,CHANGED_FIELD VARCHAR2(10 BYTE)
                ,OLD_VALUE VARCHAR2(100 BYTE)
                ,NEW_VALUE VARCHAR2(100 BYTE)
)
ORGANIZATION EXTERNAL 
(
  TYPE ORACLE_LOADER
  DEFAULT DIRECTORY FULL_SCHEMA_BACKUPS
  ACCESS PARAMETERS
  (
    RECORDS DELIMITED BY NEWLINE
    LOGFILE FULL_SCHEMA_BACKUPS:'EXT_TEST.log'
    BADFILE FULL_SCHEMA_BACKUPS:'EXT_TEST.bad' 
    SKIP 1
    FIELDS TERMINATED BY '|'
    MISSING FIELD VALUES ARE NULL
          )
  LOCATION
  (
    'EXT_TEST.txt'
  )
)

REJECT LIMIT 0;

and removed 
NOBADFILE
NODISCARDFILE
NOLOGFILE



No comments: