In previous releases, Oracle JDBC drivers required Oracle extensions to standard JDBC types to perform many operations in the Oracle Database. JDBC 3.0 reduced the requirement of using Oracle extensions and JDBC 4.0 nearly eliminated this limitation. Refer to the Javasoft Javadoc for the
javax.sql packages, and to the Oracle JDBC Javadoc for details on Oracle extensions.
This chapter contains the following sections:
Prior to Oracle Database 10g, the maximum size of a LOB was 2^32 bytes. This restriction has been removed since Oracle Database 10g, and the maximum size is limited to the size of available physical storage.
The Oracle database supports the following four LOB data types:
Binary large object (BLOB)
This data type is used for unstructured binary data.
Character large object (CLOB)
This data type is used for character data.
National character large object (NCLOB)
This data type is used for national character data.
This data type is used for large binary data objects stored in operating system files, outside of database tablespaces.
BLOBs, CLOBs, and NCLOBs are stored persistently in a database tablespace and all operations performed on these data types are under transaction control.
BFILE is an Oracle proprietary data type that provides read-only access to data located outside the database tablespaces on tertiary storage devices, such as hard disks, network mounted files systems, CD-ROMs, PhotoCDs, and DVDs. BFILE data is not under transaction control and is not stored by database backups.
The PL/SQL language supports the LOB data types and the JDBC interface allows passing IN parameters to PL/SQL procedures or functions, and retrieval of OUT parameters or returns. PL/SQL uses value semantics for all data types including LOBs, but reference semantics only for BFILE.
Oracle Database 11g Release 1 (11.1) introduced Oracle SecureFiles, a completely new storage for LOBs.
Following Features of Oracle SecureFiles are transparently available to JDBC programs through the existing APIs:
SecureFile compression enables users to compress data to save disk space.
SecureFile encryption introduces a new encryption facility that allows for random reads and writes of the encrypted data.
Deduplication enables Oracle database to automatically detect duplicate LOB data and conserve space by storing only one copy of data.
LOB data path optimization includes logical cache above storage layer and new caching modes.
High performance space management.
getLobOptions APIs are described in the PL/SQL Packages and Types Reference, and may be accessed from JDBC through callable statements.
Following Oracle SecureFiles features are implemented in the database through updates to the existing APIs:
You can check whether or not your
CLOB data uses Oracle SecureFile storage. To achieve this, use the following method from
public boolean isSecureFile() throws SQLException
If this method returns
true, then your data uses SecureFile storage.
With the release of Oracle Database 12c Release 1 (12.1) JDBC Drivers, the performance of Oracle SecureFiles operations is greatly improved because Oracle Net Services now uses zero-copy I/O framework for better buffer management.
Oracle Database 11g Release 2 introduced a new connection property
oracle.net.useZeroCopyIO. This property can be used to enable or disable the zero-copy I/O protocol. This connection property is defined as the following constant:
OracleConnection.CONNECTION_PROPERTY_THIN_NET_USE_ZERO_COPY_IO. If you want to disable the zero-copy I/O framework, then set the value of this connection property to
false. By default, the value of this connection property is
This section describes the following topics:
The Oracle Database 12c Release 1 (12.1) JDBC drivers provide a streamlined mechanism for writing and reading the entire LOB contents. This is referred to as the data interface. The data interface uses standard JDBC methods such as
setBytes to read and write LOB data. It is simpler to code and faster in many cases. Unlike the standard
java.sql.NClob interfaces, it does not provide random access capability, that is, it does not use LOB locator and cannot access data beyond 2147483648 elements.
In Oracle Database 12c Release 1 (12.1), the
setAsciiStream methods of
PreparedStatement are extended to enhance the ability to work with
NCLOB target columns.
Note:This enhancement does not affect the
BFILEdata because it is read-only.
For the JDBC Oracle Call Interface (OCI) and Thin drivers, there is no limitation on the size of the
byte array or
String, and no limitation on the length specified for the stream functions, except the limits imposed by the Java language.
Note:In Java, the array size is limited to positive Java
intor 2147483648 elements.
For the server-side internal driver, currently there is a limitation of 32767 bytes for operations on SQL statements, such as an
INSERT statement. This limitation does not apply for PL/SQL statements. There is a simple workaround for an
INSERT statement, where it is wrapped in a PL/SQL block in the following way:
BEGIN INSERT id, c INTO clob_tab VALUES(?,?); END;
You must bear in mind the following automatic switching of the input mode for large data:
There are three input modes as follows:
This binding is limited in size but most efficient. It places the data for all input columns inline in the block of data sent to the server. All data, including multiple executions of a batch, is sent in a single network operation.
This binding places data at the end. It limits batch size to one and may require multiple round trips to complete.
This binding creates a temporary LOB, copies data to the LOB, and binds the LOB locator. The temporary LOB is automatically freed after execution. The operation to create the temporary LOB and then to writing to the LOB requires multiple round trips. The input of the locators may be batched.
For SQL statements:
setBinaryStream methods use direct binding for data less than 32767 bytes.
setBinaryStream methods use stream binding for data larger than 32767 bytes.
In JDBC 4.0 has introduced new forms of the
setCharacterStream methods. The form, where the methods take a long argument as length, uses LOB binding for length larger than 2147483648. The form, where the length is not specified, always uses LOB binding.
setAsciiStream methods use direct binding for data smaller than 32767 characters.
setAsciiStream methods use stream binding for data larger than 32766 characters.
The new form of
setCharacterStream method, which takes a
long argument for length, uses LOB binding for length larger than 2147483647, in JDBC 4.0. The form, where the length is not specified, always uses LOB binding.
setBinary stream methods use direct binding for data less than 32767 bytes.
Note:If the underlying Database is Oracle Database release 10.x, then this data size limit is 32512 bytes, though you are working with Oracle Database 12c Release 1 (12.1) JDBC drivers.
setBinaryStream methods use LOB binding for data larger than 32766 bytes.
setAsciiStream methods use direct binding for data smaller than 32767 bytes in the database character set.
Note:If the underlying Database is Oracle Database release 10.x, then this data size limit is 32512 bytes, though you are working with Oracle Database 12c Release 1 (12.1) JDBC drivers.
setAsciiStream methods use LOB binding for data larger than 32766 bytes in the database character set.
The automatic switching of the input mode for large data has impact on certain programs. Previously, you used to get
ORA-17157 errors for attempts to use
setString method for
String values larger than 32766 characters. Now, depending on the type of the target parameter, an error may occur while the statement is executed or the operation may succeed.
Another impact is that the automatic switching may result in additional server-side parsing to adapt to the change in the parameter type. This would result in a performance effect, if the data sizes vary above and below the limit for repeated executions of the statement. Switching to the stream modes will effect batching as well.
setStringForClob methods, present in the
oracle.jdbc.OraclePreparedStatement interface, use LOB binding for any data size.
SetBigStringTryClob connection property of Oracle Database 10g Release 1 is no longer used or needed.
getAsciiStream methods of
CallableStatement are extended to work with
BFILE columns or
OUT parameters. These methods work for any
LOB of length less than 2147483648.
getNStringmethods cannot be used for retrieving BLOB column values. For more information about
getNStringmethod, refer to New Methods for National Character Set Type Data in JDK 6.
The data interface operates by accessing the
LOB locators within the driver and is transparent to application programming. It works with any supported version of the database, that is, Oracle Database 10.1.x and later. For Oracle Database 11g Release 1 or later versions, LOB prefetching may be used to reduce or eliminate any additional database round trips required. For more information, refer to LOB prefetching.
You can read
BFILE data and read and write
CLOB data using the
defineColumnType method. To read, use
defineColumnType(nn,Types.LONGVARCHAR) method on the column. This produces a direct stream on the data as if it were a
LONG RAW or
LONG column. This technique is limited to Oracle Database 10g release 1 (10.1) and later.
It is a PL/SQL requirement that the Java types used as input and output for an IN OUT parameter must be the same. The automatic switching of types done by the extensions described in this chapter may cause problems with this.
Consider that you have an
CLOB parameter of a stored procedure and you wish to use
setString method for setting the value for this parameter. For any
OUT parameter, the binds must be of the same type. The automatic switching of the input mode will cause problems unless you are sure of the data sizes. For example, if it is known that neither the input nor output data will ever be larger than 32766 bytes, then you could use
setString method for the input parameter and register the
OUT parameter as
Types.VARCHAR and use
getString method for the output parameter.
A better solution is to change the stored procedure to have separate
OUT parameters. That is, if you have:
CREATE PROCEDURE clob_proc( c IN OUT CLOB );
then, change it to:
CREATE PROCEDURE clob_proc( c_in IN CLOB, c_out OUT CLOB );
Another workaround is to use a container block to make the call. The
clob_proc procedure can be wrapped with a Java String to use for the
prepareCall statement, as follows:
"DECLARE c_temp; BEGIN c_temp := ?; clob_proc( c_temp); ? := c_temp; END;"
In either case, you may use the
setString method on the first parameter and the
registerOutParameter method with
Types.CLOB on the second.
Be aware of the effect on the performance of the Java memory management system due to creation of very large
byte array or
String. Read the information provided by your Java virtual machine (JVM) vendor about the impact of very large data elements on memory management, and consider using the stream interfaces instead.
Locators are small data structures, which contain information that may be used to access the actual data of the LOB. In a database table, the locator is stored directly in the table, while the data may be in the table or in separate storage. It is common to use separate tablespaces for large LOBs.
In JDBC 4.0, LOBs should be read or written using the interfaces
java.sql.NClob. These provide random access to the data in the LOB.
The Oracle implementation classes
oracle.sql.NCLOB store the locator and access the data with it. The
oracle.sql.CLOB classes implement the
java.sql.Clob interfaces respectively. In
java.sql.NClob, but in
ojdbc5.jar, it implements the
Note:Starting from Oracle Database 12c Release 1 (12.1), the
oracle.sql.CLOBclasses are deprecated and replaced with the
oracle.jdbc.OracleClobinterfaces. Oracle recommends you to use the methods available in the
java.sqlpackage, where possible, for standard compatibility and methods available in the
oracle.jdbcpackage for Oracle specific extensions. Refer to MoS Note 1364193.1 for more information about these interface.
In Oracle Database 12c Release 1 (12.1), the Oracle JDBC drivers support the JDBC 4.0
java.sql.NClob interface in
ojdbc7.jar, which are compiled with JDK 6 (must be used with JRE 6) and JDK 7 (must be used with JRE 7) respectively.
oracle.sql.BFILE is an Oracle extension, without a corresponding
See Also:The JDBC Javadoc for more details.
For Oracle Database 12c Release 1 (12.1) JDBC drivers, the number of round trips is reduced by prefetching the metadata such as the LOB length and the chunk size as well as the beginning of the LOB data along with the locator during regular fetch operations. If you select LOB columns into a result set, some or all of the data is prefetched to the client, when the locator is fetched. It saves the first roundtrip to retrieve data by deferring all preceding operations until fetching from the locator.
Note:LOB Prefetching is inversely proportional to the size of the LOB data, that is, the benefits of prefetching are more for small LOBs and less for larger LOBs.
The prefetch size is specified in bytes for BLOBs and in characters for CLOBs. It can be specified by setting the connection property
oracle.jdbc.defaultLobPrefetchSize. The value of this property can be overridden in the following two ways:
At the statement level: By using the
At the column level: By using the form of
defineColumnType method that takes length as argument
The default prefetch size is 4000.
Note:Be aware of the possible memory consumption while setting large LOB prefetch sizes in combination with a large row prefetch size and a large number of LOB columns.
See Also:The JDBC Javadoc for more details
Oracle Database 11g Release 1 introduced the
java.sql.NClob interface. The Oracle drivers implement the
java.sql.NCLOB interface in both
The Oracle drivers implement the new factory methods,
createNClob in the
java.sql.Connection interface to create temporary LOBs.
Starting from JDK 6, the
java.sql.NClob interfaces have a new method
free to free an LOB and release the associated resources. The Oracle drivers use this method to free an LOB, if it is a temporary LOB.
You can use temporary LOBs to store transient data. The data is stored in temporary table space rather than regular table space. You should free temporary LOBs after you no longer need them. If you do not, then the space the LOB consumes in temporary table space will not be reclaimed.
You can insert temporary LOBs into a table. When you do this, a permanent copy of the LOB is created and stored.
Note:Inserting a temporary LOB may be preferable in some situations. For example, when the LOB data is relatively small and the overhead of copying the data is less than the cost of a database round trip to retrieve the empty locator. Remember that the data is initially stored in the temporary table space on the server and then moved into permanent storage.
You can also create a temporary LOB/CLOB or NCLOB by using the connection factory methods available in JDBC 4.0. For more information, refer to "LOB Creation".
You can test whether a LOB is temporary or not by calling the
isTemporary method. If the LOB was created by calling the
createTemporary method, then the
isTemporary method returns
true, else it returns
If you do not free a temporary LOB, then it will make the storage used by that LOB in the database unavailable. Frequent failure to free temporary LOBs will result in filling up temporary table space with unavailable LOB storage.
When fetching data from a
ReultSet with columns that are temporary LOBs, use
getBlob methods instead of
The JDBC 4.0 method
free, present in
java.sql.NClob interfaces, supercedes the
This section discusses how to open and close your LOBs. The JDBC implementation of this functionality is provided using the following methods of
Starting from Oracle Database 12c Release 1 (12.1), the
oracle.sql.CLOB classes are deprecated and replaced with the
oracle.jdbc.OracleClob interfaces. Oracle recommends you to use the methods available in the
java.sql package, where possible, for standard compatibility and methods available in the
oracle.jdbc package for Oracle specific extensions. Refer to MoS Note 1364193.1 for more information about these interface.
You do not have to necessarily open and close your LOBs. You may choose to open and close them for performance reasons.
void open (int mode)
If you do not wrap LOB operations inside an
Open/Close call operation, then each modification to the LOB will implicitly open and close the LOB, thereby firing any triggers on a domain index. Note that in this case, any domain indexes on the LOB will become updated as soon as LOB modifications are made. Therefore, domain LOB indexes are always valid and may be used at any time within the same transaction.
If you wrap your LOB operations inside the
Open/Close call operation, then triggers will not be fired for each LOB modification. Instead, the trigger on domain indexes will be fired at the
Close call. For example, you might design your application so that domain indexes are not be updated until you call the
close method. However, this means that any domain indexes on the LOB will not be valid in-between the
You open a LOB by calling the
open(int) method. You may then read and write the LOB without any triggers associated with that LOB firing. When you finish accessing the LOB, close the LOB by calling the
close method. When you close the LOB, any triggers associated with the LOB will fire.
You can check if a LOB is open or closed by calling the
isOpen method. If you open the LOB by calling the
open(int) method, then the value of the argument must be either
MODE_READWRITE, as defined in the
oracle.sql.CLOB classes. If you open the LOB with
MODE_READONLY, then any attempt to write to the LOB will result in a SQL exception.
An error occurs if you commit the transaction before closing all LOBs that were opened by the transaction. The openness of the open LOBs is discarded, but the transaction is successfully committed. Hence, all the changes made to the LOB and non-LOB data in the transaction are committed, but the triggers for domain indexing are not fixed.
close methods apply only to persistent LOBs. The
close method is not similar to the
freeTemporary methods used for temporary LOBs. The
freeTemporary methods release storage and make a LOB unusable. On the other hand, the
close method indicates to the database that a modification on a LOB is complete, and triggers should be fired and indexes should be updated. A LOB is still usable after a call to the
This section describes how to read data from BFILEs, using file locators. This section covers the following topics:
The BFILE data type and
oracle.sql.BFILE classes are Oracle proprietary. So, there is no standard interface for them. You must use Oracle extensions for this type of data.
If you have a standard JDBC result set or callable statement object that includes BFILE locators, then you can access the locators by using the standard result set
getObject method. This method returns an
You can also access the locators by casting your result set to
OracleResultSet or your callable statement to
OracleCallableStatement and using the
Note:If you are using
getOracleObjectmethods, then remember to cast the output, as necessary.
Once you have a locator, you can access the BFILE data via the API in
oracle.sql.BFILE. These APIs are similar to the read methods of the
You cannot write data to the contents of the BFILE, but you can use an instance of
oracle.sql.BFILE as input to a SQL statement or to a PL/SQL procedure. You can achieve this by performing one of the following:
Use the standard
Cast the statement to
OracleCallableStatement, and use the
setBFILE method. These methods take the parameter index and an
oracle.sql.BFILE object as input.
There is no standard
java.sql interface for BFILEs.
getBFILE methods in the
OracleCallableStatement interfaces to retrieve an
oracle.sql.BFILE object. The
setBFILE methods in
OracleCallableStatement interfaces accept
oracle.sql.BFILE object as an argument. Use these methods to write to a BFILE.
Oracle recommends that you use the
updateBFILE methods instead of the
updateBfile methods. For example, use the
setBFILE method instead of the
BFILEs are read-only. The body of the data resides in the operating system (OS) file system and can be written to using only OS tools and commands. You can create a BFILE for an existing external file by executing the appropriate SQL statement either from JDBC or by using any other way to execute SQL. However, you cannot create an OS file that a BFILE would refer to by SQL or JDBC. Those are created only externally by a process that has access to server file systems.
Note:The code examples present in this chapter, in the earlier versions of this guide, have been removed in favor of references to the sample code available for download on OTN.