| Oracle® Database Application Developer's Guide - Large Objects 10g Release 1 (10.1) Part Number B10796-01 |
|
|
View PDF |
| Oracle® Database Application Developer's Guide - Large Objects 10g Release 1 (10.1) Part Number B10796-01 |
|
|
View PDF |
This chapter discusses the following topics:
Table 6-1 lists the programmatic environments that support LOB functionality.
| 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 the database. There are no manuals for this product, only online help. Online help is available through the Application Development submenu of the database installation. |
"Using Visual Basic (Oracle Objects for OLE (OO4O)) to Work with LOBs"." |
|
Java |
JDBC Application Programmatic Interface (API) |
||
|
OLEDB |
OraOLEDB, an OLE DB provider for Oracle. |
Oracle Provider for OLE DB Developer's Guide |
Table 6-2 and Table 6-3 compare 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 functionality of the interfaces, with regards to LOBs, is described in the following sections.
The PL/SQL DBMS_LOB package can be used for the following operations:
| See Also:
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 call the routine.
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, then specify the directory in the correct case. See "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.
The following guidelines apply to offset and amount parameters used in procedures in the DBMS_LOB PL/SQL package:
amount and offset parameters are in characters. This applies to operations on CLOB and NCLOB datatypes.offset and amount parameters are in bytes. This applies to operations on BLOB datatypes.you cannot specify an amount parameter with a value larger than the size of the BFILE you are loading from. To load the entire BFILE with these procedures, you must specify either the exact size of the BFILE, or the maximum allowable storage limit.
amount parameter can be larger than the size of the data. The amount should be less than or equal to the size of the buffer. 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:
PL/SQL: DBMS_LOB Procedures to Modify LOB Values| 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 "Opening Persistent LOBs with the OPEN and CLOSE Interfaces".
Oracle Call Interface (OCI) LOB APIs enable you to access and make changes to LOBs and read data from BFILEs in C. OCI functions for LOBs are discussed in greater detail later in this section.
If you want to read or write data in 2 byte unicode (UCS2) format, then set the csid (character set ID) parameter in OCILobRead2 and OCILobWrite2 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, then 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:
For all other LOB operations, irrespective of the client-side character set, the amount parameter is in characters for CLOBs and NCLOBs. These include OCILobCopy2(), OCILobErase2(), OCILobLoadFromFile2(), and OCILobTrim2(). All these operations refer to the amount of LOB data on the server.
NCLOB are allowed as parameters in methods.
When using OCILobLoadFromFile2() you cannot specify amount larger than the length of the BFILE. To load the entire BFILE, you can pass the value of the system defined constant OCI_LOBMAXSIZE.
To read to the end of a LOB using OCILobRead2(), you specify an amount equal to the system defined constant OCI_LOBMAXSIZE. See "Streaming Read in OCI" for more information.
Special care must be taken when assigning OCILobLocator pointers in an OCI program--using the "=" assignment operator. Pointer assignments create a shallow copy of the LOB. After the pointer assignment, the source and target LOBs point to the same copy of data.
These semantics are different from using LOB APIs, such as OCILobAssign() or OCILobLocatorAssign() to perform assignments. When the these APIs are used, the locators logically point to independent copies of data after assignment.
For temporary LOBs, before performing pointer assignments, you must ensure that any temporary LOB in the target LOB locator is freed by calling OCIFreeTemporary(). In contrast, when OCILobLocatorAssign() is used, the original temporary LOB in the target LOB locator variable, if any, is freed automatically before the assignment happens.
Before you reuse a LOB locator in a define or an out-bind variable in a SQL statement, you must free any temporary LOB in the existing LOB locator buffer using OCIFreeTemporary().
Further OCI examples are provided in:
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 |
|---|---|
|
|
Opens a LOB |
|
|
Sees if a LOB is open |
|
|
Closes a LOB |
Oracle C++ Call Interface (OCCI) is a C++ API for manipulating data in an Oracle database. OCCI is organized as an easy-to-use set of C++ classes that enable a C++ program to connect to a database, run SQL statements, insert/update values in database tables, retrieve results of a query, run 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 persistent LOB, or to pieces of the beginning, middle, or end of it, as follows:
OCCI provides the following classes that allow you to use different types of LOB instances as objects in your C++ application:
Clob class to access and modify data stored in internal CLOBs and NCLOBsBlob class to access and modify data stored in internal BLOBsBfile class to access and read data stored in external LOBs (BFILEs)
| See Also:
Syntax information on these classes and details on OCCI in general is available in the Oracle C++ Call Interface Programmer's Guide. |
The Clob driver implements a CLOB object using an SQL LOB locator. 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 value, for materializing a CLOB value on the client, and getting a substring. Methods in the ResultSet and Statement interfaces such as getClob() and setClob() allow you to access SQL CLOB values.
| See Also:
Oracle C++ Call Interface Programmer's Guide for detailed information on the Clob class. |
Methods in the ResultSet and Statement interfaces, such as getBlob() and setBlob(), allow you to access SQL BLOB values. The Blob 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.
See Also:
|
The Bfile class enables you to instantiate an Bfile object in your C++ application. You must then use methods of the Bfile class, such as the setName() method, to initialize the Bfile object which associates the object properties with an object of type BFILE in a BFILE column of the database.
| See Also:
Oracle C++ Call Interface Programmer's Guide for detailed information on the Blob class methods and details on instantiating and initializing an Blob object in your C++ application. |
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:
All these operations refer to the amount of LOB data on the server.
NCLOB instances are allowed as parameters in methodsNCLOB instances are allowed as attributes in object types.The copy() method on Clob and Blob enables you to load data from a BFILE. You can pass one of the following values for the amount parameter to this method:
BFILE to load a portion of the dataBFILE to load all of the dataUB4MAXVAL constant to load all of the BFILE dataNote that you cannot specify an amount larger than the length of the BFILE.
The read() method on an Clob, Blob, or Bfile object, reads data from a BFILE. You can pass one of the following values for the amount parameter to specify the amount of data to read:
BFILE to load a portion of the dataBFILE to load all of the data0 (zero) to read until the end of the BFILE in streaming modeNote that you cannot specify an amount larger than the length of the BFILE.
See Also:
|
OCCI methods that operate on BLOBs, CLOBs, NCLOBs, and BFILEs are as follows:
| Function/Procedure | Description |
|---|---|
|
|
Opens a LOB |
|
|
Sees if a LOB is open |
|
|
Closes a LOB |
You can make changes to an entire persistent 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 persistent 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++ 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:
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 |
|---|---|
|
ASSIGN |
Assigns one LOB locator to another. |
|
FILE SET |
Sets the directory object name 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 persistent 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 persistent 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:
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 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 LOB locator to another. |
|
FILE SET |
Sets the directory object name 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 set 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 persistent 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 the database, has no manuals, only online help.
Its online help is available through the Application Development submenu of the database 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, then 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.
Dim OraDyn as OraDynaset, OraSound1 as OraBLOB, OraSoundClone as OraBlob, OraMyBfile as OraBFile OraConnection.BeginTrans set OraDyn = OraDb.CreateDynaset("select * from print_media order by product_ 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 row OraDyn.MoveNext OraDyn.Edit 'Lets update OraSound1 data with that from the BFILE OraSound1.CopyFromBFile OraMyBFile OraDyn.Update OraDyn.MoveNext 'Go to Next row OraDyn.Edit 'Lets update OraSound1 by appending with LOB data from 1st row represenetd by 'OraSoundClone OraSound1.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.
The following OO4O methods and properties operate on BLOBs, CLOBs, NCLOBs, and BFILEs:
| Method | Description |
|---|---|
|
|
Opens BFILE. |
|
|
Closes BFILE. |
| Methods | Description |
|---|---|
|
|
Closes an open |
|
|
Closes all open |
|
|
Opens a |
|
|
Determines if a BFILE is open |
You can perform the following tasks on LOBs with Java (JDBC):
You can make changes to an entire persistent LOB, or to pieces of the beginning, middle or end of a persistent 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(), then the retrieved locator still refers to the original LOB row.
To retrieve the locator for the most current row, you must call getBLOB(), getCLOB(), or getBFILE() on the OracleResultSet each time a move operation is made depending on whether the instance is a BLOB, CLOB or BFILE.
For further JDBC syntax and information about using JDBC with LOBs:
See Also:
|
The following JDBC methods operate on BLOBs, CLOBs, and BFILEs:
| Method | Description |
|---|---|
|
|
Inserts the byte array into the BLOB, starting at the given offset |
| Method | Description |
|---|---|
|
|
Streams the LOB as a binary stream |
|
|
Writes to LOB as a binary stream |
| Method | Description |
|---|---|
|
|
Reads the BFILE as a binary stream |
Oracle Database JDBC drivers contain APIs to create and close temporary LOBs. These APIs can replace workarounds of using the following procedures from the DBMS_LOB PL/SQL package in prior releases:
oracle.sql.CLOB class is the Oracle JDBC driver implementation of standard JDBC java.sql.Clob interface. Table 6-51 lists the new Oracle extension APIs in oracle.sql.CLOB for accessing temporary CLOBs.
Oracle Database 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 implementation of standard JDBC java.sql.Blob interface. Table 6-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 implementation of the standard JDBC java.sql.Clob interface. Table 6-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 6-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 is 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 (); } }
Oracle Database JDBC drivers contain APIs to trim persistent LOBs. These APIs replace previous techniques that used DBMS_LOB.trim().
oracle.sql.BLOB class is Oracle JDBC driver implementation of the standard JDBC java.sql.Blob interface. Table 6-55 lists the new Oracle extension API in oracle.sql.BLOB that trims BLOBs.
| Methods | Description |
|---|---|
|
|
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 implementation of standard JDBC java.sql.Clob interface. Table 6-56 lists the new Oracle extension API in oracle.sql.CLOB that trims CLOBs.
| Methods | Description |
|---|---|
|
|
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:
"Trimming LOB Data", for an example. |
The JDBC interface provided with the database includes LOB streaming APIs that enable you to read from or write to a LOB at the requested position from a Java stream.
The oracle.sql.BLOB class implements the standard JDBC java.sql.Blob interface. Table 6-57 lists Oracle extensions to the oracle.sql.BLOB API that manipulate 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
The oracle.sql.CLOB class is the Oracle JDBC driver implementation of standard JDBC java.sql.Clob interface. Table 6-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 6-59 lists the new Oracle extension APIs in oracle.sql.BFILE that reads BFILE content from the requested position.
| Methods | Description |
|---|---|
|
|
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 is 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:
Oracle Data Provider for .NET (ODP.NET) is an implementation of a data provider for the Oracle database. ODP.NET uses Oracle native APIs to offer fast and reliable access to Oracle data and features from any .NET application. ODP.NET also uses and inherits classes and interfaces available in the Microsoft .NET Framework Class Library. The ODP.NET supports the following LOBs as native datatypes with .NET: BLOB, CLOB, NCLOB, and BFILE.
|
 Copyright © 1996, 2003 Oracle Corporation All Rights Reserved. |
|