This discussion is archived
11 Replies Latest reply: Jan 7, 2013 9:18 PM by 930854 RSS

External Table

930854 Newbie
Currently Being Moderated
Hi I am having trouble transfering data through an external table load. I've done it before and am not sure why I am getting these 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 "no": expecting one of: "double-quoted-string, identifier, single-quoted-string"
KUP-01007: at line 2 column 29
29913. 00000 - "error in executing %s callout"
*Cause:    The execution of the specified callout caused an error.
*Action:   Examine the error messages take appropriate action.

My query :
create directory muh_dir as 'F:\dir'


CREATE TABLE ext_dir
(
"NAME" VARCHAR2(100),
"NO" NUMBER
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER DEFAULT DIRECTORY muh_dir ACCESS PARAMETERS ( RECORDS DELIMITED BY NEWLINE BADFILE 'name.bad' LOGFILE 'name.log' DISCARDFILE ' name.dsc' SKIP 1 FIELDS TERMINATED BY ',' MISSING FIELD VALUES ARE NULL REJECT ROWS
WITH ALL NULL FIELDS (name, no ) ) LOCATION ( 'export1.csv' )
)
PARALLEL 5 REJECT LIMIT UNLIMITED;

Thanks in advance for any suggestions and help.
  • 1. Re: External Table
    BluShadow Guru Moderator
    Currently Being Moderated
    WITH ALL NULL FIELDS (name, no ) ) LOCATION ( 'export1.csv' )
    NAME is not a good name for a column as it's often reserved in various languages.
    Try changing that in your definition to something else like NM or EMP_NAME etc.
  • 2. Re: External Table
    971895 Journeyer
    Currently Being Moderated
    Can you post table script... i thinks column names should not be double quotes...
  • 3. Re: External Table
    BluShadow Guru Moderator
    Currently Being Moderated
    968892 wrote:
    Can you post table script... i thinks column names should not be double quotes...
    Rubbish. Column names can be put in double quotes, but it means that they are case sensitive. Ideally double quotes should be avoided, but still, it's not wrong.
  • 4. Re: External Table
    Solomon Yakobson Guru
    Currently Being Moderated
    927851 wrote:
    Hi I am having trouble transfering data through an external table load. I've done it before and am not sure why I am getting these errors:
    You are getting errors because column names do not match field names. Since you use quoted column names you must use quoted field names:
    SQL> CREATE TABLE ext_dir(
      2                       "NAME" VARCHAR2(100),
      3                       "NO" NUMBER
      4                      )
      5    ORGANIZATION EXTERNAL(
      6                          TYPE ORACLE_LOADER
      7                          DEFAULT DIRECTORY temp
      8                          ACCESS PARAMETERS(
      9                                            RECORDS DELIMITED BY NEWLINE
     10                                            BADFILE 'name.bad'
     11                                            LOGFILE 'name.log'
     12                                            DISCARDFILE ' name.dsc'
     13                                            SKIP 1
     14                                            FIELDS TERMINATED BY ','
     15                                            MISSING FIELD VALUES ARE NULL
     16                                            REJECT ROWS WITH ALL NULL
     17                                            FIELDS(
     18                                                   name,
     19                                                   no
     20                                                  )
     21                                          )
     22                          LOCATION('export1.csv')
     23                         )
     24    PARALLEL 5
     25    REJECT LIMIT UNLIMITED
     26  /
    
    Table created.
    
    SQL> select  *
      2    from  ext_dir
      3  /
    select  *
    *
    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 "no": expecting one of: "double-quoted-string,
    identifier, single-quoted-string"
    KUP-01007: at line 11 column 50
    
    
    SQL> DROP TABLE ext_dir
      2  /
    
    Table dropped.
    
    SQL> CREATE TABLE ext_dir(
      2                       "NAME" VARCHAR2(100),
      3                       "NO" NUMBER
      4                      )
      5    ORGANIZATION EXTERNAL(
      6                          TYPE ORACLE_LOADER
      7                          DEFAULT DIRECTORY temp
      8                          ACCESS PARAMETERS(
      9                                            RECORDS DELIMITED BY NEWLINE
     10                                            BADFILE 'name.bad'
     11                                            LOGFILE 'name.log'
     12                                            DISCARDFILE ' name.dsc'
     13                                            SKIP 1
     14                                            FIELDS TERMINATED BY ','
     15                                            MISSING FIELD VALUES ARE NULL
     16                                            REJECT ROWS WITH ALL NULL
     17                                            FIELDS(
     18                                                   "NAME",
     19                                                   "NO"
     20                                                  )
     21                                          )
     22                          LOCATION('export1.csv')
     23                         )
     24    PARALLEL 5
     25    REJECT LIMIT UNLIMITED
     26  /
    
    Table created.
    
    SQL> select  *
      2    from  ext_dir
      3  /
    
    NAME               NO
    ---------- ----------
    Joe                 2
    
    SQL>
    SY.
  • 5. Re: External Table
    Solomon Yakobson Guru
    Currently Being Moderated
    Actually I was wrong. According to External Table Restrictions:

    •When identifiers (for example, column or table names) are specified in the external table access parameters, certain values are considered to be reserved words by the access parameter parser. If a reserved word is used as an identifier, then it must be enclosed in double quotation marks.

    And it appears word NO is one of "certain values considered to be reserved words by the access parameter parser". See Reserved Words for the ORACLE_LOADER Access Driver. So all you need is to enclose no in double quotes:
    SQL> CREATE TABLE ext_dir(
      2                       "NAME" VARCHAR2(100),
      3                       "NO" NUMBER
      4                      )
      5    ORGANIZATION EXTERNAL(
      6                          TYPE ORACLE_LOADER
      7                          DEFAULT DIRECTORY temp
      8                          ACCESS PARAMETERS(
      9                                            RECORDS DELIMITED BY NEWLINE
     10                                            BADFILE 'name.bad'
     11                                            LOGFILE 'name.log'
     12                                            DISCARDFILE ' name.dsc'
     13                                            SKIP 1
     14                                            FIELDS TERMINATED BY ','
     15                                            MISSING FIELD VALUES ARE NULL
     16                                            REJECT ROWS WITH ALL NULL
     17                                            FIELDS(
     18                                                   name,
     19                                                   "NO"
     20                                                  )
     21                                          )
     22                          LOCATION('export1.csv')
     23                         )
     24    PARALLEL 5
     25    REJECT LIMIT UNLIMITED
     26  /
    
    Table created.
    
    SQL> select  *
      2    from  ext_dir
      3  /
    
    NAME               NO
    ---------- ----------
    Joe                 2
    
    SQL> 
    SY.
  • 6. Re: External Table
    930854 Newbie
    Currently Being Moderated
    sorry for the late reply, I got the answers now. Thanks to reply all
  • 7. Re: External Table
    930854 Newbie
    Currently Being Moderated
    thanks to reply all
  • 8. Re: External Table
    930854 Newbie
    Currently Being Moderated
    hi now i got another issues, Please help me out.

    Error:

    ORA-29913: error in executing ODCIEXTTABLEOPEN callout
    ORA-29400: data cartridge error
    KUP-04043: table column not found in external source: t_id
    29913. 00000 - "error in executing %s callout"
    *Cause:    The execution of the specified callout caused an error.
    *Action:   Examine the error messages take appropriate action.                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                           
  • 9. Re: External Table
    930854 Newbie
    Currently Being Moderated
    i got new issues, please help me
  • 10. Re: External Table
    Solomon Yakobson Guru
    Currently Being Moderated
    Post table definition and sample data file causing this error. But based on:
    KUP-04043: table column not found in external source: t_id
    Table declares column t_id while there is no field with sunch name in field section of external table declaration.

    SY.
  • 11. Re: External Table
    930854 Newbie
    Currently Being Moderated
    sorry for the late reply.. the same column only we were using..

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points