Release 8.0
A54642_01
 Library |
 Product |
 Contents |
 Index |
| Oracle8
Server Application Developer's Guide Release 8.0 A54642_01 |
|
Oracle8 provides support for defining and manipulating large objects (LOBs). Oracle8 extends SQL DDL and DML commands to create and update LOB columns in a table or LOB attributes of an object type. Further, Oracle8 provides Oracle Call Interface (OCI) and PL/SQL package APIs to perform random, piecewise operations on LOBs.
This chapter documents the extended SQL commands and the PL/SQL package API for LOBs. It also briefly mentions the OCI API for LOB manipulation, which is described in the Programmer's Guide to the Oracle Call Interface, Volume II: OCI Reference.
This chapter has two sections:
This section introduces the treatment of LOBs in Oracle8 under the headings that are also laid out below. Although it is not made explicit in the text, the various issues can be grouped under a number of umbrella topics.
The first topic is one of general introduction:
The second topic discusses steps involved in beginning to work with LOBs:
The third topic deals with issues specific to handling external LOBs (BFILEs):
The fourth topic takes considers how LOBs are handled by way of locators:
The fifth topic is concerned with basic manipulation of LOBs:
Finally, the last topic considers performance and optimization issues in a client/server environment:
Consider the following scenarios:
_______________________________________________________________ A law firm wishes to manage production of a significant case by means of a database. The lawyers are aware that the information will include x-rays (image data), expert analysis (character text), depositions (audio/video), and drawings (graphics). During the course of the trial they also come to utilize computer- simulated events (animation). A broadcast station wishes to manage production of its feature programs by means of a database. The program managers are aware that this information commonly includes photographs (image data), interviews (audio/video), sound-effects (sound waveforms), music (sound waveforms), and script (character text). With the advance of digitizing and storage technology, they also find it possible to include legacy silent-film (video). A geological survey team looking for oil under the sea wishes to manage its projects by means of a database. The project managers are aware that the information will include satellite pictures (image data) with complex overlay drawings (image data), sonar recordings along with their graphic representations (sound wave forms and image data), and chemical analysis (image data and character text).During the course of the project they also come to employ computer modeling of likely weather conditions (character text and image data). _______________________________________________________________
Although each of these scenarios is drawn from a different domain, it is easy to see how management of multiple media is becoming commonplace in business applications. This is relevant to this chapter because Oracle8 supports LOBs - large objects which can hold up to 4 gigabytes of RAW, binary data (e.g., graphic images, sound waveforms, video clips, etc.) or character text data.
Oracle8 regards LOBs as being of two kinds depending on their location with regard to the database - internal LOBs and external LOBs (BFILEs). When the term LOB is used without an identifying prefix term, it refers to both internal and external LOBs. Data stored in a LOB is termed the LOB's value.
Internal LOBs, as their name suggests, are stored in the database tablespaces in a way that optimizes space and provides efficient access. Internal LOBs use copy semantics and participate in the transactional model of the server. That is, all the ACID properties that pertain to using database objects pertain to internal LOBs also. This means that any changes to a internal LOB value can be committed or rolled back.
External LOBs, also referred to as BFILES, are large binary data objects stored in operating system files outside of the database tablespaces. These files use reference semantics. They may be located on hard disks, CDROMs, PhotoCDs or any such block device, but a single LOB may not extend from one device to another. The SQL datatype BFILE is supported in Oracle8 SQL and PL/SQL to enable read-only byte stream I/O access to large files existing on the filesystem of the database server. The Oracle Server can access them provided the underlying server operating system supports a stream-mode access to these files.
Note that external LOBs do not participate in transactions. Any support for integrity and durability must be provided by thLOBe underlying file system as governed by the operating system.
You can make changes to the values of internal LOBs through direct SQL DML, or through the OCI, or through the PL/SQL DBMS_LOB APIs. Changes can be made to an entire internal LOB or piecewise to the beginning, middle or end of an internal LOB. It is possible to access both internal and external LOBs for read purposes.
There are three SQL datatypes for defining instances of internal LOBs:
Varying width character data is not supported for BLOBs, CLOBs and NCLOBs, and in this regard see "Working with Varying-Width Character Data" on page 6-43. Also, see "LOB Restrictions" on page 6-88 for a discussion of migrating LONGs to LOBs.
There is one external LOB datatype:
It is possible to incorporate LOBs into tables in two ways.
In both cases SQL DDL is used - to define LOB columns in a table and LOB attributes in an object type. Refer to the Oracle8 Server SQL Reference for information about using LOBs in the following DDL commands:
The following code fragment describes creating the table, lob_table. We refer to this example throughout the text.
CREATE TABLE lob_table (
key_value INTEGER,
b_lob BLOB,
c_lob CLOB,
n_lob NCLOB,
f_lob BFILE);
When defining LOBs in a table, you can explicitly indicate the tablespace and storage characteristics for each internal LOB. There are no extra tablespace or storage characteristics for external LOBs since they are not stored in the database.
The advantage of specifying a name for the LOB data segment and the LOB index is that you can then query the system tables (e.g. seg$ and ind$) and find out the storage characteristics for the LOB data segment or index easily. You can then determine what needs to be modified.
The LOB specific storage characteristics include PCTVERSION, CACHE, NOCACHE, LOGGING, NOLOGGING, CHUNK, ENABLE/DISABLE STORAGE IN ROW. Here are some guidelines.
Use the defaults for the LOB storage clause plus specify a different tablespace for the LOB data. Best performance for LOBs can be achieved by specifying storage for LOBs in a tablespace that is different from the one used for the table that contains the LOB. If many different LOBs will be accessed frequently, it may also be useful to specify a separate tablespace for each LOB column/attribute in order to reduce device contention.
The LOB index is an internal structure that is strongly associated with the LOB storage. This implies that a user may not drop the LOB index and rebuild it. Also, the LOB index may not be renamed. The system determines which tablespace to use for the LOB data and LOB index depending on the user specification in the LOB storage clause:
A. If you do not specify a tablespace for the LOB data nor for the LOB index, the table's tablespace is used for both the LOB data and the LOB index.
B. If you specify a tablespace for the LOB data but not for the LOB index, both the LOB data and index use the tablespace that was specified for the LOB data.
C. If you specify a tablespace for the LOB index but not the LOB data, the LOB index uses the specified tablespace and the LOB data uses the table's tablespace.
D. If you specify a tablespace for the LOB data and the LOB index, the LOB data and index use the specified tablespaces respectively.
Specifying a separate tablespace for the LOB storage segments will allow for a decrease in contention on the table's tablespace. In some extreme cases, it may even be beneficial to use three separate tablespaces - one for the table data, one for the LOB data segments, and one for the LOB index segments. This would be useful if certain LOB data is to be accessed very frequently. Normally, using two tablespaces - one for the table data, and one for the LOB data and LOB index - should be sufficient.
When a LOB is modified, a new version of the LOB page is made in order to support consistent read of prior versions of the LOB value.
PCTVERSION is the percent of all used LOB data space that can be occupied
by old versions of LOB data pages. As soon as old versions of LOB data
pages start to occupy more than the PCTVERSION amount of used LOB space
Oracle will try to reclaim the old versions and reuse them. In other words,
it is the percent of used LOB data blocks that is available for versioning
of old LOB data.
Default: 10 (%) Minimum: 0 (%) Maximum: 100 (%)
One way of approximating PCTVERSION is to set PCTVERSION =% of LOBs updated
at any given point in time * % of each LOB updated whenever a LOB is updated
* % of LOBS being read at any given point in time. Basically, the idea
is to allow for a percentage of LOB storage space to be used as old versions
of LOB pages so that readers will be able to get consistent reads of data
that has been updated.
Several LOB updates concurrent with heavy reads of LOBs.
set PCTVERSION = 20%
Setting PCTVERSION to twice the default allows more free pages to be used
for old versions of data pages. Since large queries may require consistent
reads of LOBs, it is useful to keep more old versions of LOB pages around.
Of course, LOB storage may grow some because Oracle will not be reusing
free pages aggressively.
LOBs are created and written just once and are primarily read-only
afterwards. Updates are infrequent.
set PCTVERSION = 5% or lower
The more infrequent and smaller the LOB updates are, the less space that
needs to be reserved for old copies of LOB data. If existing LOBs are known
to be read-only, we could safely set PCTVERSION to 0% since there would
never be any pages needed for old versions of data.
Use the CACHE option on LOBs if LOB data will be accessed frequently. Use the NOCACHE option (the default) if LOB data will be read only once, or infrequently.
NOLOGGING has the same application with regard to using LOBs as it does for other table operations (see Oracle8 Server SQL Reference: Volume II). However, the following issues should be kept in mind.
LOBs will always generate undo for LOB index pages. Regardless of whether LOGGING or NOLOGGING is set LOBs will never generate rollback information (undo) for LOB data pages because old LOB data is stored in versions. Rollback information that is created for LOBs tends to be small because it is only for the LOB index page changes.
When LOGGING is set Oracle will generate full redo for LOB data pages. NOLOGGING is intended to be used when a customer does not care about media recovery. Thus, if the disk/tape/storage media fails, you will not be able to recover the changes you made from the log since the changes were never logged. An example is bulk loads or inserts.
For instance, when loading data into the LOB, if you don't care about redo and can just start the load over if it fails, set the LOB's data segment storage characteristics to NOCACHE NOLOGGING. This will give good performance for the initial load of data. Once you have completed loading the data, you can use ALTER TABLE to modify the LOB storage characteristics for the LOB data segment to be what you really want for normal LOB operations -- i.e. CACHE or NOCACHE LOGGING. Note that CACHE implies that you also get LOGGING.
Set CHUNK to the number of blocks of LOB data that will be accessed at one time i.e. the number of blocks that will be read/written via OCILobRead(), OCILobWrite(), DBMS_LOB.READ(), or DBMS_LOB.WRITE() during one access of the LOB value. For example, if only 1 block of LOB data is accessed at a time, set CHUNK to 1.
If you explicitly specify the storage characteristics for the LOB, make sure that INITIAL and NEXT for the LOB data segment storage are set to a size that is larger than the CHUNK size. For example, if the database block size is 2K and you specify a CHUNK of 4 (i.e. 8K is the chunk/page size), make sure that the INITIAL and NEXT are bigger than 8K and preferably considerably bigger (for example, at least 10K).
You use the ENABLE | DISABLE STORAGE IN ROW clause to indicate whether the LOB should be stored inline (i.e. in the row) or out of line. You may not alter this specification once you have made it: if you ENABLE STORAGE IN ROW, you cannot alter it to DISABLE STORAGE IN ROW and vice versa. The default is ENABLE STORAGE IN ROW.
The maximum amount of LOB data that will be stored in the row is the maximum VARCHAR size (4000). Note that this includes the control information as well as the LOB value. If the user indicates that the LOB should be stored in the row, once the LOB value and control information is larger than 4000, the LOB value is automatically moved out of the row.
This suggest the following guideline. If the LOB is small (i.e. < 4000 bytes), then storing the LOB data out of line will decrease performance. However, storing the LOB in the row increases the size of the row. This will impact performance if the user is doing a lot of base table processing, such as full table scans, multi-row accesses (range scans) or many UPDATE/SELECT to columns other than the LOB columns. If the user doesn't expect the LOB data to be < 4000, i.e. if all LOBs are big, then the default is the best choice since
(a) the LOB data is automatically moved out of line once it gets bigger than 4000 (which will be the case here since the LOB data is big to begin with), and
(b) performance will be slightly better since we still store some control information in the row even after we move the LOB data out of the row.
You can set an internal LOB - -that is, a LOB column in a table, or a LOB attribute in an object type defined by you- to be empty, or NULL. An empty LOB stored in a table is a LOB of zero length that has a locator. If you SELECT from an empty LOB column / attribute, you get back a locator which you can use to populate the LOB with data via the OCI or DBMS_LOB routines. This is discussed in more detail below.
Alternatively, LOB columns, but not LOB attributes, may be initialized to a value. Which is to say - internal LOB attributes differ from internal LOB columns in that LOB attributes may not be initialized to a value other than null or empty. As discussed below, an external LOB (i.e. BFILE) can be initialized to NULL or to a filename.
For example, let us say that you create the table, lob_table:
CREATE TABLE lob_table (
key_value INTEGER,
b_lob BLOB,
c_lob CLOB,
n_lob NCLOB,
f_lob BFILE);
You can initialize the LOBs by using the following SQL INSERT statement:
INSERT INTO lob_table VALUES (1001, EMPTY_BLOB(), NULL,
EMPTY_CLOB(),NULL, NULL);
This sets the value of b_lob and n_lob to an empty value, and sets c_lob and f_lob to NULL.
You may want to set the LOB value to null upon inserting the row in cases where you do not have the LOB data at the time of the INSERT and/or if you want to issue a SELECT statement thereafter such as:
SELECT * FROM a_table WHERE a_lob_col != NULL;
or
SELECT * FROM a_table WHERE a_lob_col == NULL;
However, the drawback to this approach is that you must then issue a SQL UPDATE statement to set the NULL LOB column to EMPTY_BLOB () /EMPTY_CLOB() or to a value (e.g. 'abc'). You cannot call the OCI or the PL/SQL DBMS_LOB functions on a NULL LOB. These functions only work with a locator and if the LOB column is NULL, there is no locator in the row.
The other option is for you to set the LOB value to empty by using the function EMPTY_BLOB () /EMPTY_CLOB() in the INSERT statement:
INSERT INTO a_table values (empty_blob());
Even better is to use the RETURNING clause (thereby eliminating a round trip that is necessary for the subsequent SELECT), and then immediately call OCI or the PL/SQL DBMS_LOB functions to populate the LOB with data (see "EMPTY_BLOB() and EMPTY_CLOB() Functions" on page 6-45 for more information).
In order to associate an operating system file to a BFILE, it is necessary to first create a DIRECTORY object which is an alias to the full pathname to the operating system file (see "Directory Object" on page 6-13).
You use Oracle8 SQL DML to associate existing operating system files with the relevant database records of a particular table. You can use the SQL INSERT statement to initialize a BFILE column to point to an existing file in the server's filesystem, and you can use a SQL UPDATE statement to change the reference target of the BFILE. You can also initialize a BFILE to NULL and then update it later to refer to an operating system file via the BFILENAME() function.
For example, the following statements associate the files image1.gif and image2.gif with records having key_value of 21 and 22 respectively. 'IMG' is a DIRECTORY object that represents the physical directory under which image1.dif and image2.dif are stored.
INSERT INTO lob_table VALUES
(21, NULL, NULL, NULL, BFILENAME('IMG', 'image1.gif'));
INSERT INTO lob_table VALUES
(12, NULL, NULL, NULL, BFILENAME('IMG', 'image2.gif'));
The UPDATE statement below changes the target file to image3.gif for the row with key_value 22.
UPDATE lob_table SET f_lob = BFILENAME('IMG', 'image3.gif')
WHERE key_value = 22;
BFILENAME() is a built-in function that is used to initialize the BFILE column to point to the external file (see "BFILENAME() Function" on page 6-47). Once physical files are associated with records using SQL DML, subsequent read operations on the BFILE can be performed using PL/SQL DBMS_LOB package and OCI. However, these files are read-only through BFILES, and so they cannot be updated or deleted through BFILES.
As a consequence of the reference-based semantics for BFILEs, it is possible to have multiple BFILE columns in the same record or different records referring to the same file. For example, the UPDATE statements below set the BFILE column of the row with key_value 21 in lob_table to point to the same file as the row with key_value 22.
UPDATE lob_table SET f_lob = (SELECT f_lob FROM lob_table WHERE key_value = 22) WHERE key_value = 21;
The DIRECTORY object enables administering the access and usage of BFILEs in an Oracle8 Server (see the CREATE DIRECTORY command in the Oracle8 Server Reference Manual). A DIRECTORY specifies a logical alias name for a physical directory on the server's filesystem under which the file to be accessed is located. You can access a file in the server's filesystem only if granted the required access privilege on the DIRECTORY object.
The DIRECTORY object also provides the flexibility to manage the locations of the files, instead of forcing you to hardcode the absolute pathnames of the physical files in your applications. A DIRECTORY alias is used in conjunction with the BFILENAME() function (in SQL and PL/SQL), or the OCILobFileSetName() (in OCI) for initializing a BFILE locator.
The naming convention followed by Oracle8 for DIRECTORY objects is the same as that done for tables and indexes. That is, normal identifiers are interpreted in uppercase, but delimited identifiers are interpreted as is. For example, the following statement
create directory scott_dir as '/usr/home/scott';
creates a directory object whose name is 'SCOTT_DIR' (in uppercase). But if a delimited identifier is used for the DIRECTORY name, as shown in the following statement
create directory "Mary_Dir" as '/usr/home/mary';
the directory object's name is 'Mary_Dir'.
This section introduces the BFILE security model and the associated SQL DDL and DML. The main features for BFILE security in Oracle 8.0 are:
The DIRECTORY is a system owned object. For more information on system owned objects, see Oracle8 Server SQL Reference. Oracle8 supports two new system privileges, which are granted only to the DBA account:
The READ privilege on the DIRECTORY object allows you to read files located under that directory. The creator of the DIRECTORY object automatically earns the READ privilege. If you have been granted the READ privilege, you may in turn grant this privilege to other users/roles and add them to your privilege domains.
It is important to note that the READ privilege is defined only on the DIRECTORY object. The physical directory that it represents may or may not have the corresponding operating system privileges (read in this case) for the Oracle Server process. It is the DBA's responsibility to ensure that the physical directory exists, and read permission is enabled on the directory (and the path leading to it) for the Oracle Server process. It is also the DBA's responsibility to make sure that the directory remains available, and the read permission remains enabled, for the entire duration of file access by database users.
The privilege just implies that as far as the Oracle8 Server is concerned, you may read from files in the directory. These privileges are checked and enforced by the PL/SQL DBMS_LOB package and OCI APIs at the time of the actual file operations.
Note: Since the CREATE ANY DIRECTORY and DROP ANY DIRECTORY privileges potentially expose the server filesystem to all database users, the DBA should be prudent in granting these privileges to normal database users to prevent any accidental or malicious security breach.
Refer to the Oracle8 Server SQL Reference for information about the following SQL DDL commands that create, replace, and drop directory objects:
Refer to the Oracle8 Server SQL Reference for information about the following SQL DML commands that provide security for BFILEs:
Catalog views are provided for directory objects to enable users to view object names and their corresponding paths and privileges. The supported views are:
This view describes all the directories accessible to the user.
This view describes all the directories specified for the entire database.
The main goal of the DIRECTORY feature in Oracle8 is to enable a simple, flexible, non-intrusive, yet secure mechanism for the DBA to manage access to large files in the server filesystem. But to realize this goal, it is very important that the DBA follow these guidelines when using directory objects:
The only recourse left to PL/SQL users, for example, will be to either execute a program block that calls DBMS_LOB FILECLOSEALL() (see "DBMS_LOB.FILECLOSEALL() Procedure" on page 6-67) and restart their file operations, or exit their sessions altogether. Hence, it is imperative that you use these commands with prudence, and preferably during maintenance downtimes.
In general, using DIRECTORY objects for managing file access is an extension of system administration work at the operating system level. With some planning, files can be logically organized into suitable directories that have read privileges for the Oracle process, DIRECTORY objects can be created with READ privileges that map to these physical directories, and specific database users granted access to these directories.
A limited number of BFILEs can be open simultaneously per session. The maximum number is specified by a new initialization parameter, the SESSION_MAX_OPEN_FILES 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, a maximum of 10 files can be opened simultaneously per session if the default value is utilized. The database administrator can change the value of this parameter in the init.ora file. For example:
SESSION_MAX_OPEN_FILES=20
Oracle8 release 8.0 does not support session migration for BFILEs in MTS mode. This implies that operations on open BFILEs can persist beyond the end of a call to an MTS server. Sessions involving BFILE operations need to be bound to one shared server, they cannot migrate from one server to another.
It is the user's responsibility to close any opened file(s) after normal or abnormal termination of a PL/SQL program block or OCI program. In other words, for every DBMS_LOB FILEOPEN call, there must be a matching DBMS_LOB FILECLOSE call not only before the termination of a PL/SQL block or OCI program, but also in a suitable exception handler to close any files that were opened before the occurrence of the exception or abnormal termination.
If this is not done, the Oracle Server will consider these files unclosed, and if the number of unclosed files exceeds 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 FILECLOSEALL call.
For more details, refer to "DBMS_LOB General Usage Notes" on page 6-54 for PL/SQL programming.
Data stored in a LOB is termed the LOB's value. The value of an internal LOB may or may not be stored inline with the other row data. If the internal LOB value is less than approximately 4000 bytes, then the value is stored inline; otherwise it is stored outside the row. Since LOBs are intended to be large objects, inline storage will only be relevant if your application mixes 'small' and 'large' LOBs.
As mentioned above ("ENABLE | DISABLE STORAGE IN ROW" on page 6-10), the LOB value is automatically moved out of the row once it extends beyond approximately 4000 bytes.
Regardless of where the value of the internal LOB is stored, a locator is stored in the row. You can think of a LOB locator as a pointer to the actual location of the LOB value. A LOB locator is a locator to an internal LOB while a BFILE locator is a locator to an external LOB. When the term locator is used without an identifying prefix term, it refers to both LOB locators and BFILE locators.
For internal LOBs, the LOB column stores a locator to the LOB's value which is stored in a database tablespace. Each LOB column/attribute for a given row has its own distinct LOB locator and copy of the LOB value stored in the database tablespace.
For BFILES, the value is stored in a server-side operating system file, i.e. external to the database. The BFILE locator that refers to that file is stored in the row. If a BFILE locator variable that is used in a DBMS_LOB FILEOPEN() (for example L1) is assigned to another locator variable, (for example L2), both L1 and L2 point to the same file. This means that two rows in a table with a BFILE column can refer to the same file or to two distinct files - a fact that the canny developer might turn to advantage, but which could well be a pitfall for the unwary.
A BFILE locator variable in a PL/SQL or OCI program behaves like any other automatic variable. With respect to file operations, it behaves like a file descriptor available as part of the standard I/O library of most conventional programming languages. This implies that once you define and initialize a BFILE locator, and open the file pointed to by this locator, all subsequent operations until the closure of this file must be done from within the same program block using this locator or local copies of this locator.
The BFILE locator variable can be used, just as any scalar, as a parameter to other procedures, member methods, or external function callouts. However, it is recommended that you open and close a file from the same program block at the same nesting level, in PL/SQL and OCI programs.
Before you can start writing data to a internal LOB, the LOB column/attribute must be made non-null, that is, it must contain a locator. Similarly, before you can start accessing the BFILE value, the BFILE column/attribute must be made non-null.
Invoking the EMPTY_BLOB() or EMPTY_CLOB() function in and of itself does not raise an exception. However, using a LOB locator that was set to empty in any PL/SQL DBMS_LOB or OCI routine will raise an exception. Valid places where empty LOB locators may be used include the VALUES clause of an INSERT statement and the SET clause of an UPDATE statement.
The following INSERT statement
- sets b_lob to NULL,
- populates c_lob with the character string 'abcde',
- sets n_lob to NULL, and
- initializes f_lob to point to the file 'scott.dat' located under
the logical directory 'SCOTT_DIR' (see the CREATE DIRECTORY command in
the Oracle8 Server Reference Manual).
Character strings are inserted using the default character set for the
instance.
INSERT INTO lob_table VALUES (1002, NULL 'abcde',
NULL,BFILENAME('SCOTT_DIR', 'scott.dat'));
Similarly, given a table person_objcol_table one of whose columns is an object with LOB attributes, the LOB attributes can be initialized to null or set to empty as shown below:
INSERT INTO person_objcol_table VALUES (1001,person_type
('Scott', EMPTY_CLOB(), EMPTY_BLOB(),
BFILENAME('SCOTT_DIR', 'scott.dat')));
Performing a SELECT on a LOB returns the locator instead of the LOB value. In the following PL/SQL fragment you select the LOB locator for b_lob and place it in the PL/SQL locator variable image1 defined in the program block. When you use PL/SQL DBMS_LOB functions to manipulate the LOB value, you refer to the LOB using the locator.
DECLARE
image1 BLOB;
image_no INTEGER := 101;
BEGIN
SELECT b_lob INTO image1 FROM lob_table
WHERE key_value = image_no;
DBMS_OUTPUT.PUT_LINE('Size of the Image is: ' ||
DBMS_LOB.GETLENGTH(image1));
-- more LOB routines
END;
In using OCI, locators are mapped to locator pointers which are used to manipulate the LOB value. As mentioned before, the OCI LOB interface is described briefly in "Using the OCI to Manipulate LOBs" on page 6-49, and more extensively in the Programmer's Guide to the Oracle Call Interface, Volume II: OCI Reference.
Prior to updating a LOB value via the PL/SQL DBMS_LOB package or the OCI, you must lock the row containing the LOB. While the SQL INSERT and UPDATE statements implicitly lock the row, locking is done explcitly by means of a SQL SELECT FOR UPDATE statement in SQL and PL/SQL programs, or by using an OCI pin or lock function in OCI programs.
Oracle provides the same read consistency mechanisms for LOBs as for all other database reads and updates (refer to Oracle8 Server Concepts: Volume I for general information about read consistency). However, read consistency has some special applications to LOB locators that need to be clearly understood.
A SELECTed locator, regardless of the existence of the FOR UPDATE clause, becomes a read consistent locator, and remains a read consistent locator until the LOB value is updated through that locator. A read consistent locator contains the snapshot environment as of the point in time of the SELECT.
This has some complex implications. Let us say that you have created a read consistent locator (L1) by way of a SELECT operation. In reading the value of the internal LOB through L1, the LOB is read as of the point in time of the SELECT statement even if the SELECT statement includes a FOR UPDATE. Further, if the LOB value is updated through a different locator (L2) in the same transaction, L1 does not see L2's updates. In addition, L1 will not see committed updates made to the LOB through another transaction.
Furthermore, if the read consistent locator L1 is copied to another locator L2 (for example, by a PL/SQL assignment of two locator variables - L2:= L1), then L2 becomes a read consistent locator along with L1 and any data read is read as of the point in time of the SELECT for L1.
Clearly you can utilize the existence of multiple locators to access different transformations of the LOB value. However, in taking this course, you must be careful to keep track of the different values accessed by different locators. The following code demonstrates the relationship between read-consistency and updating in a simple example.
Using lob_table as defined above, three CLOBs are created as potential locators: clob_selected, clob_updated and clob_copied.
INSERT INTO lob_table
VALUES (1, NULL, 'abcd', NULL, NULL);
COMMIT;
DECLARE
num_var INTEGER;
clob_selected CLOB;
clob_updated CLOB;
clob_copied CLOB;
read_amount INTEGER;
read_offset INTEGER;
write_amount INTEGER;
write_offset INTEGER;
buffer VARCHAR2(20);
BEGIN
-- At time t1:
SELECT c_lob
INTO clob_selected
FROM lob_table
WHERE key_value = 1;
-- At time t2:
SELECT c_lob
INTO clob_updated
FROM lob_table
WHERE key_value = 1
FOR UPDATE;
-- At time t3:
clob_copied := clob_selected;
-- After the assignment, both the clob_copied and the
-- clob_selected have the same snapshot as the point in time
-- as the SELECT into clob_selected
-- Reading from the clob_selected and the clob_copied will
-- return the same LOB value. clob_updated also sees the same
-- LOB value as of its select:
read_amount := 10;
read_offset := 1;
dbms_lob.read(clob_selected, read_amount, read_offset,
buffer);
dbms_output.put_line('clob_selected value: ' || buffer);
-- Produces the output 'abcd'
read_amount := 10;
dbms_lob.read(clob_copied, read_amount, read_offset, buffer);
dbms_output.put_line('clob_copied value: ' || buffer);
-- Produces the output 'abcd'
read_amount := 10;
dbms_lob.read(clob_updated, read_amount, read_offset, buffer);
dbms_output.put_line('clob_updated value: ' || buffer);
-- Produces the output 'abcd'
-- At time t4:
write_amount := 3;
write_offset := 5;
buffer := 'efg';
dbms_lob.write(clob_updated, write_amount, write_offset,
buffer);
read_amount := 10;
dbms_lob.read(clob_updated, read_amount, read_offset, buffer);
dbms_output.put_line('clob_updated value: ' || buffer);
-- Produces the output 'abcdefg'
-- At time t5:
read_amount := 10;
dbms_lob.read(clob_selected, read_amount, read_offset,
buffer);
dbms_output.put_line('clob_selected value: ' || buffer);
-- Produces the output 'abcd'
-- At time t6:
read_amount := 10;
dbms_lob.read(clob_copied, read_amount, read_offset, buffer);
dbms_output.put_line('clob_copied value: ' || buffer);
-- Produces the output 'abcd'
END;
/
When you update the value of the internal LOB through the LOB locator (L1), L1 (that is, the locator itself) is updated to contain the current snapshot environment as of the point in time after the operation was completed on the LOB value through the locator L1. L1 is then termed an updated locator. This operation allows you to see your own changes to the LOB value on the next read through the same locator, L1.
Note that the snapshot environment in the locator is not updated if the locator is used to merely read the LOB value. It is only updated when you modify the LOB value through the locator via the PL/SQL DBMS_LOB package or the OCI LOB APIs.
Any committed updates made by a different transaction are seen by L1 only if your transaction is a read-committed transaction and if you use L1 to update the LOB value after the other transaction committed. Note that when you update an internal LOB's value, the modification is always made to the most current LOB value.
Updating the value of the internal LOB through the OCI LOB APIs or the PL/SQL DBMS_LOB package can be thought of as updating the LOB value and then reselecting the locator that refers to the new LOB value. Updating the LOB value through SQL is merely an UPDATE statement. It is up to you to do the reselect of the LOB locator or use the RETURNING clause in the UPDATE statement (see the PL/SQL User's Guide and Reference) so that the locator can see the changes made by the UPDATE statement. Unless you reselect the LOB locator or use the RETURNING clause, you may think you are reading the latest value when this is not the case. For this reason you should avoid mixing SQL DML with OCI and DBMS_LOB piecewise operations.
Using lob_table as defined above, a CLOB locator is created: clob_selected.
INSERT INTO lob_table values (1, NULL, 'abcd', NULL, NULL);
COMMIT;
DECLARE
num_var INTEGER;
clob_selected CLOB;
read_amount INTEGER;
read_offset INTEGER;
buffer VARCHAR2(20);
BEGIN
-- At time t1:
SELECT c_lob INTO clob_selected
FROM lob_table
WHERE key_value = 1;
read_amount := 10;
read_offset := 1;
dbms_lob.read(clob_selected, read_amount, read_offset,
buffer);
dbms_output.put_line('clob_selected value: ' || buffer);
-- Produces the output 'abcd'
-- At time t2:
UPDATE lob_table SET c_lob = empty_clob()
WHERE key_value = 1;
-- although the most current current LOB value is now empty,
-- clob_selected still sees the LOB value as of the point
-- in time of the SELECT
read_amount := 10;
dbms_lob.read(clob_selected, read_amount, read_offset,
buffer);
dbms_output.put_line('clob_selected value: ' || buffer);
-- Produces the output 'abcd'
-- At time t3:
SELECT c_lob INTO clob_selected FROM lob_table WHERE
key_value = 1;
-- the SELECT allows clob_selected to see the most current
-- LOB value
read_amount := 10;
dbms_lob.read(clob_selected, read_amount, read_offset,
buffer);
-- ERROR: ORA-01403: no data found
END;
/
All in all, we advise that you avoid updating the same LOB with different locators. You will avoid many pitfalls if you use only one locator to update the same LOB value.
Using lob_table as defined above, two CLOBs are created as potential locators: clob_updated and clob_copied.
INSERT INTO lob_table
VALUES (1, NULL, 'abcd', NULL, NULL);
COMMIT;
DECLARE
num_var INTEGER;
clob_updated CLOB;
clob_copied CLOB;
read_amount INTEGER; ;
read_offset INTEGER;
write_amount INTEGER;
write_offset INTEGER;
buffer VARCHAR2(20);
BEGIN
-- At time t1:
SELECT c_lob INTO clob_updated FROM lob_table
WHERE key_value = 1
FOR UPDATE;
-- At time t2:
clob_copied := clob_updated;
-- after the assign, clob_copied and clob_updated see the same
-- LOB value
read_amount := 10;
read_offset := 1;
dbms_lob.read(clob_updated, read_amount, read_offset, buffer);
dbms_output.put_line('clob_updated value: ' || buffer);
-- Produces the output 'abcd'
read_amount := 10;
dbms_lob.read(clob_copied, read_amount, read_offset, buffer);
dbms_output.put_line('clob_copied value: ' || buffer);
-- Produces the output 'abcd'
-- At time t3:
write_amount := 3;
write_offset := 5;
buffer := 'efg';
dbms_lob.write(clob_updated, write_amount, write_offset,
buffer);
read_amount := 10;
dbms_lob.read(clob_updated, read_amount, read_offset, buffer);
dbms_output.put_line('clob_updated value: ' || buffer);
-- Produces the output 'abcdefg'
-- At time t4:
read_amount := 10;
dbms_lob.read(clob_copied, read_amount, read_offset, buffer);
dbms_output.put_line('clob_copied value: ' || buffer);
-- Produces the output 'abcd'
-- At time t5:
clob_copied := clob_updated;
read_amount := 10;
dbms_lob.read(clob_copied, read_amount, read_offset, buffer);
dbms_output.put_line('clob_copied value: ' || buffer);
-- Produces the output 'abcdefg'
END;
/
When a LOB locator is used as the source to update another internal LOB (as in a SQL INSERT or UPDATE statement, the DBMS_LOB.COPY routine, and so on), the snapshot environment in the source LOB locator determines the LOB value that is used as the source. If the source locator (for example L1) is a read consistent locator, then the LOB value as of the point in time of the SELECT of L1 is used. If the source locator (for example L2) is an updated locator, then the LOB value associated with L2's snapshot environment at the time of the operation is used.
Using lob_table as defined above, three CLOBs are created as potential locators: clob_selected, clob_updated and clob_copied.
INSERT INTO lob_table
VALUES (1, NULL, 'abcd', NULL, NULL);
COMMIT;
DECLARE
num_var INTEGER;
clob_selected CLOB;
clob_updated CLOB;
clob_copied CLOB;
read_amount INTEGER;
read_offset INTEGER;
write_amount INTEGER;
write_offset INTEGER;
buffer VARCHAR2(20);
BEGIN
-- At time t1:
SELECT c_lob INTO clob_updated FROM lob_table
WHERE key_value = 1
FOR UPDATE;
read_amount := 10;
read_offset := 1;
dbms_lob.read(clob_updated, read_amount, read_offset, buffer);
dbms_output.put_line('clob_updated value: ' || buffer);
-- Produces the output 'abcd'
-- At time t2:
clob_copied := clob_updated;
-- At time t3:
write_amount := 3;
write_offset := 5;
buffer := 'efg';
dbms_lob.write(clob_updated, write_amount, write_offset,
buffer);
read_amount := 10;
dbms_lob.read(clob_updated, read_amount, read_offset, buffer);
dbms_output.put_line('clob_updated value: ' || buffer);
-- Produces the output 'abcdefg'
-- note that clob_copied doesn't see the write made before
-- clob_updated
-- At time t4:
read_amount := 10;
dbms_lob.read(clob_copied, read_amount, read_offset, buffer);
dbms_output.put_line('clob_copied value: ' || buffer);
-- Produces the output 'abcd'
-- At time t5:
-- the insert uses clob_copied view of the LOB value which does
-- not include clob_updated changes
INSERT INTO lob_table values (2, NULL, clob_copied, NULL,
NULL) RETURNING c_lob INTO clob_selected;
read_amount := 10;
dbms_lob.read(clob_selected, read_amount, read_offset,
buffer);
dbms_output.put_line('clob_selected value: ' || buffer);
-- Produces the output 'abcd'
END;
/
Modifying an internal LOB's value through the LOB locator via DBMS_LOB, OCI, or SQL INSERT or UPDATE statements changes the locator from a read consistent locator to an updated locator. Further, the INSERT or UPDATE statement automatically starts a transaction and locks the row (See Oracle8 Server SQL Reference). Once this has occurred, the locator may not be used outside the current transaction. In other words, LOB locators cannot span transactions.
Using lob_table as defined above, a CLOB locator is created: clob_updated.
INSERT INTO lob_table
VALUES (1, NULL, 'abcd', NULL, NULL);
COMMIT;
DECLARE
num_var INTEGER;
clob_updated CLOB;
read_amount INTEGER;
read_offset INTEGER;
write_amount INTEGER;
write_offset INTEGER;
buffer VARCHAR2(20);
BEGIN
-- At time t1:
SELECT c_lob
INTO clob_updated
FROM lob_table
WHERE key_value = 1
FOR UPDATE;
read_amount := 10;
read_offset := 1;
dbms_lob.read(clob_updated, read_amount, read_offset,
buffer);
dbms_output.put_line('clob_updated value: ' || buffer);
-- This produces the output 'abcd'
-- At time t2:
write_amount := 3;
write_offset := 5;
buffer := 'efg';
dbms_lob.write(clob_updated, write_amount, write_offset,
buffer);
read_amount := 10;
dbms_lob.read(clob_updated, read_amount, read_offset,
buffer);
dbms_output.put_line('clob_updated value: ' || buffer);
-- This produces the output 'abcdefg'
-- At time t3:
COMMIT;
-- At time t4:
read_amount := 10;
dbms_lob.read(clob_updated , read_amount, read_offset,
buffer);
-- ERROR: ORA-22990: LOB locators cannot span transactions
END;
/
The internal LOB types - BLOB, CLOB, and NCLOB - use copy semantics, as opposed to the reference semantics which apply to BFILEs. When a BLOB, CLOB, or NCLOB is copied from one row to another row in the same table or in a different table, the actual LOB value is copied, not just the LOB locator. For example, assuming lob_table1 and lob_table2 have schemas identical to lob_table described above, the statement
INSERT INTO lob_table1 (key_value, b_lob)
(SELECT key_value, b_lob FROM lob_table2 T2
WHERE T2.key_value = 101);
creates a new LOB locator in the table lob_table1, and copies the LOB data from lob_table2 to the location pointed to by a new LOB locator which is inserted into table lob_table1.
BFILE types use reference semantics instead of copy semantics. This means that only the BFILE locator is copied from one row to another row. Put another way: it is not possible to make a copy of an external LOB value without issuing an operating system command to copy the operating system file.
You delete a row that contains an internal LOB column / attribute by (a) using the explicit SQL DML command DELETE, or (b) using a SQL DDL command that effectively deletes it, such as DROP TABLE, TRUNCATE TABLE, or DROP TABLESPACE. In either case you delete the LOB locator and the LOB value as well.
But note that due to the consistent read mechanism, the old LOB value remains accessible with the value that it had at the time of execution of the statement (such as SELECT) that returned the LOB locator (see "Read consistent locators" on page 6-21 for more information).
Of course, two distinct rows of a table with a LOB column have their own distinct LOB locators and distinct copies of the LOB values irrespective of whether the LOB values are the same or different. This means that deleting one row has no effect on the data or LOB locator in another row even if one LOB was originally copied from another.
The LOB value in a BFILE, however, does not get deleted by using SQL DDL or SQL DML commands. Only the BFILE locator is deleted. Deletion of a record containing a BFILE column amounts to de-linking that record from an existing file, not deletion of the physical operating system file itself. An SQL DELETE statement on a particular row deletes the BFILE locator for the particular row, thereby removing the reference to the operating system file.
The following DELETE, DROP TABLE, or TRUNCATE TABLE statements delete the row, and hence the BFILE locator that refers to image1.gif, but leave the operating system file undeleted in the filesystem.
DELETE FROM lob_table
WHERE key_value = 21;
DROP TABLE lob_table;
TRUNCATE TABLE lob_table;
When you create an object in the object cache that contains an internal LOB attribute, the LOB attribute is implicitly set to empty. You may not use this empty LOB locator to write data to the LOB. You must first flush the object, thereby inserting a row into the table and creating an empty LOB - that is, a LOB with 0 length. Once the object is refreshed in the object cache (use OCI_PIN_LATEST), the real LOB locator is read into the attribute, and you can then call the OCI LOB API to write data to the LOB.
When creating an object with a BFILE attribute, the BFILE is set to NULL. It must be updated with a valid directory alias and filename before reading from the file.
When you copy one object to another in the object cache with a LOB locator attribute, only the LOB locator is copied. This means that the LOB attribute in these two different objects contain exactly the same locator which refers to one and the same LOB value. Only when the target object is flushed is a separate, physical copy of the LOB value made, which is distinct from the source LOB value. See "Example of a Read Consistent Locator" on page 6-22 for a description of what version of the LOB value will be seen by each object if a write is performed through one of the locators.
Therefore, in cases where you want to modify the LOB that was the target of the copy, you must flush the target object, refresh the target object, and then write to the LOB through the locator attribute.
Oracle8 provides a LOB buffering subsystem (LBS) for advanced OCI based applications such as DataCartridges, Web servers, and other client-based applications that need to buffer the contents of one or more LOBs in the client's address space. The client-side memory requirement for the buffering subsystem during its maximum usage is 512K bytes. It is also the maximum amount that you can specify for a single read or write operation on a LOB that has been enabled for buffered access.
The advantages of buffering, especially for applications that perform a series of small reads and writes (often repeatedly) to specific regions of the LOB, are two fold:
However, the following caveats hold for buffered LOB operations:
You could potentially do this by using an SQL statement to update the server-based LOB. Oracle8 cannot distinguish, and hence prevent, such an operation. This will seriously affect the correctness and integrity of your application.
Once the locator becomes an updated locator by virtue of its being used for a buffered write, it will always provide access to the most up-to-date version of the LOB as seen through the buffering subsystem (see "Updated locators" on page 6-24). Buffering also imposes an additional significance to this updated locator - all further buffered writes to the LOB can be done only through this updated locator. Oracle8 will return an error if you attempt to write to the LOB through other locators enabled for buffering.
Similarly, if you SELECT into a locator for which buffering was originally enabled, the locator becomes overwritten with the new locator value, thereby turning buffering off.
For Oracle 8.0, each user session has a fixed page pool of 16 pages, which are to be shared by all LOBs accessed in buffering mode from that session. Each page has a fixed size of 32K bytes (not characters). A LOB's buffer consists of one or more of these pages, up to a maximum of 16 per session. The maximum amount that you can specify for any given buffered read or write operation is 512K bytes.
Consider that a LOB is divided into fixed-size, logical regions. Each page is mapped to one of these fixed size regions, and is in essence, their in-memory copy. Depending on the input offset and amount specified for a read or write operation, Oracle8 allocates one or more of the free pages in the page pool to the LOB's buffer. A free page is one that has not been read or written into by a buffered read or write operation. For example, for an input offset of 1000 and amount 30000, Oracle8 reads the first 32K byte region of the LOB into a page in the LOB's buffer. For an input offset of 33000 and amount 30000, the second 32K region of the LOB is read into a page. For an input offset of 1000, and amount 35000, the LOB's buffer will contain two pages - the first mapped to the region 1 - 32K, and the second to the region 32K+1 - 64K of the LOB.
This mapping between a page and the LOB region is temporary until Oracle8 maps another region to the page. When you attempt to access a region of the LOB that is not already available in full in the LOB's buffer, Oracle8 allocates any available free page(s) from the page pool to the LOB's buffer. If there are no free pages available in the page pool, Oracle8 reallocates the pages as follows. It ages out the least recently used page among the unmodified pages in the LOB's buffer and reallocates it for the current operation.
If no such page is available in the LOB's buffer, it ages out the least recently used page among the unmodified pages of other buffered LOBs in the same session. Again, if no such page is available, then it implies that all the pages in the page pool are dirty (i.e. they have been modified), and either the currently accessed LOB, or one of the other LOBs, need to be flushed. Oracle8 notifies this condition to the user as an error. Oracle8 never flushes and reallocates a dirty page implicitly - you can either flush them explicitly, or discard them by disabling buffering on the LOB.
To illustrate the above discussion, consider two LOBs being accessed in buffered mode - L1 and L2, each with buffers of size 8 pages. Assume that 6 of the 8 pages in L1's buffer are dirty, with the remaining 2 contain unmodified data read in from the server. Assume similar conditions in L2's buffer. Now, for the next buffered operation on L1, Oracle8 will reallocate the least recently used page from the two unmodified pages in L1's buffer. Once all the 8 pages in L1's buffer are used up for LOB writes, Oracle8 can service two more operations on L1 by allocating the two unmodified pages from L2's buffer using the least recently used policy. But for any further buffered operations on L1, Oracle8 returns an error.
In flushing the LOB, you must use the updated locator because the locator gets updated with the new snapshot environment. Trying to flush a read consistent locator will generate an error. The reason for this is that you would lose the consistent read version if this were overwritten. Flush writes the dirty pages in the LOB's buffer to the server based LOB, turns the status of these pages back from dirty to unmodified, and resets the updated locator to be a read consistent locator. Because it is now a read consistent locator, it can be assigned to another locator.
Also, the unmodifed pages can be aged out if necessary. Under default mode, the flush operation retains the data in these pages without zeroing them out, or refreshing the pages through a roundtrip read from the server. This way, a subsequent write operation using this locator to the same region of the LOB will map to the same page. However, you could set the flag parameter in OCILobFlushBuffer() to OCI_LOB_BUFFER_FREE to free the pages to the page pool, and the memory to the client address space.
Transactions involving buffered LOB operations cannot migrate across user sessions -- LBS is single user, single threaded. As in the cae of all other locators, locators enabled for LOB buffering cannot span transactions. A locator that is enabled for buffering can only be used with the following OCI APIs:
OCILobRead(), OCILobWrite(), OCILobAssign(), OCILobIsEqual(), OCILobLocatorIsInit(), OCILobLocatorSize(), OCILobCharSetId(), OCILobCharSetForm().
The following OCI APIs will return errors if used with a locator enabled for buffering:
OCILobCopy(), OCILobAppend(), OCILobErase(), OCILobGetLength(), OCILobTrim().
These APIs will also return errors when used with a locator which has not been enabled for buffering, but the LOB that the locator represents is already being accessed in buffered mode through some other locator.
If you want to access the current state of the LOB after writing to the LOB buffers, (a) flush the LOB, and (b) assign the locator (L1) used for flushing and updating to another locator (L2). L2 now becomes a read consistent locator with which you are able to access the changes made through L1 up until the time of the flush, but not after! This assignment avoids incurring a roundtrip to the server to reselect the locator into L2.
Finally, it is very important to note that you must flush a LOB that has been updated through the LBS:
Note: When the external callout is called from a PL/SQL block and the locator is passed as a parameter, all buffering operations, including the enable call, should be made within the callout itself. In other words, we recommend that you adhere to the following sequence:
- call the external callout,
- enable the locator for buffering,
- read/write using the locator,
- flush the LOB,
- disable the locator for buffering, and
- return to the calling function/procedure/method in PL/SQL.
Remember that Oracle8 never implicitly flushes the LOB.
Example of LOB Buffering
The following pseudocode for an OCI program based on the lob_table schema briefly explains the concepts listed above.
OCI_BLOB_buffering_program()
{
int amount;
int offset;
OCILobLocator lbs_loc1, lbs_loc2, lbs_loc3;
void *buffer;
int bufl;
-- Standard OCI initialization operations - logging on to
-- server, creating and initializing bind variables etc.
init_OCI();
-- Establish a savepoint before start of LBS operations
exec_statement("savepoint lbs_savepoint");
-- Initialize bind variable to BLOB columns from buffered
-- access:
exec_statement("select b_lob into lbs_loc1 from lob_table
where key_value = 12");
exec_statement("select b_lob into lbs_loc2 from lob_table
where key_value = 12 for update");
exec_statement("select b_lob into lbs_loc2 from lob_table
where key_value = 12 for update");
-- Enable locators for buffered mode access to LOB:
OCILobEnableBuffering(lbs_loc1);
OCILobEnableBuffering(lbs_loc2);
OCILobEnableBuffering(lbs_loc3);
-- Read 4K bytes through lbs_loc1 starting from offset 1:
amount = 4096; offset = 1; bufl = 4096;
OCILobFileRead(.., lbs_loc1, offset, &amount, buffer, bufl,
..);
if (exception)
goto exception_handler;
-- This will read the first 32K bytes of the LOB from
-- the server into a page (call it page_A) in the LOB's
-- client-side buffer.
-- lbs_loc1 is a read consistent locator.
-- Write 4K of the LOB throgh lbs_loc2 starting from
-- offset 1:
amount = 4096; offset = 1; bufl = 4096;
buffer = populate_buffer(4096);
OCILobFileWrite(.., lbs_loc2, offset, amount, buffer,
bufl, ..);
if (exception)
goto exception_handler;
-- This will read the first 32K bytes of the LOB from
-- the server into a new page (call it page_B) in the
-- LOB's buffer, and modify the contents of this page
-- with input buffer contents.
-- lbs_loc2 is an updated locator.
-- Read 20K bytes through lbs_loc1 starting from
-- offset 10K
amount = 20480; offset = 10240;
OCILobFileRead(.., lbs_loc1, offset, &amount, buffer,
bufl, ..);
if (exception)
goto exception_handler;
-- Read directly from page_A into the user buffer.
-- There is no round-trip to the server because the
-- data is already in the client-side buffer.
-- Write 20K bytes through lbs_loc2 starting from offset
-- 10K
amount = 20480; offset = 10240; bufl = 20480;
buffer = populate_buffer(20480);
OCILobFileWrite(.., lbs_loc2, offset, amount, buffer,
bufl, ..);
if (exception)
goto exception_handler;
-- The contents of the user buffer will now be written
-- into page_B without involving a round-trip to the
-- server. This avoids making a new LOB version on the
-- server and writing redo to the log.
-- The following write through lbs_loc3 will also
-- result in an error:
amount = 20000; offset = 1000; bufl = 20000;
buffer = populate_buffer(20000);
OCILobFileWrite(.., lbs_loc3, offset, amount, buffer,
bufl, ..);
if (exception)
goto exception_handler;
-- No two locators can be used to update a buffered LOB
-- through the buffering subsystem
-- The following update through lbs_loc3 will also
-- result in an error
OCILobFileCopy(.., lbs_loc3, lbs_loc2, ..);
if (exception)
goto exception_handler;
-- Locators enabled for buffering cannot be used with
-- operations like Append, Copy, Trim etc.
-- When done, flush LOB's buffer to the server:
OCILobFlushBuffer(.., lbs_loc2, OCI_LOB_BUFFER_NOFREE);
if (exception)
goto exception_handler;
-- This flushes all the modified pages in the LOB's buffer,
-- and resets lbs_loc2 from updated to read consistent
-- locator. The modified pages remain in the buffer
-- without freeing memory. These pages can be aged
-- out if necessary.
-- Disable locators for buffered mode access to LOB */
OCILobDisableBuffering(lbs_loc1);
OCILobDisableBuffering(lbs_loc2);
OCILobDisableBuffering(lbs_loc3);
if (exception)
goto exception_handler;
-- This disables the three locators for buffered access,
-- and frees up the LOB's buffer resources.
exception_handler:
handle_exception_reporting();
exec_statement("rollback to savepoint lbs_savepoint");
}
A. Since LOBs are big, you can obtain the best performance by reading and writing large chunks of a LOB value at a time. This helps in several respects:
a) If accessing the LOB from the client side and the client is at a different node than the server, large reads/writes reduce network overhead.
b) If using the 'NOCACHE' option, each small read/write incurs an
I/O. Reading/writing large quantities of data reduces the I/O.
c) Writing to the LOB creates a new version of the LOB chunk. Therefore, writing small amounts at a time will incur the cost of a new version for each small write. If logging is on, the chunk is also stored in the redo log.
B. If you need to read/write small pieces of LOB data on the client, use LOB buffering -- see OCILobEnableBuffering(), OCILobDisableBuffering(), OCILobFlushBuffer(), OCILobWrite(), OCILobRead(). Basically, turn on LOB buffering before reading/writing small pieces of LOB data. See "LOB Buffering Subsystem" on page 6-35 for more information on LOB buffering.
C. Use OCILobWrite() and OCILobRead() with a callback so data is streamed to/from the LOB. Make sure that the length of the entire write is set in the 'amount' parameter on input.
D. Use a checkout/checkin model for lobs. LOBs are optimized for the following:
(b) Copy the entire LOB data to the client, modify the LOB data on the client side, copy the entire LOB data back to the database.
Varying width character data is not supported for BLOBs, CLOBs and NCLOBs. However, BLOBs can contain any data. Since CLOBs/NCLOBs cannot store varying width character sets, you may be tempted to store varying width characters in a BLOB and do the character set conversion yourself. The drawback is that you need to do these conversions, and also that the offset and amount parameters are in terms of bytes instead of characters. So, the danger is that you could retrieve text information from the BLOB but cut a varying width character in half because the byte amount you specified was not correct. Consequently, we caution against taking this course of action.
BFILEs likewise can contain any data including text. But, once again, in storing the text, you will need to do your own character set conversions and offset and amount parameters will be in bytes.
Note that:
(a) If the database character set is varying width, and a user other than
the system user tries to create a table with a CLOB column, the create
will fail.
(b) If the database character set is varying width, and the system user
tries to create a table with a CLOB column, the create will succeed. However,
subsequent inserts into the table will fail.
The same holds true for NCLOBs and the database national character set.
Although not explicitly marked, this section is organized on the following basis.
You can use the special functions EMPTY_BLOB () and EMPTY_CLOB () in INSERT or UPDATE statements of SQL DML to initialize a NULL or non-NULL internal LOB to empty. These are available as special functions in Oracle8 SQL DML, and are not part of the DBMS_LOB package.
Before you can start writing data to an internal LOB using OCI or the DBMS_LOB package, the LOB column must be made non-null, that is, it must contain a locator that points to an empty or populated LOB value. You can initialize a BLOB column's value to empty by using the function EMPTY_BLOB() in the VALUES clause of an INSERT statement. Similarly, a CLOB or NCLOB column's value can be initialized by using the function EMPTY_CLOB().
Syntax
FUNCTION EMPTY_BLOB() RETURN BLOB; FUNCTION EMPTY_CLOB() RETURN CLOB;
Parameters
Return Values
EMPTY_BLOB() returns an empty locator of type BLOB and EMPTY_CLOB() returns an empty locator of type CLOB, which can also be used for NCLOBs.
Pragma
Exceptions
An exception is raised if you use these functions anywhere but in the VALUES clause of a SQL INSERT statement or as the source of the SET clause in a SQL UPDATE statement.
Examples
The following example shows EMPTY_BLOB() usage with SQL DML:
INSERT INTO lob_table VALUES (1001, EMPTY_BLOB(), 'abcde', NULL); UPDATE lob_table SET c_lob = EMPTY_CLOB() WHERE key_value = 1001; INSERT INTO lob_table VALUES (1002, NULL, NULL, NULL);
The following example shows the correct and erroneous usage of EMPTY_BLOB() and EMPTY_CLOB () in PL/SQL programs:
DECLARE
loba BLOB;
lobb CLOB;
read_offset INTEGER;
read_amount INTEGER;
rawbuf RAW(20);
charbuf VARCHAR2(20);
BEGIN
loba := EMPTY_BLOB();
read_amount := 10; read_offset := 1;
-- the following read will fail
dbms_lob.read(loba, read_amount, read_offset, rawbuf);
-- the following read will succeed;
UPDATE lob_table SET c_lob = EMPTY_CLOB() WHERE key_value =
1002 RETURNING c_lob INTO lobb;
dbms_lob.read(lobb, read_amount, read_offset, charbuf);
dbms_output.put_line('lobb value: ' || charbuf);
The BFILENAME () function should be called as part of SQL INSERT to initialize a BFILE column or attribute for a particular row by associating it with a physical file in the server's filesystem.
The DIRECTORY object represented by the directory_alias parameter to this function must already be defined using SQL DDL before this function is called in SQL DML or a PL/SQL program. However, BFILENAME() does not validate privileges on this DIRECTORY object, or check if the physical directory that the DIRECTORY object represents actually exists. These checks are performed only during file access using the BFILE locator that is initialized by the BFILENAME() function.
You can use BFILENAME() as part of a SQL INSERT and UPDATE statement to initialize a BFILE column. You can also use it to initialize a BFILE locator variable in a PL/SQL program, and use that locator for file operations. However, if the corresponding directory alias and/or filename does not exist, then PL/SQL DBMS_LOB routines that use this variable will generate errors.
The 'directory_alias' parameter in the BFILENAME() function must be specified taking case-sensitivity of the directory name into consideration (see "DIRECTORY Name Specification" on page 6-14). This is described in the examples.
Syntax
FUNCTION BFILENAME(directory_alias IN VARCHAR2,
filename IN VARCHAR2)
RETURN BFILE;
See "DIRECTORY Name Specification" on page 6-14 for information about the use of uppercase letters in the directory name. See OCILobFileSetName() in Programmer's Guide to the Oracle Call Interface, Volume II: OCI Reference for an equivalent OCI based routine.
Parameters
| Parameter Name | Meaning |
|---|---|
|
directory_alias |
The name of the DIRECTORY object that was created using the CREATE DIRECTORY command. |
|
filename |
The name of the file. |
Return Values
Pragmas
Exceptions
Example
To access a file 'scott.dat' located in Scott_Dir, and file 'mary.dat' located in Mary_Dir, the BFILE locators must be initialized as shown below.
DECLARE
fil_1, fil_2 BFILE;
result INTEGER;
BEGIN
fil_1 := BFILENAME(`SCOTT_DIR', `scott.dat');
fil_2 := BFILENAME(`Mary_Dir', `mary.dat');
DBMS_LOB.FILEOPEN(fil_1);
DBMS_LOB.FILEOPEN(fil_2);
result := DBMS_LOB.COMPARE(fil_1, fil_2);
IF (result != 0)
THEN
DBMS_OUTPUT.PUT_LINE(`The two files are different');
END IF;
DBMS_LOB.FILECLOSE(fil_1);
DBMS_LOB.FILECLOSE(fil_2);
-- FILEOPEN will fail with the following initialization (in
lowercase)
fil_1 := BFILENAME(`scott_dir', `scott.dat');
DBMS_LOB.FILEOPEN(fil_1);
-- this is an error
END;
INSERT INTO lob_table VALUES (21, NULL, NULL, NULL,
BFILENAME(`SCOTT_DIR',`scott.dat');
INSERT INTO lob_table VALUES (12, NULL, NULL, NULL,
BFILENAME(`Mary_Dir',`mary.dat');
DECLARE
fil_1, fil_2 BFILE;
result INTEGER;
BEGIN
SELECT f_lob INTO fil_1 FROM lob_table WHERE key_value = 21;
SELECT f_lob INTO fil_2 FROM lob_table WHERE key_value = 12;
DBMS_LOB.FILEOPEN(fil_1);
DBMS_LOB.FILEOPEN(fil_2);
result := DBMS_LOB.COMPARE(fil_1, fil_2);
IF (result != 0)
THEN
DBMS_OUTPUT.PUT_LINE(`The two files are different');
END IF;
DBMS_LOB.FILECLOSE(fil_1);
DBMS_LOB.FILECLOSE(fil_2);
END;
Note: See also DMBS_LOB.FILEGETNAME().
The OCI includes functions that you can use to access data stored in BLOBs, CLOBs, NCLOBs, and BFILEs. These functions are mentioned briefly below. For detailed documentation, including parameters, parameter types, return values, and example code, see the Programmer's Guide to the Oracle Call Interface, Volume II: OCI Reference.
The following chart compares the two interfaces in terms of LOB access.
The DBMS_LOB package provides routines to access BLOBs, CLOBs, NCLOBs, and BFILEs. You can use DBMS_LOB for access and manipulation of specific parts of a LOB, as well as complete LOBs. DBMS_LOB can read as well as modify BLOBs, CLOBs, and NCLOBs, and provides read-only operations on BFILEs.
All DBMS_LOB routines work based on LOB locators. For the successful completion of DBMS_LOB routines, you must provide an input locator that represents a LOB that already exists in the database tablespaces or external filesystem.
For internal LOBs, you must first use SQL DDL to define tables that contain LOB columns, and subsequently SQL DML to initialize or populate the locators in these LOB columns. See "LOB Locator Operations" on page 6-19. For external LOBs, you must ensure that a DIRECTORY object that represents a valid, existing physical directory has been defined, and physical files exist with read permission for Oracle. See "BFILE Security" on page 6-14 for more details.
Once the LOBs are defined and created, you may then SELECT a LOB locator into a local PL/SQL LOB variable and use this variable as an input parameter to DBMS_LOB for access to the LOB value. Examples provided with each DBMS_LOB routine will illustrate this in the following sections.
The routines that can modify BLOB, CLOB, and NCLOB values are:
The routines that read or examine LOB values are:
The read-only routines specific to BFILEs are:
Parameters for the DBMS_LOB routines use the datatypes:
The DBMS_LOB package defines no special types. NCLOB is a special case of CLOBs for fixed-width, multi-byte national character sets. The clause 'ANY_CS' in the specification of DBMS_LOB routines for CLOBs allows them to accept a CLOB or NCLOB locator variables as inputs. For more details, see "LOB Datatypes" in the Oracle8 Server SQL Reference.
The DBMS_LOB package defines the following constants.
LOBMAXSIZE 4294967295 FILE_READONLY 0
The maximum LOB size supported in Oracle 8.0 is 4 Gigabytes (232). However, the amount and offset parameters of the package can have values in the range 1 through 4294967295 (232-1).
The PL/SQL 3.0 language specifies the maximum size of a RAW or VARCHAR2 variable to be 32767 bytes.
Note: The value 32767 bytes is represented by MAXBUFSIZE in the following sections.
A DBMS_LOB function or procedure can raise any of the named exceptions shown in Table 6-2.
access_error 22925 "operation would exceed maximum size allowed for a LOB"
noexist_directory 22285 "%s failed - directory does not exist"
nopriv_directory 22286 "%s failed - insufficient privileges on directory"
invalid_directory 22287 "%s failed - invalid or modified directory"
invalid_operation 22288 "%s operation failed"
unopened_file 22289 "cannot perform %s operation on an unopened file"
open_toomany 22290 "%s failed - max limit reached on number of open files"
DBMS_LOB functions return a NULL value if any of the input parameters to these routines are NULL or invalid, whereas DBMS_LOB procedures will raise exceptions. This behavior is consistent with Oracle8 SQL functions, and procedures in other built-in PL/SQL packages in Oracle8.
This section describes the security domain for DBMS_LOB routines operating on internal LOBs (i.e. BLOB, CLOB and NCLOB) when you are using the Oracle server .
Note for Oracle ServerUsers: Any DBMS_LOB routine called from an anonymous PL/SQL block is executed using the privileges of the current user. Any DBMS_LOB routine called from a stored procedure is executed using the privileges of the owner of the stored procedure.
You can provide secure access to BFILEs using the DIRECTORY feature discussed in "BFILENAME() Function" on page 6-47.
1. Length, amount and offset parameters are specified in terms of bytes for BLOBs and BFILES, and characters for CLOBs and NCLOBs. 2. Note that PL/SQL 3.0 language specifies that constraints for both RAW and VARCHAR2 buffers are specified in terms of bytes. For example, if you declare a variable to be
charbuf VARCHAR2(3000)
charbuf can hold 3000 single byte characters or a 1500 2-byte fixed width characters. This has an important consequence for DBMS_LOB routines for CLOBs and NCLOBs.
3. You must ensure that the character set of the VARCHAR2 buffer in a DBMS_LOB routine for CLOBs exactly matches that of the CLOB. The package specification partially ensures this with the %CHARSET clause, but in certain cases where the fixed-width character set is actually a subset of a varying width character set, it may not be possible to enforce this.
Hence, it is your responsibility to provide a buffer with the correct character set and enough buffer size for holding all the characters. No translation on the basis of session initialization parameters is performed.
4. Only positive, non-zero values (i.e. a value greater
than or equal to 1) are allowed for the AMOUNT and OFFSET parameters. This
implies that: negative offsets and ranges observed in Oracle SQL string
functions and operators are not allowed.
5. Unless otherwise stated, the default value for an
offset parameter is 1, which indicates the first byte in the BLOB or BFILE
data, and the first character in the CLOB or NCLOB value. No default values
are specified for the AMOUNT parameter - you have to input the values explicitly.
6. You are responsible for locking the row containing
the destination internal LOB before calling APPEND, COPY, ERASE, TRIM,
or WRITE. These routines do not implicitly lock the row containing the
LOB.
In the event of normal program termination, proper file closure ensures that the number of files that are open simultaneously in the session remains less than SESSION_MAX_OPEN_FILES.
In the event of abnormal program termination from a PL/SQL program, it is imperative that you provide an exception handler that ensures closure of all files opened in that PL/SQL program. This is necessary because, once an exception occurs, only the exception handler will have access to the BFILE variable in its most current state (see "Closing BFILEs after Program Termination" on page 6-18). Once the exception transfers program control outside the PL/SQL program block, all references to the open BFILEs are lost. The result is a larger open file count which may or may not exceed the SESSION_MAX_OPEN_FILES value.
For example, consider a READ operation past the end of the BFILE value, which generates a NO_DATA_FOUND exception.
SVRMGR> #-------------------------------
SVRMGR> # dbms_lob.read past End of file
SVRMGR> #-------------------------------
SVRMGR> DECLARE
fil bfile;
pos INTEGER;
amt binary_INTEGER;
buf RAW(40);
BEGIN
SELECT f_lob INTO fil FROM lob_table WHERE key_value = 21;
dbms_lob.FILEOPEN(fil, dbms_lob.file_readonly);
amt := 40; pos := 1 + dbms_lob.getlength(fil); buf := '';
dbms_lob.read(fil, amt, pos, buf);
dbms_output.put_line('Read F1 past EOF: '||
utl_raw.cast_to_varchar2(buf));
dbms_lob.fileclose(fil);
END;
ORA-01403: no data found
ORA-06512: at "SYS.DBMS_LOB", line 373
ORA-06512: at line 10
Once the exception has occurred, the BFILE locator variable fil
goes out of scope, and no further operations on the file can be
done using that variable. So the solution is to use an exception
handler as shown below:
SVRMGR> #-------------------------------
SVRMGR> # dbms_lob.read past End of file
SVRMGR> #-------------------------------
SVRMGR> DECLARE
fil bfile;
pos INTEGER;
amt binary_INTEGER;
buf RAW(40);
BEGIN
SELECT f_lob INTO fil FROM lob_table WHERE key_value = 21;
dbms_lob.FILEOPEN(fil, dbms_lob.file_readonly);
amt := 40; pos := 1 + dbms_lob.getlength(fil); buf := '';
dbms_lob.read(fil, amt, pos, buf);
dbms_output.put_line('Read F1 past EOF: '||
utl_raw.cast_to_varchar2(buf));
dbms_lob.fileclose(fil);
exception
WHEN no_data_found
then
BEGIN
dbms_output.put_line('End of File reached. Closing file');
dbms_lob.fileclose(fil);
-- or dbms_lob.filecloseall if appropriate
END;
END;
/
Statement processed.
End of File reached. Closing file
In general, it is good coding practice to ensure that files opened in a PL/SQL block using DBMS_LOB are closed before normal/abnormal termination of the block.
You can call the internal APPEND() procedure to append the contents of a source internal LOB to a destination LOB. The procedure appends the complete source LOB. There are two overloaded APPEND() procedures, as shown in the syntax section below.
Note: If you set the source and destination LOB locator variables from the same LOB column, as shown in Example_1b, see "Example of a Read Consistent Locator" on page 6-22 for LOB operations under such a scenario.
Syntax
PROCEDURE APPEND (dest_lob IN OUT BLOB,
src_lob IN BLOB);
PROCEDURE APPEND (dest_lob IN OUT CLOB CHARACTER SET ANY_CS,
src_lob IN CLOB CHARACTER SET
dest_lob%CHARSET);
Parameters
| Parameter Name | Meaning |
|---|---|
|
dest_lob |
The locator for the internal LOB to which the data is to be appended. |
|
src_lob |
The locator for the internal LOB from which the data is to be read. |
Exceptions
VALUE_ERROR, if either the source or the destination LOB is null.
Example
PROCEDURE Example_1a IS
dest_lob, src_lob BLOB;
BEGIN
-- get the LOB locators
-- note that the FOR UPDATE clause locks the row
SELECT b_lob INTO dest_lob
FROM lob_table
WHERE key_value = 12 FOR UPDATE;
SELECT b_lob INTO src_lob
FROM lob_table
WHERE key_value = 21;
DBMS_LOB.APPEND(dest_lob, src_lob);
COMMIT;
EXCEPTION
WHEN some_exception
THEN handle_exception;
END;
PROCEDURE Example_1b IS
dest_lob, src_lob BLOB;
BEGIN
-- get the LOB locators
-- note that the FOR UPDATE clause locks the row
SELECT b_lob INTO dest_lob
FROM lob_table
WHERE key_value = 12 FOR UPDATE;
SELECT b_lob INTO src_lob
FROM lob_table
WHERE key_value = 12;
DBMS_LOB.APPEND(dest_lob, src_lob);
COMMIT;
EXCEPTION
WHEN some_exception
THEN handle_exception;
END;
You can call the COMPARE() function to compare two entire LOBs, or parts of two LOBs. You can only compare LOBs of the same datatype. That is, you compare LOBs of BLOB type with other BLOBs, and CLOBs with CLOBs, and BFILEs with BFILEs. For BFILEs, the file has to be already opened using a successful FILEOPEN() operation for this operation to succeed.
COMPARE() returns zero if the data exactly matches over the range specified by the offset and amount parameters. Otherwise, a non-zero INTEGER is returned.
For fixed-width n-byte CLOBs, if the input amount for COMPARE is specified to be greater than (4294967295/n), then COMPARE matches characters in a range of size (4294967295/n), or Max(length(clob1), length(clob2)), whichever is lesser.
Syntax
FUNCTION COMPARE (
lob_1 IN BLOB,
lob_2 IN BLOB,
amount IN INTEGER := 4294967295,
offset_1 IN INTEGER := 1,
offset_2 IN INTEGER := 1)
RETURN INTEGER;
FUNCTION COMPARE (
lob_1 IN CLOB CHARACTER SET ANY_CS,
lob_2 IN CLOB CHARACTER SET lob_1%CHARSET,
amount IN INTEGER := 4294967295,
offset_1 IN INTEGER := 1,
offset_2 IN INTEGER := 1)
RETURN INTEGER;
FUNCTION COMPARE (
lob_1 IN BFILE,
lob_2 IN BFILE,
amount IN INTEGER,
offset_1 IN INTEGER := 1,
offset_2 IN INTEGER := 1)
RETURN INTEGER;
Parameters
Return Values
Pragma
PRAGMA RESTRICT_REFERENCES(compare, WNDS, WNPS, RNDS, RNPS);
Exceptions
For BFILE operations, UNOPENED_FILE if the file was not opened using the input locator, NOEXIST_DIRECTORY if the directory does not exist, NOPRIV_DIRECTORY if you do not have privileges for the directory, INVALID_DIRECTORY if the directory has been invalidated after the file was opened, INVALID_OPERATION if the file does not exist, or if you do not have access privileges on the file.
Examples
PROCEDURE Example2a IS
lob_1, lob_2 BLOB;
retval INTEGER;
BEGIN
SELECT b_col INTO lob_1 FROM lob_table
WHERE key_value = 45;
SELECT b_col INTO lob_2 FROM lob_table
WHERE key_value = 54;
retval := DBMS_LOB.COMPARE(lob_1, lob_2, 5600, 33482,
128);
IF retval = 0 THEN
; /* process compared code */
ELSE
; /* process not compared code */
END IF;
END;
PROCEDURE Example_2b IS
fil_1, fil_2 BFILE;
retval INTEGER;
BEGIN
SELECT f_lob INTO fil_1 FROM lob_table WHERE key_value = 45;
SELECT f_lob INTO fil_2 FROM lob_table WHERE key_value = 54;
DBMS_LOB.FILEOPEN(fil_1, DBMS_LOB.FILE_READONLY);
DBMS_LOB.FILEOPEN(fil_2, DBMS_LOB.FILE_READONLY);
retval := DBMS_LOB.COMPARE(fil_1, fil_2, 5600,
3348276, 2765612);
IF (retval = 0)
THEN
; /* process compared code */
ELSE
; /* process not compared code */
END IF;
DBMS_LOB.FILECLOSE(fil_1);
DBMS_LOB.FILECLOSE(fil_2);
END;
You can call the COPY() procedure to copy all, or a part of, a source internal LOB to a destination internal LOB. You can specify the offsets for both the source and destination LOBs, and the number of bytes or characters to copy.
If the offset you specify in the destination LOB is beyond the end of the data currently in this LOB, zero-byte fillers or spaces are inserted in the destination BLOB or CLOB respectively. If the offset is less than the current length of the destination LOB, existing data is overwritten.
It is not an error to specify an amount that exceeds the length of the data in the source LOB. Thus, you can specify a large amount to copy from the source LOB which will copy data from the src_offset to the end of the source LOB.
Note: If you set the value of both source and destination LOB locator variables from the same LOB column, as shown in Example_3b, see "Example of a Read Consistent Locator" on page 6-22 for LOB operations under such a scenario.
Syntax
PROCEDURE COPY ( dest_lob IN OUT BLOB, src_lob IN BLOB, amount IN INTEGER, dest_offset IN INTEGER := 1, src_offset IN INTEGER := 1); PROCEDURE COPY ( dest_lob IN OUT CLOB CHARACTER SET ANY_CS, src_lob IN CLOB CHARACTER SET dest_lob%CHARSET, amount IN INTEGER, dest_offset IN INTEGER := 1, src_offset IN INTEGER := 1);
Parameters
Return Value
Pragma
Exceptions
VALUE_ERROR, if any of the input parameters are NULL or invalid. INVALID_ARGVAL, if
Example
PROCEDURE Example_3a IS
lobd, lobs BLOB;
amt INTEGER := 3000;
BEGIN
SELECT b_col INTO lobd
FROM lob_table
WHERE key_value = 12 FOR UPDATE;
SELECT b_col INTO lobs
FROM lob_table
WHERE key_value = 21;
DBMS_LOB.COPY(lobd, lobs, amt);
COMMIT;
EXCEPTION
WHEN some_exception
THEN handle_exception;
END;
PROCEDURE Example_3b IS
lobd, lobs BLOB;
amt INTEGER := 3000;
BEGIN
SELECT b_col INTO lobd
FROM lob_table
WHERE key_value = 12 FOR UPDATE;
SELECT b_col INTO lobs
FROM lob_table
WHERE key_value = 12;
DBMS_LOB.COPY(lobd, lobs, amt);
COMMIT;
EXCEPTION
WHEN some_exception
THEN handle_exception;
END;
You can call the ERASE() procedure to erase an entire internal LOB, or part of an internal LOB. The offset parameter specifies the starting offset for the erasure, and the amount parameter specifies the number of bytes or characters to erase.
When data is erased from the middle of a LOB, zero-byte fillers or spaces are written for BLOBs or CLOBs respectively.
The actual number of bytes or characters erased can differ from the number you specified in the amount parameter if the end of the LOB value is reached before erasing the specified number. The actual number of characters or bytes erased is returned in the amount parameter.
Syntax
PROCEDURE ERASE (
lob_loc IN OUT BLOB,
amount IN OUT INTEGER,
offset IN INTEGER := 1);
PROCEDURE ERASE (
lob_loc IN OUT CLOB,
amount IN OUT INTEGER,
offset IN INTEGER := 1);
Parameters
Return Values
None.
Pragma
None.
Exceptions
VALUE_ERROR, if any input parameter is NULL.
INVALID_ARGVAL, if
Example
PROCEDURE Example_4 IS
lobd BLOB;
amt INTEGER := 3000;
BEGIN
SELECT b_col INTO lobd
FROM lob_table
WHERE key_value = 12 FOR UPDATE;
DBMS_LOB.ERASE(dest_lob, amt, 2000);
COMMIT;
END;
Note: See also DBMS_LOB.TRIM().
You can call the FILECLOSE() procedure to close a BFILE that has already been opened via the input locator. Note that Oracle has only read-only access to BFILEs. This means that BFILEs cannot be written through Oracle.
Syntax
PROCEDURE FILECLOSE (
file_loc IN OUT BFILE);
Parameter
| Parameter Name | Meaning |
|---|---|
|
file_loc |
Locator for the BFILE to be closed. |
Return Values
Pragma
Exceptions
VALUE_ERROR, if NULL input value for file_loc. UNOPENED_FILE if the file was not opened with the input locator, NOEXIST_DIRECTORY if the directory does not exist, NOPRIV_DIRECTORY if you do not have privileges for the directory, INVALID_DIRECTORY if the directory has been invalidated after the file was opened, INVALID_OPERATION if the file does not exist, or you do not have access privileges on the file.
Example
PROCEDURE Example_5 IS
fil BFILE;
BEGIN
SELECT f_lob INTO fil FROM lob_table WHERE key_value = 99;
DBMS_LOB.FILEOPEN(fil);
-- file operations
DBMS_LOB.FILECLOSE(fil);
EXCEPTION
WHEN some_exception
THEN handle_exception;
END;
Note: See also DBMS_LOB.FILEOPEN(), DBMS_LOB.FILECLOSEALL().
You can call the FILECLOSEALL() procedure to close all BFILEs opened in the session.
Syntax
PROCEDURE FILECLOSEALL;
Return Values
Pragma
Exceptions
Example
PROCEDURE Example_6 IS
fil BFILE;
BEGIN
SELECT f_lob INTO fil FROM lob_table WHERE key_value = 99;
DBMS_LOB.FILEOPEN(fil);
-- file operations
DBMS_LOB.FILECLOSEALL;
EXCEPTION
WHEN some_exception
THEN handle_exception;
END;
Note: See also DBMS_LOB.FILEOPEN(), DBMS_LOB.FILECLOSE().
You can call the FILEEXISTS() function to find out if a given BFILE locator points to a file that actually exists on the server's filesystem.
Syntax
FUNCTION FILEEXISTS (
file_loc IN BFILE)
RETURN INTEGER;
Parameter
| Parameter Name | Meaning |
|---|---|
|
file_loc |
Locator for the BFILE. |
Return Values
INTEGER: 1 if the physical file exists, 0 if it does not exist.
Pragma
PRAGMA RESTRICT_REFERENCES(fileexists, WNDS, RNDS, WNPS, RNPS);
Exceptions
NOEXIST_DIRECTORY if the directory does not exist, NOPRIV_DIRECTORY if you do not have privileges for the directory, INVALID_DIRECTORY if the directory has been invalidated after the file was opened.
Example
PROCEDURE Exsmple_7 IS
fil BFILE;
BEGIN
SELECT f_lob INTO fil FROM lob_table WHERE key_value = 12;
IF (DBMS_LOB.FILEEXISTS(fil))
THEN
; -- file exists code
ELSE
; -- file does not exist code
END IF;
EXCEPTION
WHEN some_exception
THEN handle_exception;
END;
Note: See also DBMS_LOB.FILEISOPEN.
You can call the FILEGETNAME() procedure to determine the dir_alias and filename, given a BFILE locator. This function only indicates the directory alias name and filename assigned to the locator, not if the physical file or directory actually exists. Maximum constraint values for the dir_alias buffer is 30, and for the entire pathname is 2000.
Syntax
PROCEDURE FILEGETNAME (
file_loc IN BFILE,
dir_alias OUT VARCHAR2
filename OUT VARCHAR2);
Parameters
| Parameter Name | Meaning |
|---|---|
|
file_loc |
Locator for the BFILE. |
|
dir_alias |
Directory alias |
|
filename |
Name of the BFILE |
Return Values
Pragma
Exceptions
VALUE_ERROR, if any of the input parameters are NULL or invalid. INVALID_ARGVAL, if dir_alias or filename are NULL.
Example
PROCEDURE Example_8 IS
fil BFILE;
dir_alias VARCHAR2(30);
name VARCHAR2(2000);
BEGIN
IF (DBMS_LOB.FILEEXISTS(fil))
THEN
DBMS_LOB.FILEGETNAME(fil, dir_alias, name);
DBMS_OUTPUT.PUT_LINE ("Opening " || dir_alias ||
name);
DBMS_LOB.FILEOPEN(fil, DBMS_LOB.FILE_READONLY);
-- file operations
DBMS_OUTPUT.FILECLOSE(fil);
END IF;
END;
Note: See also BFILENAME() function
You can call the FILEISOPEN() function to find out whether a BFILE was opened with the give FILE locator. If the input FILE locator was never passed to the DBMS_LOB.FILEOPEN procedure, the file is considered not to be opened by this locator. However, a different locator may have this file open. In other words, openness is associated with a specific locator.
Syntax
FUNCTION FILEISOPEN (
file_loc IN BFILE)
RETURN INTEGER;
Parameter
| Parameter Name | Meaning |
|---|---|
|
file_loc |
Locator for the BFILE. |
Return Values
Pragma
PRAGMA RESTRICT_REFERENCES(fileisopen, WNDS, RNDS, WNPS, RNPS);
Exceptions
NOEXIST_DIRECTORY if the directory does not exist, NOPRIV_DIRECTORY if you do not have privileges for the directory, INVALID_DIRECTORY if the directory has been invalidated after the file was opened. INVALID_OPERATION if the file does not exist, or you do not have access privileges on the file.
Example
PROCEDURE Example_9 IS
DECLARE
fil BFILE;
pos INTEGER;
pattern VARCHAR2(20);
BEGIN
SELECT f_lob INTO fil FROM lob_table
WHERE key_value = 12;
-- open the file
IF (FILEISOPEN(fil))
THEN
pos := DBMS_LOB.INSTR(fil, pattern, 1025, 6);
-- more file operations
DBMS_LOB.FILECLOSE(fil);
ELSE
; -- return error
END IF;
END;
Note: See also DBMS_LOB.FILEEXISTS
You can call the FILEOPEN procedure to open a BFILE for read-only access. BFILEs may not be written through Oracle.
Syntax
PROCEDURE FILEOPEN (
file_loc IN OUT BFILE,
open_mode IN BINARY_INTEGER := file_readonly);
Parameters
| Parameter Name | Meaning |
|---|---|
|
file_loc |
Locator for the BFILE. |
|
open_mode |
Open mode. |
Return Values
Pragma
Exceptions
VALUE_ERROR exception is raised if file_loc or open_mode is NULL. INVALID_ARGVAL exception is raised if open_mode is not equal to FILE_READONLY. OPEN_TOOMANY if the number of open files in the session exceeds SESSION_MAX_OPEN_FILES, NOEXIST_DIRECTORY if the directory does not exist, INVALID_DIRECTORY if the directory has been invalidated after the file was opened, INVALID_OPERATION if the file does not exist, or you do not have access privileges on the file.
Example
PROCEDURE Example_10 IS
fil BFILE;
BEGIN
-- open BFILE
SELECT f_lob INTO fil FROM lob_table WHERE key_value =
99;
IF (DBMS_LOB.FILEEXISTS(fil))
THEN
DBMS_LOB.FILEOPEN(fil, DBMS_LOB.FILE_READONLY);
-- file operation
DBMS_LOB.FILECLOSE(fil);
END IF;
EXCEPTION
WHEN some_exception
THEN handle_exception;
END;
Note: See also DBMS_LOB.FILECLOSE(), DBMS_LOB.FILECLOSEALL()
You can call the GETLENGTH() function to get the length of the specified LOB. The length in bytes or characters is returned. The length returned for a BFILE includes the EOF if it exists. Note that any 0-byte or space filler in the LOB caused by previous ERASE() or WRITE() operations is also included in the length count. The length of an empty internal LOB is 0.
Syntax
FUNCTION GETLENGTH ( lob_loc IN BLOB) RETURN INTEGER; FUNCTION GETLENGTH ( lob_loc IN CLOB CHARACTER SET ANY_CS) RETURN INTEGER; FUNCTION GETLENGTH ( lob_loc IN BFILE) RETURN INTEGER;
Parameter
| Parameter Name | Meaning |
|---|---|
|
lob_loc |
The locator for the LOB whose length is to be returned. |
Return Values
The length of the LOB in bytes or characters as an INTEGER. NULL is returned if the input LOB is null. NULL is returned in the following cases for BFILEs:
Pragmas
PRAGMA RESTRICT_REFERENCES(getlength, WNDS, WNPS, RNDS, RNPS);
Exceptions
Examples
PROCEDURE Example_11a IS
lobd BLOB;
length INTEGER;
BEGIN
-- get the LOB locator
SELECT b_lob INTO lobd FROM lob_table
WHERE key_value = 42;
length := DBMS_LOB.GETLENGTH(lob_loc);
IF length IS NULL THEN
DBMS_OUTPUT.PUT_LINE('LOB is null.');
ELSE
DBMS_OUTPUT.PUT_LINE('The length is '
|| length);
END IF;
END;
PROCEDURE Example_11b IS
DECLARE
len INTEGER;
fil BFILE;
BEGIN
SELECT f_lob INTO fil FROM lob_table WHERE key_value = 12;
len := DBMS_LOB.LENGTH(fil);
END;
You can call the INSTR function to return the matching position of the Nth occurrence of the pattern in the LOB, starting from the offset you specify. For CLOBs, the VARCHAR2 buffer (the PATTERN parameter) and the LOB value must be from the same character set (single byte or fixed-width multibyte). For BFILEs, the file has to be already opened using a successful FILEOPEN() operation for this operation to succeed.
Operations that accept RAW or VARCHAR2 parameters for pattern matching, such as INSTR, do not support regular expressions or special matching characters (as in the case of SQL LIKE) in the pattern parameter or substrings.
Syntax
FUNCTION INSTR ( lob_loc IN BLOB, pattern IN RAW, offset IN INTEGER := 1, nth IN INTEGER := 1) RETURN INTEGER; FUNCTION INSTR ( lob_loc IN CLOB CHARACTER SET ANY_CS, pattern IN VARCHAR2 CHARACTER SET lob_loc%CHARSET, offset IN INTEGER := 1, nth IN INTEGER := 1) RETURN INTEGER; FUNCTION INSTR ( lob_loc IN BFILE, pattern IN RAW, offset IN INTEGER := 1, nth IN INTEGER := 1) RETURN INTEGER;
Parameters
Return Values
INTEGER, offset of the start of the matched pattern, in bytes or characters. It returns 0 if the pattern is not found.
Pragma
PRAGMA RESTRICT_REFERENCES(instr, WNDS, WNPS, RNDS, RNPS);
Exceptions
For BFILEs, UNOPENED_FILE if the file was not opened using the input locator, NOEXIST_DIRECTORY if the directory does not exist, NOPRIV_DIRECTORY if you do not have privileges for the directory, INVALID_DIRECTORY if the directory has been invalidated after the file was opened, INVALID_OPERATION if the file does not exist, or if you do not have access privileges on the file.
Examples
PROCEDURE Example_12a IS
lobd CLOB;
pattern VARCHAR2 := 'abcde';
position INTEGER := 10000;
BEGIN
-- get the LOB locator
SELECT b_col INTO lobd
FROM lob_table
WHERE key_value = 21;
position := DBMS_LOB.INSTR(lobd,
pattern, 1025, 6);
IF position = 0 THEN
DBMS_OUTPUT.PUT_LINE('Pattern not found');
ELSE
DBMS_OUTPUT.PUT_LINE('The pattern occurs at '
|| position);
END IF;
END;
PROCEDURE Example_12b IS
DECLAR E
fil BFILE;
pattern VARCHAR2;
pos INTEGER;
BEGIN
-- initialize pattern
-- check for the 6th occurrence starting from 1025th byte
SELECT f_lob INTO fil FROM lob_table WHERE key_value = 12;
DBMS_LOB.FILEOPEN(fil, DBMS_LOB.FILE_READONLY);
pos := DBMS_LOB.INSTR(fil, pattern, 1025, 6);
DBMS_LOB.FILECLOSE(fil);
END;
Note: See also DBMS_LOB.SUBSTR()
You can call the LOADFROMFILE() procedure to copy all, or a part of, a source external LOB (BFILE) to a destination internal LOB. You can specify the offsets for both the source and destination LOBs, and the number of bytes to copy from the source BFILE. Note that the amount and src_offset, since they refer to the BFILE, are in terms of bytes and the destination offset is either in bytes or characters for BLOBs and CLOBs respectively.
Note that the input BFILE must have already been opened prior to using this procedure. Also note that no character set conversions are performed implicitly when binary BFILE data is loaded into a CLOB. The BFILE data must already be in the same character set as the CLOB in the database. No error checking is performed to verify this.
If the offset you specify in the destination LOB is beyond the end of the data currently in this LOB, zero-byte fillers or spaces are inserted in the destination BLOB or CLOB respectively. If the offset is less than the current length of the destination LOB, existing data is overwritten.
It is not an error to specify an amount that exceeds the length of the data in the source BFILE. Thus, you can specify a large amount to copy from the BFILE which will copy data from the src_offset to the end of the BFILE.
Syntax
PROCEDURE loadfromfile ( dest_lob IN OUT BLOB, src_file IN BFILE, amount IN INTEGER, dest_offset IN INTEGER := 1, src_offset IN INTEGER := 1);
PROCEDURE LOADFROMFILE(
dest_lob IN OUT CLOB CHARACTER SET ANY_CS,
src_file IN BFILE,
amount IN INTEGER,
dest_offset IN INTEGER := 1,
src_offset IN INTEGER := 1);
Parameters
Return Values
Pragma
Exceptions
VALUE_ERROR, if any of the input parameters are NULL or invalid.
INVALID_ARGVAL, if
Examples
PROCEDURE Example_l2f IS
lobd BLOB;
fils BFILE := BFILENAME('SOME_DIR_OBJ',
'some_file');
amt INTEGER := 4000;
BEGIN
DBMS_LOB.FILEOPEN(fils, dbms_lob.file_readonly);
DBMS_LOB.LOADFROMFILE(lobd, fils, amt);
COMMIT;
DBMS_LOB.FILECLOSE(fils);
END;
You can call the READ() procedure to read a piece of a LOB, and return the specified amount into the buffer parameter, starting from an absolute offset from the beginning of the LOB.
The number of bytes or characters actually read is returned in the amount parameter. If the end of LOB value is reached during a READ(), amount will be set to 0, and a NO_DATA_FOUND exception will be raised.
Syntax
PROCEDURE READ ( lob_loc IN BLOB, amount IN OUT BINARY_INTEGER, offset IN INTEGER, buffer OUT RAW); PROCEDURE READ ( lob_loc IN CLOB CHARACTER SET ANY_CS, amount IN OUT BINARY_INTEGER, offset IN INTEGER, buffer OUT VARCHAR2 CHARACTER SET lob_loc%CHARSET); PROCEDURE READ ( lob_loc IN BFILE, amount IN OUT BINARY_INTEGER, offset IN INTEGER, buffer OUT RAW);
Parameters
Return Values
Pragmas
Exceptions
- AMOUNT is greater, in bytes or characters, than the capacity of BUFFER
- the end of the LOB is reached and there are no more bytes or
characters to read from the LOB. AMOUNT has a value of 0.
Examples
PROCEDURE Example_13a IS
src_lob BLOB;
buffer RAW;
amt BINARY_INTEGER := 32767;
pos INTEGER := 2147483647;
BEGIN
SELECT b_col INTO src_lob
FROM lob_table
WHERE key_value = 21;
LOOP
DBMS_LOB.READ (src_lob, amt, pos, buffer);
/* process the buffer */
pos := pos + amt;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('End of data');
END;
PROCEDURE Example_13b IS
fil BFILE;
buf RAW(32767);
amt BINARY_INTEGER := 32767;
pos INTEGER := 2147483647;
BEGIN
SELECT f_lob INTO fil FROM lob_table WHERE key_value = 21;
DBMS_LOB.FILEOPEN(fil, DBMS_LOB.FILE_READONLY);
LOOP
DBMS_LOB.READ(fil, amt, pos, buf);
-- process contents of buf
pos := pos + amt;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
BEGIN
DBMS_OUTPUT.PUTLINE (`End of LOB value reached');
DBMS_LOB.FILECLOSE(fil);
END;
END;
/* Example for efficient I/O on OS that performs */
/* better with block I/O rather than stream I/O */
PROCEDURE Example_13c IS
fil BFILE;
amt BINARY_INTEGER := 1024; -- or n x 1024 for reading n
buf RAW(1024); -- blocks at a time
tmpamt BINARY_INTEGER;
BEGIN
SELECT f_lob INTO fil FROM lob_table WHERE key_value = 99;
DBMS_LOB.FILEOPEN(fil, DBMS_LOB.FILE_READONLY);
LOOP
DBMS_LOB.READ(fil, amt, pos, buf);
-- process contents of buf
pos := pos + amt;
END LOOP;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
BEGIN
DBMS_OUTPUT.PUTLINE (`End of data reached');
DBMS_LOB.FILECLOSE(fil);
END;
END;
You can call the SUBSTR() function to return amount bytes or characters of a LOB, starting from an absolute offset from the beginning of the LOB.
For fixed-width n-byte CLOBs, if the input amount for SUBSTR() is specified to be greater than (32767/n), then SUBSTR() returns a character buffer of length (32767/n), or the length of the CLOB, whichever is lesser.
Syntax
FUNCTION SUBSTR( lob_loc IN BLOB, amount IN INTEGER := 32767, offset IN INTEGER := 1) RETURN RAW; FUNCTION SUBSTR( lob_loc IN CLOB CHARACTER SET ANY_CS, amount IN INTEGER := 32767, offset IN INTEGER := 1) RETURN VARCHAR2 CHARACTER SET lob_loc%CHARSET; FUNCTION SUBSTR( lob_loc IN BFILE, amount IN INTEGER := 32767, offset IN INTEGER := 1) RETURN RAW;
Parameters
Return Values
RAW, for the function overloading that has a BLOB or BFILE in parameter.
Pragma
PRAGMA RESTRICT_REFERENCES(substr, WNDS, WNPS, RNDS, RNPS);
Exceptions
For BFILE operations, UNOPENED_FILE if the file is not opened using the input locator, NOEXIST_DIRECTORY if the directory does not exist, NOPRIV_DIRECTORY if you do not have privileges for the directory, INVALID_DIRECTORY if the directory has been invalidated after the file was opened, INVALID_OPERATION if the file does not exist, or if you do not have access privileges on the file
Example
PROCEDURE Example_14a IS
src_lob CLOB;
pos INTEGER := 2147483647;
buf VARCHAR2(32000);
BEGIN
SELECT c_lob INTO src_lob FROM lob_table
WHERE key_value = 21;
buf := DBMS_LOB.SUBSTR(src_lob, 32767, pos);
/* process the data */
END;
PROCEDURE Example_14b IS
fil BFILE;
pos INTEGER := 2147483647;
pattern RAW;
BEGIN
SELECT f_lob INTO fil FROM lob_table WHERE key_value = 21;
DBMS_LOB.FILEOPEN(fil, DBMS_LOB.FILE_READONLY);
pattern := DBMS_LOB.SUBSTR(fil, 255, pos);
DBMS_LOB.FILECLOSE(fil);
END;
Note: See also DBMS_LOB.INSTR(), DBMS_LOB.READ().
You can call the TRIM() procedure to trim the value of the internal LOB to the length you specify in the newlen parameter. Specify the length in bytes for BLOBs, and in characters for CLOBs.
If you attempt to TRIM() an empty LOB, nothing occurs, and TRIM() returns no error. If the new length that you specify in newlen is greater than the size of the LOB, an exception is raised.
Syntax
FUNCTION TRIM (
lob_loc IN BLOB,
newlen IN INTEGER);
FUNCTION TRIM (
lob_loc IN CLOB,
newlen IN INTEGER):
Parameters
| Parameter Name | Meaning |
|---|---|
|
lob_loc |
The locator for the internal LOB whose length is to be trimmed. |
|
newlen |
The new, trimmed length of the LOB value in bytes for BLOBs or characters for CLOBs. |
Return Values
Pragmas
Exceptions
Example
PROCEDURE Example_15 IS
lob_loc BLOB;
BEGIN
-- get the LOB locator
SELECT b_col INTO lob_loc
FROM lob_table
WHERE key_value = 42 FOR UPDATE;
DBMS_LOB.TRIM(lob_loc, 4000);
COMMIT;
END;
Note: See also DBMS_LOB.ERASE().
You can call the WRITE() procedure to write a specified amount of data into an internal LOB, starting from an absolute offset from the beginning of the LOB. The data is written from the buffer parameter.
WRITE() replaces (overwrites) any data that already exists in the LOB at the offset, for the length you specify.
It is an error if the input amount is more than the data in the buffer. If the input amount is less than the data in the buffer, only amount bytes/characters from the buffer is written to the LOB. If the offset you specify is beyond the end of the data currently in the LOB, zero-byte fillers or spaces are inserted in the BLOB or CLOB respectively.
Syntax
PROCEDURE WRITE ( lob_loc IN OUT BLOB, amount IN BINARY_INTEGER, offset IN INTEGER, buffer IN RAW); PROCEDURE WRITE ( lob_loc IN OUT CLOB CHARACTER SET ANY_CS, amount IN BINARY_INTEGER, offset IN INTEGER, buffer IN VARCHAR2 CHARACTER SET lob_loc%CHARSET);
Parameters
Return Values
Pragmas
Exceptions
- if any of LOB_LOC, AMOUNT, or OFFSET parameters are null,
out of range, or invalid
Example
PROCEDURE Example_16 IS
lob_loc BLOB;
buffer RAW;
amt BINARY_INTEGER := 32767;
pos INTEGER := 2147483647;
BEGIN
SELECT b_col INTO lob_loc
FROM lob_table
WHERE key_value = 12;
-3 4` LOOP
DBMS_LOB.WRITE (lob_loc, amt, pos, buffer);
/* fill in more data */
pos := pos + amt;
END LOOP;
EXCEPTION4
WHEN some_exception
THEN handle_exception;
END;
Note: See also DBMS_LOB.APPEND(), DBMS_LOB.COPY(), DBMS_LOB.WRITE().
The use of LOBs are subject to some restrictions:
1. Write the data in the long RAW to a server side file.
2. Use the Oracle8 command CREATE DIRECTORY to point to the directory where the file was written.
3. Use the Oracle8 command OCILobLoadFromFile() or DBMS_LOB.LOADFROMFILE() to populate the LOB with the data in the file.
If the LONG isn't too big, another way is to read the LONG into a buffer and call OCILobWrite or DBMS_LOB.WRITE() to write the LONG data to the LOB.
In either case, you'll need to either add a LOB column to the original table or create a new table that contains the LOB column. Oracle8 does not allow changing the datatype of a column to a LOB type.
Also, LOBs are not allowed in the following places:
- in regular triggers old values may be read but not written and new
values may not be read nor written.
- in 'instead of triggers', the:old and:new values can be read but not
written.
|
 Copyright © 1997 Oracle Corporation. All Rights Reserved. |
|