<\/span><\/h3>\nSetting default values in a SQL Loader control file is a straightforward process. By utilizing the “DEFAULTIF” clause, you can specify a default value for any specific column.<\/p>\n
To set a default value in a control file, follow the format below:<\/p>\n
“`
\ncolumn_name POSITION(start:end) DATATYPE “DEFAULTIF column_name = ‘ ‘ value”
\n“`<\/p>\n
Let’s understand this with an example. Suppose we have a control file named “example.ctl” and we want to load data into a table called “Employees”. The table has various columns such as “employee_id”, “first_name”, “last_name”, “email”, and “salary”. If the “salary” column is missing or contains blank values in the input file, we want to set a default value of 5000.<\/p>\n
Here’s how the control file would look:<\/p>\n
“`
\nOPTIONS (SKIP=1)
\nLOAD DATA
\nINFILE ‘example.dat’
\nAPPEND INTO TABLE Employees
\nFIELDS TERMINATED BY ‘,’ OPTIONALLY ENCLOSED BY ‘”‘
\nTRAILING NULLCOLS
\n(
\n employee_id INTEGER EXTERNAL,
\n first_name CHAR(50),
\n last_name CHAR(50),
\n email CHAR(50),
\n salary INTEGER “DEFAULTIF salary = ‘ ‘ 5000”
\n)
\n“`<\/p>\n
In the above example, the “DEFAULTIF” clause is used to check if the “salary” column is blank. If it is, the default value of 5000 is set. This feature ensures that even when data is missing or has blank values, a specific default value is assigned during the loading process.<\/p>\n