We used the Data Pump to read, write and send data from one database to another, but all the data that was dealt with was actually in Oracle proprietary format.

But what if we wanted to transfer data to an Oracle database and this data is not in Oracle proprietary format, here we cannot use the Data Pump but rather use the SQL*Loader

It loads data from external files to a database Data.

The above figure shows the components of SQL*Loader.

1- Input Data Files:

It is the file or files that contain the data that we want to transfer to the database via SQL*Loader, and the path of this file is specified in the Control File file that is configured by the database manager, which we will talk about later. It is also possible to include the data that is to be transferred to the database in the Control File file, and in this case we do not need For the Input File.

The data format in this file must be one of three:-

1- Fixed-record format:-

The data in this file must be in the form of rows of equal length, and this type increases performance during the loading process, but is less flexible.

Here is an example of this file:

Mohammed,ali

Ahmed,yousif

Mogahid,omer

Note that all rows in this Input File are of equal length and that the length of all rows in this file is 12 characters.

2- Variable-record format:

The data in this file is in the form of rows of unequal length, but the length of any row is determined at the beginning of the row, i.e. cells are allocated at the beginning of any row to determine the length of the row.

This type is more flexible than the previous type, but its performance is less than the first type.

Here is an example of this file:

014Mohammed,ahmed

009Ahmed,ali

010telal,omer

The first three fields have been reserved to specify the length of the row.

3- Stream-record format:-

The data in this file is in the form of rows of unequal length

The length of the rows is not specified at the beginning of the rows, which requires a complete scan of the rows before the loading process to determine the length of the rows,

Therefore, this type is more flexible than the previous two types, but of course it reduces performance.

The length of the rows is not specified at the beginning of the rows and the rows are not equal.

2- Control File:

It is a text file that is written and formatted by the database manager. This file is necessary during the process of uploading data to the database, as this file contains the location of the data that we want to upload by specifying the Input Data file or by containing the data to be uploaded. It also specifies how to upload the data and where to create it. Other files and other important information.

load data

infile 'fixed.dat' "fix 14"

insert into table names fields terminated by ','

(first,last)

This is a sample of a Control File where the Input File is specified and the data format is fixed-record

The length of the rows in the file to be uploaded is 14 characters and the separators between the fields are the characters ','.

Here is another sample of a Control File.

load data
infile 'names.dat' "var ٣"

insert into table names

fields terminated by ','

(first,last)

Note here that the format of the data being loaded has been specified in the form of Variable-record, and three fields have been specified to specify the length of the rows, and the separators between the fields are the characters ','.

Here is another example of a Control File.

load data

infile 'names.dat' "str '\n'"

insert into table names

fields terminated by ','

(first,last)

Here it is specified that the data format to be loaded is in the form of Stream-record

and that the rows end at the end of the row and the beginning of the new row and that the separator between the fields is the character ','.

3- Log File:

It is a file that is created during the loading process by SQL Loader, and if the process of creating this file fails, the loading process will stop.

This file contains information about the loading process and errors that occurred during the loading process.

4- Bad File:

This file contains records that were rejected because their format is not compatible with the table fields. During the loading process

and when adding data to the tables, there is some data that is not compatible with the fields, so it is rejected and stored in this file.

5- Discard File:

This file is created only when we want it, and it is specified in the Control File,

The task of this file is that it contains the records that were rejected because they do not comply with the upload conditions.

Loading method:

There are two methods for loading data using SQL*Loader:

1- Conventional Path Load:

In this method, a matrix of rows is built in memory and then added to the tables using SQL INSERT statement to load data into the database.

2- :Direct Path Load:

In this method, blocks of data are built in memory and then saved directly in the Extent that contains the table in the database.

Data Loading:

Now let's follow the following example that explains the process of loading a text file into an Oracle database using SQL*Loader.

Let's assume that we want to load the following text file named c:\name.txt, notice the file in the figure. Notice that the file contains six rows and that the length of the rows is not equal

and the length of the rows is not specified at the beginning; so the data format in this file is in the form of Stream-record, and we want to load this data into the table tname belonging to the user test.

Step one: We make sure that the table exists in the database and that it does not contain data.CONN TEST/TEST

DESC TNAME;

SELECT * FROM TNAME;

Step 2: We write the Control File.

Note that we used the INSERT command because the table does not contain data, but if the table contains data and we want to add new data to it, we use the APPEND command.

But if we want to delete the old data and add new data, we use the TRUNCATE command.

Step 3: We use the SQL*Loader to execute the Control File.

c:\sqlldr test/test control=c:\name.ctl log=name.log

Step 4: We run a query on the table to ensure that the data is loaded.

In general, all the options available to SQL*Loader can be reviewed using the command ?- sqlldr