7 Replies Latest reply: Jul 15, 2014 1:12 PM by 969952 RSS

    External tables issue

    969952

      Hi All ,

       

      I have a .txt file. I have created an external table. I have written External table script to load .TXT file data into the External table. Table got created successfully. But the proper data was not loaded into the External tables. Can any one please share your thoughts what might be the reasin.

      Oracle Version : 11.2.03

       

      Here is the create table statement.

      {code}

      CREATE TABLE SCOTT.EMP_DESC_EXT

      (

        EMP_TYPE       VARCHAR2(1 BYTE),

        EMP_CODE         VARCHAR2(6 BYTE),

        EMP_DESC         VARCHAR2(60 BYTE),

        EMP_CLASS        VARCHAR2(40 BYTE),

        EMP_DIVISION     VARCHAR2(40 BYTE),

        EMP_GROUP        VARCHAR2(40 BYTE),

        EMP_FLAG        VARCHAR2(1 BYTE),

        EMP_DOJ_DT   DATE,

        EMP_LAST_DT  DATE,

        FILLER            VARCHAR2(796 BYTE)

      )

      ORGANIZATION EXTERNAL

        (  TYPE ORACLE_LOADER

           DEFAULT DIRECTORY   EMP_DIR

           ACCESS PARAMETERS

             ( RECORDS DELIMITED BY NEWLINE SKIP 1

               NOLOGFILE

               NODISCARDFILE

               BADFILE  CRTS_LOGFILE:'EMP_DESC.bad'

          FIELDS

          (

              EMP_TYPE          POSITION (1 : 1) CHAR,

              EMP_CODE            POSITION (2 : 7) CHAR,

              EMP_DESC            POSITION (8: 67) CHAR,

              EMP_CLASS           POSITION (68 : 107) CHAR,

              EMP_DIVISION        POSITION (108 : 147) CHAR,

              EMP_GROUP           POSITION (148 : 187) CHAR,

              EMP_FLAG           POSITION (188 : 188) CHAR,

              EMP_DOJ_DT     POSITION (189 : 196) CHAR,

              EMP_LAST_DT  DATE POSITION (197 : 204) CHAR,

              FILLER               POSITION (205 : 1000) CHAR

              )

                     )

           LOCATION (EMP_DIR:'EMPS_DESC.TXT')

        )

      REJECT LIMIT UNLIMITED

      NOPARALLEL

      NOMONITORING;

      {code}

       

      Please have a look and suggest me why .TXT data is loading into EXT table. But the table is getting created successfully.

       

      Thanks

        • 1. Re: External tables issue
          EdStevens

          text files are not "loaded into" external tables.  An "external table" is simply a mechanism to describe a text file and be able to treat it as if it were a table by using normal SQL statements to access it.  So I have no clue as to what you mean by "data is (not) loading into EXT table.  What error do you get when you try to SELECT * FROM SCOTT.EMP_TEST_EXT

          • 2. Re: External tables issue
            969952

            getting the belwo error.. have gone thorugh the google with many posts but could fidn the solution. Table got created successfully. but getting error while executing SELECT statement.

             

            Here the External table create statement.

             

            {code}

            {code}

            CREATE TABLE SCOTT.EMP_DESC_EXT

            (

              EMP_TYPE       VARCHAR2(1 BYTE),

              EMP_CODE         VARCHAR2(6 BYTE),

              EMP_DESC         VARCHAR2(60 BYTE),

              EMP_CLASS        VARCHAR2(40 BYTE),

              EMP_DIVISION     VARCHAR2(40 BYTE),

              EMP_GROUP        VARCHAR2(40 BYTE),

              EMP_FLAG        VARCHAR2(1 BYTE),

              EMP_DOJ_DT   DATE,

              EMP_LAST_DT  DATE,

              FILLER            VARCHAR2(796 BYTE)

            )

            ORGANIZATION EXTERNAL

              (  TYPE ORACLE_LOADER

                 DEFAULT DIRECTORY   EMP_DIR

                 ACCESS PARAMETERS

                   ( RECORDS DELIMITED BY NEWLINE SKIP 1

                     NOLOGFILE

                     NODISCARDFILE

                     BADFILE  CRTS_LOGFILE:'EMP_DESC.bad'

                FIELDS

                (

                    EMP_TYPE          POSITION (1 : 1) CHAR,

                    EMP_CODE            POSITION (2 : 7) CHAR,

                    EMP_DESC            POSITION (8: 67) CHAR,

                    EMP_CLASS           POSITION (68 : 107) CHAR,

                    EMP_DIVISION        POSITION (108 : 147) CHAR,

                    EMP_GROUP           POSITION (148 : 187) CHAR,

                    EMP_FLAG           POSITION (188 : 188) CHAR,

                    EMP_DOJ_DT     POSITION (189 : 196) CHAR,

                    EMP_LAST_DT  DATE POSITION (197 : 204) CHAR,

                    FILLER               POSITION (205 : 1000) CHAR

                    )

                           )

                 LOCATION (EMP_DIR:'EMPS_DESC.TXT')

              )

            REJECT LIMIT UNLIMITED

            NOPARALLEL

            NOMONITORING;

            {code}

            Error :

            [Error] Execution (1: 15): 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 "oid": expecting one of: "double-quoted-string, identifier, single-quoted-string"

            KUP-01007: at line 8 column 10

             

            Have a look and advise.

            • 3. Re: External tables issue
              Richard Harrison .

              Hi,

              I think the problem might be due to a reserved word usage somewhere - i can;t believe this is thrown because of what is in the datafile.

               

              At first look i wonder if the problem is you called one of the external table columns 'FILLER' in sqlldr at least that has a special meaning - do you actually need that data - can you just rename the column if you do and try again?

               

              Cheers,

              Rich

              • 4. Re: External tables issue
                Barbara Boehmer

                Surprisingly, user filler as a field and column name doesn't seem to cause a problem, but what is missing are date maks.  Please see the example below.  Note that the original poster's date formats may not be the same.  Also, there may be other errors due to data that might be found in a log file, if a log file was used.

                 

                 

                SCOTT@orcl> SELECT banner FROM v$version

                  2  /

                 

                BANNER

                --------------------------------------------------------------------------------

                Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production

                PL/SQL Release 11.2.0.1.0 - Production

                CORE    11.2.0.1.0      Production

                TNS for 64-bit Windows: Version 11.2.0.1.0 - Production

                NLSRTL Version 11.2.0.1.0 - Production

                 

                5 rows selected.

                 

                SCOTT@orcl> HOST TYPE c:\my_oracle_files\emps_desc.txt

                LINE TO SKIP

                ABbbbbBCccccccccccccccccccccccccccccccccccccccccccccccccccccccccccCDddddddddddddddddddddddddddddddddddddddDEeeeeeeeeeeeeeeeee

                eeeeeeeeeeeeeeeeeeeeeEFffffffffffffffffffffffffffffffffffffffFG2014071420140714J

                 

                SCOTT@orcl> CREATE OR REPLACE DIRECTORY emp_dir AS 'c:\my_oracle_files'

                  2  /

                 

                Directory created.

                 

                SCOTT@orcl> CREATE OR REPLACE DIRECTORY crts_logfile AS 'c:\my_oracle_files'

                  2  /

                 

                Directory created.

                 

                SCOTT@orcl> CREATE TABLE SCOTT.EMP_DESC_EXT

                  2    (EMP_TYPE         VARCHAR2(  1 BYTE),

                  3     EMP_CODE         VARCHAR2(  6 BYTE),

                  4     EMP_DESC         VARCHAR2( 60 BYTE),

                  5     EMP_CLASS        VARCHAR2( 40 BYTE),

                  6     EMP_DIVISION     VARCHAR2( 40 BYTE),

                  7     EMP_GROUP        VARCHAR2( 40 BYTE),

                  8     EMP_FLAG         VARCHAR2(  1 BYTE),

                  9     EMP_DOJ_DT       DATE,

                10     EMP_LAST_DT      DATE,

                11     FILLER           VARCHAR2(796 BYTE))

                12  ORGANIZATION EXTERNAL

                13    (TYPE ORACLE_LOADER

                14     DEFAULT DIRECTORY EMP_DIR

                15     ACCESS PARAMETERS

                16       (RECORDS DELIMITED BY NEWLINE SKIP 1

                17        NOLOGFILE

                18        NODISCARDFILE

                19        BADFILE  CRTS_LOGFILE:'EMP_DESC.bad'

                20        FIELDS

                21          (EMP_TYPE            POSITION (  1 :    1) CHAR,

                22           EMP_CODE            POSITION (  2 :    7) CHAR,

                23           EMP_DESC            POSITION (  8:    67) CHAR,

                24           EMP_CLASS           POSITION ( 68 :  107) CHAR,

                25           EMP_DIVISION        POSITION (108 :  147) CHAR,

                26           EMP_GROUP           POSITION (148 :  187) CHAR,

                27           EMP_FLAG            POSITION (188 :  188) CHAR,

                28           EMP_DOJ_DT          POSITION (189 :  196) DATE MASK 'YYYYMMDD',

                29           EMP_LAST_DT         POSITION (197 :  204) DATE MASK 'YYYYMMDD',

                30           FILLER              POSITION (205 : 1000) CHAR))

                31       LOCATION (EMP_DIR:'EMPS_DESC.TXT')

                32    )

                33  REJECT LIMIT UNLIMITED

                34  NOPARALLEL

                35  NOMONITORING

                36  /

                 

                Table created.

                 

                SCOTT@orcl> SELECT * FROM emp_desc_ext

                  2  /

                 

                E EMP_CO EMP_DESC

                - ------ ------------------------------------------------------------

                EMP_CLASS

                ----------------------------------------

                EMP_DIVISION

                ----------------------------------------

                EMP_GROUP                                E EMP_DOJ_DT      EMP_LAST_DT

                ---------------------------------------- - --------------- ---------------

                FILLER

                --------------------------------------------------------------------------------

                A BbbbbB CccccccccccccccccccccccccccccccccccccccccccccccccccccccccccC

                DddddddddddddddddddddddddddddddddddddddD

                EeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeeE

                FffffffffffffffffffffffffffffffffffffffF G Mon 14-Jul-2014 Mon 14-Jul-2014

                J

                 

                 

                1 row selected.

                • 5. Re: External tables issue
                  969952

                  Please find the below error:

                   

                  [Error] Execution (3: 15): 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 "EMP_CODE": expecting one of: "double-quoted-string, identifier, single-quoted-string"

                  KUP-01007: at line 8 column 10

                   

                  Please havea a look and advise.

                   

                  Thanks.

                  • 6. Re: External tables issue
                    Barbara Boehmer

                    You need to post a copy and paste of a run of the code that produced that error, so that we can see what is wrong.  It is difficult to guess from just an error message, what mistake you made.  In general, the error message is self-explanatory:  Someplace where you have "EMP_CODE" in your create table statement, it was expecting something else.  This could be because there is something wrong on that line or the line before or after.  For example, if you forget something like a comma at the end of the previous line, it can throw everything off.

                    • 7. Re: External tables issue
                      969952

                      Hi,

                       

                      It got resolved.. Thank you so much for your support and response.

                       

                      Thanks