0 Replies Latest reply: Apr 14, 2007 12:52 PM by Prathap S RSS

    sql*loader - loading based on file name

    Prathap S
      Hi,

      I am facing an issue with using sql*loader. I want to load two data files into one table. While loading the data I have to set a column value based on which file the loader uses. I will have to pass the data file name thru command line parameter.
      Can anyone give the code to do this?

      Data File names:
      sproc01.dat
      mproc01.dat
      sproc02.dat
      mproc02.dat, etc.

      One control file:
      load_data.ctl

      The control file should look like....
      load data
      append
      into a
      when substr(<file_name>,1,5) = 'mproc'
      (
      data1 position(01:20),
      data2 constant 'M'
      )
      into a
      when substr(<file_name>,1,5) = 'sproc'
      (
      data1 position(01:20),
      data2 constant 'S'
      )

      my doubt is, how to get the <file_name> in the when clause.