Skip Headers

Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 2 (9.2)

Part Number A96591-01
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback

Go to previous page Go to next page

4
Managing LOBs

This chapter describes the following topics:

Rules for using Directory Objects and BFILEs

When creating a directory object or BFILEs, ensure that the following conditions are met:

DBA Actions Required Prior to Working with LOBs

This section describes actions that you or your database administrator must take prior to working with LOBs.

Set Maximum Number of Open BFILEs

A limited number of BFILEs can be open simultaneously in each session. The initialization parameter, SESSION_MAX_OPEN_FILES defines an upper limit on the number of simultaneously open files in a session.

The default value for this parameter is 10. That is, you can open a maximum of 10 files at the same time in each session if the default value is utilized. If you want to alter this limit, the database administrator can change the value of this parameter in the init.ora file. For example:

SESSION_MAX_OPEN_FILES=20

If the number of unclosed files reaches the SESSION_MAX_OPEN_FILES value then you will not be able to open any more files in the session. To close all open files, use the DBMS_LOB.FILECLOSEALL call.

Using SQL DML for Basic Operations on LOBs

SQL Data Manipulation Language (DML) includes basic operations, such as, INSERT, UPDATE, DELETE -- that let you make changes to the entire value of internal LOBs within Oracle RDBMS.

Changing Tablespace Storage for a LOB

It is possible to change the default storage for a LOB after the table has been created.

Oracle8 Release 8.0.4.3

To move the CLOB column from tablespace A to tablespace B, in Oracle8 release 8.0.4.3, requires the following statement:

ALTER TABLE test lob(test) STORE AS (tablespace tools);

However, this returns the following error message:

ORA-02210: no options specified for ALTER TABLE

Oracle8i and Oracle9i

Managing Temporary LOBs

Management and security issues of temporary LOBs are discussed in Chapter 11, "Temporary LOBs",

Using SQL*Loader to Load LOBs

You can use SQL*Loader to bulk load LOBs. See "Loading LOBs" in Oracle9i Database Utilities for details on using SQL*Loader control file data definition language (DDL) to load LOB types.

Data loaded into LOBs can be lengthy and it is likely that you will want to have the data out- of-line from the rest of the data. LOBFILES provide a method to separate lengthy data.

LOBFILES

LOBFILES are simple datafiles that facilitate LOB loading. LOBFILEs are distinguished from primary datafiles in that in LOBFILEs there is no concept of a record. In LOBFILEs the data is of any of the following types:

Inline versus Out-of-Line LOBs

Inline LOBs are LOBs whose value comes from the primary data file.

Out-of-Line LOBs are LOBs whose value comes from LOBFILEs.

Loading InLine and Out-Of-Line Data into Internal LOBs Using SQL*Loader

The following sections describe procedures for loading differently formatted inline and out-of-line data into internal LOBs:

Other topics discussed are

SQL*Loader Performance: Loading Into Internal LOBs

See Table 4-1, "SQL*Loader Performance: Loading Data Into Internal LOBs" for the relative performance when using the preceding methods of loading data into internal LOBs.

Table 4-1 SQL*Loader Performance: Loading Data Into Internal LOBs
Loading Method For In-Line or Out-Of-Line Data Relative Performance

In Predetermined Size Fields

Highest

In Delimited Fields

Slower

In Length Value-Pair Fields

High

One LOB Per File

High

See Also:

Chapter 9, "LOBS: Best Practices"

Loading Inline LOB Data

Loading Inline LOB Data in Predetermined Size Fields

This is a very fast and simple way to load LOBs. Unfortunately, the LOBs to be loaded are not usually the same size.


Note:

A possible work-around is to pad LOB data with white space to make all LOBs the same length within the particular datafield; for information on trimming of trailing white spaces see "Trimming of Blanks and Tabs" in Oracle9i Database Utilities.


To load LOBs using this format, use either CHAR or RAW as the loading datatype. For example:

Control File

LOAD DATA 
INFILE 'sample.dat' "fix 21" 
INTO TABLE Multimedia_tab 
    APPEND
    (Clip_ID POSITION(1:3) INTEGER EXTERNAL, 
    Story POSITION(5:20)   CHAR DEFAULTIF Story=BLANKS) 



