Oracle® Database Data Cartridge Developer's Guide, 10g Release 2 (10.2) Part Number B14289-02 |
|
|
View PDF |
This chapter contains these topics:
Some data cartridges need to handle large amounts of raw binary data, such as graphic images or sound waveforms, or character data, such as text or streams of numbers. Oracle supports large objects(LOB
s) to handle these kinds of data.
Internal LOBs are stored in the database tablespaces in way that optimizes space and provides efficient access. Internal LOB
s participate in the transactional model of the server.
Internal LOBs
can store binary data (BLOB
s), single-byte character data (CLOB
s), or fixed-width single-byte or multibyte character data (NCLOB
s). An NCLOB
consists of character data that corresponds to the national character set defined for the Oracle database. Varying width character data is not supported in Oracle.
External LOBs
are stored in operating system files outside the database tablespaces as BFILE
s, binary data. They cannot participate in transactions.
Together, internal LOB
s and in BFILE
s provide considerable flexibility in handling large amounts of data.
Data stored in a LOB
is called the LOB
's value. To the Oracle server, a LOB
's value is unstructured and cannot be queried. You must unpack and interpret a LOB
's value in cartridge-specific ways.
LOB
s can be manipulated using the Oracle Call Interface (OCI) or the PL/SQL DBMS_LOB
package. You can write functions (including methods on object types that can contain LOB
s) to manipulate parts of LOB
s. Details on LOBs
can be found in the Oracle Database Application Developer's Guide - Large Objects.
LOB definition can involve the CREATE
TYPE
and the CREATE
TABLE
statements. For example, the following statement specifies a CLOB
within a datatype named lob_type:
CREATE OR REPLACE TYPE lob_type AS OBJECT ( id INTEGER, data CLOB );
The following statement creates an object table, lob_table
, in which each row is an instance of lob_type
data:
CREATE TABLE lob_table OF lob_type;
The following statement stores LOBs
in a regular table, as opposed to an object table as in the preceding statement:
CREATE TABLE lob_table1 ( id INTEGER, b_lob BLOB, c_lob CLOB, nc_lob NCLOB, b_file BFILE );
When creating LOBs in tables, you can set the LOB storage, buffering, and caching properties. See the Oracle Database SQL Reference manual and the Oracle Database Application Developer's Guide - Large Objects for information about using LOBs in the following DDL statements:
CREATE
TABLE
and ALTER
TABLE
LOB
columns
LOB
storage clause
NOCACHE
and NOLOGGING
options
CREATE
TYPE
and ALTER
TYPE
BLOB
, CLOB
and BFILE
datatypes
LOBs
can be stored with other row data or separate from row data. Regardless of the storage location, each LOB
has a locator, which can be viewed as a handle or pointer to the actual location. Selecting a LOB
returns the LOB
locator instead of the LOB
value.
The following PL/SQL code selects the LOB
locator for b_lob and place it a PL/SQL local variable named image1
:
DECLARE image1 BLOB; image_no INTEGER := 101; BEGIN SELECT b_lob INTO image1 FROM lob_table WHERE key_value = image_no; ... END;
When you use an API function to manipulate the LOB
value, you refer to the LOB
using the locator. The PL/SQL DBMS_LOB
package contains useful routines to manipulate LOBs
, such as PUT_LINE
and GETLENGTH
:
BEGIN DBMS_OUTPUT.PUT_LINE('Size of the Image is: ', DBMS_LOB.GETLENGTH(image1)); END;
In the OCI, LOB
locators are mapped to LOBLocatorPointers
, such as OCILobLocator *
.
The OCI LOB
interface and the PL/SQL DBMS_LOB
package are described briefly in this chapter. The OCI is described in more detail in the Oracle Call Interface Programmer's Guide. The DBMS_LOB
API is described in the Oracle Database Application Developer's Guide - Large Objects.
For a BFILE
, the LOB
column has its own distinct locator, which refers to the LOB's
value that is stored in an external file in the server's file system. This implies that two rows in a table with a BFILE
column may refer to the same file or two distinct files. 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.
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 Oracle 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;
Note:
The parentheses are required syntax for both functions.Parameters
None.
Return Values
EMPTY_BLOB
returns an empty locator of type BLOB
and EMPTY_CLOB
returns an empty locator of type CLOB
, which can also be used for NCLOBs
.
Pragma
None.
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
used with SQL DML:
INSERT INTO lob_table VALUES (1001, EMPTY_BLOB(), 'abcde', NULL); UPDATE lob_table SET c_lob = EMPTY_CLOB() WHERE key_value = 1001; INSERT INTO lob_table VALUES (1002, NULL, NULL, NULL);
The following example shows the correct and erroneous usage of EMPTY_BLOB
and EMPTY_CLOB
in PL/SQL programs:
DECLARE loba BLOB; lobb CLOB; read_offset INTEGER; read_amount INTEGER; rawbuf RAW(20); charbuf VARCHAR2(20); BEGIN loba := EMPTY_BLOB(); read_amount := 10; read_offset := 1; -- the following read will fail dbms_lob.read(loba, read_amount, read_offset, rawbuf); -- the following read will succeed; UPDATE lob_table SET c_lob = EMPTY_CLOB() WHERE key_value = 1002 RETURNING c_lob INTO lobb; dbms_lob.read(lobb, read_amount, read_offset, charbuf); dbms_output.put_line('lobb value: ' || charbuf);
The OCI includes functions that you can use to access data stored in BLOBs
, CLOBs
, NCLOBs
, and BFILEs
. These functions are mentioned briefly in Table 6–1. For detailed documentation, including parameters, parameter types, return values, and example code, see the Oracle Call Interface Programmer's Guide.
Table 6-1 OCI Functions for Manipulating LOBs
Function | Description |
---|---|
OCILobAppend() |
Appends |
OCILobAssign() |
Assigns one |
OCILobCharSetForm() |
Returns the character set form of a |
OCILobCharSetId() |
Returns the character set ID of a |
OCILobCopy() |
Copies a portion of a |
OCILobDisableBuffering() |
Disables the buffering subsystem use. |
OCILobEnableBuffering() |
Uses the |
OCILobErase() |
Erases part of a |
OCILobFileClose() |
Closes an open |
OCILobFileCloseAll() |
Closes all open |
OCILobFileExists() |
Tests to see if a |
OCILobFileGetName() |
Returns the name of a |
OCILobFileIsOpen() |
Tests to see if a |
OCILobFileOpen() |
Opens a |
OCILobFileSetName() |
Sets the name of a |
OCILobFlushBuffer() |
Flushes changes made to the |
OCILobGetLength() |
Returns the length of a |
OCILobIsEqual() |
Tests to see if two |
OCILobLoadFromFile() |
Loads |
OCILobLocatorIsInit() |
Tests to see if a |
OCILobLocatorSize() |
Returns the size of a |
OCILobRead() |
Reads a specified portion of a non-null |
OCILobTrim() |
Truncates a |
OCILobWrite() |
Writes data from a buffer into a |
Table 6–2 compares the OCI and PL/SQL (DBMS_LOB
package) interfaces in terms of LOB
access.
Table 6-2 OCI and PL/SQL (DBMS_LOB) Interfaces Compared
OCI (ociap.h) | PL/SQL DBMS_LOB (dbmslob.sql) |
---|---|
N/A |
DBMS_LOB.COMPARE() |
N/A |
DBMS_LOB.INSTR() |
N/A |
DBMS_LOB.SUBSTR() |
OCILobAppend |
DBMS_LOB.APPEND() |
OCILobAssign |
N/A [use PL/SQL assign operator] |
OCILobCharSetForm |
N/A |
OCILobCharSetId |
N/A |
OCILobCopy |
DBMS_LOB.COPY() |
OCILobDisableBuffering |
N/A |
OCILobEnableBuffering |
N/A |
OCILobErase |
DBMS_LOB.ERASE() |
OCILobFileClose |
DBMS_LOB.FILECLOSE() |
OCILobFileCloseAll |
DBMS_LOB.FILECLOSEALL() |
OCILobFileExists |
DBMS_LOB.FILEEXISTS() |
OCILobFileGetName |
DBMS_LOB.FILEGETNAME() |
OCILobFileIsOpen |
DBMS_LOB.FILEISOPEN() |
OCILobFileOpen |
DBMS_LOB.FILEOPEN() |
OCILobFileSetName |
N/A (use |
OCILobFlushBuffer |
N/A |
OCILobGetLength |
DBMS_LOB.GETLENGTH() |
OCILobIsEqual |
N/A [use PL/SQL equal operator] |
OCILobLoadFromFile |
DBMS_LOB.LOADFROMFILE() |
OCILobLocatorIsInit |
N/A [always initialize] |
OCILobRead |
DBMS_LOB.READ() |
OCILobTrim |
DBMS_LOB.TRIM() |
OCILobWrite |
DBMS_LOB.WRITE() |
The following example shows a LOB
being selected from the database into a locator. This example assumes that the type lob_type
has two attributes (id
of type INTEGER
and data
of type CLOB
) and that a table (lob_table
) of this type (lob_type
) has been created.
/*---------------------------------------------------------------------*/ /* Select lob locators from a CLOB column */ /* We need the 'FOR UPDATE' clause because we need to write to the LOBs. */ /*---------------------------------------------------------------------*/ static OCIEnv *envhp; static OCIServer *srvhp; static OCISvcCtx *svchp; static OCIError *errhp; static OCISession *authp; static OCIStmt *stmthp; static OCIDefine *defnp1; static OCIBind *bndhp; sb4 select_locator(int rowind) { sword retval; boolean flag; int colc = rowind; OCILobLocator *clob; text *sqlstmt = (text *)"SELECT DATA FROM LOB_TABLE WHERE ID = :1 FOR UPDATE"; if (OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4) strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtPrepare() sqlstmt\n"); return OCI_ERROR; } if (OCIStmtBindByPos(stmthp, bndhp, errhp, (ub4) 1, (dvoid *) &colc, (sb4) sizeof(colc), SQLT_INT, (dvoid *) 0, (ub2 *)0, (ub2 *)0, (ub4) 0, (ub4 *) 0, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtBindByPos()\n"); return OCI_ERROR; } if (OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1, (dvoid *) &clob, (sb4) -1, (ub2) SQLT_CLOB, (dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIDefineByPos()\n"); return OCI_ERROR; } /* Execute the select and fetch one row */ if (OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)) { (void) printf("FAILED: OCIStmtExecute() sqlstmt\n"); report_error(); return OCI_ERROR; } /* Now test to see if the LOB locator is initialized */ retval = OCILobLocatorIsInit(envhp, errhp, clob, &flag); if ((retval != OCI_SUCCESS) && (retval != OCI_SUCCESS_WITH_INFO)) { (void) printf("Select_Locator --ERROR: OCILobLocatorIsInit(), retval = %d\n", retval); report_error(); checkerr(errhp, retval); return OCI_ERROR; } if (!flag) { (void) printf("Select_Locator --ERROR: LOB Locator is not initialized.\n"); return OCI_ERROR; } return OCI_SUCCESS; }
A sample program, populate.c
, uses the OCI to populate a CLOB
with the contents of a file is included on the disk.
The DBMS_LOB
package can be used to manipulate LOBs
from PL/SQL.
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
SUBSTR
() -- return part of the LOB
value starting at the specified offset
The read-only routines specific to BFILEs
are:
FILECLOSE
() -- close the file
FILECLOSEALL
() -- close all previously opened files
FILEEXISTS
() -- test to see if the file exists on the server
FILEGETNAME
() -- get the directory alias and file name
FILEISOPEN
() -- test to see if the file was opened using the input BFILE
locators
FILEOPEN
() -- open a file
The following example calls the TRIM
procedure to trim a CLOB
value to a smaller length is shown in the following example. This example assumes that the type lob_type
has two attributes (id
of type INTEGER
and data
of type CLOB
) and that a table (lob_table
) of this type (lob_type
) has been created.
PROCEDURE Trim_Clob IS clob_loc CLOB; BEGIN -- get the LOB Locator SELECT data into clob_loc FROM lob_table WHERE id = 179 FOR UPDATE; -- call the TRIM Routine DBMS_LOB.TRIM(clob_loc, 834004); COMMIT; END;
Because this example deals with CLOB
data, the second argument (834004
) to DBMS_LOB
.TRIM
specifies the number of characters. If the example dealt with BLOB
data, this argument would be interpreted as the number of bytes.
LOB locators can be passed as arguments to an external procedure. The corresponding C routine gets an argument of type OCILobLocator
*. For example, a PL/SQL external procedure could be defined as:
FUNCTION DS_Findmin(data CLOB) RETURN PLS_INTEGER IS EXTERNAL NAME "c_findmin" LIBRARY DS_Lib LANGUAGE C;
When this function is called, it invokes a routine (c_findmin
) with the signature:
int c_findmin (OCILobLocator *)
This routine in a shared library associated with DS_Lib
. In order to use the pointer OCILobLocator *
to get data from the LOB
(for example, using OCILobRead
()), you must reconnect to the database by performing a callback.
You cannot write to a LOB
(:old
or :new
value) in any kind of trigger.
In regular triggers, you can read the :old value but you cannot read the :new value. In INSTEAD
OF
triggers, you can read the :old and the :new values.
You cannot specify LOB
type columns in an OF
clause, because BFILE
types can be updated without updating the underlying table on which the trigger is defined.
Using OCI functions or the 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.
The Open/Close
functions let you indicate the beginning and end of a series of LOB operations so that large-scale operations, such updating indexes, can be performed once the Close
function is called. This means that once the Open
call is made, the index would not be updated each time the LOB is modified, and that such updating would not resume until the Close
call.
You do not have to wrap all LOB
operations inside the Open/Close
operations, but this function can be very useful for cartridge developers.
For one thing, if the you do not wrap LOB
operations inside an Open/Close
call, then each modification to the LOB
will implicitly open and close the LOB,
thereby firing any triggers. But if do you wrap the LOB
operations inside a pair of Open/Close
operations, then the triggers will not be fired for each LOB
modification. Instead, one trigger will be fired at the time the Close
call is made. LIkewise, extensible indexes will not be updated until the user calls Close
. This means that any extensible indexes on the LOB
are not valid between the Open/Close
calls.
You need to apply this technology carefully since state, reflecting the changes to the LOB
, is not saved between the Open
and the Close
operations. Once you have called Open
, Oracle no longer keeps track of what portions of the LOB
value were modified, nor of the old and new values of the LOB
that result from any modifications. The LOB
value is still updated directly for each OCILob*
or DBMS_LOB
operation, and the usual read consistency mechanism is still in place. Moreover, you may want extensible indexes on the LOB
to be updated as LOB modifications are made because in that case, the extensible LOB
indexes are always valid and may be used at any time.
The API enables you to find out if the LOB
is "open" or not. In all cases openness is associated with the LOB
, not the locator. The locator does not save any information as to whether the LOB
to which it refers is open.
Note that it is an error to commit the transaction before closing all previously opened LOB
s. At transaction rollback time, all LOB
s that are still open will be discarded, which means that they will not be closed thereby firing the triggers).
Only 32 LOB
s may be open at any one time. An error will be returned when the 33rd LOB
is opened. Assigning an already opened locator to another locator does not incur a round trip to the server and does not count as opening a new LOB
(both locators refer to the same LOB
).
It is an error to Open/Close
the same LOB
twice either with different locators or with the same locator. It is an error to close a LOB
that has not been opened.
Assume that loc1
is refers to an opened LOB
and is assigned to loc2
. If loc2
is subsequently used to modify the LOB
value, the modification is grouped together with loc1
's modifications (that is, there is only one entry in the LOB
manager's state, not one for each locator). Once the LOB
is closed (through loc1
or loc2
), the triggers are fired and all updates made to the LOB
through any locator are committed. After the close of the LOB
, if the user tries to use either locator to modify the LOB
, the operation will be performed as Open/
operation
/Close
. Note that consistent read is still maintained for each locator. This discussion is merely showing that the LOB
, not the locator, is opened and closed. No matter how many copies of the locator are made, the triggers for the LOB
are fired only once on the first Close
call.
open (loc1); loc2 := loc1; write (loc1); write (loc2); open (loc2); /* error because the LOB is already open */ close (loc1); /* triggers are fired and all LOB updates made prior to this statement by any locator are incorporated in the extensible index */ write (loc2); /* implicit open, write, implicit close */