1 Reply Latest reply: Nov 23, 2012 3:21 AM by AlbertoFaenza RSS

    Load Selected Columns w/o FILLER option using SQL loader

    revpree
      Hi all,

      From the previous forums i came to know about the "FILLER" option to skip any columns from loading into table.

      In my case,i have columns say: col1,col2,col3,col4.....coln

      Here i want to load only col3 and col4 and skip rest of the columns.I dont want to use FILLER option for each "n" number of columns to be skipped.Instead is there any options to load only the required 2 columns.

      Kindly help.

      Thanks!

      Rev
        • 1. Re: Load Selected Columns w/o FILLER option using SQL loader
          AlbertoFaenza
          Hi,

          Have you tried?
          Just specify the columns until col4 and you can avoid the rest of the columns.

          i.e.:
          CREATE TABLE test
          (
             num1   INTEGER
           , num2   INTEGER
          );
          my input data (40 columns):
          -- test.dat
          1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0
          1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0
          1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0
          1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0
          1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0
          1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0
          1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0
          1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0
          1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0
          1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0
          1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0
          1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0
          My control file:
          --test.ctl
          load data
          INFILE 'test.dat'
          APPEND
          INTO TABLE test
          FIELDS TERMINATED BY ','
          TRAILING NULLCOLS
          (
           filler1         FILLER,
           filler2         FILLER,
           num1            ,
           num2
          )
          As you can see I don't specify any column after the 4th column.

          Now loading records:
          sqlldr userid=scott/tiger control=test.ctl
          Now check in database:
          SELECT * FROM test;
          
                NUM1       NUM2
          ---------- ----------
                   3          4
                   3          4
                   3          4
                   3          4
                   3          4
                   3          4
                   3          4
                   3          4
                   3          4
                   3          4
                   3          4
                   3          4
          Regards.
          Al