OraFAQ Forum: Server Utilities » Create a External Table in Oracle 10g:==> ERROR: KUP-01005

Home » RDBMS Server » Server Utilities » Create a External Table in Oracle 10g:==> ERROR: KUP-01005 (Oracle Database 10g Release 2 10.2.0.3)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Create a External Table in Oracle 10g:==> ERROR: KUP-01005 [message #348703]

Wed, 17 September 2008 12:57

ajda_duenas
Messages: 3
Registered: September 2008
Location: Honduras

Junior Member

Hello.

I have a problem working with external tables, hope someone can help me with this problem. Thanks. Sad

This is the code of the external table
========================

CREATE TABLE SIAFI.RNP_IDS
(
  NUMERO_ID         VARCHAR2(30 BYTE),
  PRIMER_NOMBRE     VARCHAR2(300 BYTE),
  SEGUNDO_NOMBRE    VARCHAR2(300 BYTE),
  APELLIDO_PATERNO  VARCHAR2(300 BYTE),
  APELLIDO_MATERNO  VARCHAR2(300 BYTE),
  DEPARTAMENTO      VARCHAR2(300 BYTE),
  CORRELATIVO       VARCHAR2(300 BYTE)
)
ORGANIZATION EXTERNAL
  (  TYPE ORACLE_LOADER
     DEFAULT DIRECTORY SEG_DIRECTORIO
     ACCESS PARAMETERS 
       ( records delimited by NEWLINE 
        badfile SEG_DIRECTORIO:'censo.bad' 
        logfile SEG_DIRECTORIO:'censo.log' 
        FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' and '"'LDRTRIM
      REJECT ROWS WITH ALL NULL FIELDS
        (NUMERO_ID VARCHAR(30) NULLIF NUMERO_ID=BLANKS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' and '"'LDRTRIM,   
        PRIMER_NOMBRE VARCHAR(300) NULLIF PRIMER_NOMBRE=BLANKS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' and '"'LDRTRIM,
        SEGUNDO_NOMBRE VARCHAR(300) NULLIF SEGUNDO_NOMBRE=BLANKS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' and '"'LDRTRIM,
        APELLIDO_PATERNO VARCHAR(300) NULLIF APELLIDO_PATERNO=BLANKS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' and '"'LDRTRIM,
        APELLIDO_MATERNO VARCHAR(300) NULLIF APELLIDO_MATERNO=BLANKS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' and '"'LDRTRIM,
        DEPARTAMENTO VARCHAR(300) NULLIF DEPARTAMENTO=BLANKS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' and '"'LDRTRIM,
        CORRELATIVO VARCHAR(300) NULLIF CORRELATIVO=BLANKS TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' and '"'LDRTRIM,
        ) 
       )
     LOCATION (SEG_DIRECTORIO:'censo.txt')
  )
REJECT LIMIT UNLIMITED
NOPARALLEL
NOMONITORING;

When executing the stament select * from RNP_IDS it returns the following error message:
===========================================================
*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 "terminated": expecting one of: "and, comma, defaultif, not, nullif, or, )"*
*KUP-01007: at line 6 column 56*
*ORA-06512: at "SYS.ORACLE_LOADER", line 19*
*ORA-06512: at line 1*

This is the example of the file I'm using:
==========================
"0","DOUGLAS","AUGUSTO","ABBOTT","","1","3672097"
"0101190600010","MARIA","URBANA","GOMEZ","URBINA","2","1949122"
"0101190600076","ENRIQUETA","","GARCIA","","2","1162025"
"0101190800106","LUCILA","","FLORES","","2","1658013"

[mod-edit] added code tags.

[Updated on: Wed, 17 September 2008 12:59] by Moderator

Report message to a moderator

Re: Create a External Table in Oracle 10g:==> ERROR: KUP-01005 [message #348816 is a reply to message #348703]

Thu, 18 September 2008 00:31

Barbara Boehmer
Messages: 9101
Registered: November 2002
Location: California, USA

Senior Member

You need to use CHAR not VARCHAR and TERMINATED ... NULLIF ... not NULLIF ... TERMINATED and you have an extra comma after your last field description before the closing parentheses.

SCOTT@orcl_11g> CREATE OR REPLACE DIRECTORY seg_directorio AS 'c:\oracle11g'
  2  /

Directory created.

SCOTT@orcl_11g> CREATE TABLE RNP_IDS
  2  (
  3    NUMERO_ID	 VARCHAR2(30 BYTE),
  4    PRIMER_NOMBRE	 VARCHAR2(300 BYTE),
  5    SEGUNDO_NOMBRE	 VARCHAR2(300 BYTE),
  6    APELLIDO_PATERNO  VARCHAR2(300 BYTE),
  7    APELLIDO_MATERNO  VARCHAR2(300 BYTE),
  8    DEPARTAMENTO	 VARCHAR2(300 BYTE),
  9    CORRELATIVO	 VARCHAR2(300 BYTE)
 10  )
 11  ORGANIZATION EXTERNAL
 12    (  TYPE ORACLE_LOADER
 13  	  DEFAULT DIRECTORY SEG_DIRECTORIO
 14  	  ACCESS PARAMETERS
 15  	    ( records delimited by NEWLINE
 16  	     badfile SEG_DIRECTORIO:'censo.bad'
 17  	     logfile SEG_DIRECTORIO:'censo.log'
 18  	     FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"' and '"' LDRTRIM
 19  	   REJECT ROWS WITH ALL NULL FIELDS
 20  	     (NUMERO_ID CHAR(30) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' and '"' LDRTRIM NULLIF NUMERO_ID=BLANKS,
 21  	     PRIMER_NOMBRE CHAR(300) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' and '"' LDRTRIM NULLIF PRIMER_NOMBRE=BLANKS,
 22  	     SEGUNDO_NOMBRE CHAR(300) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' and '"' LDRTRIM NULLIF SEGUNDO_NOMBRE=BLANKS,
 23  	     APELLIDO_PATERNO CHAR(300) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' and '"' LDRTRIM NULLIF APELLIDO_PATERNO=BLANKS,
 24  	     APELLIDO_MATERNO CHAR(300) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' and '"' LDRTRIM NULLIF APELLIDO_MATERNO=BLANKS,
 25  	     DEPARTAMENTO CHAR(300) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' and '"' LDRTRIM NULLIF DEPARTAMENTO=BLANKS,
 26  	     CORRELATIVO CHAR(300) TERMINATED BY "," OPTIONALLY ENCLOSED BY '"' and '"' LDRTRIM NULLIF CORRELATIVO=BLANKS
 27  	     )
 28  	    )
 29  	  LOCATION (SEG_DIRECTORIO:'censo.txt')
 30    )
 31  REJECT LIMIT UNLIMITED
 32  NOPARALLEL
 33  NOMONITORING
 34  /

Table created.

SCOTT@orcl_11g> COLUMN PRIMER_NOMBRE	 FORMAT A15
SCOTT@orcl_11g> COLUMN SEGUNDO_NOMBRE	 FORMAT A15
SCOTT@orcl_11g> COLUMN APELLIDO_PATERNO  FORMAT A15
SCOTT@orcl_11g> COLUMN APELLIDO_MATERNO  FORMAT A15
SCOTT@orcl_11g> COLUMN DEPARTAMENTO	 FORMAT A15
SCOTT@orcl_11g> COLUMN CORRELATIVO	 FORMAT A15
SCOTT@orcl_11g> SELECT * FROM rnp_ids
  2  /

NUMERO_ID                      PRIMER_NOMBRE   SEGUNDO_NOMBRE  APELLIDO_PATERN APELLIDO_MATERN DEPARTAMENTO    CORRELATIVO
------------------------------ --------------- --------------- --------------- --------------- --------------- ---------------
0                              DOUGLAS         AUGUSTO         ABBOTT                          1               3672097
0101190600010                  MARIA           URBANA          GOMEZ           URBINA          2               1949122
0101190600076                  ENRIQUETA                       GARCIA                          2               1162025
0101190800106                  LUCILA                          FLORES                          2               1658013

SCOTT@orcl_11g>

Report message to a moderator