CODEWITHSUNDEEP
sqloracle-database
setius666
setius666

Reputation: 46

External table syntax error KUP-01005 Oracle

I receive error as below every time when i select external table that i have created.

ORA-29913: bład podczas wykonywania wywołania (callout) ODCIEXTTABLEOPEN

ORA-29400: bład kartrydza danych

KUP-00554: error encountered while parsing access parameters

KUP-01005: syntax error: found "minussign": expecting one of: "badfile, byteordermark, characterset, column, data, delimited, discardfile, dnfs_enable, dnfs_disable, disable_directory_link_check, field, fields, fixed, io_options, load, logfile, language, nodiscardfile, nobadfile, nologfile, date_cache, dnfs_readbuffers, preprocessor, readsize, string, skip, territory, variable, xmltag"

KUP-01007: at line 4 column 23 29913. 00000 - "error in executing %s callout"

The external table is created successfully. Here is the script which creates external table:

CREATE TABLE TB_CNEI_01C
(
    NEW_OMC_ID              VARCHAR(2),
    NEW_OMC_NM              VARCHAR(8),
    NEW_BSS_ID              VARCHAR(6),
    NEW_BSS_NM              VARCHAR(20),
    OMC_ID                  VARCHAR(2),
    OMC_NM                  VARCHAR(8),
    OLD_BSS_ID              VARCHAR(6),
    OLD_BSS_NM              VARCHAR(20),
    DEPTH_NO                INTEGER,
    NE_TP_NO                INTEGER,
    OP_YN                   INTEGER,
    FAC_ALIAS_NM            VARCHAR(20),
    FAC_GRP_ALIAS_NM        VARCHAR(20),
    SPC_VAL                 VARCHAR(4),
    INMS_FAC_LCLS_CD        VARCHAR(2),
    INMS_FAC_MCLS_CD        VARCHAR(3),
    INMS_FAC_SCLS_CD        VARCHAR(3),
    INMS_FAC_SCLS_DTL_CD    VARCHAR(2),
    LDEPT_ID                VARCHAR(3),
    FAC_ID                  VARCHAR(15),
    MME_IP_ADDR             VARCHAR(20),
    MDEPT_ID                VARCHAR(4),
    HW_TP_NM                VARCHAR(20),
    MME_POOL_NM             VARCHAR(20),
    BORD_CNT                INTEGER,
    FAC_DTL_CLSFN_NM        VARCHAR(50),
    INSTL_FLOOR_NM          VARCHAR(20),
    INSTL_LOC_NM            VARCHAR(30)
)
ORGANIZATION EXTERNAL
(
TYPE oracle_loader
DEFAULT DIRECTORY EXTERNAL_DATA
ACCESS PARAMETERS
    (
        RECORDS DELIMITED BY NEWLINE
        badfile EXTERNAL_DATA:'testTable.bad'
        logfile EXTERNAL_DATA:'testTable.log'
        CHARACTERSET x-IBM949
        FIELDS TERMINATED BY ','
        MISSING FIELD VALUES ARE NULL
            (
                    NEW_OMC_ID              VARCHAR(2),
                    NEW_OMC_NM              VARCHAR(8),
                    NEW_BSS_ID              VARCHAR(6),
                    NEW_BSS_NM              VARCHAR(20),
                    OMC_ID                  VARCHAR(2),
                    OMC_NM                  VARCHAR(8),
                    OLD_BSS_ID              VARCHAR(6),
                    OLD_BSS_NM              VARCHAR(20),
                    DEPTH_NO                INTEGER,
                    NE_TP_NO                INTEGER,
                    OP_YN                   INTEGER,
                    FAC_ALIAS_NM            VARCHAR(20),
                    FAC_GRP_ALIAS_NM        VARCHAR(20),
                    SPC_VAL                 VARCHAR(4),
                    INMS_FAC_LCLS_CD        VARCHAR(2),
                    INMS_FAC_MCLS_CD        VARCHAR(3),
                    INMS_FAC_SCLS_CD        VARCHAR(3),
                    INMS_FAC_SCLS_DTL_CD    VARCHAR(2),
                    LDEPT_ID                VARCHAR(3),
                    FAC_ID                  VARCHAR(15),
                    MME_IP_ADDR             VARCHAR(20),
                    MDEPT_ID                VARCHAR(4),
                    HW_TP_NM                VARCHAR(20),
                    MME_POOL_NM             VARCHAR(20),
                    BORD_CNT                INTEGER,
                    FAC_DTL_CLSFN_NM        VARCHAR(50),
                    INSTL_FLOOR_NM          VARCHAR(20),
                    INSTL_LOC_NM            VARCHAR(30)    
            )
    )
LOCATION ('TB_CNEI_01C.csv')
);

I have checked all permisions for data directory and data files

Upvotes: 1

Views: 4985

Answers (2)

Narayanan Muthusamy
Narayanan Muthusamy

Reputation: 11

I had a few commented lines in my 'CREATE TABLE ..' script. I removed those commented lines and the error disappeared. I received the above suggestion from : http://www.orafaq.com/forum/t/182288/

Upvotes: 1

Barbaros Özhan
Barbaros Özhan

Reputation: 65408

It seems your CHARACTERSET(x-IBM949) containing - character is not valid

You may try the other alternatives without that sign,

such as AL32UTF8, US7ASCII, WE8MSWIN1252 .. etc.

Upvotes: 0

Related Questions