Data File (sample.dat)

007 Once upon a time


Note:

One space separates the Clip_ID,(O07) from the beginning of the story. The story is 15 bytes long.


If the datafield containing the story is empty, then an empty LOB instead of a NULL LOB is produced. A NULL LOB is produced only if the NULLIF clause is used instead of the DEFAULTIF clause. You can use loader datatypes other than CHAR to load LOBs. Use the RAW datatype when loading BLOBs.


Note:

You can specify both NULLIF and DEFAULTIF for the same field, although NULLIF has a higher 'priority' than DEFAULTIF.


Loading Inline LOB Data in Delimited Fields

Loading different size LOBs in the same column (that is, datafile field) is not a problem. The trade-off for this added flexibility is performance. Loading in this format is somewhat slower because the loader has to scan through the data, looking for the delimiter string. For example:

Control File

LOAD DATA
INFILE 'sample1.dat' "str '<endrec>\n'"
INTO TABLE Multimedia_tab
FIELDS TERMINATED BY ','
(
Clip_ID   CHAR(3),
 Story    CHAR(507) ENCLOSED BY '<startlob>' AND '<endlob>'
)

Data File (sample1.dat)

007,    <startlob>     Once upon a time,The end. '<endlob>' '<endrec>'
008,    <startlob>     Once upon another time ....The end. '<endlob>' '<endrec>'

Loading Inline LOB Data in Length-Value Pair Fields

You could use VARCHAR (see Oracle9i Database Utilities), VARCHARC, or VARRAW datatypes to load LOB data organized in this way. Note that this method of loading produces better performance over the previous method, however, it removes some of the flexibility, that is, it requires you to know the LOB length for each LOB before loading. For example:

Control File

LOAD DATA
INFILE 'sample2.dat' "str '<endrec>\r\n'"
INTO TABLE Multimedia_tab
APPEND
FIELDS TERMINATED BY ','
(
Clip_ID  INTEGER EXTERNAL (3),
 Story   VARCHARC (3, 500)
)

Data File (sample2.dat)

007,041    Once upon a time...  ....  The end.  <endrec>
008,000 <endrec>

Note:
  • Story is a field corresponding to a CLOB column. In the control file, it is described as a VARCHARC (3, 500) whose length field is 3 bytes long and maximum size is 500 bytes. This tells the Loader that it can find the length of the LOB data in the first 3 bytes.
  • The length subfield of the VARCHARC is 0 (that is, the value subfield is empty); consequently, the LOB instance is initialized to empty.
  • Make sure the last character of the last line of the preceding data file is a line feed.
  • When loading a data stream where a record is terminated by a newline (as on Unix platforms), or a carriage-return and newline (as on Windows platforms), you must specify \n (for Unix) or \r\n (for Windows) to ensure that these characters are not interpreted as the start of the next record.

Loading Out-Of-Line LOB Data

This section describes the following topics:

As mentioned earlier, LOB data can be so large that it is reasonable to want to load it from secondary datafile(s).

In LOBFILEs, LOB data instances are still thought to be in fields (predetermined size, delimited, length-value), but these fields are not organized into records (the concept of a record does not exist within LOBFILES); thus, the processing overhead of dealing with records is avoided. This type of organization of data is ideal for LOB loading.

Loading One LOB Per File

Each LOBFILE contains a single LOB. For example:

Control File

LOAD DATA
INFILE 'sample3.dat'
INTO TABLE Multimedia_tab
REPLACE
FIELDS TERMINATED BY ','
(
 Clip_ID        INTEGER EXTERNAL(5),
 ext_FileName   FILLER CHAR(40),
 Story          LOBFILE(ext_FileName) TERMINATED BY EOF
)

Data File (sample3.dat)

007,FirstStory.txt,
008,/tmp/SecondStory.txt, 

Secondary Data File (FirstStory.txt)

Once upon a time ...
The end.

Secondary Data File (SecondStory.txt)

Once upon another time ....
The end.

Note::
  • STORY tells the Loader that it can find the LOB data in the file whose name is stored in the ext_FileName field.
  • TERMINATED BY EOF tells the Loader that the LOB will span the entire file.
  • SQL*Plus users: Viewing CLOB data using the SQL*Plus long parameter displays only the first 80 characters of the CLOB because the default value of the long parameter is 80.
  • See also Oracle9i Database Utilities

