Release 8.0
A58241-01
 Library |
 Product |
 Contents |
 Index |
| Oracle8 Application Developer's Guide Release 8.0 A58241-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 Oracle Call Interface Programmer's Guide.
This chapter has two sections:
DBMS_LOB package
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:
LOBs?
LOBs and External LOBs (BFILEs)
LOB Datatypes
LOBs in comparison to LONG and LONG RAW types
LOBs
The second topic discusses steps involved in beginning to work with LOBs:
LOBs for Tables (SQL DDL)
LOBs
LOBs (SQL DML)
The third topic deals with issues specific to handling external LOBs (BFILEs):
LOBs (SQL DML)
BFILE Security
DIRECTORY Usage
BFILES
BFILEs in MTS mode
BFILEs after Program Termination
The fourth topic considers how LOBs are handled by way of locators:
The fifth topic is concerned with basic manipulation of LOBs:
LOB data
LOBs
LOBs
LONGs to LOBs
Finally, the last topic considers performance and optimization issues in a client/server environment:
LOBs in the Object Cache
LOB Buffering Subsystem
Consider the following application scenarios:
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. Internal LOBs are also recoverable in the event of transaction or media failure. 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 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.
LOBs are similar to LONG and LONG RAW types, but differ in the following ways:
LOBs are allowed in a single row.
LOBs can be attributes of a user-defined datatype (object).
BLOB and CLOB data can be stored in separate tablespaces and BFILE data is stored as an external file.
LOB column, it is the locator which is returned.
BFILE maximum is operating system dependent, but cannot exceed 4 gigabytes. The valid accessible range is 1 to (232-1).
LOBs let you access and manipulate data in a random, piece-wise manner.
You can make changes to the entire values of internal LOBs through direct SQL DML. You can make to an entire internal LOB, or piecewise to the beginning, middle or end of an internal LOB through the OCI, or through the PL/SQL DBMS_LOB APIs. It is possible to access both internal and external LOBs for read purposes and also write to internal LOBs.
LOB interface is described briefly in "Using the OCI to Manipulate LOBs" on page 6-64, and more extensively in the Oracle Call Interface Programmer's Guide.
There are three SQL datatypes for defining instances of internal LOBs:
BLOB, a LOB whose value is composed of unstructured binary ("raw") data.
CLOB, a LOB whose value is composed of single-byte fixed-width character data that corresponds to the database character set defined for the Oracle8 database.
NCLOB, a LOB whose value is composed of fixed-width multi-byte character data that corresponds to the national character set defined for the Oracle8 database.
Varying width character data is not supported for BLOBs, CLOBs and NCLOBs.
There is one external SQL LOB datatype:
BFILE, a LOB whose value is composed of binary ("raw") data, and is stored outside of the database tablespaces in a server-side operating system file.
It is possible to incorporate LOBs into tables in two ways.
LOBs may be columns in a table - the case in which the large object is 'in relation' with other data entities.
LOBs may be attributes of an object - the case in which a data entity (i.e. an object type) has one or more LOBs as attributes.
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 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.
Specifying a name for the LOB data segment and the LOB index makes for a much more intuitive working environment. When querying the LOB data dictionary views USER_LOBS, ALL_LOBS, DBA_LOBS (see Oracle8 Reference), you see the LOB data segment and LOB index names that you chose instead of system-generated names that are non-intuitive.
The LOB storage characteristics that can be specified for a LOB column or a LOB attribute include PCTVERSION, CACHE, NOCACHE, LOGGING, NOLOGGING, CHUNK and ENABLE/DISABLE STORAGE IN ROW. For most users, defaults for these storage characteristics will be sufficient. If you want to fine-tune LOB storage, you should consider the following guidelines.
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. Note that the LOB index cannot be altered through the ALTER INDEX statement although you can alter it through the ALTER TABLE statement. However, you may not rename the LOB index. The system determines which tablespace to use for the LOB data and LOB index depending on the user specification in the LOB storage clause:
LOB data nor for the LOB index, the table's tablespace is used for both the LOB data and the LOB index.
LOB data but not for the LOB index, both the LOB data and index use the tablespace that was specified for the LOB data.
LOB index but not the LOB data, the LOB index uses the specified tablespace and the LOB data uses the table's tablespace.
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, PCTVERSION 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 the same LOB data will be accessed frequently. Use the NOCACHE option (the default) if LOB data will be read only once, or infrequently.
[NO] LOGGING has a similar application with regard to using LOBs as it does for other table operations. In the normal case, if the [NO]LOGGING clause is omitted, this means that neither NO LOGGING nor LOGGING is specified and the logging attribute of the table or table partition defaults to the logging attribute of the tablespace in which it resides.
For LOBs, there is a further alternative depending on how CACHE is stipulated.
NO]LOGGING clause is omitted and CACHE is specified, LOGGING is automatically implemented (because you cannot have CACHE NOLOGGING).
NO]LOGGING clause is omitted and CACHE is not specified, the process defaults in the same way as it does for tables and partitioned tables i.e.,the [NO]LOGGING value is obtained from the tablespace in which the LOB value resides.
The following issues should also 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.
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 your changes from the log since the changes were never logged.
An example of when NOLOGGING is useful 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.
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 the size of one block. For example, if the database block size is 2K, then set CHUNK to 2K.
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 8K, make sure that the INITIAL and NEXT are bigger than 8K and preferably considerably bigger (for example, at least 16K).
Put another way: If you specify a value for INITIAL, NEXT or the LOB CHUNK size, make sure that:
and
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 suggests 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);
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 internal 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') for internal LOBs or to a filename for external LOBs. 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.
The DIRECTORY object enables administering the access and usage of BFILEs in an Oracle8 Server (see the CREATE DIRECTORY command in the Oracle8 Reference). 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.
In order to associate an operating system file to a BFILE, it is necessary to first create a DIRECTORY object which is an alias for the full pathname to the operating system file.
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. OCI users can also use OCILobFIleSetName() to initialize a BFILE locator variable that is then used in the VALUES clause of an INSERT statement.
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 (22, 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.
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 when accessed 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 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'. Use 'SCOTT_DIR' and 'Mary_Dir' when calling BFILENAME(). For example:
BFILENAME('SCOTT_DIR', 'afile') BFILENAME('Mary_Dir', 'afile')
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:
CREATE and REPLACE/ALTER a DIRECTORY object.
GRANT and REVOKE the READ system and object privileges on DIRECTORY objects.
The DIRECTORY is a system owned object. For more information on system owned objects, see Oracle8 SQL Reference. Oracle8 supports two new system privileges, which are granted only to the DBA account:
CREATE ANY DIRECTORY - for creating or altering the directory object creation
DROP ANY DIRECTORY - for deleting the directory object
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 with GRANT option, 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 for the Oracle Server process is enabled on the file, the directory, and the path leading to it. 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.
Refer to the Oracle8 SQL Reference for information about the following SQL DDL commands that create, replace, and drop directory objects:
Refer to the Oracle8 SQL Reference for information about the following SQL DML commands that provide security for BFILEs:
GRANT (system privilege)
GRANT (object privilege)
REVOKE (system privilege)
REVOKE (object privilege)
AUDIT (new statements)
AUDIT (schema objects)
Catalog views are provided for directory objects to enable users to view object names and their corresponding paths and privileges. The supported views are:
ALL_DIRECTORIES (OWNER, DIRECTORY_NAME, DIRECTORY_PATH)
This view describes all the directories accessible to the user.
DBA_DIRECTORIES(OWNER, DIRECTORY_NAME, DIRECTORY_PATH)
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:
DIRECTORY should not be mapped to physical directories which contain Oracle datafiles, control files, log files, and other system files. Tampering with these files (accidental or otherwise) could potentially corrupt the database or the server operating system.
CREATE ANY DIRECTORY (granted to the DBA initially) should be used carefully and not granted to other users indiscriminately. In most cases, only the database administrator should have these privileges.
WITH GRANT OPTION clause when granting privileges to users.
DIRECTORY objects should not be arbitrarily dropped or replaced when the database is in operation. If this were to happen, DBMS_LOB or OCI operations from all sessions on all files associated with this directory object will fail. Further, if a DROP or REPLACE command is executed before these files could be successfully closed, the references to these files will be lost in the programs, and system resources associated with these files will not be released until the session(s) is shutdown.
The only recourse left to PL/SQL users, for example, will be to either execute a program block that calls DBMS_LOB FILECLOSEALL() 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.
REVOKE statement causes all subsequent operations on dependent files from the user's session to fail. Either you must re-acquire the privileges to close the file, or execute a FILECLOSEALL() in the session and restart the file operations.
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. So, for instance, for every DBMS_LOB FILEOPEN call, there must be a matching DBMS_LOB FILECLOSE call. You should close open files before the termination of a PL/SQL block or OCI program, and also in situations which have raised errors. The exception handler should make provision to close any files that were opened before the occurrence of the exception or abnormal termination.
If this is not done, Oracle 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.
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-12), 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.
LOBs, you can accomplish this by initializing the internal LOB to empty in an INSERT/UPDATE statement using the functions EMPTY_BLOB() for BLOBs or EMPTY_CLOB() for CLOBs and NCLOBs.
LOBs, you can initialize the BFILE column to point to an external file by using the BFILENAME() function.
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 to access or manipulate the LOB value 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
NULL,
n_lob to NULL, and
SCOTT_DIR' (see the CREATE DIRECTORY command in the Oracle8 Reference). 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-64, and more extensively in the Oracle Call Interface Programmer's Guide.
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 explicitly 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 Concepts 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 and PL/SQL, three CLOBs are created as potential locators: clob_selected, clob_updated and clob_copied.
SELECT INTO (at t1), the value in c_lob is associated with the locator clob_selected.
dbms_lob.read calls.
dbms_lob.write to alter the value in clob_updated, and a dbms_lob.read reveals a new value.
dbms_lob.read of the value through clob_selected (at t5) reveals that it is a read consistent locator, continuing to refer to the same value as of the time of its SELECT.
dbms_lob.read of the value through clob_copied (at t6) reveals that it is a read consistent locator, continuing to refer to the same value as clob_selected.
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 of the point in time -- of 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.
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.
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.
Note that 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.
SELECT INTO (at t1), the value in c_lob is associated with the locator clob_selected.
SQL UPDATE command, bypassing the clob_selected locator. The locator still sees the value of the LOB as of the point in time of the original SELECT. In other words, the locator does not see the update made via the SQL UPDATE command. This is illustrated by the subsequent dbms_lob.read call.
LOB value into the locator clob_selected. The locator is thus updated with the latest snapshot environment which allows the locator to see the change made by the previous SQL UPDATE command. Therefore, in the next dbms_lob.read, an error is returned because the LOB value is empty (i.e., it does not contain any data).
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; /
Using lob_table as defined above, two CLOBs are created as potential locators: clob_updated and clob_copied.
SELECT INTO (at t1), the value in c_lob is associated with the locator clob_updated.
dbms_lob.read calls.
dbms_lob.write to alter the value in clob_updated, and a dbms_lob.read reveals a new value.
dbms_lob.read of the value through clob_copied (at t4) reveals that it still sees the value of the LOB as of the point in time of the assignment from clob_updated (at t2).
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.
SELECT INTO (at t1), the value in c_lob is associated with the locator clob_updated.
dbms_lob.write to alter the value in clob_updated, and a dbms_lob.read reveals a new value.
dbms_lob.read of the value through clob_copied (at t4) reveals that clob_copied does not see the change made by clob_updated.
INSERT statement, we insert the value associated with clob_copied (i.e. without the new changes made by clob_updated). This is demonstrated by the subsequent dbms_lob.read of the value just inserted.
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. 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.
SELECT INTO (at t1), the value in c_lob is associated with the locator clob_updated.
dbms_lob.write command to alter the value in clob_updated, and a dbms_lob.read reveals a new value.
commit statement (at t3) ends the current transaction.
dbms_lob.read operation fails because the clob_updated locator refers to a different (already committed) transaction. This is noted by the error returned. You must re-select the LOB locator before using it in further dbms_lob (and OCI) operations.
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; /
Assume the following tables: CREATE TABLE tdrslob01 ( a CLOB, b BLOB, c NUMBER); CREATE TABLE foo ( key NUMBER); CONNECT to the database EXECUTE "SELECT C,'I',A FROM tdrslob01 ORDER BY 1" [ARRAY FETCH GET 16 rows INTO OCILobLocator ARRAY] for (i=0; i<16; i++) { EXECUTE "INSERT INTO foo VALUES(5)" OCITransCommit(...); FETCH TEXT in CLOB USING locators fetched }
The sequence runs successfully because the SELECT of the locators occurs outside of a transaction. This means that the locators selected are not associated with a transaction. Even though the INSERT in the 'for' loop implicitly starts a transaction, the subsequent COMMIT in the 'for' loop ends the transaction. The FETCH of the LOB data via the locator returned from the SELECT outside a transaction succeeds. Both the SELECT of the locator and the FETCH of the locator data occur outside a transaction.
However, the addition of one statement produces an error:
CONNECT to the database EXECUTE "INSERT INTO foo VALUES(5)" <=== EXECUTE "SELECT C,'I',A FROM tdrslob01 ORDER BY 1" [GET 16 rows] for (i=0; i<16; i++) { EXECUTE "INSERT INTO foo VALUES(5)" OCITransCommit(...); FETCH text in CLOB USING locators fetched <== get ORA-22990 }
In the second example, the SELECT of the locators occurs inside a transaction (the INSERT statement implicitly started a transaction). This means that the locators selected are associated with a transaction. The COMMIT in the 'for' loop commits the transaction in which the locators were selected. Therefore, the subsequent FETCH is trying to fetch locator values from the previous transaction which was already committed. Consequently, the 22990 error is returned.
Executing a COMMIT right after the first INSERT will succeed:
CONNECT to the database EXECUTE "INSERT INTO foo VALUES(5)" <=== OCITransCommit(...); <=== EXECUTE "SELECT C,'I',A FROM tdrslob01 ORDER BY 1" [get 16 rows] for (i=0; i<16; i++) { EXECUTE "INSERT INTO foo VALUES(5)" OCITransCommit(...); FETCH text in CLOB using locators fetched }
In this example, the INSERT implicitly starts a transaction and the COMMIT ends the transaction. Therefore, the SELECT of the locators occurs outside of a transaction. This means that the locators selected are not associated with a transaction. Again, even though the INSERT in the 'for' loop implicitly starts a transaction, the subsequent COMMIT in the 'for' loop ends the transaction. Therefore, the FETCH of the LOB data via the locator returned from the SELECT which occurred outside a transaction succeeds. Both the SELECT of the locator and the FETCH of the locator data occur outside of a transaction.
The most efficient way to read or write large amounts of LOB data is to use OCILobRead() or OCILobWrite() with the streaming mechanism enabled via polling or a callback.
|
See Also:
Oracle Call Interface Programmer's Guide for more information about these APIs and a sample program of how to use them. |
When reading the LOB value, it is not an error to try to read beyond the end of the LOB. This means that you can always specify an input amount of 4 gigabytes regardless of the starting offset and the amount of data in the LOB. You do need to incur a round-trip to the server to call OCILobGetLength() to find out the length of the LOB value in order to determine the amount to read.
For example, assume that the length of a LOB is 5,000 bytes and you want to read the entire LOB value starting at offset 1,000. Also assume that you do not know the current length of the LOB value. Here's the OCI read call, excluding the initialization all parameters:
#define MAX_LOB_SIZE 4294967295 ub4 amount = MAX_LOB_SIZE; ub4 offset = 1000; OCILobRead(svchp, errhp, locp, &amount, offset, bufp, bufl, 0, 0, 0, 0)
As noted previously, the best way to populate the LOB with data, or write large amounts of data to the LOB, is to use the OCILobWrite() call with streaming. If you know how much data will be written to the LOB, specify that amount when calling OCILobWrite(). This will allow for the contiguity of the LOB data on disk. Apart from being spatially efficient, contiguous structure of the LOB data will make for faster reads and writes in subsequent operations.
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.
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 row.
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 deleting 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;
One of the problems you may face is how to convert data from the LONG datatype into LOB format. The loadlob.sql PL/SQL program demonstrates how to convert a LONG to a LOB by using the DBMS_LOB.LOADFROMFILE method. The program requires that you perform a sequence of steps:
LONG column in the old table and add a new LOB column using the ALTER TABLE command.
LONG or LONG RAW to a flat file.
CREATE DIRECTORY to point to the directory where the BFILE (flat file) was written.
OCILobLoadFromFile or the PL/SQL command DBMS_LOB.LOADFROMFILE(): This is the fastest ways to copy from a server-side operating system flat file to a LOB.
OCILobWrite() from a server-side external procedure: The flat file will be on the server-side even if the program which calls the server-side external procedure is run from the client. This is the second fastest way to transfer from a server side operating system flat file to a LOB.
OCILobWrite(): This method is used in the bull_lob program listed below. This may not be the fastest way to convert a LONG to a LOB, but it may be the only alternative in the circumstances. In such cases, when the program is run on a remote client machine, the LONG data on the server must be written to a client machine flat file, and then the client flat file written back to the server LOB column. Since this will involve two trips across the network, the load on performance must be considered if this will be an operation that is frequently repeated.
The example that follows shows the PL/SQL version of method 4(a) listed above for loading a LONG which has been written to a flat file named /tmp/sound_clip into a LOB column.
Complete the following steps to execute the loadlob.sql PL/SQL script:
/tmp directory:
sound_clip: abcdefghijklmnopqrstuvwxyz
% sqlplus scott/tiger @loadlob loadlob.sql ----------- set echo on; connect sys/change_on_install; grant all on dbms_lob to scott; grant create any directory to scott; connect scott/tiger; drop directory some_dir_alias; create directory some_dir_alias as '/tmp'; drop table multimedia;/* Create the table */CREATE TABLE multimedia ( id NUMBER, video_clip CLOB DEFAULT empty_clob(), audio_clip CLOB DEFAULT NULL, some_file BFILE DEFAULT NULL ) ;/* Load data into the table *//* Insert 10 rows into the table which defaults to initializing *//* the video_clip to empty and the audio_clip and some_file to null. *//* The fastest way to do this is to use array inserts with OCI *//* (see OCIBindArrayOfStruct) *//* The less speedy method is to use a loop in PL/SQL as follows. */declare loop_count integer; begin loop_count := 1; while loop_count <= 10 loop insert into multimedia (id) values (loop_count); loop_count := loop_count + 1; end loop; end; //* Initialize the first audio clip to the actual value. *//* Then copy this value to all rows in the table. */declare ac clob; amount integer; a_file bfile := BFILENAME('SOME_DIR_ALIAS', 'sound_clip'); begin update multimedia set audio_clip = empty_clob() where id = 1 returning audio_clip into ac; /* Open the server side file that contains the audio clip, load it into*//* the CLOB and then close the file. Assume that the audio clip is*//* only 32,000 bytes long and that it starts at position 1 in the file.*/dbms_lob.fileopen(a_file, dbms_lob.file_readonly); amount := 26;/* Note that the destination and source offsets default to 1 */dbms_lob.loadfromfile(ac, a_file, amount); dbms_lob.fileclose(a_file); commit;/* Update all rows in the table to the audio clip you just loaded. */update multimedia set audio_clip = (select audio_clip from multimedia where id = 1) where audio_clip is null; end; / select id, audio_clip from multimedia;
SQL> @loadlob SQL> set echo on; SQL> connect sys/change_on_install; Connected. SQL> GRANT ALL on dbms_lob to scott; Grant succeeded. SQL> GRANT CREATE ANY DIRECTORY to scott; Grant succeeded. SQL> CONNECT scott/tiger; Connected. SQL> DROP DIRECTORY some_dir_alias; Directory dropped. SQL> CREATE DIRECTORY some_dir_alias as '/tmp'; Directory created. SQL> DROP TABLE multimedia; Table dropped. SQL> SQL>/* CREATE THE TABLE */SQL> SQL> create table multimedia 2 ( 3 id number, 4 video_clip clob default empty_clob(), 5 audio_clip clob default null, 6 some_file bfile default null 7 ) ; Table created. SQL> SQL> SQL>/* LOAD DATA INTO THE TABLE */SQL>/* Insert 10 rows into the table which defaults to initializing */DOC> /* the video_clip to empty and the audio_clip and some_file to null.*/DOC>*/SQL> SQL>/* The fast way to do this is to use array inserts with OCI*/DOC> /* (see OCIBindArrayOfStruct)*/DOC> /* The not so fast way is to use a loop in plsql as follows.*/SQL> SQL> declare 2 loop_count integer; 3 begin 4 loop_count := 1; 5 while loop_count <= 10 loop 6 insert into multimedia (id) values (loop_count); 7 loop_count := loop_count + 1; 8 end loop; 9 end; 10 / PL/SQL procedure successfully completed. SQL>/* Initialize the first audio clip to the actual value. */DOC>/* Then copy this value to all rows in the table. */SQL> DECLARE 2 ac CLOB; 3 amount INTEGER; 4 a_file BFILE := BFILENAME('SOME_DIR_ALIAS', 'sound_clip'); 5 BEGIN 6 UPDATE multimedia SET audio_clip = empty_clob() WHERE id = 1 returning 7 audio_clip into ac; 8 8/* Open the server side file that contains the audio clip, load it */9/* into the clob and then close the file. Note, assume that the */10/* audio clip is only 32,000 bytes long and that it starts at */11/* position 1 in the file.*/12 dbms_lob.fileopen(a_file, dbms_lob.file_readonly); 13 amount := 26; 14/* note that the destination and source offsets default to 1 */15 dbms_lob.loadfromfile(ac, a_file, amount); 16 dbms_lob.fileclose(a_file); 17 COMMIT; 18 18/* Update all rows in the table to the audio clip we just loaded. */ 19 UPDATE multimedia SET audio_clip = 20 (SELECT audio_clip FROM multimedia WHERE id = 1) 21 WHERE audio_clip is null; 22 end; 23 / PL/SQL procedure successfully completed. SQL> SQL> select id, audio_clip from multimedia; ID ----------- AUDIO_CLIP --------------------------------------------------------------------------- 1 abcdefghijklmnopqrstuvwxyz 2 abcdefghijklmnopqrstuvwxyz 3 abcdefghijklmnopqrstuvwxyz ID ----------- AUDIO_CLIP --------------------------------------------------------------------------- 4 abcdefghijklmnopqrstuvwxyz 5 abcdefghijklmnopqrstuvwxyz 6 abcdefghijklmnopqrstuvwxyz ID ----------- AUDIO_CLIP --------------------------------------------------------------------------- 7 abcdefghijklmnopqrstuvwxyz 8 abcdefghijklmnopqrstuvwxyz 9 abcdefghijklmnopqrstuvwxyz ID ----------- AUDIO_CLIP --------------------------------------------------------------------------- 10 abcdefghijklmnopqrstuvwxyz 10 rows selected. SQL> SQL> quit
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 Also:
"Example of a Read Consistent Locator" on page 6-26 for a description of what version of the |
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:
LOB's buffer in the client's address space and eventually flush the buffer to the server. This reduces the number of network roundtrips from your client application to the server, and hence, makes for better overall performance for LOB updates.
LOB updates on the server, thereby reducing the number of LOB versions and amount of logging. This results in better overall LOB performance and disk space usage.
The following caveats hold for buffered LOB operations:
LOB's buffer are always in synch with the LOB value in the server. Unless you explicitly flush the contents of a LOB's buffer, you will not see the results of your buffered writes reflected in the actual LOB on the server.
LOB operations is your responsibility. Owing to the deferred nature of the actual LOB update, error reporting for a particular buffered read or write operation is deferred until the next access to the server based LOB.
LOB operations cannot migrate across user sessions - LBS is a single user, single threaded system.
LOB operations. To ensure transactional semantics for buffered LOB updates, you must maintain logical savepoints in your application to rollback all the changes made to the buffered LOB in the event of an error. You should always wrap your buffered LOB updates within a logical savepoint.
LOB using buffered writes, it is your responsibility to ensure that the same LOB is not updated through any other operation within the scope of the same transaction that bypasses the buffering subsystem.
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.
LOB are done through its locator, just as in the conventional case. A locator that is enabled for buffering will provide a consistent read version of the LOB, until you perform a write operation on the LOB through that locator.
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. 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.
IN parameter to a PL/SQL procedure. However, passing an IN OUT or an OUT parameter will produce an error, as will an attempt to return an updated locator.
OCILobAssign(), through assignment of PL/SQL variables, through OCIObjectCopy() where the object contains the LOB attribute, and so on. Assigning a consistent read locator that was enabled for buffering to a locator that did not have buffering enabled, turns buffering on for the target locator. By the same token, assigning a locator that was not enabled for buffering to a locator that did have buffering enabled, turns buffering off for the target locator.
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.
LOB value using buffered write(s) is allowed, but only if the starting offset of these write(s) is exactly one byte (or character) past the end of the BLOB (or CLOB/NCLOB). In other words, the buffering subsystem does not support appends that involve creation of zero-byte fillers or spaces in the server based LOB.
CLOBs, Oracle8 requires that the character set form for the locator bind variable on the client side be the same as that of the LOB in the server. This is usually the case in most OCI LOB programs. The exception is when the locator is SELECTed from a remote database, which may have a different character set form from the database which is currently being accessed by the OCI program. In such a case, an error is returned. If there is no character set form input by the user, then we assume it is SQLCS_IMPLICIT.
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 up to 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 ought to specify for any given buffered read or write operation is 512K bytes, remembering that under different circumstances the maximum amount you may read/write could be smaller.
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 by a buffered read or write operation.
For example, assuming a page size of 32K, for an input offset of 1000 and a specified read/write amount of 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 a read/write amount of 30000, the second 32K region of the LOB is read into a page. For an input offset of 1000, and a read/write amount of 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 or L2, Oracle8 returns an error.
If all the buffers are dirty and you attempt another read from or write to a buffered LOB, you will raise the following error:
Error 22280: no more buffers available for operation
There are two possible causes:
In this case, flush the LOB(s) through the locator that is being used to update the LOB.
LOB without any previous buffered update operations.
In this case, write to the LOB through a locator enabled for buffering before attempting to flush buffers.
The term flush refers to a set of processes. Writing data to the LOB in the buffer through the locator transforms the locator into an updated locator. Once you have updated the LOB data in the buffer through the updated locator, a flush call will
LOB's buffer to the server-based LOB, thereby updating the LOB value,
After the flush, the locator becomes a read consistent locator and can be assigned to another locator (L2 := L1).
For instance, suppose you have two locators, L1 and L2. Let us say that they are both read consistent locators and consistent with the state of the LOB data in the server. If you then update the LOB by writing to the buffer, L1 becomes an updated locator. L1 and L2 now refer to different versions of the LOB value. If you wish to update the LOB in the server, you must use L1 to retain the read consistent state captured in L2. The flush operation writes a new snapshot environment into the locator used for the flush. The important point to remember is that you must use the updated locator (L1), when you flush the LOB buffer. Trying to flush a read consistent locator will generate an error.
This raises the question: What happens to the data in the LOB buffer? There are two possibilities. In the default mode, the flush operation retains the data in the pages that were modified. In this case, when you read or write to the same range of bytes no roundtrip to the server is necessary. Note that flush in this context does not clear the data in the buffer. It also does not return the memory occupied by the flushed buffer to the client address space.
In the second case, you set the flag parameter in OCILobFlushBuffer() to OCI_LOB_BUFFER_FREE to free the buffer pages, and so return the memory to the client address space. Note that flush in this context updates the LOB value on the server, returns a read consistent locator, and frees the buffer pages.
It is very important to note that you must flush a LOB that has been updated through the LBS:
LOB
Note that there are several cases in which you can use buffer-enabled locators and others in which you cannot.
OCILobRead(), OCILobWrite(), OCILobAssign(), OCILobIsEqual(), OCILobLocatorIsInit(), OCILobLocatorSize(), OCILobCharSetId(), OCILobCharSetForm().
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.
DBMS_LOB APIs if the input lob locator has buffering enabled.
LOB buffering cannot span transactions.
Suppose you want to save the current state of the LOB before further writing to the LOB buffer. In performing updates while using LOB buffering, writing to an existing buffer does not make a roundtrip to the server, and so does not refresh the snapshot environment in the locator. This would not be the case if you were updating the LOB directly without using LOB buffering. In that case, every update would involve a roundtrip to the server, and so would refresh the snapshot in the locator. In order to save the state of a LOB that has been written through the LOB buffer, you therefore need to
LOB, thereby updating the LOB and the snapshot environment in the locator (L1). At this point, the state of the locator (L1) and the LOB are the same.
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.
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");
}
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:
LOB from the client side and the client is at a different node than the server, large reads/writes reduce network overhead.
NOCACHE' option, each small read/write incurs an I/O. Reading/writing large quantities of data reduces the I/O.
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.
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.
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.
LOBs. LOBs are optimized for the following:
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.
As stated above, CLOBs store fixed width single byte data, and NCLOBs store fixed width multi byte data. Neither supports varying width data.
You might expect from this that if the database character set is varying width, and a user tries to create a table with a CLOB column, the create will fail. This is almost the case, but the reality is a little different.
CLOB column, the create will fail.
CLOB column, the create will succeed. However, subsequent inserts into the table will fail if the CLOB column has a value other than NULL.
The same holds true for NCLOBs and the database national character set.
The reason for allowing the SQL DDL to pass while making sure that the SQL DML fails if the user tries to insert a non-null value into the LOB that has a varying width character set is so that the same table can be created and exist in several different databases regardless of the underlying CHAR (NCHAR) character set. The user can write one application and modify it slightly for databases where the CHAR (NCHAR) character set is varying width such that the insert sets the varying width LOB to NULL.
Although not explicitly marked, this section is organized on the following basis.
EMPTY_BLOB(), EMPTY_CLOB() and BFILENAME() which are used for initialization (immediately following this overview).
LOBs (beginning with "Using the OCI to Manipulate LOBs" on page 6-64).
DBMS_LOB package, listing all functions and procedures (beginning with "DBMS_LOB Package" on page 6-68). This section contains the main body of technical specifications that underlie LOBs.
LOBs at the time of the first production release of Oracle8.
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.
Pragmas
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, NULL); UPDATE lob_table SET c_lob = EMPTY_CLOB() WHERE key_value = 1001; INSERT INTO lob_table VALUES (1002, NULL, 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. You can call the CREATE DIRECTORY() command after BFILENAME(). However, the target object must exist by the time you actually use the BFILE locator (for example, as having been used as a parameter to an operation such as OCILobFileOpen()or DBMS_LOB.FILEOPEN()).
Note that 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 was 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. This is described in the examples.
Syntax
FUNCTION BFILENAME(directory_alias IN VARCHAR2, filename IN VARCHAR2) RETURN BFILE;
|
See Also:
"DIRECTORY Name Specification" on page 6-17 for information about the use of uppercase letters in the directory name, and |
Parameters
| Parameter Name | Meaning |
|---|---|
|
directory_alias |
The name of the |
|
filename |
The name of the operating system file on the server. |
Return Values
Pragmas
None.
Exceptions
None.
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;
The OCI includes functions that you can use to access data stored in BLOBs, CLOBs, NCLOBs, and BFILEs. These functions are mentioned briefly below.
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.
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. If your operating system uses case-sensitive pathnames, be sure you specify the directory in the correct format.
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:
APPEND() - append the contents of the source LOB to the destination LOB
COPY() - copy all or part of the source LOB to the destination LOB
ERASE() - erase all or part of a LOB
LOADFROMFILE() - load BFILE data into an internal LOB
TRIM() - trim the LOB value to the specified shorter length
WRITE()- write data to the LOB from a specified offset
The routines that read or examine LOB values are:
GETLENGTH() - get the length of the LOB value
INSTR() - return the matching position of the nth occurrence of the pattern in the LOB
READ() - read data from the LOB starting at the specified offset
The read-only routines specific to BFILEs are:
FILECLOSE() - close the file
FILECLOSEALL()- close all previously opened files
FILEEXISTS() - check if the file exists on the server
FILEGETNAME() - get the directory alias and file name
FILEISOPEN() - check if the file was opened using the input BFILE
FILEOPEN() - open a file
Parameters for the DBMS_LOB routines use the datatypes:
BLOB, for a source or destination binary LOB
RAW, for a source or destination raw buffer (used with BLOB)
CLOB, for a source or destination character LOB (including NCLOB)
VARCHAR2, for a source or destination character buffer (used with CLOB and NCLOB)
INTEGER, to specify the size of a buffer or LOB, the offset into a LOB, or the amount to access
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 variable as input.
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.
A DBMS_LOB function or procedure can raise any of the named exceptions shown in Table 6-4 .
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.
You can provide secure access to BFILEs using the DIRECTORY feature discussed in "BFILENAME() Function" on page 6-62.
BLOBs and BFILES, and characters for CLOBs and NCLOBs.
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.
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.
AMOUNT and OFFSET parameters. This implies that: negative offsets and ranges observed in Oracle SQL string functions and operators are not allowed.
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.
LOB before calling any routines that modify the LOB such as APPEND, COPY, ERASE, TRIM, or WRITE. These routines do not implicitly lock the row containing the LOB.
COMPARE(), INSTR() and SUBSTR() are DBMS_LOB specific, the operations COMPARE(), INSTR(), READ(), SUBSTR(), FILECLOSE(), FILECLOSEALL() and LOADFROMFILE() operate only on an opened BFILE locator, that is, a successful FILEOPEN() call must precede a call to any of these routines.
FILEEXISTS(), FILEGETNAME() and GETLENGTH(), a file's open/close status is unimportant, however the file must exist physically and you must have adequate privileges on the DIRECTORY object and the file.
DBMS_LOB package does not support any concurrency control mechanism for BFILE operations.
FILECLOSEALL() routine to close all files opened in the session, and resume file operations from the beginning.
DIRECTORY or have system privileges, use the CREATE OR REPLACE, DROP and REVOKE statements in SQL with extreme caution.
If you or other grantees of a particular directory object have several open files in a session, any of the above commands can adversely affect file operations. In the event of such abnormal termination, your only choice is to invoke a program or anonymous block that calls FILECLOSEALL(), reopen your files, and restart your file operations.
BFILE.
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.
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.
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 file 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:
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.
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 |
|---|---|
|
|
The locator for the internal |
|
|
The locator for the internal |
Exceptions
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
Pragmas
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.
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
Table 6-7 COPY Parameters
Return Value
None.
Pragmas
None.
Exceptions
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
Table 6-8 ERASE Parameters
Return Values
None.
Pragmas
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;
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
Locator for the
Table 6-9 FILECLOSE Parameter
Parameter Name
Meaning
file_loc
BFILE to be closed.
Return Values
Pragmas
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;
You can call the FILECLOSEALL() procedure to close all BFILEs opened in the session.
Syntax
PROCEDURE FILECLOSEALL;
Return Values
Pragmas
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;
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
Locator for the
Table 6-10 FILEEXISTS Parameter
Parameter Name
Meaning
file_loc
BFILE.
Return Values
Pragmas
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;
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
Locator for the
Directory alias
Name of the
Table 6-11 FILEGETNAME Parameters
Parameter Name
Meaning
file_loc
BFILE.
dir_alias
filename
BFILE
Return Values
Pragmas
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;
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
Locator for the
Table 6-12 FILEISOPEN Parameter
Parameter Name
Meaning
file_loc
BFILE.
Return Values
Pragmas
PRAGMARESTRICT_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;
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
Locator for the BFILE.
Open mode.
Table 6-13 FILEOPEN Parameters
Parameter Name
Meaning
file_loc
open_mode
Return Values
Pragmas
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;
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
l
The locator for the
Table 6-14 GETLENGTH Parameter
Parameter Name
Meaning
ob_loc
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
Table 6-15 INSTR 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.
A NULL is returned if:
Pragmas
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;
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.
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
Table 6-16 INSTR Parameters
Return Values
Pragmas
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
Table 6-17 READ Parameters
Return Values
Pragmas
Exceptions
READ can raise any of the following exceptions:
VALUE_ERROR
INVALID_ARGVAL
NO_DATA_FOUND
BFILEs 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
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.
VARCHAR2, for the CLOB version.
NULL, if:
Pragmas
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;
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
lob_loc
The locator for the internal LOB whose length is to be trimmed.
The new, trimmed length of the
Table 6-19 TRIM Parameters
Parameter Name
Meaning
newlen
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;
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
Table 6-20 WRITE Parameters
Return Values
Pragmas
Exceptions
Example
PROCEDURE Example_16 IS lob_loc BLOB; buffer RAW; amt BINARY_INTEGER := 32767; pos INTEGER := 2147483647; i INTEGER; BEGIN SELECT b_col INTO lob_loc FROM lob_table WHERE key_value = 12; FOR i IN 1..3 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;
The use of LOBs are subject to some restrictions:
LOBs must be stored in tables -- they cannot be transient/temporary.
LONG datatype may not be converted nor migrated to a LOB datatype and vice versa.
A workaround is to do the following:
RAW to a server side file.
CREATE DIRECTORY to point to the directory where the file was written.
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.
LOBs are not supported. Specifically, this means that the user cannot use a remote locator in the SELECT and WHERE clauses. This includes using DBMS_LOB package functions. In addition, references to objects in remote tables with or without LOB attributes is not allowed.
For example, the following operations are invalid:
SELECT lobcol from table1@remote_site;
INSERT INTO lobtable select type1.lobattr from table1@remote_site;
SELECT dbms_lob.length(lobcol) from table1@remote_site;
Valid operations on LOB columns in remote tables include:
LOBs. Instead, use OCILobLoadFromFile(), DBMS_LOB.LOADFROMFILE(), or OCILobWrite() with streaming.
LOB in order to use piece-wise INSERT/UPDATE, the bind variable may be of type SQLT_CHR or SQLT_LBI but is limited to 4k. You cannot bind a SQLT_LNG to a LOB or a SQLT_LBI that is longer than 4k.
Also, LOBs are not allowed in the following places:
LOBs are not allowed in partitioned tables nor are they allowed in clustered tables and thus cannot be a cluster key.
LOBs are not allowed in GROUP BY, ORDER BY, SELECT DISTINCT, aggregates and JOINS. However, UNION ALL is allowed on tables with LOBs. UNION, MINUS, and SELECT DISTINCT are allowed on LOB attributes if the object type has a MAP or ORDER function.
LOBS are not analyzed in ANALYZE... COMPUTE/ESTIMATE STATISTICS statements.
LOBs are not allowed in index only tables.
LOBs are not allowed in VARRAYs.
NCLOBs are not allowed as attributes in object types but NCLOB parameters are allowed in methods.
LOBs. However, you can use a LOB in the body of a trigger as follows:
LOB (:old or :new value) in any kind of trigger.
INSTEAD OF triggers, you can read the :old and the :new values, which is to say that the :old and :new values can be read but not written.
LOB type columns in an OF clause, because BFILE types can be updated without updating the underlying table on which the trigger is defined.
DBMS_LOB package to update LOB values or LOB attributes of object columns will not fire triggers defined on the table containing the columns or the attributes.
DBMS_LOB package routines. However, you can use server-side PL/SQL procedures or anonymous blocks in PRO*C to call the DBMS_LOB package routines.
