SQL LOADER with less fields in data file.

User_CS62G

Hello,

I have a scenario to implement where in I have 10 columns in a table which has to be loaded in APPEND mode, i.e. the table already has records and we have to insert new records into it.

In data file we are only getting 8 columns data as the first 2 columns which are NOT NULL will contain constant values as 'CONS' & SYSDATE for every record to be inserted. Data file contains data from 3 column.

Now the requirement is to generate the control file dynamically and using the control file generated and data file load the data using sql loader.

For example :

Data file :

ABC,123,DEF,456,01/10/2021,GHI,100,09/30/2021

Record Inserted in table should be like :

Please suggest how to generate the control file dynamically which will help in loading 'CONS' for 1st column & SYSDATE for 2nd column and rest of the columns should be loaded as per the data available in data file.

Thanks & Regards,

Biswajeet Ghosh

cormaco

There is no need to dynamically create the controlfile.

Use the CONSTANT parameter for the first column and the SYSDATE parameter for the second:

10.15.2.1 CONSTANT Parameter

10.15.5.1 SYSDATE Parameter

User_H3J7U

Utilities/SQL*Loader

Expression and trailing nullcols

Warren Tolentino

a database trigger would also accomplish the same task.

example:

create or replace trigger [trigger name]
before update or insert on [table name] for each row
begin
  if :new.col1 is null then
    :new.col1 := 'CONS';
  end if;
  if :new.col2 is null then
    :new.col2 := sysdate;
  end if;  
end;
/

or you can build a procedure that pass a parameter and call the UTL_FILE to read a row from a file and insert it into a table.