Oracle 10g/11g Data and Database Management Utilities: Master 12 must-use Oracle Database Utilities with this Oracle book and eBook

Sometimes, an external source provides data in an unwanted format. As database users, we can only deal with whatever way the data has been formatted, and do our best to load it. Sometimes an interface has to be built specifically to perform a complex format load. The purpose of SQL*Loader is not only to provide a plain format data loader tool, but also a means to allow a complex data set to be loaded. The user can leverage the power of SQL*Loader by:

SQL*Loader's architecture is both simple and elegant. It requires at least one or two input files to start processing...

We will require a demo user to perform the loads, a regular tablespace, and some basic privileges for the SQL*Loader demo user. The paths and other particular references are included as mere examples, and to meet the syntax requirements. Actual implementations may differ. During the development of these demonstrations, data from either the HR or SCOTT schemas was used. The next examples use the SYS user to perform administrative tasks, it is only for demonstration purposes, any user with privileges to create a tablespace is enough to setup the environment, and any user who has permission to insert data into a given table is enough to perform the data loading procedure. The paths used and the Oracle SID referred here are just based on a specific demo database used.

connect / as sysdba
create tablespace sqlldrdemo
datafile '/u01/oracle/oradata/beta/sqlldr01.dbf' size 32m
autoextend on next 16m;
create user sqlldrdemo
identified by oracle
default tablespace sqlldrdemo...

A commonly seen requirement is loading data in a character set different from the WE8ISO family. When performing a load with a particular character set it should be specified with the NLS_LANG environment variable. In case it is not specified this way, it must be declared at the control file with the CHARACTERSET parameter. The character set will automatically be converted to the specified character set as long as the target database supports the conversion, otherwise a question mark will be loaded instead, indicating that the target database either didn't understand the conversion or the character set at the target database is not a superset of the source character set data.

In this example a load takes place using different character sets on this table, the purpose of the table is to store a multilingual error catalog:

CREATE TABLE ERRORCATALOG
(
LANGUAGE VARCHAR2(3),
ERROR_NUMBER NUMBER(6),
ERROR_STRING NVARCHAR2(1000)
);

The first datafile to store...

SQL*Loader has a powerful interface that allows loads from different sources; it can read data from disk, tape or a named pipe. This feature allows loads on the fly without the creation of an intermediate data file, which is a very convenient strategy for loads that will only take place once and whose data source is dynamic. Let's have a named pipe created, so there is no datafile in-between, and data is directly consumed by the database through SQL*Loader.

On the OS prompt, let's create a named pipe. A process will then send data to the named pipe and SQL*Loader will read it from there.

At a Unix prompt, let's create a regular named pipe:

mkfifo abcpipe.dat

Send data to the named pipe and leave the process in the background:

cat abc.dat > abcpipe.dat &

Load data from the named pipe:

sqlldr sqlldrdemo/oracle...

If SQL*Loader is properly configured the load can be sped up in a meaningful way by means of the direct path load. Direct path was a new feature introduced in Oracle 7.3, and it hasn't changed too much since then. Direct path is an Oracle feature that allows an insertion process to go directly to the database files without using the database transactional mechanism. It allows the data load process to be performed in the fastest possible way. It must be noted that there is a price to pay in the transaction and recoverability models for the increased processing speed.

When a conventional path load takes place, SQL*Loader fills a bind array buffer and passes it to the Oracle database so it is processed by means of regular SQL INSERT commands—SQL*Loader performs a batch insert assembling 64 rows (by default). Afterwards a commit command is issued. This approach makes the database buffer to allocate resources to perform the insert, and generates a transaction...

There are several ways to load multimedia files. This kind of data is provided in a raw format, so the most suitable data type to store this information in is: the Binary Large Object (BLOB). Both the LONG and LONG RAW are not considered in this discussion as it is not good practice to preserve these kinds of columns. The long data type is considered deprecated for the new features; it has been a constant throughout all the new releases, starting with 8.0. Most of the new features are supported for LOB data types, but not for LONG data types.

A LOB is a data type that stores a Large Object, and it can be of BLOB, Character Large Object(CLOB) , National Character Large Object(NCLOB) or BFILE specific data type. It is useful to employ large objects to store multimedia files (binary LOBs), large amounts of text such as descriptions, commentaries, or the particular case of XMLType columns. Text can also be specified in national character sets. There are two kinds...

This is another case of LOB loading, in this case the BLOB data type is used to store binary data. When loading records from the same data file, there is an overhead involved to find out the record length. Loading from a secondary data file is more suitable for loading LOB data. When loading LOB data this way there is no requirement that the LOB field fits in memory, the load takes place by reading from the LOB file in 64K chunks.

When loading data from a LOB file, there are two ways to specify the LOB file; it can be specified either statically or dynamically. In the first case, the file is specified in the same control file. In the case of a dynamically defined LOB file, the file is specified within the data file and it is read into a FILLER field (from the datafile) which is then used as a parameter in the control file to specify where the LOB file is.

A FILLER field acts as a place holder; it is not read as actual data, its position is just considered in the data...

When a lengthy load takes place, the last thing a user wants to see is an ORA- error or any other error displayed on the console. This is an emerging issue which usually has to do with a lack of resources to finish the job. There should be enough free space not only for the data to be loaded, but also for the related indexes as well. As SQL*Loader has a default number of rows to commit in a batch load, there is no risk of exhausting the undo regions during a conventional data load.

While performing a lengthy load with a constrained time frame it is better (for peace of mind) to reduce the possibility of any unforeseen circumstances arising that may prevent the successful completion of the task. That is why storage levels must be evaluated to ensure there is enough free space to hold the massive load, otherwise it will have to be restarted by means of a recalculation of the SKIP value to jump to the point of failure, and continue the load from that point on.

Another more practical...

A parallel load can be used to perform the data load more efficiently. It is a suitable data load strategy for partitioned tables. When performing the load the data file is partitioned so that each single session grabs its piece of data and all the sessions can execute the job simultaneously on the same table.

In order for the user to enable parallel loading, once the data has been split into several files, the clause PARALLEL=TRUE must be specified for each session. If the user is working on a Unix like operating system, then the workload can be left running as a background process.

sqlldr sqlldrdemo/oracle control=pload01.ctl DIRECT=TRUE PARALLEL=TRUE &
sqlldr sqlldrdemo/oracle control=pload02.ctl DIRECT=TRUE PARALLEL=TRUE &
sqlldr sqlldrdemo/oracle control=pload03.ctl DIRECT=TRUE PARALLEL=TRUE &
sqlldr sqlldrdemo/oracle control=pload04.ctl DIRECT=TRUE PARALLEL=TRUE &

In this example, four processes execute the data load in parallel. The degree of parallelism...

In order to take advantage of maintenance windows to perform the data load, here is some advice for improving load performance and better using the time frame.

Loading ASCII data from an external source is a frequent task in data warehouse environments when migrating data from other non-compatible databases. The kind of information and the way it may be formatted are important to consider it is the time to define the control file to execute the load.

When the load takes place it is sensible to take advantage of the time frame, avoiding unexpected issues that may prevent the process from finishing on time, particularly those loads that must be executed on a just-in-time basis. Proactively defining a resumable load will help the user to avoid problems and automatically correct unforeseen issues.

A situation may arise where you would need to load different character sets on the same database, and the user should be aware of the implications and best practices in this situation.

Several kinds of loads can be performed, from just plain and simple fixed record rows to complex formats. Loading a variety of data types, from plain text files to binaries...