2 Replies Latest reply: Jul 3, 2013 1:09 PM by Barbara Boehmer RSS

    SQL Loader - Skip empty, blank & white-spaces rows

    RENGANATHANMG

      Hi All,

       

      I am loading data through MS-Excel using SQL Loader.

       

       

      Issue:

       

      If any blank rows or white-space rows are there, currently it is inserting as a blank entry.

       

       

      Control File

       

      OPTIONS (SKIP=1)

      load data

      into table temp_upload

      append

      fields terminated by "," optionally enclosed by '"'

      TRAILING NULLCOLS

      (

          SPNAME  "TRIM(:SPNAME)",

          USERGROUP "TRIM(:USERGROUP)",

          IS_BUSINESS_CRITICAL "TRIM(:IS_BUSINESS_CRITICAL)",

          BUSINESS_CRITICAL_COMMENT "TRIM(:BUSINESS_CRITICAL_COMMENT)",

          BATCH_ID expression "(select batch_id from batch_tab)",

          ID   "TEMP_UPLOAD_ID_SEQ.NEXTVAL"

      )

       

      Help:


      Any solution for this issue? . Please help me to fix this issue.

        • 1. Re: SQL Loader - Skip empty, blank & white-spaces rows
          DK2010

          Hi,

           

          I would suggest to use the External Table, since you are tagging 11g.

          • 2. Re: SQL Loader - Skip empty, blank & white-spaces rows
            Barbara Boehmer

            Ordinarily, SQL*Loader will by default reject all rows where all columns are null.  However, when you assign the batch_id and id, those columns are no longer null, so the row is loaded.  You can use either SQL*Loader or external table to load the data into a staging table without those id's, which will cause it to skip null rows, then insert into your target table, adding the id's, as demonstrated below.

             

            SCOTT@orcl_11gR2> host type test.dat

            some title or column names

            spname1,usergroup1,ibc1,bcc1,batch1,

             

            spname3,usergroup3,ibc3,bcc3,batch3,

             

            SCOTT@orcl_11gR2> host type test.ctl

            OPTIONS (SKIP=1)

            load data

            into table staging

            append

            fields terminated by "," optionally enclosed by '"'

            TRAILING NULLCOLS

            (

            SPNAME  "TRIM(:SPNAME)"

            ,   USERGROUP "TRIM(:USERGROUP)"

            ,   IS_BUSINESS_CRITICAL "TRIM(:IS_BUSINESS_CRITICAL)"

            ,   BUSINESS_CRITICAL_COMMENT "TRIM(:BUSINESS_CRITICAL_COMMENT)"

            )

             

            SCOTT@orcl_11gR2> create table staging

              2     (SPNAME                    varchar2( 7),

              3      USERGROUP                 varchar2(10),

              4      IS_BUSINESS_CRITICAL      varchar2( 4),

              5      BUSINESS_CRITICAL_COMMENT varchar2( 4))

              6  /

             

            Table created.

             

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

             

            SQL*Loader: Release 11.2.0.1.0 - Production on Wed Jul 3 11:08:04 2013

             

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

             

            Commit point reached - logical record count 3

             

            SCOTT@orcl_11gR2> select * from staging

              2  /

             

            SPNAME  USERGROUP  IS_B BUSI

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

            spname1 usergroup1 ibc1 bcc1

            spname3 usergroup3 ibc3 bcc3

             

            2 rows selected.

             

            SCOTT@orcl_11gR2> create table temp_upload

              2     (SPNAME                    varchar2( 7),

              3      USERGROUP                 varchar2(10),

              4      IS_BUSINESS_CRITICAL      varchar2( 4),

              5      BUSINESS_CRITICAL_COMMENT varchar2( 4),

              6      BATCH_ID                  number,

              7      ID                        number)

              8  /

             

            Table created.

             

            SCOTT@orcl_11gR2> create table batch_tab as select 10 batch_id from dual

              2  /

             

            Table created.

             

            SCOTT@orcl_11gR2> create sequence temp_upload_id_seq

              2  /

             

            Sequence created.

             

            SCOTT@orcl_11gR2> insert into temp_upload

              2  select spname, usergroup, is_business_critical, business_critical_comment,

              3         (select batch_id from batch_tab),

              4         temp_upload_id_seq.nextval

              5  from   staging

              6  /

             

            2 rows created.

             

            SCOTT@orcl_11gR2> select * from temp_upload

              2  /

             

            SPNAME  USERGROUP  IS_B BUSI   BATCH_ID         ID

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

            spname1 usergroup1 ibc1 bcc1         10          1

            spname3 usergroup3 ibc3 bcc3         10          2

             

            2 rows selected.