4 Replies Latest reply on Mar 4, 2014 10:12 PM by Barbara Boehmer

    Help Needed:OPTIONALLY ENCLOSED BY: SQL LOader:Oracle 10g

    2ee4e929-dd0a-4704-95ce-354e8b8327c1

      Dear Experts ,

       

      it is beneficial to support 'OPTIONALLY ENCLOSED BY' double quotes.I am using flat file in Microsoft Excel.When used, if there are multiple delimited null columns, the data is slid over and mapped to the incorrect column. The example below can be used to recreate the error condition. When the option 'OPTIONALLY ENCLOSED BY' is removed, the data is mapped correctly.

       

       

      CREATE TABLE AKS.AKS_TEST

      (

      AKSCOL1 VARCHAR2(255)

      ,AKSCOL2 VARCHAR2(255)

      ,AKSCOL3 VARCHAR2(255)

      ,AKSCOL4 VARCHAR2(255)

      ,AKSCOL5 VARCHAR2(255)

      ,AKSCOL6 VARCHAR2(255)

      ,AKSCOL7 VARCHAR2(255)

      ,AKSCOL8 VARCHAR2(255)

      ,AKSCOL9 VARCHAR2(255)

      ,AKSCOL10 VARCHAR2(255)

      )

      TABLESPACE Tools

       

       

      options (direct=false,bindsize=100000,readsize=100000,skip=1,errors=1000)

      LOAD DATA REPLACE

      INTO TABLE AKS.AKS_TEST

      FIELDS TERMINATED BY X'09'

      OPTIONALLY ENCLOSED BY '"'

      TRAILING NULLCOLS

      (

      AKSCOL1

      ,AKSCOL2

      ,AKSCOL3

      ,AKSCOL4

      ,AKSCOL5

      ,AKSCOL6

      ,AKSCOL7

      ,AKSCOL8

      ,AKSCOL9

      ,AKSCOL10

      )

       

      Please suggest me what I am doing wrong in the above.

      =================

       

      Sample Input in excel flat file

       

      MYCOL1 MYCOL2 MYCOL3 MYCOL4 MYCOL5 MYCOL6 MYCOL7 MYCOL8 MYCOL9 MYCOL10

      MYCOL1 MYCOL10

       

       

       

       

      Thankyou

      Bill

        • 1. Re: Help Needed:OPTIONALLY ENCLOSED BY: SQL LOader:Oracle 10g
          KarK

          Since you dont have " in your flat file(input data) , you no need to use  'OPTIONALLY ENCLOSED BY' in the control file.

           

          But it should work properly eventhough if you specify OPTIONALLY ENCLOSED BY clause.Better have a check on your data for any ".

          • 2. Re: Help Needed:OPTIONALLY ENCLOSED BY: SQL LOader:Oracle 10g
            CloudDB

            As suggested by KarK

            No need to use  'OPTIONALLY ENCLOSED BY' in the control file

            • 3. Re: Help Needed:OPTIONALLY ENCLOSED BY: SQL LOader:Oracle 10g
              Barbara Boehmer

              This is a bug that persists in 12c, as demonstrated below.

               

              SCOTT@orcl12c> select banner from v$version

                2  /

               

              BANNER

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

              Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

              PL/SQL Release 12.1.0.1.0 - Production

              CORE    12.1.0.1.0      Production

              TNS for 64-bit Windows: Version 12.1.0.1.0 - Production

              NLSRTL Version 12.1.0.1.0 - Production

               

              5 rows selected.

               

              SCOTT@orcl12c> host type test.dat

              MYCOL1  MYCOL2  MYCOL3

              MYCOL1          MYCOL3

               

              SCOTT@orcl12c> host type test1.ctl

              LOAD DATA REPLACE

              INTO TABLE TEST

              FIELDS TERMINATED BY X'09'

              optionally enclosed by '"'

              TRAILING NULLCOLS

              ( COL1

              , COL2

              , COL3

              )

               

              SCOTT@orcl12c> host type test2.ctl

              LOAD DATA REPLACE

              INTO TABLE TEST

              FIELDS TERMINATED BY X'09'

              TRAILING NULLCOLS

              ( COL1

              , COL2

              , COL3

              )

               

              SCOTT@orcl12c> create table test

                2    ( col1 varchar2(6)

                3    , col2 varchar2(6)

                4    , col3 varchar2(6))

                5  /

               

              Table created.

               

              SCOTT@orcl12c> host sqlldr scott/tiger control=test1.ctl data=test.dat log=test1.log

               

              SQL*Loader: Release 12.1.0.1.0 - Production on Tue Mar 4 13:43:51 2014

               

              Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

               

              Path used:      Conventional

              Commit point reached - logical record count 2

               

              Table TEST:

                2 Rows successfully loaded.

               

              Check the log file:

                test1.log

              for more information about the load.

               

              SCOTT@orcl12c> select * from test

                2  /

               

              COL1   COL2   COL3

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

              MYCOL1 MYCOL2 MYCOL3

              MYCOL1 MYCOL3

               

              2 rows selected.

               

              SCOTT@orcl12c> host sqlldr scott/tiger control=test2.ctl data=test.dat log=test2.log

               

              SQL*Loader: Release 12.1.0.1.0 - Production on Tue Mar 4 13:43:51 2014

               

              Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

               

              Path used:      Conventional

              Commit point reached - logical record count 2

               

              Table TEST:

                2 Rows successfully loaded.

               

              Check the log file:

                test2.log

              for more information about the load.

               

              SCOTT@orcl12c> select * from test

                2  /

               

              COL1   COL2   COL3

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

              MYCOL1 MYCOL2 MYCOL3

              MYCOL1        MYCOL3

               

              2 rows selected.

              • 4. Re: Help Needed:OPTIONALLY ENCLOSED BY: SQL LOader:Oracle 10g
                Barbara Boehmer

                Another alternative, instead of removing the optionally enclosed by '"' is to use another delimiter, like a comma, as shown below.

                 

                SCOTT@orcl12c> select banner from v$version

                  2  /

                 

                BANNER

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

                Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production

                PL/SQL Release 12.1.0.1.0 - Production

                CORE    12.1.0.1.0      Production

                TNS for 64-bit Windows: Version 12.1.0.1.0 - Production

                NLSRTL Version 12.1.0.1.0 - Production

                 

                5 rows selected.

                 

                SCOTT@orcl12c> host type test.dat

                MYCOL1,MYCOL2,MYCOL3

                MYCOL1,,MYCOL3

                 

                SCOTT@orcl12c> host type test1.ctl

                LOAD DATA REPLACE

                INTO TABLE TEST

                FIELDS TERMINATED BY ','

                optionally enclosed by '"'

                TRAILING NULLCOLS

                ( COL1

                , COL2

                , COL3

                )

                 

                SCOTT@orcl12c> create table test

                  2    ( col1 varchar2(6)

                  3    , col2 varchar2(6)

                  4    , col3 varchar2(6))

                  5  /

                 

                Table created.

                 

                SCOTT@orcl12c> host sqlldr scott/tiger control=test1.ctl data=test.dat log=test1.log

                 

                SQL*Loader: Release 12.1.0.1.0 - Production on Tue Mar 4 14:11:39 2014

                 

                Copyright (c) 1982, 2013, Oracle and/or its affiliates.  All rights reserved.

                 

                Path used:      Conventional

                Commit point reached - logical record count 2

                 

                Table TEST:

                  2 Rows successfully loaded.

                 

                Check the log file:

                  test1.log

                for more information about the load.

                 

                SCOTT@orcl12c> select * from test

                  2  /

                 

                COL1   COL2   COL3

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

                MYCOL1 MYCOL2 MYCOL3

                MYCOL1        MYCOL3

                 

                2 rows selected.