Certain utilities are recommended for bulk loading data into LOB columns as part of the database set up or maintenance tasks.
The following utilities are recommended for bulk loading data into LOB columns as part of database setup or maintenance tasks:
- External Tables
- Oracle Data Pump
13.3.1 Loading LOBs with SQL*Loader
Learn about conventional and direct-path loads, when Oracle recommends that you use direct-path loads, and what rules and guidelines you should follow to avoid issues.
There are two options for loading large object (LOB) data:
A conventional path load executes SQL
INSERT statements to populate tables in an Oracle Database.
A direct-path load eliminates much of the Oracle Database overhead by formatting Oracle data blocks, and writing the data blocks directly to the database files. Additionally, a direct-path load does not compete with other users for database resources, so it can usually load data at near disk speed. Be aware that there are also other restrictions, security, and backup implications for direct path loads, which you should review.
For each of these options of loading large object data (LOBs), you can use the following techniques to load data into LOBs:
Loading LOB data from primary data files.
When you load data from a primary data file, the data for the LOB column is part of the record in the file that you are loading.
Loading LOB data from a secondary data file using LOB files.
When you load data from a secondary data file, the data for a LOB column is in a different file from the primary data file. Instead of the data itself, the primary data file contains information about the location of the content of the LOB data in other files.
Recommendations for Using SQL*Loader to Load LOBs
Oracle recommends that you keep the following guidelines and rules in mind when loading LOBs using SQL*Loader:
Tables that you want to load must already exist in the database. SQL*Loader never creates tables. It loads existing tables that either contain data, or are empty.
When you load data from LOB files, specify the maximum length of the field corresponding to a LOB-type column. If the maximum length is specified, then SQL*Loader uses this length as a hint to help optimize memory usage. You should ensure that the maximum length you specify does not underestimate the true maximum length.
If you use conventional path loads, then be aware that failure to load a particular LOB does not result in the rejection of the record containing that LOB; instead, the record ends up containing an empty LOB.
If you use direct-path loads, then be aware that loading LOBs can take up substantial memory. If the message
SQL*Loader 700 (out of memory)appears when loading LOBs, then internal code is probably batching up more rows in each load call than can be supported by your operating system and process memory. One way to work around this problem is to use the
ROWSoption to read a smaller number of rows in each data save.
Only use direct path loads to load XML documents that are known to be valid into XMLtype columns that are stored as CLOBS. Direct path load does not validate the format of XML documents as the are loaded as CLOBs.
With direct-path loads, errors can be critical. In direct-path loads, the LOB could be empty or truncated. LOBs are sent in pieces to the server for loading. If there is an error, then the LOB piece with the error is discarded and the rest of that LOB is not loaded. As a result, if the entire LOB with the error is contained in the first piece, then that LOB column is either empty or truncated.
You can also use the Direct Path API to load LOBs.
Privileges Required for Using SQL*Loader to Load LOBs
The following privileges are required for using SQL*Loader to load LOBs:
You must have
INSERTprivileges on the table that you want to load.
You must have
DELETEprivileges on the table that you want to load, if you want to use the
TRUNCATEoption to empty out the old data before loading the new data in its place.
Example 13-1 Loading LOB from a primary data file using Delimited Fields
Review this example to see how to load LOB data in delimited fields. Note the callouts "1" and "2" in bold:
Control File Contents
LOAD DATA INFILE 'sample.dat' "str '|'" INTO TABLE person_table FIELDS TERMINATED BY ',' (name CHAR(25), 1 "RESUME" CHAR(507) ENCLOSED BY '<startlob>' AND '<endlob>')
Data File (
Julia Nayer,<startlob> Julia Nayer 500 Example Parkway firstname.lastname@example.org ... <endlob> 2 |Bruce Ernst, .......
The callouts, in bold, to the left of the example correspond to the following notes:
<endlob>are the enclosure strings. With the default byte-length semantics, the maximum length for a LOB that can be read using
CHAR(507)is 507 bytes. If character-length semantics were used, then the maximum would be 507 characters. For more information, refer to character-length semantics.
If the record separator
'|'had been placed right after
<endlob>and followed with the newline character, then the newline would have been interpreted as part of the next record. An alternative would be to make the newline part of the record separator (for example,
'|\n'or, in hexadecimal notation,
Example 13-2 Loading a LOB from secondary data file, using Delimited Fields:
In this example, note the callout "1" in bold:
Control File Contents
LOAD DATA INFILE 'sample.dat' INTO TABLE person_table FIELDS TERMINATED BY ',' (name CHAR(20), 1 "RESUME" LOBFILE( CONSTANT 'jqresume') CHAR(2000) TERMINATED BY "<endlob>\n")
Data File (sample.dat)
Johny Quest, Speed Racer,
Secondary Data File (jqresume.txt)
Johny Quest 500 Oracle Parkway ... <endlob> Speed Racer 400 Oracle Parkway ... <endlob>
The callout, in bold, to the left of the example corresponds to the following note:
Because a maximum length of 2000 is specified for
CHAR, SQL*Loader knows what to expect as the maximum length of the field, which can result in memory usage optimization. If you choose to specify a maximum length, then you should be sure not to underestimate its value. The
TERMINATED BYclause specifies the string that terminates the LOBs. Alternatively, you can use the
ENCLOSED BYclause. The
ENCLOSED BYclause allows a bit more flexibility with the relative positioning of the LOBs in the
LOBFILE, because the LOBs in the
LOBFILEdo not need to be sequential.
13.3.2 Loading BFILEs with SQL*Loader
This section describes how to load data from files in the file system into a
BFILE column using SQL*Loader.
BFILEdata type stores unstructured binary data in operating system files outside the database. A
BFILEcolumn or attribute stores a file locator that points to a server-side external file containing the data.
- A particular file to be loaded as a
BFILEdoes not have to actually exist at the time of loading. SQL*Loader assumes that the necessary
DIRECTORYobjects have been created.
See Also:DIRECTORY Objects for more information
A control file field corresponding to a
BFILE column consists of the column name followed by the
BFILE directive takes as arguments a
DIRECTORY object name followed by a
BFILE name. Both of these can be provided as string constants, or they can be dynamically sourced through some other field.
See Also:Oracle Database Utilities for details on SQL*Loader syntax
The following two examples illustrate the loading of
You need to set up the following data structures for certain examples to work:
CONNECT pm/pm CREATE OR REPLACE DIRECTORY adgraphic_photo as '/tmp'; CREATE OR REPLACE DIRECTORY adgraphic_dir as '/tmp';
In the following example, only the file name is specified dynamically. The directory name,
adgraphic_photo, is in quotation marks. Therefore, the string is used as is, and is not capitalized.
LOAD DATA INFILE sample9.dat INTO TABLE Print_media FIELDS TERMINATED BY ',' (product_id INTEGER EXTERNAL(6), FileName FILLER CHAR(30), ad_graphic BFILE(CONSTANT "adgraphic_photo", FileName))
007, modem_2268.jpg, 008, monitor_3060.jpg, 009, keyboard_2056.jpg,
In the following example, the
BFILE and the
DIRECTORY objects are specified dynamically.
LOAD DATA INFILE sample10.dat INTO TABLE Print_media FIELDS TERMINATED BY ',' ( product_id INTEGER EXTERNAL(6), ad_graphic BFILE (DirName, FileName), FileName FILLER CHAR(30), DirName FILLER CHAR(30) )
007,monitor_3060.jpg,ADGRAPHIC_PHOTO, 008,modem_2268.jpg,ADGRAPHIC_PHOTO, 009,keyboard_2056.jpg,ADGRAPHIC_DIR,
13.3.3 Loading LOBs with External Tables
External tables are particularly useful for loading large numbers of records from a single file, so that each record appears in its own row in the table.
188.8.131.52 Overview of LOBs and External Tables
Learn the benefits of using external tables with your database to read and write data, and to understand how to create them.
External tables enable you to treat the contents of external files as if they are rows in a table in your Oracle Database. After you create an external table, you can then use SQL statements to read rows from the external table, and insert them into another table.
To perform these operations, Oracle Database uses one of the following access drivers:
ORACLE_LOADERaccess driver reads text files and other file formats, similar to SQL Loader.
ORACLE_DATAPUMPaccess driver creates binary files that store data returned by a query. It also returns rows from files in binary format.
When you create an external table, you specify column and data types for the external
table. The access driver has a list of columns in the data file, and maps the contents
of the field in the data file to the column with the same name in the external table.
The access driver takes care of finding the fields in the data source, and converting
these fields to the appropriate data type for the corresponding column in the external
table. After you create an external table, you can load the target table by using an
INSERT AS SELECT statement.
One of the advantages of using external tables to load data over SQL Loader is that
external tables can load data in parallel. The easiest way to do this is to specify the
PARALLEL clause as part of
CREATE TABLE for both
the external table and the target table.
This example creates a table,
CANDIDATE, that can be loaded by an
external table. When it is loaded, it then creates an external table,
CANDIDATE_XT. Next, it executes an
statement to load the table. The
INSERT statement includes the
+APPEND hint, which uses direct load to insert the rows into
PARALLEL parameter tells
SQL that the tables can be accessed in parallel.
PARALLEL parameter setting specifies that there can be four
4) parallel query processes reading from
CANDIDATE_XT, and four parallel processes inserting into
CANDIDATE. Note that LOBs that are stored as
BASICFILE cannot be loaded in parallel. You can only load
SECUREFILE LOBS in parallel. The variable
where additional external table information can be inserted.
CREATE TABLE CANDIDATES (candidate_id NUMBER, first_name VARCHAR2(15), last_name VARCHAR2(20), resume CLOB, picture BLOB ) PARALLEL 4; CREATE TABLE CANDIDATE_XT (candidate_id NUMBER, first_name VARCHAR2(15), last_name VARCHAR2(20), resume CLOB, picture BLOB ) PARALLEL 4; ORGANIZATION EXTERNAL additional-external-table-info PARALLEL 4; INSERT /*+APPEND*/ INTO CANDIDATE SELECT * FROM CANDIDATE_XT;
File Locations for External Tables Created By Access Drivers
All files created or read by
ORACLE_DATAPUMP reside in directories pointed to by directory
objects. Either the DBA or a user with the
privilege can create a directory object that maps a new to a path on the file
system. These users can grant
EXECUTE privileges on the created directory object to other
users. A user granted
READ privilege on a directory object can use
external tables to read files from directory for the directory object. Similarly, a
WRITE privilege on a directory object can use external
tables to write files to the directory for the directory object.
Example 13-4 Creating Directory Object
The following example shows how to create a directory object and grant
WRITE access to user
create directory HR_DIR as /usr/hr/files/exttab; grant read, write on directory HR_DIR to HR;
When using external tables in an Oracle Real Application Clusters (Oracle RAC) environment, you must make sure that the directory pointed to by the directory object maps to a directory that is accessible from all nodes.