Home » RDBMS Server » Server Utilities » Error in executing ODCIEXTTABLEOPEN (Oracle 10G)
icon4.gif  Error in executing ODCIEXTTABLEOPEN [message #469922] Fri, 06 August 2010 07:25 Go to next message
fehein
Messages: 2
Registered: August 2010
Location: Brasil
Junior Member

Confused
Hy, i created the External Table using the script below.

CREATE TABLE EXT_ST_FINANCEIRO_REAL (
DT_DATA   NUMBER,
  TIPO      NUMBER,
  ENTIDADE  NUMBER,
  VALOR     Varchar2(40))

ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY FILE_LOC_OWBARQUIVOS
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
BADFILE FILE_LOC_OWBARQUIVOS:'EXT_ST_FINANCEIRO_REALIZADO.bad'
LOGFILE FILE_LOC_OWBARQUIVOS:'EXT_ST_FINANCEIRO_REALIZADO.log'
FIELDS TERMINATED BY WHITESPACE
REJECT ROWS WITH ALL NULL FIELDS
MISSING FIELD VALUES ARE NULL
(         DT_DATA  INTEGER TERMINATED BY WHITESPACE NULLIF DT_DATA=BLANKS, 
          TIPO     INTEGER TERMINATED BY WHITESPACE NULLIF TIPO=BLANKS, 
          ENTIDADE INTEGER TERMINATED BY WHITESPACE NULLIF ENTIDADE=BLANKS, 
          VALOR    CHAR(40) TERMINATED BY WHITESPACE NULLIF VALOR=BLANKS))
LOCATION (FILE_LOC_OWBARQUIVOS:'EXT_ST_FINANCEIRO_REALIZADO.txt'))
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;


But, returned the errors.


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 "missing" expecting on of: "column, exit,("
KUP-01007: at line 6 column 1
ORA-06512: at "SYS.ORACLE_LOADER", line 19

Anyone have any solutions to the script?
Re: Error in executing ODCIEXTTABLEOPEN [message #469929 is a reply to message #469922] Fri, 06 August 2010 08:00 Go to previous messageGo to next message
joy_division
Messages: 4490
Registered: February 2005
Location: East Coast USA
Senior Member
Get rid of the blank line after the table definition.
Re: Error in executing ODCIEXTTABLEOPEN [message #469932 is a reply to message #469929] Fri, 06 August 2010 08:09 Go to previous messageGo to next message
fehein
Messages: 2
Registered: August 2010
Location: Brasil
Junior Member

CREATE TABLE EXT_ST_FINANCEIRO_REAL (
DT_DATA   NUMBER,
  TIPO      NUMBER,
  ENTIDADE  NUMBER,
  VALOR     Varchar2(40))
ORGANIZATION EXTERNAL
(TYPE oracle_loader
DEFAULT DIRECTORY FILE_LOC_OWBARQUIVOS
ACCESS PARAMETERS (
RECORDS DELIMITED BY NEWLINE
BADFILE FILE_LOC_OWBARQUIVOS:'EXT_ST_FINANCEIRO_REALIZADO.bad'
LOGFILE FILE_LOC_OWBARQUIVOS:'EXT_ST_FINANCEIRO_REALIZADO.log'
FIELDS TERMINATED BY WHITESPACE
REJECT ROWS WITH ALL NULL FIELDS
MISSING FIELD VALUES ARE NULL
(         DT_DATA  INTEGER TERMINATED BY WHITESPACE NULLIF DT_DATA=BLANKS, 
          TIPO     INTEGER TERMINATED BY WHITESPACE NULLIF TIPO=BLANKS, 
          ENTIDADE INTEGER TERMINATED BY WHITESPACE NULLIF ENTIDADE=BLANKS, 
          VALOR    CHAR(40) TERMINATED BY WHITESPACE NULLIF VALOR=BLANKS))
LOCATION (FILE_LOC_OWBARQUIVOS:'EXT_ST_FINANCEIRO_REALIZADO.txt'))
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;


But, the problem persist, returned the error.
Re: Error in executing ODCIEXTTABLEOPEN [message #469994 is a reply to message #469932] Fri, 06 August 2010 13:07 Go to previous message
joy_division
Messages: 4490
Registered: February 2005
Location: East Coast USA
Senior Member
Works for me:
SQL> select * from v$version
  2  /

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 - 64bi
PL/SQL Release 10.2.0.1.0 - Production
CORE    10.2.0.1.0      Production
TNS for Solaris: Version 10.2.0.1.0 - Production
NLSRTL Version 10.2.0.1.0 - Production

SQL> get ri
  1  CREATE TABLE EXT_ST_FINANCEIRO_REAL (
  2  DT_DATA   NUMBER,
  3    TIPO      NUMBER,
  4    ENTIDADE  NUMBER,
  5    VALOR     Varchar2(40))
  6  ORGANIZATION EXTERNAL
  7  (TYPE oracle_loader
  8  DEFAULT DIRECTORY MOD_SOURCE
  9  ACCESS PARAMETERS (
 10  RECORDS DELIMITED BY NEWLINE
 11  BADFILE MOD_SOURCE:'EXT_ST_FINANCEIRO_REALIZADO.bad'
 12  LOGFILE MOD_SOURCE:'EXT_ST_FINANCEIRO_REALIZADO.log'
 13  FIELDS TERMINATED BY WHITESPACE
 14  REJECT ROWS WITH ALL NULL FIELDS
 15  MISSING FIELD VALUES ARE NULL
 16  (         DT_DATA  INTEGER TERMINATED BY WHITESPACE NULLIF DT_DATA=BLANKS,
 17            TIPO     INTEGER TERMINATED BY WHITESPACE NULLIF TIPO=BLANKS,
 18            ENTIDADE INTEGER TERMINATED BY WHITESPACE NULLIF ENTIDADE=BLANKS,
 19            VALOR    CHAR(40) TERMINATED BY WHITESPACE NULLIF VALOR=BLANKS))
 20  LOCATION (MOD_SOURCE:'EXT_ST_FINANCEIRO_REALIZADO.txt'))
 21  REJECT LIMIT UNLIMITED
 22  NOPARALLEL
 23* NOMONITORING
 24  /

Table created.


Of course replacing your DIRECTORY name with one that I have.
Previous Topic: Imp-00033 table not found while importing
Next Topic: Importing a dump
Goto Forum:
  


Current Time: Fri Aug 01 05:58:30 CDT 2014

Total time taken to generate the page: 0.09190 seconds