|Oracle9i Application Developer's Guide - Large Objects (LOBs)
Release 2 (9.2)
Part Number A96591-01
This chapter describes the following topics:
When creating a directory object or BFILEs, ensure that the following conditions are met:
This section describes actions that you or your database administrator must take prior to working with LOBs.
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:
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
SQL Data Manipulation Language (DML) includes basic operations, such as,
DELETE -- that let you make changes to the entire value of internal
LOBs within Oracle RDBMS.
For use case examples refer to the following sections in Chapter 10, "Internal Persistent LOBs":
It is possible to change the default storage for a LOB after the table has been created.
To move the CLOB column from tablespace A to tablespace B, in Oracle8 release 126.96.36.199, requires the following statement:
However, this returns the following error message:
ORA-02210: no options specified for ALTER TABLE
Management and security issues of temporary LOBs are discussed in Chapter 11, "Temporary 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 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 LOBs are LOBs whose value comes from the primary data file.
Out-of-Line LOBs are LOBs whose value comes from LOBFILEs.
The following sections describe procedures for loading differently formatted inline and out-of-line data into internal LOBs:
Other topics discussed are
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.
|Loading Method For In-Line or Out-Of-Line Data||Relative Performance|
In Predetermined Size Fields
In Delimited Fields
In Length Value-Pair Fields
One LOB Per File
This is a very fast and simple way to load
LOBs. Unfortunately, the LOBs to be loaded are not usually the same size.
A possible work-around is to pad
To load LOBs using this format, use either
RAW as the loading datatype. For example:
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)
One space separates the
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.
You can specify both NULLIF and DEFAULTIF for the same field, although NULLIF has a higher 'priority' than DEFAULTIF.
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:
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>' )
007, <startlob> Once upon a time,The end. '<endlob>' '<endrec>' 008, <startlob> Once upon another time ....The end. '<endlob>' '<endrec>'
You could use
VARCHAR (see Oracle9i Database Utilities),
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:
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) )
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).
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
LOBFILE contains a single LOB. For example:
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 )
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:
LOAD DATA INFILE 'sample4.dat' INTO TABLE Multimedia_tab FIELDS TERMINATED BY ',' ( Clip_ID INTEGER EXTERNAL(5), Story LOBFILE (CONSTANT 'FirstStory1.txt') CHAR(32) )
SQL *Loader loads 2000 bytes of data from the
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:
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:
LOB in the
LOBFILE is preceded by its length. You can use
VARCHAR (see Oracle8 Utilities),
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:
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) )
LOBdoes not result in the rejection of the record containing that
LOB; instead, the record ends up containing an empty
For SQL*Loader 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, the LOB piece with the error is discarded and the rest of that LOB is not loaded. In other words, if the entire LOB with the error is contained in the first piece, then that LOB column will either be empty or truncated.
LOB-type column. If the maximum length is specified, it is taken as a hint to help optimize memory usage. It is important that the maximum length specification does not underestimate the true maximum length.
This section provides details on LOB restrictions.
LOB columns are subject to the following restrictions:
WHEREclauses of queries or in functions of the
The following syntax is not supported for LOBs:
SELECT lobcol FROM table1@remote_site; INSERT INTO lobtable SELECT type1.lobattr FROM table1@remote_site; SELECT DBMS_LOB.getlength(lobcol) FROM table1@remote_site;
(This statement produces error: ORA-22992 cannot use LOB locators selected from remote tables.)
However, you can use a remote locator in others parts of queries that reference LOBs. The following syntax is supported on remote LOB columns:
CREATE TABLE t AS SELECT * FROM table1@remote_site; INSERT INTO t SELECT * FROM table1@remote_site; UPDATE t SET lobcol = (SELECT lobcol FROM table1@remote_site); INSERT INTO table1@remote_site ... UPDATE table1@remote_site ... DELETE FROM table1@remote_site ...
For the first three types of statement, which contain subqueries, only standalone LOB columns are allowed in the select list. SQL functions or
DBMS_LOB APIs on LOBs are not supported. For example, the following statement is supported:
However, the following statement is not supported:
ORA-02335: invalid datatype for cluster column.
ORA-02348: cannot create VARRAY column with embedded LOB.
BYclause of a query, or in the
BYclause of a query or in an aggregate function. This produces error,
ORA-00932: inconsistent datatypes.
UNIQUEstatement or in a join. However, you can specify a LOB attribute of an object type column in a
DISTINCTstatement or in a query that uses the
MINUSset operator if the column's object type has a
ORDERfunction defined on it.
NCLOBas an attribute of an object type when creating a table. However, you can specify
NCLOBparameters in methods.
UPDATEDML trigger on a LOB column.
See Oracle9i Data Cartridge Developer's Guide for more information about defining triggers on domain indexes
UPDATEoperation, you can bind data of any size to a LOB column, but you cannot bind data to a LOB attribute of an object type. In an
SELECToperation, you can bind up to 4000 bytes of data to LOB columns.
LONGand LOB columns, you cannot bind more than 4000 bytes of data to both the
LONGand LOB columns in the same SQL statement. However, you can bind more than 4000 bytes of data to either the
LONGor the LOB column.
The first extent of any segment requires at least 2 blocks (if FREELIST GROUPS was 0). That is, the initial extent size of the segment should be at least 2 blocks. LOBs segments are different because they need at least 3 blocks in the first extent. If you try to create a LOB segment in a permanent dictionary managed tablespace with initial = 2 blocks, it will still work because it is possible for segments in permanent dictionary managed tablespaces to override tablespaces' default storage setting.
But if uniform locally managed tablespaces or dictionary managed tablespaces of the temporary type, or locally managed temporary tablespaces have an extent size of 2 blocks, LOB segments cannot be created in these tablespaces. This is because in these tablespace types, extent sizes are fixed and tablespaces' default storage setting is not ignored.
You will get a message on trying to create the LOB segment: ORA-3237 "initial extent of specified size cannot be allocated". You could be confused by this error, especially if your tablespace has lots of free space.
BFILEs in Shared Server (Multi-Threaded Server -- MTS) mode. This implies that operations on open
BFILEs can persist beyond the end of a call to a shared server. In shared server sessions,
BFILEoperations will be bound to one shared server, they cannot migrate from one server to another. This restriction will be removed in a forthcoming release.
Other general LOB restrictions include the following:
DBMS_LOB.LOADFROMFILE, you cannot specify an
amountparameter value larger than the size of the BFILE. See "PL/SQL - LOB Guidelines" for more information.