11 Replies Latest reply: Jan 7, 2013 11:18 PM by 930854 RSS

    External Table

    930854
      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
          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
            Can you post table script... i thinks column names should not be double quotes...
            • 3. Re: External Table
              BluShadow
              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
                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
                  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
                    sorry for the late reply, I got the answers now. Thanks to reply all
                    • 7. Re: External Table
                      930854
                      thanks to reply all
                      • 8. Re: External Table
                        930854
                        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
                          i got new issues, please help me
                          • 10. Re: External Table
                            Solomon Yakobson
                            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
                              sorry for the late reply.. the same column only we were using..