6 Replies Latest reply: Apr 17, 2014 6:28 AM by Srini Chavali-Oracle RSS

    loading null using sql loader

    Yoohoo

      hi there,

       

      i am trying to load data using sqlloader but some records are getting skip.  this is my data, ctl and table

       

      1,"0000"

      2,"0003"

      3,"0004"

      6,

       

       

      LOAD DATA

      REPLACE

      INTO TABLE test_multi_loader

      WHEN G_ID <> '0003'

      FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

      TRAILING NULLCOLS

      ( FIELD1,

        G_ID

      )

       

       

       

      CREATE TABLE test_multi_loader (

        field1  NUMBER(10,0) NOT NULL,

        g_id VARCHAR2(20) NULL

      )

       

      i am trying to load data where G_ID <> '0003'. when i try loading the data mentioned above, only 0000 and 0004 gets inserted.   0003 get skip due to when clause which is expected

      but the last row with null g_id also get skip and not loaded in the table.  is it because null is not equal to anything.  how can i load the last row using my control file above?

      i am using oracle 10g with latest patch. thanks

       

      current output

      ==========

      field1    g_id

      1          0000

      3          0004

       

      the below output is what i want

      ==========

      field1    g_id

      1          0000

      3          0004

      6         

        • 1. Re: loading null using sql loader
          rp0428
          is it because null is not equal to anything.

          Yes - the TRAILING NULLCOLS only applies to rows that will be loaded.

            how can i load the last row using my control file above?

          Huh? You've already proven that you can NOT use that control file.

           

          See the SQL*Plus section of the utilities doc

          http://docs.oracle.com/cd/B28359_01/server.111/b28319/ldr_field_list.htm#sthref1252

          Specifying Field Conditions

          A field condition is a statement about a field in a logical record that evaluates as true or false. It is used in the WHEN, NULLIF, and DEFAULTIF clauses.

          Note:

          If a field used in a clause evaluation has a NULL value, then that clause will always evaluate to FALSE. This feature is illustrated in Example 9-5.

          • 2. Re: loading null using sql loader
            user10857924

            may be like below? untested...

             

            G_ID <> '0003' OR G_ID is NULL

            • 3. Re: loading null using sql loader
              michaelrozar17

              Yoohoo wrote:

              ...  is it because null is not equal to anything.  ..

              Thats right. See below

               

              SQL> with t as (select 1 field,'0000'gid from dual union all

                2  select 2,'0003'from dual union all

                3  select 3,'0004'from dual union all

                4  select 6, null from dual

                5  )

                6  select * from t

                7      where gid<>'0003'

                8  /

               

                   FIELD GID

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

                       1 0000

                       3 0004

               

              SQL> with t as (select 1 field,'0000'gid from dual union all

                2  select 2,'0003'from dual union all

                3  select 3,'0004'from dual union all

                4  select 6, null from dual

                5  )

                6  select * from t

                7      where gid<>'0003'

                8          or gid is NULL

                9  /

               

                   FIELD GID

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

                       1 0000

                       3 0004

                       6

               

              SQL>

              • 4. Re: loading null using sql loader
                Manik

                If you are on 10g you may think of external tables as well.

                 

                Cheers,

                Manik.

                • 5. Re: loading null using sql loader
                  SKP

                  Instead of use the when clause in sql loader

                  You can use the check constraint in your table.

                  There you  can get your expected result.

                   

                  CREATE TABLE test_multi_loader1 (

                    field1  NUMBER(10,0) NOT NULL,

                    g_id VARCHAR2(20) check (g_id <>'0003')

                  );

                   

                  LOAD DATA

                  infile *

                  REPLACE

                  INTO TABLE test_multi_loader1

                  FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'

                  TRAILING NULLCOLS

                  ( FIELD1,

                    G_ID

                  )

                  begindata

                  1,"0000"

                  2,"0003"

                  3,"0004"

                  6,

                    OUTPUT

                  1    0000

                  3    0004

                  6    null

                  • 6. Re: loading null using sql loader
                    Srini Chavali-Oracle

                    Moved to the SQL*Loader forum - pl post future questions in appropriate forum