Release 2 (9.2)
Part Number A96591-01
 Home |
 Book List |
 Contents |
 Index |
 Master Index |
 Feedback |
| Oracle9i Application Developer's Guide - Large Objects (LOBs) Release 2 (9.2) Part Number A96591-01 |
|
This chapter discusses the following topics:
Table 3-1 lists the eight programmatic environments (languages) that support LOB functionality. Chapter 10, Chapter 11, and Chapter 12 cover the supported LOB functions in terms of use cases. Examples are provided in each programmatic environment for most LOB use cases.
| Language | Precompiler or Interface Program | Syntax Reference | In This Chapter See... |
|---|---|---|---|
|
PL/SQL |
DBMS_LOB Package |
||
|
C |
Oracle Call Interface for C (OCI) |
||
|
C++ |
Oracle Call Interface for C++ (OCCI) |
"Using C++ (OCCI) To Work With LOBs" |
|
|
C/C++ |
Pro*C/C++ Precompiler |
||
|
COBOL |
Pro*COBOL Precompiler |
||
|
Visual Basic |
Oracle Objects For OLE (OO4O) |
Oracle Objects for OLE (OO4O) is a Windows-based product included with Oracle9i Client for Windows NT. There are no manuals for this product, only online help. Online help is available through the Application Development submenu of the Oracle9i installation. |
"Using Visual Basic (Oracle Objects for OLE (OO4O)) to Work with LOBs"." |
|
Java |
JDBC Application Programmatic Interface (API) |
Oracle9i SQLJ Developer's Guide and Reference and Oracle9i JDBC Developer's Guide and Reference. |
|
|
OLEDB |
OraOLEDB, an OLE DB provider for Oracle. |
Oracle Provider for OLE DB Developer's Guide |
Table 3-2 and Table 3-3compare the eight LOB programmatic interfaces by listing their functions and methods used to operate on LOBs. The tables are split in two simply to accommodate all eight interfaces. The interfaces' functionality, with regards LOBs, is described in the following sections.
The PL/SQL DBMS_LOB package can be used for the following operations:
| See Also:
Oracle9i Supplied PL/SQL Packages and Types Reference for detailed documentation, including parameters, parameter types, return values, and example code. |
As described in more detail in the following, DBMS_LOB routines work based on LOB locators. For the successful completion of DBMS_LOB routines, you must provide an input locator representing a LOB that exists in the database tablespaces or external file system, before you invoke the routine.
LOB columns, and subsequently you can use SQL to initialize or populate the locators in these LOB columns.DIRECTORY object that maps to a valid physical directory containing the external LOBs that you intend to access. These files must exist, and have READ permission for Oracle Server to process. If your operating system uses case-sensitive path names, specify the directory in the correct case. See Chapter 12, "External LOBs (BFILEs)", "Directory Object" for more information.
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.
Client-side PL/SQL procedures cannot call DBMS_LOB package routines.
However, you can use server-side PL/SQL procedures or anonymous blocks in Pro*C/C++ to call DBMS_LOB package routines.
For DBMS_LOB package, for example, for both fixed and varying-width character sets, the following rules apply:
When using DBMS_LOB.LOADFROMFILE, you cannot specify an amount parameter value larger than the size of the BFILE. (Although you can specify the LOBMAXSIZE constant for the amount parameter value to load the entire BFILE.)
When using DBMS_LOB.READ, the amount parameter can be larger than the size of the data. In PL/SQL, the amount should be less than or equal to the size of the buffer, and the buffer size is limited to 32K.
PL/SQL functions and procedures that operate on BLOBs, CLOBs, NCLOBs, and BFILEs are summarized in the following:
| Function/Procedure | Description |
|---|---|
|
CREATETEMPORARY() |
Creates a temporary LOB |
|
ISTEMPORARY() |
Checks if a LOB locator refers to a temporary LOB |
|
FREETEMPORARY() |
Frees a temporary LOB |
| Function/Procedure | Description |
|---|---|
|
OPEN() |
Opens a LOB |
|
ISOPEN() |
Sees if a LOB is open |
|
CLOSE() |
Closes a LOB |
These procedures are described in detail for specific LOB operations, such as, INSERT a row containing a LOB, in these chapters:
You can access many of the PL/SQL LOB example scripts from these chapters, at $ORACLE_HOME/rdbms/demo/lobs/plsql.
Oracle Call Interface (OCI) can be used to make changes to an entire internal LOB, or to pieces of the beginning, middle or end of it through OCI, as follows:
OCI also includes functions that you can use to:
BLOBs, CLOBs, NCLOBs) and external LOBs (BFILEs)These functions are listed in the following tables, and are discussed in greater detail later in this section.
If you want to read or write data in 2 byte unicode (UCS2) format, set the csid (character set ID) parameter in OCILobRead and OCILobWrite to OCI_UCS2ID. The csid parameter indicates the character set id 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.
See Also:
|
In OCI, for fixed-width client-side character sets, the following rules apply:
The following rules apply only to varying-width client-side character sets:
CLOBs and NCLOBs:
CLOBs and NCLOBs:
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.
NCLOBs parameters are allowed in methods.
When using OCILobLoadFromFile you cannot specify amount larger than the length of the BFILE.
In OCILobRead, you can specify amount = 4 gigabytes-1, and it will read to the end of the LOB.
Further OCI examples are provided in:
You can access most of the OCI LOB example scripts from your Oracle9i distribution software at $ORACLE_HOME/rdbms/demo/lobs/oci.
There are further example OCI scripts:
See also Appendix B, "OCI Demonstration Programs" in Oracle Call Interface Programmer's Guide, for further OCI demonstration script listings.
For further information and features of OCI, refer to the OTN Web site, http://otn.oracle.com/ for OCI features and FAQs.
OCI functions that operate on BLOBs, CLOBs, NCLOBs, and BFILEs are as follows:
| Function/Procedure | Description |
|---|---|
|
|
Creates a temporary LOB |
|
|
Sees if a temporary LOB exists |
|
|
Frees a temporary LOB |
| Function/Procedure | Description |
|---|---|
|
OCILobOpen() |
Opens a LOB |
|
OCILobIsOpen() |
Sees if a LOB is open |
|
OCILobClose() |
Closes a LOB |
To work with the OCI examples in the remainder of the book, you can use a main() like the following. Here, it is used with seeIfLOBIsOpen 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; }
Oracle C++ Call Interface (OCCI) is a C++ API for manipulating data in an Oracle database. OCCI is organized as an easy-to-use collection of C++ classes which enable a C++ program to connect to a database, execute SQL statements, insert/update values in database tables, retrieve results of a query, execute stored procedures in the database, and access metadata of database schema objects. OCCI also provides a seamless interface to manipulate objects of user-defined types as C++ class instances.
Oracle C++ Call Interface (OCCI) is designed so that you can use OCI and OCCI together to build applications.
The OCCI API provides the following advantages over JDBC and ODBC:
You can use Oracle C++ Call Interface (OCCI) to make changes to an entire internal LOB, or to pieces of the beginning, middle, or end of it, as follows:
Unlike OCI which uses a common API for operations on BLOBs, CLOBs, and BFILEs, OCCI has distinct classes for each LOB types, as follows:
OCCIClob Class to access and modify data stored in internal CLOBs and NCLOBsThe OCCIClob driver implements a CLOB object using an SQL locator(CLOB). This means that a CLOB object contains a logical pointer to the SQL CLOB data rather than the data itself.
The CLOB interface provides methods for getting the length of an SQL CLOB (Character Large Object) value, for materializing a CLOB value on the client, and getting a substring. Methods in the interfaces ResultSet statement such as getClob() and setClob() allow you to access SQL CLOB values.
Methods in the OCCIResultSet and OCCIStatement interfaces, such as getBlob() and setBlob(), allow you to access SQL BLOB values. The OCCIBlob interface provides methods for getting the length of a SQL BLOB value, for materializing a BLOB value on the client, and for extracting a part of the BLOB.
These methods are listed in the following tables.
See Also:
|
In OCCI, for fixed-width client-side character sets, the following rules apply:
The following rules apply only to varying-width client-side character sets:
CLOBs and NCLOBs:
CLOBs and NCLOBs:
For all other OCCI LOB operations, irrespective of the client-side character set, the amount parameter is in characters for CLOBs and NCLOBs. These include the following:
OCCIClob.copy()OCCIClob.erase()OCCIClob.trim()OCCICLob.copy()All these operations refer to the amount of LOB data on the server.
NCLOBs parameters are allowed in methodsNCLOBs parameters are not allowed as attributes in object typesThe LoadFromFile functionality in OCCI is provided though the OCCIClob.copy() and OCCIBlob.copy() methods. These take an OCCIBfile argument.
You cannot specify amounts larger than the length of the BFILE. You must specify an amount parameter less than length of the BFILE.
When reading in OCCIClobs, OCCIBlobs, and OCCIBfiles, specify the amount = 4 gigabytes-1, and it will read to the end of the LOB.
See Also:
|
OCCI methods that operate on BLOBs, CLOBs, NCLOBs, and BFILEs are as follows:
| Function/Procedure | Description |
|---|---|
|
OCCIClob/Blob/Bfile.Open() |
Opens a LOB |
|
OCCIClob/Blob/Bfile.IsOpen() |
Sees if a LOB is open |
|
OCCIClob/Blob/Bfile.Close() |
Closes a LOB |
You can make changes to an entire internal LOB, or to pieces of the beginning, middle or end of a LOB by using embedded SQL. You can access both internal and external LOBs for read purposes, and you can 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 following tables, and are discussed in greater detail later in the chapter.
| See Also:
Pro*C/C++ Precompiler Programmer's Guide for detailed documentation, including syntax, host variables, host variable types and example code. |
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.
To successfully complete an embedded SQL LOB statement you must do the following:
Examples provided with each embedded SQL LOB statement are illustrated in:
You can access these Pro*C/C++ LOB example scripts from $ORACLE_HOME/rdbms/demo/lobs/.
Pro*C statements that operate on BLOBs, CLOBs, and NCLOBs are listed in the following tables:
| Statement | Description |
|---|---|
|
CREATE TEMPORARY |
Creates a temporary LOB. |
|
DESCRIBE [ISTEMPORARY] |
Sees if a LOB locator refers to a temporary LOB. |
|
FREE TEMPORARY |
Frees a temporary LOB. |
| Statement | Description |
|---|---|
|
FILE CLOSE ALL |
Closes all open |
|
DESCRIBE [FILEEXISTS] |
Checks whether a |
|
DESCRIBE [DIRECTORY,FILENAME] |
Returns the directory alias and/or filename of a |
| Statement | Description |
|---|---|
|
ASSIGN |
Assigns one |
|
FILE SET |
Sets the directory alias and filename of a |
| Statement | Description |
|---|---|
|
OPEN |
Opens a LOB or BFILE. |
|
DESCRIBE [ISOPEN] |
Sees if a LOB or BFILE is open. |
|
CLOSE |
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 following tables, and are discussed in greater detail later in the manual.
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 perform the following:
Examples provided with each embedded SQL LOB statement are illustrated in:
You can access these Pro*COBOL LOB example scripts from $ORACLE_HOME/rdbms/demo/lobs/.
Where the Pro*COBOL interface does not supply the required functionality, you can call OCI using C. Such an example is not provided here because such programs are operating system dependent.
| See Also:
Pro*COBOL Precompiler Programmer's Guide for detailed documentation, including syntax, host variables, host variable types, and example code. |
The following Pro*COBOL statements operate on BLOBs, CLOBs, NCLOBs, and BFILEs:
| Statement | Description |
|---|---|
|
CREATE TEMPORARY |
Creates a temporary LOB. |
|
DESCRIBE [ISTEMPORARY] |
Sees if a LOB locator refers to a temporary LOB. |
|
FREE TEMPORARY |
Frees a temporary LOB. |
| Statement | Description |
|---|---|
|
ASSIGN |
Assigns one |
|
FILE SET |
Sets the directory alias and filename of a |
| Statement | Description |
|---|---|
|
OPEN |
Opens a LOB or BFILE. |
|
DESCRIBE [ISOPEN] |
Sees if a LOB or BFILE is open. |
|
CLOSE |
Closes a LOB or BFILE. |
Oracle Objects for OLE (OO4O) is a collection of programmable COM objects that simplifies the development of applications designed to communicate with an Oracle database. OO4O offers high performance database access. It also provides easy access to features unique to Oracle, yet otherwise cumbersome or inefficient to use from other ODBC or OLE DB-based components, such as ADO.
You can make changes to an entire internal LOB, or to pieces of the beginning, middle or end of it, with the Oracle Objects for OLE (OO4O) API, by using one of the following objects interfaces:
: To provide methods for performing operations on BLOB datatypes in the database: To provide methods for performing operations on CLOB datatypes in the database: To provide methods for performing operations on BFILE data stored in operating system files.
The OO4O syntax reference and further information is viewed from the OO4O online help. Oracle Objects for OLE (OO4O), a Windows-based product included with Oracle9i Client for Windows NT, has no manuals, only online help.
Its online help is available through the Application Development submenu of the Oracle9i installation. To view specific methods and properties from the Help Topics menu, select the Contents tab > OO4O Automation Server > Methods or Properties.
For further information about OO4O, refer to the following Web site:
These interfaces encapsulate LOB locators, so you do not deal directly with locators, but instead, can use methods and properties provided to perform operations and get state information.
When OraBlob and 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 a move operation, the OraBlob and OraClob objects represent the LOB locator for the new current row.
To retain the LOB locator of the OraBlob and OraClob object independent of the dynaset move operation, use the Clone method. This method returns the OraBlob and OraClob object. You can also use these objects as PL/SQL bind parameters.
The following example shows usage of OraBlob and OraBfile. Functions and examples are explained in greater detail in Chapters 10 and 12.
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 preceding example:
OraSound1 -- represents the locator for the current row in the dynaset OraSoundClone -- represents the locator for the 1st row.
A change in the current row (say a OraDyn.MoveNext) means the following:
OraSound1 -- will represent the locator for the 2nd row
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 obtained from an PL/SQL "OUT" parameter as a result of executing a PL/SQL procedure, either by doing an OraDatabase.ExecuteSQL.
|
Note: A LOB obtained by executing SQL is only valid for the duration of the transaction. For this reason, "BEGINTRANS" and "COMMITTRANS" are used to specify the duration of the transaction. |
Oracle Objects for OLE (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 following tables, and are discussed in greater detail in the following chapters:
You can also access OO4O LOB example scripts at $ORACLE_HOME/rdbms/demo/lobs.
The following OO4O methods and properties operate on BLOBs, CLOBs, NCLOBs, and BFILEs:
| Method | Description |
|---|---|
|
OraBFile.Open |
|
|
OraBFile.Close |
|
| Methods | Description |
|---|---|
|
OraBFile.Close |
Closes an open |
|
OraBFile.CloseAll |
Closes all open |
|
OraBFile.Open |
Opens a |
|
OraBFile.IsOpen |
Determines if a BFILE is open |
You can perform the following tasks on LOBs with Java (JDBC):
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 using the objects:
These objects also implement 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.
With JDBC you can use Java to read both internal persistent LOBs and external LOBs (BFILEs).
BLOB and CLOB data types.The BLOB, CLOB, and BFILE classes encapsulate LOB locators, so you do not deal with locators but instead use methods and properties provided to perform operations and get state information.
Any LOB functionality not provided by these classes can be accessed by a call to the PL/SQL DBMS_LOB package. This technique is used repeatedly in the examples throughout this manual.
You can get a reference to any of the preceding LOBs in the following two ways:
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.
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 further JDBC syntax and information about using JDBC with LOBs:
See:
|
The following JDBC methods operate on BLOBs, CLOBs, and BFILEs:
| Method | Description |
|---|---|
|
int putBytes(long, by |
Inserts the byte array into the |
| Method | Description |
|---|---|
|
public java.io.InputStream getBinaryStream()) |
Streams the |
|
public java.io.OutputStream getBinaryOutputStream() |
Writes to |
| Method | Description |
|---|---|
|
public java.io.InputStream getBinaryStream() |
Reads the BFILE as a binary stream |
OracleBlob and OracleClob were Oracle specific functions used in JDBC 8.0.x drivers to access LOB data. In Oracle8i Releases 8.1.x and higher, OracleBlob and OracleClob are deprecated.
If you use OracleBlob or OracleClob to access LOB data, you will receive the following typical error message, for example, when attempting to manipulate LOBs with Oracle8i Release 8.1.5 JDBC Thin Driver:
"Dumping lobs java.sql.SQLException: ORA-03115: unsupported network datatype or representation etc."
See Oracle9i JDBC Developer's Guide and Reference for a description of these non-supported functions and alternative and improved JDBC methods.
For further ideas on working with LOBs with Java, refer to the LOB examples shipped with Oracle9i or see http://www.oracle.com/.
Oracle9i JDBC drivers contain APIs to create and close temporary LOBs. These APIs can replace prior releases' workaround of using the following procedures from the DBMS_LOB PL/SQL package:
oracle.sql.CLOB class is the Oracle JDBC driver's implementation of standard JDBC java.sql.Clob interface. Table 3-51 lists the new Oracle extension APIs in oracle.sql.CLOB for accessing temporary CLOBs.
Oracle9i JDBC drivers contain APIs to explicitly open and close LOBs. These APIs replace previous techniques that use DBMS_LOB.open() and DBMS_LOB.close().
oracle.sql.BLOB class is the Oracle JDBC driver's implementation of standard JDBC java.sql.Blob interface. Table 3-52 lists the Oracle extension APIs in oracle.sql.BLOB that open and close BLOBs. These are new for this release.
To open a BLOB, your JDBC application can use the open method as defined in oracle.sql.BLOB class as follows:
/** * Open a BLOB in the indicated mode. Valid modes include MODE_READONLY, * and MODE_READWRITE. It is an error to open the same LOB twice. */ public void open (int mode) throws SQLException
Possible values of the mode parameter are:
public static final int MODE_READONLY public static final int MODE_READWRITE
Each call to open opens the BLOB. For example:
BLOB blob = ... blob.open (BLOB.MODE_READWRITE);
To see if a BLOB is opened, your JDBC application can use the isOpen method defined in oracle.sql.BLOB. The return boolean value indicates whether the BLOB has been previously opened or not. The isOpen method is defined as follows:
/** * Check whether the BLOB is opened. * @return true if the LOB is opened. */ public boolean isOpen () throws SQLException
The usage example is:
BLOB blob = ... // See if the BLOB is opened boolean isOpen = blob.isOpen ();
To close a BLOB, your JDBC application can use the close method defined in oracle.sql.BLOB. The close API is defined as follows:
/** * Close a previously opened BLOB. */ public void close () throws SQLException
The usage example is:
BLOB blob = ... // close the BLOB blob.close ();
Class, oracle.sql.CLOB, is the Oracle JDBC driver's implementation of the standard JDBC java.sql.Clob interface. Table 3-53 lists the new Oracle extension APIs in oracle.sql.CLOB to open and close CLOBs.
To open a CLOB, your JDBC application can use the open method defined in oracle.sql.CLOB class as follows:
/** * Open a CLOB in the indicated mode. Valid modes include MODE_READONLY, * and MODE_READWRITE. It is an error to open the same LOB twice. */ public void open (int mode) throws SQLException
The possible values of the mode parameter are:
public static final int MODE_READONLY public static final int MODE_READWRITE
Each call to open opens the CLOB. For example,
CLOB clob = ... clob.open (CLOB.MODE_READWRITE);
To see if a CLOB is opened, your JDBC application can use the isOpen method defined in oracle.sql.CLOB. The return boolean value indicates whether the CLOB has been previously opened or not. The isOpen method is defined as follows:
/** * Check whether the CLOB is opened. * @return true if the LOB is opened. */ public boolean isOpen () throws SQLException
The usage example is:
CLOB clob = ... // See if the CLOB is opened boolean isOpen = clob.isOpen ();
To close a CLOB, the JDBC application can use the close method defined in oracle.sql.CLOB. The close API is defined as follows:
/** * Close a previously opened CLOB. */ public void close () throws SQLException
The usage example is:
CLOB clob = ... // close the CLOB clob.close ();
oracle.sql.BFILE class wraps the database BFILE object. Table 3-54 lists the new Oracle extension APIs in oracle.sql.BFILE for opening and closing BFILEs.
To open a BFILE, your JDBC application can use the OPEN method defined in oracle.sql.BFILE class as follows:
/** * Open a external LOB in the readonly mode. It is an error * to open the same LOB twice. */ public void open () throws SQLException /** * Open a external LOB in the indicated mode. Valid modes include * MODE_READONLY only. It is an error to open the same * LOB twice. */ public void open (int mode) throws SQLException
The only possible value of the mode parameter is:
public static final int MODE_READONLY
Each call to open opens the BFILE. For example,
BFILE bfile = ... bfile.open ();
To see if a BFILE is opened, your JDBC application can use the ISOPEN method defined in oracle.sql.BFILE. The return boolean value indicates whether the BFILE has been previously opened or not. The ISOPEN method is defined as follows:
/** * Check whether the BFILE is opened. * @return true if the LOB is opened. */ public boolean isOpen () throws SQLException
The usage example is:
BFILE bfile = ... // See if the BFILE is opened boolean isOpen = bfile.isOpen ();
To close a BFILE, your JDBC application can use the CLOSE method defined in oracle.sql.BFILE. The CLOSE API is defined as follows:
/** * Close a previously opened BFILE. */ public void close () throws SQLException
The usage example is --
BFILE bfile = ... // close the BFILE bfile.close ();
/* * This sample shows how to open/close BLOB and CLOB. */ // You need to import the java.sql package to use JDBC import java.sql.*; // You need to import the oracle.sql package to use oracle.sql.BLOB import oracle.sql.*; class OpenCloseLob { public static void main (String args []) throws SQLException { // Load the Oracle JDBC driver DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); String url = "jdbc:oracle:oci8:@"; try { String url1 = System.getProperty("JDBC_URL"); if (url1 != null) url = url1; } catch (Exception e) { // If there is any security exception, ignore it // and use the default } // Connect to the database Connection conn = DriverManager.getConnection (url, "scott", "tiger"); // It's faster when auto commit is off conn.setAutoCommit (false); // Create a Statement Statement stmt = conn.createStatement (); try { stmt.execute ("drop table basic_lob_table"); } catch (SQLException e) { // An exception could be raised here if the table did not exist already. } // Create a table containing a BLOB and a CLOB stmt.execute ("create table basic_lob_table (x varchar2 (30), b blob, c clob)"); // Populate the table stmt.execute ( "insert into basic_lob_table values" + " ('one', '010101010101010101010101010101', 'onetwothreefour')"); // Select the lobs ResultSet rset = stmt.executeQuery ("select * from basic_lob_table"); while (rset.next ()) { // Get the lobs BLOB blob = (BLOB) rset.getObject (2); CLOB clob = (CLOB) rset.getObject (3); // Open the lobs System.out.println ("Open the lobs"); blob.open (BLOB.MODE_READWRITE); clob.open (CLOB.MODE_READWRITE); // Check if the lobs are opened System.out.println ("blob.isOpen()="+blob.isOpen()); System.out.println ("clob.isOpen()="+clob.isOpen()); // Close the lobs System.out.println ("Close the lobs"); blob.close (); clob.close (); // Check if the lobs are opened System.out.println ("blob.isOpen()="+blob.isOpen()); System.out.println ("clob.isOpen()="+clob.isOpen()); } // Close the ResultSet rset.close (); // Close the Statement stmt.close (); // Close the connection conn.close (); } }
Oracle9i JDBC drivers contain APIs to trim internal LOBs. These APIs replace previous techniques that used DBMS_LOB.trim().
oracle.sql.BLOB class is Oracle JDBC driver's implementation of the standard JDBC java.sql.Blob interface. Table 3-55 lists the new Oracle extension API in oracle.sql.BLOB that trims BLOBs.
| Methods | Description |
|---|---|
|
public void trim(long newlen) throws SQLException |
Trims the BLOB |
The trim API is defined as follows:
/** * Trim the value of the BLOB to the length you specify in the newlen parameter. * @param newlen the new length of the BLOB. */ public void trim (long newlen) throws SQLException
The newlen parameter specifies the new length of the BLOB.
oracle.sql.CLOB class is the Oracle JDBC driver's implementation of standard JDBC java.sql.Clob interface. Table 3-56 lists the new Oracle extension API in oracle.sql.CLOB that trims CLOBs.
| Methods | Description |
|---|---|
|
public void trim(long newlen) throws SQLException |
Trims the CLOB |
The trim API is defined as follows:
/** * Trim the value of the CLOB to the length you specify in the newlen parameter. * @param newlen the new length of the CLOB. */ public void trim (long newlen) throws SQLException
The newlen parameter specifies the new length of the CLOB.
| See:
Chapter 10, "Internal Persistent LOBs" "Java (JDBC): Trimming LOB Data" , for an example. |
Oracle9i JDBC drivers contains the new LOB streaming APIs to read from/write to a LOB at the requested position from a Java stream. In prior releases, LOB streaming APIs did not specify the offset.
oracle.sql.BLOB class is the Oracle JDBC driver's implementation of standard JDBC java.sql.Blob interface. Table 3-57 lists the new Oracle extension APIs in oracle.sql.BLOB that manipulate the BLOB content from the requested position.
These APIs are defined as follows:
/** * Write to the BLOB from a stream at the requested position. * * @param pos is the position data to be put. * @return a output stream to write data to the BLOB */ public java.io.OutputStream getBinaryOutputStream(long pos) throws SQLException /** * Read from the BLOB as a stream at the requested position. * * @param pos is the position data to be read. * @return a output stream to write data to the BLOB */ public java.io.InputStream getBinaryStream(long pos) throws SQLException
oracle.sql.CLOB class is the Oracle JDBC driver's implementation of standard JDBC java.sql.Clob interface. Table 3-58 lists the new Oracle extension APIs in oracle.sql.CLOB that manipulate the CLOB content from the requested position.
These APIs are defined as follows:
/** * Write to the CLOB from a stream at the requested position. * @param pos is the position data to be put. * @return a output stream to write data to the CLOB */ public java.io.OutputStream getAsciiOutputStream(long pos) throws SQLException /**
* Write to the CLOB from a stream at the requested position. * @param pos is the position data to be put. * @return a output stream to write data to the CLOB */ public java.io.Writer getCharacterOutputStream(long pos) throws SQLException /** * Read from the CLOB as a stream at the requested position. * @param pos is the position data to be put. * @return a output stream to write data to the CLOB */ public java.io.InputStream getAsciiStream(long pos) throws SQLException /** * Read from the CLOB as a stream at the requested position. * @param pos is the position data to be put. * @return a output stream to write data to the CLOB */ public java.io.Reader getCharacterStream(long pos) throws SQLException
oracle.sql.BFILE class wraps the database BFILEs. Table 3-59 lists the new Oracle extension APIs in oracle.sql.BFILE that reads BFILE content from the requested position.
| Methods | Description |
|---|---|
|
public java.io.InputStream getBinaryStream(long pos) throws SQLException |
Reads from the BFILE as a stream |
These APIs are defined as follows:
/** * Read from the BLOB as a stream at the requested position. * * @param pos is the position data to be read. * @return a output stream to write data to the BLOB */ public java.io.InputStream getBinaryStream(long pos) throws SQLException
/* * This sample shows how to read/write BLOB and CLOB as streams. */ import java.io.*; // You need to import the java.sql package to use JDBC import java.sql.*; // You need to import the oracle.sql package to use oracle.sql.BLOB import oracle.sql.*; class NewStreamLob { public static void main (String args []) throws Exception { // Load the Oracle JDBC driver DriverManager.registerDriver(new oracle.jdbc.driver.OracleDriver()); String url = "jdbc:oracle:oci8:@"; try { String url1 = System.getProperty("JDBC_URL"); if (url1 != null) url = url1; } catch (Exception e) { // If there is any security exception, ignore it // and use the default } // Connect to the database Connection conn = DriverManager.getConnection (url, "scott", "tiger"); // It's faster when auto commit is off conn.setAutoCommit (false); // Create a Statement Statement stmt = conn.createStatement (); try { stmt.execute ("drop table basic_lob_table"); } catch (SQLException e) { // An exception could be raised here if the table did not exist already. } // Create a table containing a BLOB and a CLOB stmt.execute ( "create table basic_lob_table" + "(x varchar2 (30), b blob, c clob)"); // Populate the table stmt.execute ( "insert into basic_lob_table values" + "('one', '010101010101010101010101010101', 'onetwothreefour')"); System.out.println ("Dumping lobs"); // Select the lobs ResultSet rset = stmt.executeQuery ("select * from basic_lob_table"); while (rset.next ()) { // Get the lobs BLOB blob = (BLOB) rset.getObject (2); CLOB clob = (CLOB) rset.getObject (3); // Print the lob contents dumpBlob (conn, blob, 1); dumpClob (conn, clob, 1); // Change the lob contents fillClob (conn, clob, 11, 50); fillBlob (conn, blob, 11, 50); } rset.close (); System.out.println ("Dumping lobs again"); rset = stmt.executeQuery ("select * from basic_lob_table"); while (rset.next ()) { // Get the lobs BLOB blob = (BLOB) rset.getObject (2); CLOB clob = (CLOB) rset.getObject (3); // Print the lobs contents dumpBlob (conn, blob, 11); dumpClob (conn, clob, 11); } // Close all resources rset.close(); stmt.close(); conn.close(); } // Utility function to dump Clob contents static void dumpClob (Connection conn, CLOB clob, long offset) throws Exception { // get character stream to retrieve clob data Reader instream = clob.getCharacterStream(offset); // create temporary buffer for read char[] buffer = new char[10]; // length of characters read int length = 0; // fetch data while ((length = instream.read(buffer)) != -1) { System.out.print("Read " + length + " chars: "); for (int i=0; i<length; i++) System.out.print(buffer[i]); System.out.println(); } // Close input stream instream.close(); } // Utility function to dump Blob contents static void dumpBlob (Connection conn, BLOB blob, long offset) throws Exception { // Get binary output stream to retrieve blob data InputStream instream = blob.getBinaryStream(offset); // Create temporary buffer for read byte[] buffer = new byte[10]; // length of bytes read int length = 0; // Fetch data while ((length = instream.read(buffer)) != -1) { System.out.print("Read " + length + " bytes: "); for (int i=0; i<length; i++) System.out.print(buffer[i]+" "); System.out.println(); } // Close input stream instream.close(); } // Utility function to put data in a Clob static void fillClob (Connection conn, CLOB clob, long offset, long length) throws Exception { Writer outstream = clob.getCharacterOutputStream(offset); int i = 0; int chunk = 10; while (i < length) { outstream.write("aaaaaaaaaa", 0, chunk); i += chunk; if (length - i < chunk) chunk = (int) length - i; } outstream.close(); } // Utility function to put data in a Blob static void fillBlob (Connection conn, BLOB blob, long offset, long length) throws Exception { OutputStream outstream = blob.getBinaryOutputStream(offset); int i = 0; int chunk = 10; byte [] data = { 1, 1, 1, 1, 1, 1, 1, 1, 1, 1 }; while (i < length) { outstream.write(data, 0, chunk); i += chunk; if (length - i < chunk) chunk = (int) length - i; } outstream.close(); } }
An empty BLOB can be created from the following API from oracle.sql.BLOB:
public static BLOB empty_lob () throws SQLException
Similarly, the following API from oracle.sql.CLOB creates a empty CLOB:
public static CLOB empty_lob () throws SQLException
Empty LOB instances are created by JDBC drivers without making database round trips. Empty LOBs can be used in the following cases:
JDBC applications cannot read or write to empty LOBs created from the preceding APIs. An ORA-17098 "Invalid empty lob operation" results if your application attempts to read/write to an empty LOB.
Oracle Provider for OLE DB (OraOLEDB) offers high performance and efficient access to Oracle data for OLE DB and ADO developers. Developers programming with Visual Basic, C++, or any COM client can use OraOLEDB to access Oracle databases.
OraOLEDB is an OLE DB provider for Oracle. It offers high performance and efficient access to Oracle data including LOBs, and also allows updates to certain LOB types.
The following LOB types are supported by OraOLEDB:
See Also:
|
|
 Copyright © 1996, 2002 Oracle Corporation. All Rights Reserved. |
|