Release 8.1.5
A68004-01
 Library |
 Product |
 Contents |
 Index |
| Oracle 8i Application Developer's Guide - Large Objects (LOBs) Release 8.1.5 A68004-01 |
|
This introductory chapter discusses with the following topics:
Oracle8 regards LOBs as being of two kinds depending on their location with regard to the database -- internal LOBs and external LOBs, also referred to as BFILEs (binary files). Note that when we discuss some aspect of working with LOBs without specifying whether the LOB is internal or external, the characteristic under discussion pertains to both internal and external LOBs.
Internal LOBs are further divided into those that are persistent and those that are temporary.
Internal LOBs, as their name suggests, are stored inside 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. You can recover internal LOBs in the event of transaction or media failure, and any changes to a internal LOB value can be committed or rolled back. In other words, all the ACID properties that pertain to using database objects pertain to using internal LOBs.
There are three SQL datatypes for defining instances of internal LOBs:
LOB whose value is composed of unstructured binary ("raw") data.
LOB whose value is composed of character data that corresponds to the database character set defined for the Oracle8 database.
LOB whose value is composed of character data that corresponds to the national character set defined for the Oracle8 database.
External LOBs (BFILES) are large binary data objects stored in operating system files outside of database tablespaces. These files use reference semantics. Apart from conventional secondary storage devices such as hard disks, BFILEs may also be located on tertiary block storage devices such as CD-ROM, PhotoCDs and DVDs. But note that you cannot locate a single BFILE on more than one device, for instance, striped across a disk array.
The SQL datatype BFILE allows read-only byte stream I/O access to large files existing on the filesystem of the database server. The Oracle Server can access BFILEs provided the underlying server operating system supports a stream-mode access to these operating system (OS) files.
There is one external SQL LOB datatype:
LOB whose value is composed of binary ("raw") data, and is stored outside of the database tablespaces in a server-side operating system file.
You can create a table with CLOB/NCLOB columns even if the CHAR/NCHAR database character set is varying width. You can also create a table with a type that has a CLOB attribute irrespective of whether the CHAR database character set is of varying width. However, NCLOBs are not allowed as attributes in object types.
The CLOB/NCLOB value is stored in the database using the 2 byte Unicode character set which is fixed width. The stored Unicode value is translated to the (possibly varying width) character set that you request on either the client or the server. When you insert data into the CLOB/NCLOB, the data input can be in a varying width character set. This varying width character data is implicitly converted into Unicode before the data is stored in the database. Note that all translations to and from Unicode are implicitly performed by Oracle.
You can perform the full gamut of LOB operations on CLOB/NCLOBs (read, write, trim, erase, compare, etc.) All programmatic environments that provide access to CLOBs/NCLOBs work on CLOBs/NCLOBs in databases where the CHAR/NCHAR character set is of varying width. This includes SQL, PL/SQL, OCI, PRO*C, DBMS_LOB, and so on. However, you should take note of the following issue that pertain to specific environments.
Regardless of the client-side character set, the offset and amount parameters are always in characters for CLOBs/NCLOBs and in bytes for BLOBs/BFILEs.
The following decisions only apply to varying-width client-side character sets. For fixed-width client side character sets, the offset and amount parameters are always in characters for CLOBs and NCLOBs and in bytes for BLOBs and BFILEs.
CLOBs and NCLOBs, and in bytes for BLOBs and BFILEs.
OCILobFileGetLength: Regardless of whether the client-side character set is varying-width, the output length is in characters for CLOBs and NCLOBs and in bytes for BLOBs and BFILEs.
OCILobRead: If the client-side character set is varying-width, for CLOBs and NCLOBs, the input amount is in characters and the output amount is in bytes. The input amount refers to the number of characters to read from the server-side CLOB or NCLOB. The output amount indicates how many bytes were read into the buffer 'bufp'.
OCILobWrite: If the client-side character set is varying-width, for CLOBs and NCLOBs, the input amount is in bytes and the output amount is in characters. The input amount refers to the number of bytes of data that are in the input buffer 'bufp'. The output amount refers to the number of characters written into the server-side CLOB or NCLOB.
For all other LOB operations, irrespective of the client-side character set, the amount parameter is in characters for CLOBs and NCLOBs. These include OCILobCopy, OCILobErase, OCILobLoadFromFile, and OCILobTrim. All these operations refer to the amount of LOB data on the server.
LOBs are similar to LONG and LONG RAW types, but differ in the following ways:
LOBs in a single row but you can store only one LONG or LONG RAW per row.
A LOBs can be attributes of a user-defined datatype but this is not possible with either a LONG or LONG RAW.
BLOB and CLOB data can be stored in separate tablespaces and BFILE data is stored as an external file. In the case of a LONG or LONG RAW the entire value is stored in the table column. For inline LOBs, Oracle will store up to 3964 bytes of data in the table column.
LOB column, it is the locator which is returned. When you access a LONG or LONG RAW, the entire value is returned.
BFILE maximum is operating system dependent, but cannot exceed 4 gigabytes. The valid accessible range is 1 to (232-1). By contrast, a LONG or LONG RAW is limited to 2 gigabytes.
LOBs than there is with LONG or LONG RAW data. LOBs can be accessed at random offsets while LONGs must be accessed from the beginning to the desired location
LOBs in both local and distributed environments, but this is not possible with aLONG or LONG RAW (see Oracle8i Replication).
Existing LONG columns can be converted to LOBs using the TO_LOB() function (see "Copy LONG to LOB" in Chapter 2, ". Internal Persistent LOBs"). However note that Oracle8i does not support conversion of LOBs back to LONGs.
The use of LOBs are subject to some restrictions:
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.getlength(lobcol) from table1@remote_site;
Valid operations on LOB columns in remote tables include:
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 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 partitioned index organized tables but are allowed non-partitioned index organized tables.
LOBs are not allowed in VARRAYs.
NCLOBs are not allowed as attributes in object types but NCLOB parameters are allowed in methods.
new and :old LOB values bound in the trigger are read-only which means that you cannot write to the LOB. More specifically:
INSTEAD OF triggers on views, you can read both the :new and :old values.
LOB column in an OF clause (Note that a BFILE can be modified without updating the underlying tables on which it is based).
DBMS_LOB routines to update LOB values or LOB attributes on object columns, the functions or routines will not fire the triggers defined on the tables containing the columns or attributes.
DBMS_LOB package routines. However, you can use server-side PL/SQL procedures or anonymous blocks in Pro*C/C++ to call the DBMS_LOB package routines.
A limited number of BFILEs can be open simultaneously per session. The initialization parameter, SESSION_MAX_OPEN_FILES defines an upper limit on the number of simultaneously open files in a session.
The default value for this parameter is 10. That is, you can open a maximum of 10 files at the same time per session if the default value is utilized. If you want to alter this limit, the database administrator can change the value of this parameter in the init.ora file. For example:
SESSION_MAX_OPEN_FILES=20
If the number of unclosed files 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.
SQL DML provides basic operations -- INSERT, UPDATE, SELECT, DELETE -- that let you make changes to the entire values of internal LOBs within the Oracle ORDBMS. To work with parts of internal LOBs, you will need to use one of the interfaces that have been developed to handle more complex requirements.
Oracle8 supports read-only operations on external LOBs. So if you need to update/write to external LOBs, you will have to develop client side applications suited to your needs
Oracle now offers you six different environments for working with LOBs:
The following chart compares the six LOB interfaces.
The following subsections describe each of the interfaces in more detail.
The DBMS_LOB package can be used to read and modify internal LOBs (persistent and temporary) either entirely or in a piece-wise manner. This package can also be used for read operations on BFILEs.
|
For more information see:
|
As described in more detail below, 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 exists in the database tablespaces or external filesystem before you invoke the routine.
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 define a DIRECTORY object that maps to a valid physical directory containing the external LOBs that you intend to access. Also, these files must exist, and must be set to have read permissions for the Oracle server process. If your operating system uses case-sensitive path names, 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:
DBMS_LOB Routines that Modify BLOB, CLOB, and NCLOB values
The routines involved in reading or examining LOB values are:
The following routines have to do with temporary lobs:
| Function/Procedure | Description |
|---|---|
|
|
creates a temporary LOB |
|
|
checks if a LOB locator refers to a temporary LOB |
|
|
frees a temporary LOB |
The read-only routines specific to BFILEs are:
BFILEs
The following routines have to do with opening and closing LOBs:
| Function/Procedure | Description |
|---|---|
|
|
opens a LOB |
|
|
sees if a LOB is open |
|
|
closes a LOB |
We will describe these routines in greater detail as we explore specific LOB operations (e.g., INSERT a row containing a LOB).
You can make changes to an entire internal LOB, or to pieces of the beginning, middle or end of it through the OCI API. You can access both internal and external LOBs for read purposes, and you can also write to internal LOBs.
The OCI includes functions that you can use to access data stored in BLOBs, CLOBs, NCLOBs, and BFILEs. These functions are listed in the tables below, and are discussed in greater detail later in the chapter.
Users who want to read or write data in UCS2 format can set the 'csid' parameter in OCILobRead and OCILobWrite to OCI_UCS2ID. The 'csid' parameter indicates the csid for the buffer parameter. You can set the 'csid' parameter to any character set id. If the csid parameter is set, it will override the NLS_LANG environment variable.
|
For more information see:
|
The routines that can modify BLOB, CLOB, and NCLOB values are:
The routines that read or examine LOB values are:
The following routines are have to do with temporary lobs:
| Function/Procedure | Description |
|---|---|
|
|
creates a temporary LOB |
|
|
sees if a temporary LOB exists |
|
|
frees a temporary LOB |
Read-only routines specific to BFILEs are:
These routines are used for working with LOB locators:
The following three routines have to do with LOB-buffering:
The following routines have to do with opening and closing LOBs:
| Function/Procedure | Description |
|---|---|
|
|
opens a LOB |
|
|
sees if a LOB is open |
|
|
closes a LOB |
In order to work with the OCI examples in the remainder of the book, you could use a main() like the following. Here, its use with the seeIfLOBIsOpen procedure is shown as an example.
int main(char *argv, int argc) { /* Declare OCI Handles to be used */ OCIEnv *envhp; OCIServer *srvhp; OCISvcCtx *svchp; OCIError *errhp; OCISession *authp; OCIStmt *stmthp; OCILobLocator *Lob_loc; /* Create and Initialize an OCI Environment: */ (void) OCIEnvCreate(&envhp, (ub4)OCI_DEFAULT, (dvoid *)0, (dvoid * (*)(dvoid *, size_t)) 0, (dvoid * (*)(dvoid *, dvoid *, size_t))0, (void (*)(dvoid *, dvoid *))0, (size_t) 0, (dvoid **) 0); /* Allocate error handle: */ (void) OCIHandleAlloc((dvoid *) envhp, (dvoid **) &errhp, OCI_HTYPE_ERROR, (size_t) 0, (dvoid **) 0); /* Allocate server contexts: */ (void) OCIHandleAlloc((dvoid *) envhp, (dvoid **) &srvhp, OCI_HTYPE_SERVER, (size_t) 0, (dvoid **) 0); /* Allocate service context: */ (void) OCIHandleAlloc((dvoid *) envhp, (dvoid **) &svchp, OCI_HTYPE_SVCCTX, (size_t) 0, (dvoid **) 0); /* Attach to the Oracle database: */ (void) OCIServerAttach(srvhp, errhp, (text *)"", strlen(""), 0); /* Set the server context attribute in the service context: */ (void) OCIAttrSet ((dvoid *) svchp, OCI_HTYPE_SVCCTX, (dvoid *)srvhp, (ub4) 0, OCI_ATTR_SERVER, (OCIError *) errhp); /* Allocate the session handle: */ (void) OCIHandleAlloc((dvoid *) envhp, (dvoid **)&authp, (ub4) OCI_HTYPE_SESSION, (size_t) 0, (dvoid **) 0); /* Set the username in the session handle:*/ (void) OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION, (dvoid *) "samp", (ub4)4, (ub4) OCI_ATTR_USERNAME, errhp); /* Set the password in the session handle: */ (void) OCIAttrSet((dvoid *) authp, (ub4) OCI_HTYPE_SESSION, (dvoid *) "samp", (ub4) 4, (ub4) OCI_ATTR_PASSWORD, errhp); /* Authenticate and begin the session: */ checkerr(errhp, OCISessionBegin (svchp, errhp, authp, OCI_CRED_RDBMS, (ub4) OCI_DEFAULT)); /* Set the session attribute in the service context: */ (void) OCIAttrSet((dvoid *) svchp, (ub4) OCI_HTYPE_SVCCTX, (dvoid *) authp, (ub4) 0, (ub4) OCI_ATTR_SESSION, errhp); /* ------- At this point a valid session has been created -----------*/ printf ("user session created \n"); /* Allocate a statement handle: */ checkerr(errhp, OCIHandleAlloc( (dvoid *) envhp, (dvoid **) &stmthp, OCI_HTYPE_STMT, (size_t) 0, (dvoid **) 0)); /* ============= Sample procedure call begins here ===================*/ printf ("calling seeIfLOBIsOpen...\n"); seeIfLOBIsOpen(envhp, errhp, svchp, stmthp); return 0; } void checkerr(errhp, status) OCIError *errhp; sword status; { text errbuf[512]; sb4 errcode = 0; switch (status) { case OCI_SUCCESS: break; case OCI_SUCCESS_WITH_INFO: (void) printf("Error - OCI_SUCCESS_WITH_INFO\n"); break; case OCI_NEED_DATA: (void) printf("Error - OCI_NEED_DATA\n"); break; case OCI_NO_DATA: (void) printf("Error - OCI_NODATA\n"); break; case OCI_ERROR: (void) OCIErrorGet((dvoid *)errhp, (ub4) 1, (text *) NULL, &errcode, errbuf, (ub4) sizeof(errbuf), OCI_HTYPE_ERROR); (void) printf("Error - %.*s\n", 512, errbuf); break; case OCI_INVALID_HANDLE: (void) printf("Error - OCI_INVALID_HANDLE\n"); break; case OCI_STILL_EXECUTING: (void) printf("Error - OCI_STILL_EXECUTE\n"); break; case OCI_CONTINUE: (void) printf("Error - OCI_CONTINUE\n"); break; default: break; } } /* Select the locator into a locator variable */ sb4 select_frame_locator(Lob_loc, errhp, svchp, stmthp) OCILobLocator *Lob_loc; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { text *sqlstmt = (text *)"SELECT Frame FROM Multimedia_tab WHERE Clip_ID=1"; OCIDefine *defnp1; checkerr (errhp, OCIStmtPrepare(stmthp, errhp, sqlstmt, (ub4)strlen((char *)sqlstmt), (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT)); checkerr (errhp, OCIDefineByPos(stmthp, &defnp1, errhp, (ub4) 1, (dvoid *)&Lob_loc, (sb4)0, (ub2) SQLT_BLOB,(dvoid *) 0, (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT)); /* execute the select and fetch one row */ checkerr(errhp, OCIStmtExecute(svchp, stmthp, errhp, (ub4) 1, (ub4) 0, (CONST OCISnapshot*) 0, (OCISnapshot*) 0, (ub4) OCI_DEFAULT)); return (0); } void seeIfLOBIsOpen(envhp, errhp, svchp, stmthp) OCIEnv *envhp; OCIError *errhp; OCISvcCtx *svchp; OCIStmt *stmthp; { OCILobLocator *Lob_loc; int isOpen; /* allocate locator resources */ (void) OCIDescriptorAlloc((dvoid *)envhp, (dvoid **)&Lob_loc, (ub4)OCI_DTYPE_LOB, (size_t)0, (dvoid **)0); /* Select the locator */ (void)select_frame_locator(Lob_loc, errhp, svchp, stmthp); /* See if the LOB is Open */ checkerr (errhp, OCILobIsOpen(svchp, errhp, Lob_loc, &isOpen)); if (isOpen) { printf(" Lob is Open\n"); /* ... Processing given that the LOB has already been Opened */ } else { printf(" Lob is not Open\n"); /* ... Processing given that the LOB has not been Opened */ } /* Free resources held by the locators*/ (void) OCIDescriptorFree((dvoid *) Lob_loc, (ub4) OCI_DTYPE_LOB); return; }
You can make changes to an entire internal LOB, or to pieces of the beginning, middle or end of it by using embedded SQL. You can access both internal and external LOBs for read purposes, and you can also write to internal LOBs.
Embedded SQL statements allow you to access data stored in BLOBs, CLOBs, NCLOBs, and BFILEs. These statements are listed in the tables below, and are discussed in greater detail later in the chapter.
|
For more information see:
|
Unlike locators in PL/SQL, locators in Pro*C/C++ are mapped to locator pointers which are then used to refer to the LOB or BFILE value. For the successful completion of an embedded SQL LOB statement you must provide an allocated input locator pointer that represents a LOB that exists in the database tablespaces or external file system before you execute the statement.
Once a locator pointer has been allocated, you may then SELECT a LOB locator into a LOB locator pointer variable and use that variable in an embedded SQL LOB statement to access and manipulate the LOB value. Examples provided with each embedded SQL LOB statement will illustrate this in the following sections.
The statements that can modify BLOB, CLOB, and NCLOB values are:
The statements that read or examine LOB values are:
The statements that deal with temporary LOBs are:
| Statement | Description |
|---|---|
|
|
creates a temporary LOB. |
|
|
sees if a LOB locator refers to a temporary LOB. |
|
|
frees a temporary LOB. |
The statements specific to BFILEs are:
These statements are used for working with LOB locators:
| Statement | Description |
|---|---|
|
|
assigns one |
|
|
sets the directory alias and filename of a |
The following three statements have to do with the LOB Buffering Subsystem:
The following statements have to do with opening and closing LOBs and BFILEs:
Embedded SQL Statements for Opening and CLosing LOBs and BFILEs
| Statement | Description |
|---|---|
|
|
opens a LOB or BFILE. |
|
|
sees if a LOB or BFILE is open. |
|
|
closes a LOB or BFILE. |
You can make changes to an entire internal LOB, or to pieces of the beginning, middle or end of it by using embedded SQL. You can access both internal and external LOBs for read purposes, and you can also write to internal LOBs.
Embedded SQL statements allow you to access data stored in BLOBs, CLOBs, NCLOBs, and BFILEs. These statements are listed in the tables below, and are discussed in greater detail later in the chapter.
Unlike locators in PL/SQL, locators in Pro*COBOL are mapped to locator pointers which are then used to refer to the LOB or BFILE value. For the successful completion of an embedded SQL LOB statement you must provide an allocated input locator pointer that represents a LOB that exists in the database tablespaces or external file system before you execute the statement.
Once a locator pointer has been allocated, you may then SELECT a LOB locator into a LOB locator pointer variable and use that variable in an embedded SQL LOB statement to access and manipulate the LOB value. Examples provided with each embedded SQL LOB statement will illustrate this in the following sections.
In cases in which the Pro*COBOL interface does not supply the required functionality, you can call the OCI via C. We do not provide an example because such programs are operating system dependent.
|
For more information see:
|
The statements that can modify BLOB, CLOB, and NCLOB values are:
The statements that read or examine LOB values are:
The statements that deal with temporary LOBs are:
| Statement | Description |
|---|---|
|
|
creates a temporary LOB. |
|
|
sees if a LOB locator refers to a temporary LOB. |
|
|
frees a temporary LOB. |
The statements specific to BFILEs are:
These statements are used for working with LOB locators:
| Statement | Description |
|---|---|
|
|
assigns one |
|
|
sets the directory alias and filename of a |
The following three statements have to do with the LOB Buffering Subsystem:
The following statements have to do with opening and closing LOBs and BFILEs:
Embedded SQL Statements for Opening and CLosing LOBs and BFILEs
| Statement | Description |
|---|---|
|
|
opens a LOB or BFILE. |
|
|
sees if a LOB or BFILE is open. |
|
|
closes a LOB or BFILE. |
You can make changes to an entire internal LOB, or to pieces of the beginning, middle or end of it via the OO4O API. Specifically, you employ the OraBlob, OraClob and OraBFile objects. You can access both internal and external LOBs for read purposes, and you can also write to internal LOBs.
The OraBlob, OraClob interfaces in OO4O provides methods for performing operations on large objects in the database including BLOB, CLOB and NCLOB data types. The OraBFile interface provides methods for performing operations on BFILE data in the database. These interfaces (OraBlob, OraClob, OraBFile) encapsulate LOB locators, so the user does not deal with locators but instead uses the methods and properties provided to perform operations and get state information.
OraMyBFile refers to the locator obtained from a PL/SQL "OUT" parameter as a result of executing a PL/SQL procedure (either by doing an OraDatabase.ExecuteSQL or by using the OraSqlStmt object). Note that an OraConnect.BeginTrans has been called since the locator became invalid after the COMMIT.
When OraBlob, OraClob objects are retrieved as a part of a dynaset, these objects represent LOB locators of the dynaset current row. If the dynaset current row changes due to move operation, OraBlob, OraClob objects will represent LOB locator for the new current row. In order to retain the LOB locator of the OraBlob, OraClob object independent of the dynaset move operation, use the Clone method. This method returns the OraBlob and OraClob object. One could also use these objects as PL/SQL bind parameters. Here is an example which shows both types of usage. The functions and samples are explained in greater detail as part of the reference documentation.
Dim OraDyn as OraDynaset, OraSound1 as OraBLOB, OraSoundClone as OraBlob, OraMyBfile as OraBFile OraConnection.BeginTrans set OraDyn = OraDb.CreateDynaset("select * from Multimedia_tab order by clip_ id", ORADYN_DEFAULT) set OraSound1 = OraDyn.Fields("Sound").value set OraSoundClone = OraSound1 OraParameters.Add "id", 1,ORAPARAM_INPUT OraParameters.Add "mybfile", Empty,ORAPARAM_OUTPUT OraParameters("mybfile").ServerType = ORATYPE_BFILE OraDatabase.ExecuteSQL ("begin GetBFile(:id, :mybfile ") end") Set OraMyBFile = OraParameters("mybfile").value'Go to Next rowOraDyn.MoveNext OraDyn.Edit'Lets update OraSound1 data with that from the BFILEOraSound1.CopyFromBFile OraMyBFile OraDyn.Update OraDyn.MoveNext'Go to Next rowOraDyn.Edit'Lets update OraSound1 by appending with LOB data from 1st row represenetd by 'OraSoundCloneOraSound1.Append OraSoundClone OraDyn.Update OraConnection.CommitTrans
In the above example OraSound1 represents the locator for the current row in the dynaset where as OraSoundClone represents the locator for the 1st row. A change in the current row (say a OraDyn.MoveNext) will mean that OraSound1 will actually represent locator for the 2nd row whereas OraSoundClone will represent the locator in the 1st row (OraSoundClone only refers the locator for the 1st row irrespective of any OraDyn row navigation).
OraMyBFile refers to the locator got an PL/SQL "OUT" parameter as a result of executing a PL/SQL procedure (either by doing an OraDatabase.ExecuteSQL or by using the OraSqlStmt object). Note that an OraConnect.BeginTrans has been called since with a database "COMMIT" the locator becomes invalid.
OO4O includes methods and properties that you can use to access data stored in BLOBs, CLOBs, NCLOBs, and BFILEs. These methods and properties are listed in the tables below, and are discussed in greater detail later in the chapter.
The routines that can modify BLOB, CLOB, and NCLOB values are:
The routines that read or examine LOB values are:
The following methods have to do with opening and closing LOBs:
| Methods | Description |
|---|---|
|
|
|
|
|
|
The following methods have to do with LOB-buffering:
OO4O LOB-Buffering methods
OO4O LOB- properties
Methods specific to BFILEs are:
OO4O Read-Only methods that are Specific to BFILES
| Methods | Description |
|---|---|
|
|
closes an open |
|
|
closes all open |
|
|
opens a |
|
OraBFile.IsOpen |
determine if a BFILE is open |
You can make changes to an entire internal LOB, or to pieces of the beginning, middle or end of an internal LOB in Java by means of the JDBC API via the Oracle.sql.BLOB and Oracle.sql.CLOB objects. These objects also implement the java.sql.Blob and java.sql.Clob interfaces according to the JDBC 2.0 specification. With this implementation, an Oracle.sql.BLOB can be used wherever a java.sql.Blob is expected and an Oracle.sql.CLOB can be used wherever a java.sql.Clob is expected.
The JDBC interface will let you access both internal and external LOBs for read purposes, and you can also write to internal LOBs.
The BLOB and CLOB classes in JDBC provide methods for performing operations on large objects in the database including BLOB, CLOB and NCLOB data types. The BFILE class provides methods for performing operations on BFILE data in the database. These classes (BLOB, CLOB, BFILE) encapsulate LOB locators, so the user does not deal with locators but instead uses the methods and properties provided to perform operations and get state information. Any of Oracle's LOB functionality not provided by these classes can be accessed by a call to the DBMS_LOB PL/SQL package. This technique is used repeatedly in the examples throughout the book.
You can get a reference to any of the above LOBs either as a column of an OracleResultSet or as an "OUT" type PL/SQL parameter from an OraclePreparedStatement. When BLOB and CLOB objects are retrieved as a part of aN OracleResultSet, these objects represent LOB locators of the currently selected row. If the current row changes due to a move operation (for example, rset.next()), the retrieved locator still refers to the original LOB row. In order to retrieve the locator for the most current row, you must call getXXXX() on the OracleResultSet each time a move operation is made (where XXXX is a BLOB, CLOB or BFILE).
|
For more information see:
|
oracle.sql.BLOB methods for modifying values:
| Function/Procedure | Description |
|---|---|
|
|
inserts the byte array into the |
oracle.sql.BLOB methods for reading or examining values:
oracle.sql.BLOB LOB-buffering methods and properties:
| Function/Procedure | Description |
|---|---|
|
|
streams the |
|
|
writes to |
oracle.sql.CLOB methods for modifying values
oracle.sql.CLOB methods for reading or examining values:
oracle.sql.CLOB LOB-buffering methods and properties:
oracle.sql.BFILE methods for reading or examining values
oracle.sql.BFILE methods for LOB-buffering methods and properties:
| Function/Procedure | Description |
|---|---|
|
|
streams the LOB as a binary stream |
|
|
streams the LOB as a byte array |
Oracle8 supports LOBs, large objects which can hold up to 4 gigabytes of binary or character data. What does this mean for you, the application developer?
Consider the following hypothetical application:
Multimedia data is used in an increasing variety of media channels -- film, television, webpages, and CD-ROM being the most prevalent. The media experiences having to do with these different channels vary in many respects (interactivity, physical environment, the structure of information, to name a few). Yet despite these differences, there is often considerable similarity in the multimedia authoring process, especially with regard to assembling content.
For instance, a television station that creates complex documentaries, an advertising agency that produces advertisements for television, and a software production house that specializes in interactive games for the web could all make good use of a database management system for collecting and organizing the multimedia data. Presumably, they each have sophisticated editing software for composing these elements into their specific products, but the complexity of such projects creates a need for a pre-composition application for organizing the multimedia elements into appropriate groups.
Taking our lead from movie-making, our hypothetical application for collecting content uses the clip as its basic unit of organization. Any clip is able to include one or more of the following media types:
Since this is a pre-editing application, the precise relationship of elements within a clip (such as the synchronization of voice-over audio with a photograph) and between clips (such as the sequence of clips) is not defined.
The application should allow multiple editors working simultaneously to store, retrieve and manipulate the different kinds of multimedia data. We assume that some material is gathered from in-house databases. At the same time, it should also be possible to purchase and download data from professional services.
Our mission in this chapter is not to create this real-life application, but to describe everything you need to know about working with LOBs. Consequently, we only implement the application sufficiently to demonstrate the technology. For example, we deal with only a limited number of multimedia types. We make no attempt to create the client-side applications for manipulating the LOBs. And we do not deal with deployment issues such as, the fact that you should implement disk striping of LOB files, if possible, for best performance.
CLIP_ID: Every row (clip object) must have a number which identifies the clip. This number is generated by the Oracle number SEQUENCER as a matter of convenience, and has nothing to do with the eventual ordering of the clip.
STORY: The application design requires that every clip must also have text, that is a storyboard, that describes the clip. Since we do not wish to limit the length of this text, or restrict its format, we use a CLOB datatype.
FLSUB: Subtitles have many uses -- for closed-captioning, as titles, as overlays that draw attention, and so on. A full-fledged application would have columns for each of these kinds of data but we are considering only the specialized case of a foreign language subtitle, for which we use the NCLOB datatype.
PHOTO: Photographs are clearly a staple of multimedia products. We assume there is a library of photographs stored in the PhotoLib_tab archive. Since a large database of this kind would be stored on tertiary storage that was periodically updated, the column for photographs makes use of the BFILE datatype.
FRAME: It is often necessary to extract elements from dynamic media sources for further processing For instance, VRML game-builders and animation cartoonists are often interested in individual cells. Our application takes up the need to subject film/video to frame-by-frame analysis such as was performed on the film of the Kennedy assassination. While it is assumed that the source is on persistent storage, our application allows for an individual frame to be stored as a BLOB.
SOUND: The table includes a column for sound-effects in the form of a BLOB.
VOICED_REF: This column allows for a reference to a specific row in a table which must be of the type Voiced_typ. In our application, this is a reference to a row in the table VoiceOver_tab whose purpose is to store audio recordings for use as voice-over commentaries. For instance, these might be readings by actors of words spoken or written by people for whom no audio recording can be made, perhaps because they are no longer alive, or because they spoke or wrote in a foreign language.
This structure offers the application builder a number of different strategies from those discussed thus far. Instead of loading material into the row from an archival source, an application can simply reference the data. This means that the same data can be referenced from other tables within the application, or by other applications. The single stipulation is that the reference can only be to tables of the same type. Put another way: the reference, Voiced_ref, can refer to row objects in any table which conforms to the type, Voiced_typ.
Note that Voiced_typ combines the use of two LOB datatypes: a CLOB to store the script which the actor reads, and a BFILE for the audio recordings.
INSEG_NTAB: While it is not possible to store a Varray of LOBs, application builders are able to store a variable number of multimedia elements in a single row by means of nested tables. In the case of our application, a nested table InSeg_ntab of the predefined type InSeg_typ can be used to store zero, one or many interview segments in a given clip. So, for instance, a hypothetical user could use this facility to collect together one or more interview segments having to do with the same theme that occurred at different times.
In this case, the nested table makes use of two LOB datatypes -- a BFILE to store the audio recording of the interview, and a CLOB for transcript. Since such segments might be of great length, it is important to keep in mind that LOBs cannot be more than 4 gigabytes in size.
MUSIC: The ability to handle music must be one of the basic requirements of any multimedia database management system. In this case, the BFILE datatype is used to store the audio as an operating system file.
MAP_OBJ: Multimedia applications must be be able to handle many different kinds of line art -- cartoons, diagrams, and fine art, to name a few. In our application, provision is made for a clip to contain a map as a column object, MAP_OBJ, of the object type MAP_TYP. In this case, the object is contained by value, being embedded in the row. As defined in our application, MAP_TYP has only one LOB in its structure -- a BLOB for the drawing itself. However, as in the case of the types underlying REFs and nested tables, there is no restriction on the number of LOBs that an object type may contain.
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 below ("ENABLE | DISABLE STORAGE IN ROW" on page 1-45), 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.
Before you can start writing data to an 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, and
AUDIO_DIR' (see the CREATE DIRECTORY command in the Oracle8i Reference. Character strings are inserted using the default character set for the instance.
INSERT INTO Multimedia_tab VALUES (101, 'JFK interview', EMPTY_CLOB(), NULL, EMPTY_BLOB(), EMPTY_BLOB(), NULL, NULL, BFILENAME('AUDIO_DIR', 'JFK_interview'), NULL);
Similarly, the LOB attributes for the Map_typ column in Multimedia_tab can be initialized to NULL or set to empty as shown below. Note that you cannot initialize a LOB object attribute with a literal.
INSERT INTO Multimedia_tab VALUES (1, EMPTY_CLOB(), EMPTY_CLOB(), NULL, EMPTY_BLOB(), EMPTY_BLOB(), NULL, NULL, NULL, Map_typ('Moon Mountain', 23, 34, 45, 56, EMPTY_BLOB(), NULL);
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 story 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; ImageNum INTEGER := 101; BEGIN SELECT story INTO Image1 FROM Multimedia_tab WHERE clip_id = ImageNum; DBMS_OUTPUT.PUT_LINE('Size of the Image is: ' || DBMS_LOB.GETLENGTH(Image1)); /* more LOB routines */ END;
In the case of 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 "Support Libraries" on page 1-309, and more extensively in the Oracle Call Interface Programmer's Guide.
If you begin a transaction and then select a locator, the locator contains the transaction ID. Note that you can implicitly be in a transaction without explicitly beginning one. For example, SELECT ... FOR UPDATE implicitly begins a transaction. In such a case, the locator will contain a transaction ID. By contrast, if you select a locator outside of a transaction, the locator does not contain a transaction ID. Note that a transaction ID will not be assigned until the first DML statement executes. Therefore, locators that are selected out prior to such a DML statement will not contain a transaction ID.
You can always read the LOB data using the locator irrespective of whether the locator contains a transaction id. However, if the locator contains a transaction id, you cannot write to the LOB outside of that particular transaction. If the locator does not contain a transaction id, you can write to the LOB after beginning a transaction either explicitly or implicitly. We can show the relationship between transactions and locators by considering a few examples. However, if the locator contains a transaction id and the transaction is serializable, you cannot read or write outside of that particular transaction. If the transaction is non-serializable, you can read, but not write outside of that transaction. The following examples show the relationship between locators and non-serializable transactions
Select the locator with no current transaction.
At this point, the locator does not contain a transaction id.
Begin the transaction.
Use the locator to read data from the LOB.
Commit or rollback the transaction.
Use the locator to read data from the LOB.
Begin a transaction.
The locator does not contain a transaction id.
Use the locator to write data to the LOB.
This operation is valid because the locator did not contain a transaction id prior to the write. After this call, the locator contains a transaction id.
Select the locator with no current transaction.
At this point, the locator does not contain a transaction id.
Begin the transaction.
The locator does not contain a transaction id.
Use the locator to read data from the LOB.
The locator does not contain a transaction id.
Use the locator to write data to the LOB
This operation is valid because the locator did not contain a transaction id prior to the write. After this call, the locator contains a transaction id. You can continue to read from and/or write to the LOB.
Commit or rollback the transaction.
The locator continues to contain the transaction id.
Use the locator to read data from the LOB.
This is a valid operation.
Begin a transaction.
The locator already contains the previous transaction's id.
Use the locator to write data to the LOB.
This write operation will fail because the locator does not contain the transaction id that matches the current transaction.
Select the locator within a transaction.
At this point, the locator contains the transaction id.
Begin the transaction.
The locator contains the previous transaction's id.
Use the locator to read data from the LOB.
This operation is valid even though the transaction id in the locator does not match the current transaction.
Use the locator to write data to the LOB
This operation fails because the transaction id in the locator does not match the current transaction.
Begin a transaction.
Select the locator.
The locator contains the transaction id because it was selected within a transaction.
Use the locator to read from and/or write to the LOB.
These operations are valid.
Commit or rollback the transaction.
The locator continues to contain the transaction id.
Use the locator to read data from the LOB.
This operation is valid even though there's a transaction id in the locator and the transaction was previously committed or rolled back.
Use the locator to write data to the LOB
This operation fails because the transaction id in the locator is for a transaction that was previously committed or rolled back.
These interfaces let you open and close an internal LOB and test whether an internal LOB is already open.
It is not mandatory that you wrap all LOB operations inside the Open/Close APIs. The addition of this feature will not impact already-existing applications that write to LOBs without first opening them, since these calls did not exist in 8.0.
It is important to note that 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.
If you do not wrap LOB operations inside an Open/Close call, each modification to the LOB will implicitly open and close the LOB thereby firing any triggers on an extensible index. Note that in this case, any extensible indexes on the LOB will become updated as soon as LOB modifications are made. Therefore, extensible LOB indexes are always valid and may be used at any time. By contrast, if you wrap your LOB operations inside the Open/Close operations, triggers will not be fired for each LOB modification. Instead, the trigger on extensible indexes will be fired at the Close call. For example, you might design your application so that extensible indexes are not be updated until you call Close. However, this means that any extensible indexes on the LOB will not be valid in-between the Open/Close calls.
Note that the definition of a 'transaction' within which an open LOB value must be closed is one of the following:
SELECT ... FOR UPDATE)' and COMMIT
A LOB opened when there is no transaction must be closed before the end of the session. If there are still open lobs at the end of the session, the openness will be discarded and no triggers on extensible indexes will be fired.
It is an error to commit the transaction before closing all opened LOBs that were opened by the transaction. When the error is returned, the openness of the open LOBs is discarded. At this point, the user must decide whether to close all the LOBs and reissue the call to commit, or rollback the transaction. Note that the changes to the LOB are not discarded if the COMMIT returns an error. At transaction rollback time, the openness of all open LOBs that are still open for that transaction will be discarded. Discarding the openness means that the LOBs won't be closed, thereby firing the triggers on extensible indexes.
It is also an error to open/close the same LOB twice either with different locators or with the same locator.
DECLARE Lob_loc1 CLOB; Lob_loc2 CLOB; Buffer VARCHAR2(32767); Amount BINARY_INTEGER := 32767; Position INTEGER := 1; BEGIN /* Select a LOB: */ SELECT Story INTO Lob_loc1 FROM Multimedia_tab WHERE Clip_ID = 1; /* The following statement opens the LOB outside of a transaction so it must be closed before the session ends: */ DBMS_LOB.OPEN(Lob_loc1, DBMS_LOB.LOB_READONLY); /* The following statement begins a transaction. Note that Lob_loc1 and Lob_loc2 point to the same LOB: */ SELECT Story INTO Lob_loc2 FROM Multimedia_tab WHERE Clip_ID = 1 for update; /* The following LOB open operation is allowed since this lob has not been opened in this transaction: */ DBMS_LOB.OPEN(Lob_loc2, DBMS_LOB.LOB_READWRITE); /* Fill the buffer with data to write to the LOB */ buffer := 'A good story'; Amount := 12; /* Write the buffer to the LOB: */ DBMS_LOB.WRITE(Lob_loc2, Amount, Position, Buffer); /* Closing the LOB is mandatory if you have opened it: */ DBMS_LOB.CLOSE(Lob_loc2); /* The COMMIT ends the transaction. It is allowed because all LOBs opened in the transaction were closed. */ COMMIT; /* The the following statement closes the LOB that was opened before the transaction started: */ DBMS_LOB.CLOSE(Lob_loc1); END;
DECLARE Lob_loc CLOB; BEGIN /* Note that the FOR UPDATE clause starts a transaction: */ SELECT Story INTO Lob_loc FROM Multimedia_tab WHERE Clip_ID = 1 for update; DBMS_LOB.OPEN(Lob_loc, DBMS_LOB.LOB_READONLY); /* COMMIT returns an error because there is still an open LOB associated with this transaction: */ COMMIT; END;
You cannot build B-tree or bitmap indexes on a LOB column. However, depending on your application and its usage of the LOB column, you might be able to improve the performance of queries by building indexes specifically attuned to your domain. Oracle's extensibility interfaces allow for Extensible Indexing, a framework for implementing such domain specific indexes.
|
For more information regarding building domain specific indexes, see: Oracle8i Data Cartridge Developer's Guide. |
Depending on the nature of the contents of the LOB column, one of the Oracle intermedia options could also be used for building indexes. For example, if a text document is stored in a CLOB column, you can build a text index (provided by Oracle) to speed up the performance of text-based queries over the CLOB column.
|
For more information regarding Oracle's intermedia options, see: Oracle8i interMedia Audio, Image, and Video User's Guide and Reference and Oracle8i Context Cartridge Reference. |
