: Useful for fixed-width files or fields that might contain spaces. column_name NULLIF (column_name = BLANKS) Use code with caution. Copied to clipboard 3. Using the DEFAULTIF Clause
By default, if a field in the data file is empty (contains no data between delimiters), SQL*Loader attempts to load it as a . However, if the column in the database has a NOT NULL constraint, the record will be rejected and moved to the .bad file. 2. Using the NULLIF Clause Handle Null Values In Sql Loader
A keyword used with NULLIF to identify fields containing only whitespace. : Useful for fixed-width files or fields that
The DEFAULTIF clause works similarly to NULLIF , but instead of forcing a NULL, it tells SQL*Loader to use the defined for that column. column_name DEFAULTIF (column_name = '0') Use code with caution. Copied to clipboard 4. Handling Trailing Nulls Using the DEFAULTIF Clause By default, if a
LOAD DATA INFILE 'data.csv' INTO TABLE employees FIELDS TERMINATED BY ',' TRAILING NULLCOLS ( emp_id, emp_name, commission_pct -- If this is missing in the file, it becomes NULL ) Use code with caution. Copied to clipboard 5. Using SQL Functions
You can also use Oracle SQL functions within the control file to handle null logic during the load process. This is helpful for replacing nulls with a specific value (like NVL ). column_name "NVL(:column_name, 'Unknown')" Use code with caution. Copied to clipboard Summary of Key Commands NULLIF Sets column to NULL if a specific condition is met. DEFAULTIF Uses the database's default value if a condition is met. TRAILING NULLCOLS Prevents errors when the last columns in a row are missing. BLANKS