Oracle SQLLoader

SQLLoader is a tool in the Oracle database that allows you to load the data from external files into a database. This tool lets you load the data from multiple formats including the delimited (comma, tab, and pipe delimited) text files, fixed-width, and binary files. SQLLoader is often used to load the large amounts of data into a database, as it is much faster than the traditional INSERT statements.

SQL*Loader has several advantages over other methods of loading the data into a database. It is fast, efficient, and can handle large amounts of data. It also can load the data from multiple data files and selectively load the specific rows of data based on specified criteria.

This tutorial aims to understand how we can use the SQLLoader tool in Oracle databases to load the data from external files.

To use the SQLLoader, you first need to create a control file that specifies the details of the data that you want to load such as the data files’ location, the data format, and the columns and tables into which the data should be loaded. Once the control file is created, you can use the sqlldr command to run the SQLLoader and load the data into the database.

Oracle SQLLDR Command

The SQLLDR command allows you to invoke the SQLLoader tool and load a specified data. The syntax for the command is as shown:

Where control_file is the path and filename of the control file that specifies the details of the data that you want to load.

Some accepted options to use with the sqlldr command to customize how the data is loaded include the following:

1. Userid – It specifies the username and password when connecting to the database. This option is typically used in the userid=username/password form.
2. Log – It specifies the path and filename of the log file where SQL*Loader writes an information about the data load.
3. Bad – It specifies the path and filename of the bad file where SQL*Loader writes the records that could not be loaded.
4. Skip – It specifies the number of records to skip before loading the data. This can be useful if you want to skip a header row in a data file.

NOTE: You need both the control file and the data file. As the name suggests, the control file, also known as the parameter file, is used to define the load parameters that are used by the SQLLoader tool while the data file contains the data to be loaded into the database.

How to Use the SQLLoader Tool

Let us demonstrate how we can use the SQLLoader tool to load a sample CSV file into a database.

The file contains the following information:

Let us start by defining the control file with the contents as shown in the following:

The provided code specifies the control file for the SQL*Loader tool to load the data from a CSV file into an Oracle database table.

In the control file, you will find an information such as:

1. The LOAD DATA statement which indicates that the SQL*Loader tool loads the data from a data file into the database.
2. The INFILE clause which defines the path to the data file. In our case, this is a CSV file called user_info.dat
3. The INTO TABLE clause which sets the name of the target table in the database. In our case, it is the users table.
4. Next, the FIELDS TERMINATED BY clause which specifies that the data values in the CSV file are separated by a comma (,).
5. We then set the list of columns in the users table and the corresponding data types.
6. You can also use the SET clause to define that the id column is set to the next value in the user_sequence sequence. This automatically generates a unique primary key value for each record loaded into the user table.

NOTE: You may need to define the user_sequence as shown in the following example:

This statement creates a sequence called user_sequence that starts at one and increments by 1 for each new value that is added to the table.

Once we have the data and control files ready, we need to define the parameter file that governs how the SQLLoader tool handles the load process.

The parameters are explained as follows:

1. userid – The userid parameter specifies the username and password to use when connecting to the Oracle database. In this case, we need to connect as the HR user with the specified password.
2. control – This parameter defines the name and location of the control file for the SQL*Loader tool. In our example, we use the control_file.ctl from the current working directory.
3. log – The log parameter sets the log file name that the SQL*Loader tool generates to keep track of the load operation logs.
4. bad – In this case, the bad parameter represents the name of the bad file that the SQL*Loader tool generates to store any records that could not be loaded due to errors.
5. data – This parameter sets the filename to the data file to be loaded into the database.
6. direct – Finally, the direct parameter specifies whether the SQL*Loader tool should use the direct path load mode. The direct mode bypasses some built-in database checks such as integrity and constraint checks. This can dramatically increase the loading speed, especially on a large dataset.

The next step is to create the users’ table in which we load the target data.

Once all the files are ready, we can open the terminal and invoke the sqlldr command as shown in the following example:

NOTE: You can name the data, control, and param file with any acceptable filename. Make sure to reference the correct filename in the commands.

This should load the data to the users table and print the following output:

To verify if the data has been loaded into the database, we can select all values from the table as follows:

Result:

Conclusion

In this tutorial, you encountered the SQLLoader tool, a command-line utility in the Oracle database that allows you to load the data from the external files into an Oracle database. You discovered the various methods and techniques, including defining a control file and a parameter file, and loading the data into the database. Although the SQLLoader tool can initially seem intimidating, we hope this tutorial helped you become more confident in using the tool.

Hence, using the SQL*Loader tool can be beneficial for quick and efficient loading of large amounts of data into an Oracle database.