Loading Out-of-Line LOB Data in Predetermined Size Fields

In the control file, the size of the LOBs to be loaded into a particular column is specified. During the load, any LOB data loaded into that column is assumed to be the specified size. The predetermined size of the fields allows the dataparser to perform very well. Unfortunately, it is often hard to guarantee that all the LOBs are the same size. For example:

Control File

LOAD DATA 
INFILE 'sample4.dat'
INTO TABLE Multimedia_tab
FIELDS TERMINATED BY ','
(
 Clip_ID    INTEGER EXTERNAL(5),
 Story      LOBFILE (CONSTANT 'FirstStory1.txt') CHAR(32)
)

Data File (sample4.dat)

007,
008,

Secondary Data File (FirstStory1.txt)

Once upon the time ...
The end,
Upon another time ...
The end, 


Note::

SQL *Loader loads 2000 bytes of data from the FirstStory.txt LOBFILE, using CHAR datatype, starting with the byte following the byte loaded last during the current loading session.


Loading Out-of-Line LOB Data in Delimited Fields

LOB data instances in LOBFILE files are delimited. In this format, loading different size LOBs into the same column is not a problem. The trade-off for this added flexibility is performance. Loading in this format is somewhat slower because the loader has to scan through the data, looking for the delimiter string. For example:

Control File

LOAD DATA
INFILE 'sample5.dat'
INTO TABLE Multimedia_tab
FIELDS  TERMINATED BY ','
(Clip_ID    INTEGER EXTERNAL(5),
Story       LOBFILE (CONSTANT 'FirstStory2.txt') CHAR(2000)
TERMINATED BY "<endlob>\n")

Windows users: Terminate the control file using:

TERMINATED BY "<endlob>\r\n")

Data File (sample5.dat)

007,
008,

Secondary Data File (FirstStory2.txt)

Once upon a time...
The end.<endlob>
Once upon another time...
The end.<endlob>

Note:
  • The TERMINATED BY clause specifies the string that terminates the LOBs.
  • When loading a data stream where a record is terminated by a newline (as on Unix platforms), or a carriage-return and newline (as on Windows platforms), you must specify \n (for Unix) or \r\n (for Windows) to ensure that these characters are not interpreted as the start of the next record.

Loading Out-of-Line LOB Data in Length-Value Pair Fields

Each LOB in the LOBFILE is preceded by its length. You can use VARCHAR (see Oracle8 Utilities), VARCHARC, or VARRAW datatypes to load LOB data organized in this way. The controllable syntax for loading length-value pair specified LOBs is quite simple.

Note that this method of loading performs better than the previous one, but at the same time it takes some of the flexibility away, that is, it requires that you know the length of each LOB before loading. For example:

Control File

LOAD DATA
INFILE 'sample6.dat'
INTO TABLE Multimedia_tab
FIELDS TERMINATED BY ','
(
Clip_ID     INTEGER EXTERNAL(5),
Story       LOBFILE (CONSTANT 'FirstStory3.txt') VARCHARC(4,2000)
)

Data File (sample6.dat)

007,
008,

Secondary Data File (FirstStory3.txt)

0031
Once upon a time ... The end.
0000

Note:

VARCHARC(4,2000) tells the loader that the LOBs in the LOBFILE are in length-value pair format and that the first four bytes should be interpreted as length. The max_length part (that is, 2000) gives the hint to the loader as to the maximum size of the field.

  • 0031 tells the loader that the next 31 bytes belong to the specified LOB.
  • 0000 results in an empty LOB (not a NULL LOB).

SQL*Loader LOB Loading Tips

LOB Restrictions

This section provides details on LOB restrictions.

See Also:

LOB columns are subject to the following restrictions:

Other general LOB restrictions include the following:


Go to previous page Go to next page
Oracle
Copyright © 1996, 2002 Oracle Corporation.

All Rights Reserved.
Go To Documentation Library
Home
Go To Product List
Book List
Go To Table Of Contents
Contents
Go To Index
Index

Master Index

Feedback