Can variables be passed to a SQL*Loader control file via sqlldr command?

arul.k picture arul.k · Jan 22, 2014 · Viewed 18.6k times · Source

Below is my Control file example :

    OPTIONS (skip=1,errors=1000,direct=true,rows=10000)
    load data 
    append
    into table TABLE_NAME
    fields terminated by ','
    OPTIONALLY ENCLOSED BY '"'
    trailing nullcols(
      DATE_ID       DATE_ID_VALUE,
      DESC1         char(1000),
      DESC2         char(1000),
      DISP_URL      char(1000),
      DEST_URL      char(1000),
      ACCT_ID       ACCOUNTID_VALUE,
      Acct_num      ACCOUNT_NUM,
      created_date SYSDATE
    )

I need to pass DATE_ID_VALUE,ACCOUNTID, ACCOUNTNUM values from sqlldr command. Am passing the remaming columns data via csv file which inturn is passed from sqlldr "DATA" parameter. Is there a way to pass other required parameters via sqlldr command or any other way to do it?

Below is my sqlldr command :

   sqlldr userid=abc/abcdef@abcdefgh CONTROL= cont.ctl DATA= $csvFilePath  LOG=admaster.log BAD=admaster.bad

Answer

Gary_W picture Gary_W · Dec 16, 2014

I know this is old, but I just stumbled on it and I recently answered a similar question. Please see my reply here for a technique to create a control file from a wrapper program.

insert timestanp of INFILE into a column from SQLLOADER