Oracle® Database Application Developer's Guide - Large Objects 10g Release 2 (10.2) Part Number B14249-01 |
|
|
View PDF |
This chapter contains these topics:
Table 6-1 lists the programmatic environments that support LOB functionality.
See Also:
APIs for supported LOB operations are described in detail in:Table 6-1 Programmatic Environments That Support LOBs
Language | Precompiler or Interface Program | Syntax Reference | In This Chapter See... |
---|---|---|---|
PL/SQL | DBMS_LOB Package | Oracle Database PL/SQL Packages and Types Reference | "Using PL/SQL (DBMS_LOB Package) to Work with LOBs". |
C | Oracle Call Interface for C (OCI) | Oracle Call Interface Programmer's Guide | "Using OCI to Work with LOBs". |
C++ | Oracle Call Interface for C++ (OCCI) | Oracle C++ Call Interface Programmer's Guide |
"Using C++ (OCCI) to Work with LOBs" . |
C/C++ | Pro*C/C++ Precompiler | Pro*C/C++ Programmer's Guide | "Using C/C++ (Pro*C) to Work with LOBs". |
COBOL | Pro*COBOL Precompiler | Pro*COBOL Programmer's Guide | "Using COBOL (Pro*COBOL) to Work with LOBs". |
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) to Work with LOBs"." |
Java | JDBC Application Programmatic Interface (API) | Oracle Database JDBC Developer's Guide and Reference. | "Using Java (JDBC) to Work with LOBs". |
OLEDB | OraOLEDB, an OLE DB provider for Oracle. | Oracle Provider for OLE DB Developer's Guide |
"Oracle Provider for OLE DB (OraOLEDB)" |
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.
Table 6-2 Comparing the LOB Interfaces, 1 of 2
Table 6-3 Comparing the LOB Interfaces, 2 of 2
The PL/SQL DBMS_LOB
package can be used for the following operations:
Internal persistent LOBs and Temporary LOBs: Read and modify operations, either entirely or in a piece-wise manner.
BFILEs: Read operations
See Also:
Oracle Database 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.
Persistent LOBs: First use SQL to define tables that contain LOB columns, and subsequently you can use SQL to initialize or populate the locators in these LOB columns.
External LOBs: Define a 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 Objects" 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:
For character data—in all formats, fixed-width and varying-width—the amount
and offset
parameters are in characters. This applies to operations on CLOB
and NCLOB
datatypes.
For binary data, the offset
and amount
parameters are in bytes. This applies to operations on BLOB
datatypes.
When using the following procedures:
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.
When using DBMS_LOB.READ
, the 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.
See Also:
"Reading Data from a LOB"To determine the character set ID, you must know the character set name (a user can select from the V$NLS_VALID_VALUES
view, which lists the names of the character sets that are valid as database and national character sets). Then call the function NLS_CHARSET_ID
with the desired character set name as the one string argument. The character set ID is returned as an integer. UTF16
does not work because it has no character set name. Use character set ID = 1000 for UTF16
. Although UTF16
is not allowed as a database or national character set, the APIs in DBMS_LOB
support it for database conversion purposes. DBMS_LOB.LOADCLOBFROMFILE
and other procedures in DBMS_LOB
take character set ID, not character set name, as an input.
See Also:
Oracle Database Globalization Support Guide, Appendix A, for supported languagesPL/SQL functions and procedures that operate on BLOB
s, CLOB
s, NCLOB
s, and BFILE
s are summarized in the following:
Table 6-4 PL/SQL: DBMS_LOB Procedures to Modify LOB Values
Function/Procedure | Description |
---|---|
APPEND |
Appends the LOB value to another LOB |
CONVERTTOBLOB |
Converts a CLOB to a BLOB |
CONVERTTOCLOB |
Converts a BLOB to a CLOB |
COPY |
Copies all or part of a LOB to another LOB |
ERASE |
Erases part of a LOB, starting at a specified offset |
LOADFROMFILE |
Load BFILE data into a persistent LOB |
LOADCLOBFROMFILE |
Load character data from a file into a LOB |
LOADBLOBFROMFILE |
Load binary data from a file into a LOB |
TRIM |
Trims the LOB value to the specified shorter length |
WRITE |
Writes data to the LOB at a specified offset |
WRITEAPPEND |
Writes data to the end of the LOB |
Table 6-5 PL/SQL: DBMS_LOB Procedures to Read or Examine Internal and External LOB values
Function/Procedure | Description |
---|---|
COMPARE |
Compares the value of two LOBs |
GETCHUNKSIZE |
Gets the chunk size used when reading and writing. This only works on persistent LOBs and does not apply to external LOBs (BFILE s). |
GETLENGTH |
Gets the length of the LOB value |
INSTR |
Returns the matching position of the nth occurrence of the pattern in the LOB |
READ |
Reads data from the LOB starting at the specified offset |
SUBSTR |
Returns part of the LOB value starting at the specified offset |
Table 6-7 PL/SQL: DBMS_LOB Read-Only Procedures for BFILEs
Function/Procedure | Description |
---|---|
FILECLOSE |
Closes the file. Use CLOSE() instead. |
FILECLOSEALL |
Closes all previously opened files |
FILEEXISTS |
Checks if the file exists on the server |
FILEGETNAME |
Gets the directory object name and file name |
FILEISOPEN |
Checks if the file was opened using the input BFILE locators. Use ISOPEN() instead. |
FILEOPEN |
Opens a file. Use OPEN() instead. |
Table 6-8 PL/SQL: DBMS_LOB Procedures to Open and Close Internal and External LOBs
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 BFILE
s 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 byteUunicode (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:
Oracle Call Interface Programmer's Guide for information on the OCIUnicodeToCharSet()
function and details on OCI syntax in general.
Oracle Database Globalization Support Guide for detailed information about implementing applications in different languages.
In OCI, for fixed-width client-side character sets, the following rules apply:
CLOB
s and NCLOB
s: offset and amount parameters are always in characters
BLOB
s and BFILE
s: offset and amount parameters are always in bytes
The following rules apply only to varying-width client-side character sets:
Offset parameter: Regardless of whether the client-side character set is varying-width, the offset parameter is always as follows:
CLOB
s and NCLOB
s: in characters
BLOB
s and BFILE
s: in bytes
Amount parameter: The amount parameter is always as follows:
When referring to a server-side LOB: in characters
When referring to a client-side buffer: in bytes
OCILobFileGetLength(): Regardless of whether the client-side character set is varying-width, the output length is as follows:
CLOB
s and NCLOB
s: in characters
BLOB
s and BFILE
s: in bytes
OCILobRead2(): With client-side character set of varying-width, CLOB
s and NCLOB
s:
Input amount is in characters. Input amount refers to the number of characters to read from the server-side CLOB
or NCLOB
.
Output amount is in bytes. Output amount indicates how many bytes were read into the buffer bufp
.
OCILobWrite2(): With client-side character set of varying-width, CLOB
s and NCLOB
s:
Input amount is in bytes. The input amount refers to the number of bytes of data in the input buffer bufp
.
Output amount is in characters. 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 CLOB
s and NCLOB
s. These include OCILobCopy2()
, OCILobErase2()
, OCILobLoadFromFile2()
, and OCILobTrim2()
. All these operations refer to the amount of LOB data on the server.
When using OCILobLoadFromFile2()
you cannot specify amount
larger than the length of the BFILE
. To load the entire BFILE
, you can pass the value returned by OCILobGetStorageLimit()
.
To read to the end of a LOB using OCILobRead2()
, you specify an amount equal to the value returned by OCILobGetStorageLimit()
. See "Reading Data from a LOB" 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()
.
OCI functions that operate on BLOB
s, CLOB
s, NCLOB
s, and BFILE
s are as follows:
To modify persistent LOBs, see Table 6-9
To read or examine LOB values, see Table 6-10
To create or free temporary LOB, or check if Temporary LOB exists, see Table 6-11
For read only functions on external LOBs (BFILE
s), see Table 6-12
To operate on LOB locators, see Table 6-13
For LOB buffering, see Table 6-14
To open and close LOBs, see Table 6-15
Table 6-9 OCI Functions to Modify Persistent LOB (BLOB, CLOB, and NCLOB) Values
Function/Procedure | Description |
---|---|
OCILobArrayWrite() |
Writes data using multiple locators in one round trip. |
OCILobAppend() |
Appends LOB value to another LOB. |
OCILobCopy2() |
Copies all or part of a LOB to another LOB. |
OCILobErase2() |
Erases part of a LOB, starting at a specified offset. |
OCILobLoadFromFile2() |
Loads BFILE data into a persistent LOB. |
OCILobTrim2() |
Truncates a LOB. |
OCILobWrite2() |
Writes data from a buffer into a LOB, overwriting existing data. |
OCILobWriteAppend2() |
Writes data from a buffer to the end of the LOB. |
Table 6-10 OCI Functions to Read or Examine persistent LOB and external LOB (BFILE) Values
Function/Procedure | Description |
---|---|
OCILobArrayRead() |
Reads data using multiple locators in one round trip. |
OCILobGetChunkSize() |
Gets the Chunk size used when reading and writing. This works on persistent LOBs and does not apply to external LOBs (BFILE s). |
OCILobGetLength2() |
Returns the length of a LOB or a BFILE . |
OCILobRead2() |
Reads a specified portion of a non-NULL LOB or a BFILE into a buffer. |
Table 6-12 OCI Read-Only Functions for BFILES
Function/Procedure | Description |
---|---|
OCILobFileClose() |
Closes an open BFILE . |
OCILobFileCloseAll() |
Closes all open BFILE s. |
OCILobFileExists() |
Checks whether a BFILE exists. |
OCILobFileGetName() |
Returns the name of a BFILE . |
OCILobFileIsOpen() |
Checks whether a BFILE is open. |
OCILobFileOpen() |
Opens a BFILE . |
Table 6-13 OCI LOB-Locator Functions
Function/Procedure | Description |
---|---|
OCILobAssign() |
Assigns one LOB locator to another. |
OCILobCharSetForm() |
Returns the character set form of a LOB. |
OCILobCharSetId() |
Returns the character set ID of a LOB. |
OCILobFileSetName() |
Sets the name of a BFILE in a locator. |
OCILobIsEqual() |
Checks whether two LOB locators refer to the same LOB. |
OCILobLocatorIsInit() |
Checks whether a LOB locator is initialized. |
Table 6-14 OCI LOB-Buffering Functions
Function/Procedure | Description |
---|---|
OCILobDisableBuffering() |
Disables the buffering subsystem use. |
OCILobEnableBuffering() |
Uses the LOB buffering subsystem for subsequent reads and writes of LOB data. |
OCILobFlushBuffer() |
Flushes changes made to the LOB buffering subsystem to the database (server) |
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://www.oracle.com/technology/
for OCI features and FAQs.
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:
OCCI encompasses more Oracle functionality than JDBC. OCCI provides all the functionality of OCI that JDBC does not provide.
OCCI provides compiled performance. With compiled programs, the source code is already written as close to the computer as possible. Because JDBC is an interpreted API, it cannot provide the performance of a compiled API. With an interpreted program, performance degrades as each line of code must be interpreted individually into code that is close to the computer.
OCCI provides memory management with smart pointers. You do not have to be concerned about managing memory for OCCI objects. This results in robust higher performance application code.
Navigational access of OCCI enables you to intuitively access objects and call methods. Changes to objects persist without need to write corresponding SQL statements. If you use the client side cache, then the navigational interface performs better than the object interface.
With respect to ODBC, the OCCI API is simpler to use. Because ODBC is built on the C language, OCCI has all the advantages C++ provides over C. Moreover, ODBC has a reputation as being difficult to learn. The OCCI, by contrast, is designed for ease of use.
You can use OCCI to make changes to an entire persistent LOB, or to pieces of the beginning, middle, or end of it, as follows:
For reading from internal and external LOBs (BFILE
s)
For writing to persistent LOBs
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 CLOB
s and NCLOB
s
Blob
class to access and modify data stored in internal BLOB
s
Bfile
class to access and read data stored in external LOBs (BFILE
s)
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:
Oracle C++ Call Interface Programmer's Guide for detailed information on the Blob class methods and details on instantiating and initializing a Blob object in your C++ application.
Oracle Database Globalization Support Guide for detailed information about implementing applications in different languages.
The Bfile
class enables you to instantiate a 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 theBlob
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:
Clob
: offset and amount parameters are always in characters
Blob
: offset and amount parameters are always in bytes
Bfile
: offset and amount parameters are always in bytes
The following rules apply only to varying-width client-side character sets:
Offset parameter: Regardless of whether the client-side character set is varying-width, the offset parameter is always as follows:
Clob()
: in characters
Blob()
: in bytes
Bfile()
: in bytes
Amount parameter: The amount parameter is always as follows:
Clob
: in characters, when referring to a server-side LOB
Blob
: in bytes, when referring to a client-side buffer
Bfile
: in bytes, when referring to a client-side buffer
length(): Regardless of whether the client-side character set is varying-width, the output length is as follows:
Clob.length()
: in characters
Blob.length()
: in bytes
Bfile.length()
: in bytes
Clob.read() and Blob.read(): With client-side character set of varying-width, CLOB
s and NCLOB
s:
Input amount is in characters. Input amount refers to the number of characters to read from the server-side CLOB
or NCLOB
.
Output amount is in bytes. Output amount indicates how many bytes were read into the OCCI buffer parameter, buffer
.
Clob.write() and Blob.write(): With client-side character set of varying-width, CLOB
s and NCLOB
s:
Input amount is in bytes. Input amount refers to the number of bytes of data in the OCCI input buffer, buffer
.
Output amount is in characters. Output amount refers to the number of characters written into the server-side CLOB
or NCLOB
.
For all other OCCI LOB operations, irrespective of the client-side character set, the amount parameter is in characters for CLOB
s and NCLOB
s. These include the following:
Clob.copy()
Clob.erase()
Clob.trim()
For LoadFromFile functionality, overloaded Clob.copy()
All these operations refer to the amount of LOB data on the server.
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:
An amount smaller than the size of the BFILE
to load a portion of the data
An amount equal to the size of the BFILE
to load all of the data
The UB4MAXVAL
constant to load all of the BFILE
data
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:
An amount smaller than the size of the BFILE
to load a portion of the data
An amount equal to the size of the BFILE
to load all of the data
0
(zero) to read until the end of the BFILE
in streaming mode
You cannot specify an amount larger than the length of the BFILE
.
See Also:
http://www.oracle.com/
search for articles and product information featuring OCCI.
OCCI methods that operate on BLOB
s, CLOB
s, NCLOB
s, and BFILE
s are as follows:
To modify persistent LOBs, see Table 6-16
To read or examine LOB values, see Table 6-17
For read only methods on external LOBs (BFILE
s), see Table 6-18
Other LOB OCCI methods are described in Table 6-19
To open and close LOBs, see Table 6-20
Table 6-16 OCCI Clob and Blob Methods to Modify Persistent LOB (BLOB, CLOB, and NCLOB) Values
Function/Procedure | Description |
---|---|
Blob/Clob.append() |
Appends CLOB or BLOB value to another LOB. |
Blob/Clob.copy() |
Copies all or part of a CLOB or BLOB to another LOB. |
Blob/Clob.copy() |
Loads BFILE data into a persistent LOB. |
Blob/Clob.trim() |
Truncates a CLOB or BLOB . |
Blob/Clob.write() |
Writes data from a buffer into a LOB, overwriting existing data. |
Table 6-17 OCCI Blob/Clob/Bfile Methods to Read or Examine persistent LOB and BFILE Values
Function/Procedure | Description |
---|---|
Blob/Clob.getChunkSize() |
Gets the Chunk size used when reading and writing. This works on persistent LOBs and does not apply to external LOBs (BFILE s). |
Blob/Clob.length() |
Returns the length of a LOB or a BFILE . |
Blob/Clob.read() |
Reads a specified portion of a non-NULL LOB or a BFILE into a buffer. |
Table 6-18 OCCI Read-Only Methods for BFILES
Table 6-19 Other OCCI LOB Methods
Methods | Description |
---|---|
Clob/Blob/Bfile.operator=() |
Assigns one LOB locator to another. Use = or the copy constructor. |
Clob.getCharSetForm() |
Returns the character set form of a LOB. |
Clob.getCharSetId() |
Returns the character set ID of a LOB. |
Bfile.setName() |
Sets the name of a BFILE . |
Clob/Blob/Bfile.operator==() |
Checks whether two LOB refer to the same LOB. |
Clob/Blob/Bfile.isInitialized() |
Checks whether a LOB is initialized. |
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 BLOB
s, CLOB
s, NCLOB
s, and BFILE
s. 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:
Provide an allocated input locator pointer that represents a LOB that exists in the database tablespaces or external file system before you run the statement.
SELECT a LOB locator into a LOB locator pointer variable
Use this variable in the embedded SQL LOB statement to access and manipulate the LOB value
See Also:
APIs for supported LOB operations are described in detail in:Pro*C statements that operate on BLOBs, CLOBs, and NCLOBs are listed in the following tables:
To modify persistent LOBs, see Table 6-21
To read or examine LOB values, see Table 6-22
To create or free temporary LOB, or check if Temporary LOB exists, see Table 6-23
To operate close and 'see if file exists' functions on BFILEs, see Table 6-24
To operate on LOB locators, see Table 6-25
For LOB buffering, see Table 6-26
To open or close LOBs or BFILE
s, see Table 6-27
Table 6-21 Pro*C/C++: Embedded SQL Statements to Modify Persistent LOB Values
Statement | Description |
---|---|
APPEND |
Appends a LOB value to another LOB. |
COPY |
Copies all or a part of a LOB into another LOB. |
ERASE |
Erases part of a LOB, starting at a specified offset. |
LOAD FROM FILE |
Loads BFILE data into a persistent LOB at a specified offset. |
TRIM |
Truncates a LOB. |
WRITE |
Writes data from a buffer into a LOB at a specified offset. |
WRITE APPEND |
Writes data from a buffer into a LOB at the end of the LOB. |
Table 6-22 Pro*C/C++: Embedded SQL Statements for Introspection of LOBs
Statement | Description |
---|---|
DESCRIBE [CHUNKSIZE] |
Gets the Chunk size used when writing. This works for persistent LOBs only. It does not apply to external LOBs (BFILE s). |
DESCRIBE [LENGTH] |
Returns the length of a LOB or a BFILE . |
READ |
reads a specified portion of a non-NULL LOB or a BFILE into a buffer. |
Table 6-26 Pro*C/C++ Embedded SQL Statements for LOB Buffering
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 BLOB
s, CLOB
s, NCLOB
s, and BFILE
s. 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:
Provide an allocated input locator pointer that represents a LOB that exists in the database tablespaces or external file system before you run the statement.
SELECT a LOB locator into a LOB locator pointer variable
Use this variable in an embedded SQL LOB statement to access and manipulate the LOB value.
See Also:
APIs for supported LOB operations are described in detail in: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:
To modify persistent LOBs, see Table 6-28
To read or examine internal and external LOB values, see Table 6-29
To create or free temporary LOB, or check LOB locator, see Table 6-30
To operate close and 'see if file exists' functions on BFILE
s, see Table 6-31
To operate on LOB locators, see Table 6-32
For LOB buffering, see Table 6-33
To open or close persistent LOBs or BFILE
s, see Table 6-34
Table 6-28 Pro*COBOL Embedded SQL Statements to Modify LOB Values
Statement | Description |
---|---|
APPEND |
Appends a LOB value to another LOB. |
COPY |
Copies all or part of a LOB into another LOB. |
ERASE |
Erases part of a LOB, starting at a specified offset. |
LOAD FROM FILE |
Loads BFILE data into a persistent LOB at a specified offset. |
TRIM |
Truncates a LOB. |
WRITE |
Writes data from a buffer into a LOB at a specified offset |
WRITE APPEND |
Writes data from a buffer into a LOB at the end of the LOB. |
Table 6-33 Pro*COBOL Embedded SQL Statements for LOB Buffering
Statement | Description |
---|---|
DISABLE BUFFERING |
Disables the use of the buffering subsystem. |
ENABLE BUFFERING |
Uses the LOB buffering subsystem for subsequent reads and writes of LOB data. |
FLUSH BUFFER |
Flushes changes made to the LOB buffering subsystem to the database (server) |
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:
OraBlob:
To provide methods for performing operations on BLOB datatypes in the database
OraClob:
To provide methods for performing operations on CLOB datatypes in the database
OraBFile:
To provide methods for performing operations on BFILE data stored in operating system files.
Note:
OracleBlob and OracleClob have been deprecated and no longer workThe 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:
http://www.oracle.com/technology/index.html
Select Products > Internet Tools > Programmer. Scroll down to "Oracle Objects for OLE". At the bottom of the page is a list of useful articles for using the interfaces.
http://www.oracle.com/
Search for articles on OO4O or Oracle Objects for OLE.
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 BLOB
s, CLOB
s, NCLOB
s, and BFILE
s.
See Also:
APIs for supported LOB operations are described in detail in:See Also:
The OO4O online help for detailed information including parameters, parameter types, return values, and example code. Oracle Objects for OLE (OO4O), a Windows-based product included with the database, has no manuals, only online help. The OO4O online help is available through the Application Development submenu of the database installation.The following OO4O methods and properties operate on BLOB
s, CLOB
s, NCLOB
s, and BFILE
s:
To modify persistent LOBs, see Table 6-35
To read or examine internal and external LOB values, see Table 6-36
To open and close BFILE
s, see Table 6-37
For LOB buffering, see Table 6-38
Properties such as to see if LOB is NULL
, or to get or set polling amount, see Table 6-39
For read-only BFILE
methods, see Table 6-40
For BFILE properties, see Table 6-41
Table 6-35 OO4O Methods to Modify BLOB, CLOB, and NCLOB Values
Methods | Description |
---|---|
OraBlob.Append
|
Appends BLOB value to another LOB.
Appends |
OraBlob.Copy
|
Copies a portion of a BLOB into another LOB
Copies a portion of a |
OraBlob.Erase
|
Erases part of a BLOB , starting at a specified offset
Erases part of a |
OraBlob.CopyFromBFile
|
Loads BFILE data into an internal BLOB
Loads |
OraBlob.Trim
|
Truncates a BLOB
Truncates a |
OraBlob.CopyFromFile
|
Writes data from a file to a BLOB
Writes data from a file to a |
OraBlob.Write
|
Writes data to the BLOB
Writes data to the |
Table 6-36 OO4O Methods to Read or Examine Internal and External LOB Values
Function/Procedure | Description |
---|---|
OraBlob.Read
|
Reads a specified portion of a non-NULL BLOB into a buffer
Reads a specified portion of a non- Reads a specified portion of a non- |
OraBlob.CopyToFile
|
Reads a specified portion of a non-NULL BLOB to a file
Reads a specified portion of a non- |
Table 6-38 OO4O Methods for Persistent LOB Buffering
Method | Description |
---|---|
OraBlob.FlushBuffer
|
Flushes changes made to the BLOB buffering subsystem to the database
Flushes changes made to the |
OraBlob.EnableBuffering
|
Enables buffering of BLOB operations
Enables buffering of |
OraBlob.DisableBuffering
|
Disables buffering of BLOB operations
Disables buffering of |
Table 6-39 OO4O Properties for Operating on LOBs
Property | Description |
---|---|
IsNull (Read) |
Indicates when a LOB is NULL |
PollingAmount(Read/Write) |
Gets/Sets total amount for Read/Write polling operation |
Offset(Read/Write) |
Gets/Sets offset for Read/Write operation. By default, it is set to 1. |
Status(Read) |
Returns the polling status.Possible values are
|
Size(Read) |
Returns the length of the LOB data |
You can perform the following tasks on LOBs with Java (JDBC):
Reading Internal Persistent LOBs and External LOBs (BFILEs) with Java
Create and Manipulate Temporary LOBs and Store Them in Tables as Permanent LOBs. See JDBC Temporary LOB APIs
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 classes:
oracle
.sql
.BLOB
oracle
.sql
.CLOB
These classes implement java.sql.Blob
and java.sql.Clob
interfaces according to the JDBC 3.0 specification, which has methods for LOB modification. They also include legacy Oracle proprietary methods for LOB modification. These legacy methods are marked as deprecated and may be removed in a future release.
If you use JDK 1.4 or higher, then you can use variables typed java.sql.Blob
and java.sql.Clob
.
The JDBC 3.0 methods are included in classes12.jar
, so that they can be used in JDK 1.2 or 1.3, but since they are not part of the java.sql.Blob
and java.sql.Clob
interfaces in those JDK versions, you must use variables typed or cast to oracle.sql.BLOB
or oracle.sql.CLOB
.
Table 6-42 and Table 6-43 show the conversions between Oracle proprietary methods that have been deprecated and JDBC 3.0 standard methods.
Table 6-42 BLOB Method Equivalents
Oracle Proprietary Method (Deprecated) | JDBC 3.0 Standard Method Replacement |
---|---|
putBytes(long pos, byte [] bytes) | setBytes(long pos, byte[] bytes) |
putBytes(long pos, byte [] bytes, int length) | setBytes(long pos, byte[] bytes, int offset, int len) |
getBinaryOutputStream(long pos) | setBinaryStream(long pos) |
trim (long len) | truncate(long len) |
Table 6-43 CLOB Method Equivalents
Oracle Proprietary Method (Deprecated) | JDBC 3.0 Standard Method Replacement |
---|---|
putString(long pos, String str) | setString(long pos, String str) |
N/A | setString(long pos, String str, int offset, int len) |
getAsciiOutputStream(long pos) | setAsciiStream(long pos) |
getCharacterOutputStream(long pos) | setCharacterStream(long pos) |
trim (long len) | truncate(long len) |
With JDBC you can use Java to read both internal persistent LOBs and external LOBs (BFILE
s).
BLOB and CLOB Classes. In JDBC theses classes provide methods for performing operations on large objects in the database including BLOB
and CLOB
data types.
BFILE Class. In JDBC this class provides methods for performing operations on BFILE data in the database.
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:
As a column of an OracleResultSet
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
(), 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:
Oracle Database JDBC Developer's Guide and Reference, "Working with LOBs and BFILEs" chapter, for detailed documentation, including parameters, parameter types, return values, and example code.
The following JDBC methods operate on BLOB
s, CLOB
s, and BFILE
s:
BLOB
s:
To modify BLOB
values, see Table 6-44
To read or examine BLOB
values, see Table 6-45
For BLOB
buffering, see Table 6-46
Temporary BLOB
s: Creating, checking if LOB is open, and freeing. See Table 6-54
Opening, closing, and checking if BLOB
is open, see Table 6-54
Truncating BLOB
s, see Table 6-57
BLOB
streaming API, see Table 6-59
CLOB
s:
To read or examine CLOB
values, see Table 6-48
For CLOB
buffering, see Table 6-49
To modify CLOB
s, see Table 6-59
Temporary CLOB
s:
Opening, closing, and checking if CLOB
is open, see Table 6-55
Truncating CLOB
s, see Table 6-58
CLOB
streaming API, see Table 6-60
BFILE
s:
To read or examine BFILE
s, see Table 6-50
For BFILE
buffering, see Table 6-51
Opening, closing, and checking if BFILE
is open, see Table 6-56
BFILE streaming API, see Table 6-61
Table 6-45 JDBC oracle.sql.BLOB Methods to Read or Examine BLOB Values
Method | Description |
---|---|
byte[] getBytes(long, int) |
Gets the contents of the LOB as an array of bytes, given an offset |
long position(byte[],long) |
Finds the given byte array within the LOB, given an offset |
long position(Blob,long) |
Finds the given BLOB within the LOB |
public boolean equals(java.lang.Object) |
Compares this LOB with another. Compares the LOB locators. |
public long length() |
Returns the length of the LOB |
public int getChunkSize() |
Returns the ChunkSize of the LOB |
Table 6-46 JDBC oracle.sql.BLOB Methods and Properties for BLOB Buffering
Method | Description |
---|---|
public java.io.InputStream getBinaryStream()) |
Streams the LOB as a binary stream |
public java.io.OutputStream setBinaryStream() |
Retrieves a stream that can be used to write to the BLOB value that this Blob object represents |
Table 6-47 JDBC oracle.sql.CLOB Methods to Modify CLOB Values
Method | Description |
---|---|
int setString(long, java.lang.String) |
JDBC 3.0: Writes the given Java String to the CLOB value that this Clob object designates at the position pos . |
int putChars(long, char[]) |
Inserts the character array into the LOB, starting at the given offset |
Table 6-48 JDBC oracle.sql.CLOB Methods to Read or Examine CLOB Values
Method | Description |
---|---|
java.lang.String getSubString(long, int) |
Returns a substring of the LOB as a string |
int getChars(long, int, char[]) |
Reads a subset of the LOB into a character array |
long position(java.lang.String, long) |
Finds the given String within the LOB, given an offset |
long position(oracle.jdbc2.Clob, long) |
Finds the given CLOB within the LOB, given an offset |
long length() |
Returns the length of the LOB |
int getChunkSize() |
Returns the ChunkSize of the LOB |
Table 6-49 JDBC oracle.sql.CLOB Methods and Properties for CLOB Buffering
Method | Description |
---|---|
java.io.InputStream getAsciiStream() |
Implements the Clob interface method. Gets the CLOB value designated by this Clob object as a stream of ASCII bytes |
java.io.OutputStream setAsciiStream(long pos) |
JDBC 3.0: Retrieves a stream to be used to write ASCII characters to the CLOB value that this Clob object represents, starting at position pos |
java.io.Reader getCharacterStream() |
Reads the CLOB as a character stream |
java.io.Writer setCharacterStream(long pos) |
JDBC 3.0: Retrieves a stream to be used to write Unicode characters to the CLOB value that this Clob object represents, starting at position pos |
Table 6-50 JDBC oracle.sql.BFILE Methods to Read or Examine External LOB (BFILE) Values
Method | Description |
---|---|
byte[] getBytes(long, int) |
G ets the contents of the BFILE as an array of bytes, given an offset |
int getBytes(long, int, byte[]) |
Reads a subset of the BFILE into a byte array |
long position(oracle.sql.BFILE, long) |
Finds the first appearance of the given BFILE contents within the LOB, from the given offset |
long position(byte[], long) |
Finds the first appearance of the given byte array within the BFILE , from the given offset |
long length() |
Returns the length of the BFILE |
boolean fileExists() |
Checks if the operating system file referenced by this BFILE exists |
public void openFile() |
Opens the operating system file referenced by this BFILE |
public void closeFile() |
Closes the operating system file referenced by this BFILE |
public boolean isFileOpen() |
Checks if this BFILE is already open |
public java.lang.String getDirAlias() |
Gets the directory object name for this BFILE |
public java.lang.String getName() |
Gets the file name referenced by this BFILE |
Oracle Database JDBC drivers contain APIs to create and close temporary LOBs. These APIs can replace workarounds that use the following procedures from the DBMS_LOB
PL/SQL package in prior releases:
DBMS_LOB.createTemporary()
DBMS_LOB.isTemporary()
DBMS_LOB.freeTemporary()
Table 6-52 JDBC: Temporary BLOB APIs
Methods | Description |
---|---|
public static BLOB createTemporary(Connection conn,
|
Creates a temporary BLOB |
public static boolean isTemporary(BLOB blob)
|
Checks if the specified BLOB locator refers to a temporary BLOB |
public boolean isTemporary() throws SQLException |
Checks if the current BLOB locator refers to a temporary BLOB |
public static void freeTemporary(BLOB temp_blob)
|
Frees the specified temporary BLOB |
public void freeTemporary() throws SQLException |
Frees the temporary BLOB |
Table 6-53 JDBC: Temporary CLOB APIs
Methods | Description |
---|---|
public static CLOB createTemporary(Connection conn,
|
Creates a temporary CLOB |
public static boolean isTemporary(CLOB clob)
|
Checks if the specified CLOB locator refers to a temporary CLOB |
public boolean isTemporary() throws SQLException |
Checks if the current CLOB locator refers to a temporary CLOB |
public static void freeTemporary(CLOB temp_clob)
|
Frees the specified temporary CLOB |
public void freeTemporary() throws SQLException |
Frees the temporary CLOB |
oracle.sql.CLOB
class is the Oracle JDBC driver implementation of standard JDBC java.sql.Clob
interface. Table 6-53 lists the 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-54 lists the Oracle extension APIs in oracle.sql.BLOB
that open and close BLOBs.
Table 6-54 JDBC: Opening and Closing BLOBs
Methods | Description |
---|---|
public void open(int mode) throws SQLException |
Opens the BLOB |
public boolean isOpen() throws SQLException |
Sees if the BLOB is open |
public void close() throws SQLException |
Closes the BLOB |
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-55 lists the Oracle extension APIs in oracle.sql.CLOB
to open and close CLOBs.
Table 6-55 JDBC: Opening and Closing CLOBs
Methods | Description |
---|---|
public void open(int mode) throws SQLException |
Open the CLOB |
public boolean isOpen() throws SQLExceptio |
See if the CLOB is opened |
public void close() throws SQLException |
Close the CLOB |
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-56 lists the Oracle extension APIs in oracle.sql.BFILE
for opening and closing BFILE
s.
Table 6-56 JDBC API Extensions for Opening and Closing BFILEs
Methods | Description |
---|---|
public void open() throws SQLException |
Opens the BFILE |
public void open(int mode) throws SQLException |
Opens the BFILE |
public boolean isOpen() throws SQLException |
Checks if the BFILE is open |
public void close() throws SQLException |
Closes the BFILE |
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 truncate 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-57 lists the Oracle extension API in oracle.sql.BLOB
that truncates BLOBs.
Table 6-57 JDBC: Truncating BLOBs
Methods | Description |
---|---|
public void truncate(long newlen) throws SQLException |
Truncates the BLOB |
The truncate API is defined as follows:
/** *Truncate the value of the BLOB to the length you specify in the newlen parameter. * @param newlen the new length of the BLOB. */ public void truncate (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-58 lists the Oracle extension API in oracle.sql.CLOB
that truncates CLOB
s.
Table 6-58 JDBC: Truncating CLOBs
Methods | Description |
---|---|
public void truncate(long newlen) throws SQLException |
Truncates the CLOB |
The truncate API is defined as follows:
/** *Truncate the value of the CLOB to the length you specify in the newlen parameter. * @param newlen the new length of the CLOB. */ public void truncate (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-59 lists BLOB Streaming APIs.
Table 6-59 JDBC: BLOB Streaming APIs
Methods | Description |
---|---|
public java.io.OutputStream
|
JDBC 3.0: Retrieves a stream that can be used to write to the BLOB value that this Blob object represents, starting at position pos |
public java.io.InputStream
|
JDBC 3.0: Retrieves a stream that can be used to read the BLOB value that this Blob object represents, starting at the beginning |
public java.io.InputStream
|
Oracle extension: Retrieves a stream that can be used to read the BLOB value that this Blob object represents, starting at position pos |
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 setBinaryStream(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-60 lists the CLOB streaming APIs.
Table 6-60 JDBC: CLOB Streaming APIs
Methods | Description |
---|---|
public java.io.OutputStream
|
JDBC 3.0: Retrieves a stream to be used to write ASCII characters to the CLOB value that this Clob object represents, starting at position pos |
public java.io.Writer
|
JDBC 3.0: Retrieves a stream to be used to write Unicode characters to the CLOB value that this Clob object represents, starting, at position pos |
public java.io.InputStream
|
JDBC 3.0: Retrieves a stream that can be used to read ASCII characters from the CLOB value that this Clob object represents, starting at the beginning |
public java.io.InputStream
|
Oracle extension: Retrieves a stream that can be used to read ASCII characters from the CLOB value that this Clob object represents, starting at position pos |
public java.io.Reader
|
JDBC 3.0: Retrieves a stream that can be used to read Unicode characters from the CLOB value that this Clob object represents, starting at the beginning |
public java.io.Reader
|
Oracle extension: Retrieves a stream that can be used to read Unicode characters from the CLOB value that this Clob object represents, starting at position pos |
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 setAsciiStream(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 setCharacterStream(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-61 lists the Oracle extension APIs in oracle.sql.BFILE
that reads BFILE
content from the requested position.
Table 6-61 JDBC: BFILE Streaming APIs
Methods | Description |
---|---|
public java.io.InputStream
|
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.setCharacterStream(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.setBinaryStream(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 an 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:
"set" APIs of PreparedStatement
"update" APIs of updatable result set
attribute value of STRUCTs
element value of ARRAYs
Note:
Empty LOBs are special marker LOBs but not real LOB values.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:
For Persistent LOBs. READ/WRITE through the rowset.
For BFILEs. READ-ONLY through the rowset.
Temporary LOBs are not supported through the rowset.
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
.