SQL Loader Utility:

SQL Loader is a command-line utility that is used to load data from external files into tables of an Oracle database. It is a powerful tool that can load large amounts of data quickly and efficiently.

Syntax:

The basic syntax for using SQL Loader is:

sqlldr userid=<username>/<password>@<database> control=<control file> log=<log file>

Here:

userid specifies the username, password, and database to connect to.

control specifies the name of the control file that contains the instructions for the load process.

log specifies the name of the log file to store any errors or status messages that occur during the load process.

Example 1: Simple Load

Suppose we have a data file employee.csv containing the following data:

1001,John Smith,10000

1002,Jane Doe,12000

1003,Bob Johnson,15000

We want to load this data into a table employees in the Oracle database. Here's what our control file employee.ctl looks like:

LOAD DATA

INFILE 'employee.csv'

INTO TABLE employees

FIELDS TERMINATED BY ','

(

  emp_id,

  name,

  salary

)

The LOAD DATA keyword indicates that we are starting a new load process. The INFILE keyword specifies the name of the data file we want to load. The INTO TABLE keyword indicates the name of the table we want to load the data into. The FIELDS TERMINATED BY keyword specifies the delimiter used in the data file. Finally, we specify the columns we want to load, along with their corresponding data types.

To execute this load, we run the following command:

sqlldr userid=scott/tiger@orcl control=employee.ctl log=employee.log

This will load the data from the file employee.csv into the table employees in the Oracle database. Any errors or status messages will be recorded in the log file employee.log.

Example 2: Conditional Load

Suppose we have a data file sales.csv containing the following data:

1001,2021-01-01,5000

1002,2021-01-01,6000

1003,2021-01-01,7000

We want to load this data into a table sales in the Oracle database, but only for records where the sales amount is greater than $6000. Here's what our control file sales.ctl looks like:

LOAD DATA

INFILE 'sales.csv'

INTO TABLE sales

FIELDS TERMINATED BY ','

(

  sale_id,

  sale_date "TO_DATE(:sale_date,'YYYY-MM-DD')",

  sale_amount

)

WHERE sale_amount > 6000

In this control file, we've added a WHERE clause to specify that we only want to load records where the sale_amount is greater than 6000. We've also added a conversion function to convert the sale_date field from a string to a date.

To execute this load, we run the following command:

sqlldr userid=scott/tiger@orcl control=sales.ctl log=sales.log

This will load the data from the file sales.csv into the table sales in the Oracle database, but only for records where the sales amount is greater than $6000. Any errors or status messages will be recorded in the log file sales.log.