How to reject the data when particular column value is null in sql loader?

[Vinarashi]

Hi,

I am trying to load a data file into database. The data should be rejected if one particular column is null. As far as i googled, there is only one way, I should make that column "not null" in database. But already some null values are available. So I dont want to disturb that.

If I include "when" in the load command,the data is not getting rejected. It is getting skipped. I want the data to be saved in the bad file. Is there any other way to define that column should be "not null" in the control file?

Please guide me. Reply me asap.

Thanks,
Flora

[amitpatel66]

Data with NULL values in the column without NOT NULL constraint cannot be considered as BAD record.

An alternative would be that load the complete data in another temp table, or create an external table for the data file. And from the temp table, use DML to select only the records where column value IS NOT NULL and insert those in to destnation table.

Or

1. Create a temporary table and load all the data in the temp table.
2. Load the data with column = NOT NULL in your destination table
3. Compare two tables to extract the records that are not loaded and use the extract as required.