This chapter describes how to use Java Database Connectivity (JDBC) and theoracle.sql.* classes to access and manipulate large object (LOB) and binary file (BFILE) locators and data. This chapter contains the following sections:
Notes:
In Oracle Database 11g, the Oracle JDBC drivers support the JDBC 4.0java.sql.NClob interface.
In Oracle Database 10g, the Oracle JDBC drivers support the JDBC 3.0java.sql.Clob andjava.sql.Blob interfaces. Certain Oracle extensions made inoracle.sql.CLOB andoracle.sql.BLOB in earlier Oracle Database releases are no longer necessary and are deprecated. You should port your application to the standard JDBC 3.0 interface.
Prior to Oracle Database 10g, the maximum size of a LOB was 232 bytes. This restriction has been removed since Oracle Database 10g, and the maximum size is limited to the size of available physical storage. The Java LOB application programming interface (API) has not changed.
LOBs are stored in a way that optimizes space and provides efficient access. The JDBC drivers provide support for three types of LOB: binary large object (BLOB), which is used for unstructured binary data, character large object (CLOB), which is used for character data, and national character large object (NCLOB), which is used for national character data. BLOB, CLOB, and NCLOB data is accessed and referenced by using a locator that is stored in the database table and points to the BLOB, CLOB, and NCLOB data, which is outside the table.
BFILEs are large binary data objects stored in operating system files outside of database tablespaces. These files use reference semantics. They can also be located on tertiary storage devices, such as hard disks, CD-ROMs, PhotoCDs, and DVDs. As with BLOB, CLOB, and NCLOBs, a BFILE is accessed and referenced by a locator which is stored in the database table and points to the BFILE data.
To work with LOB data, you must first obtain a LOBlocator. Then you can read or write LOB data and perform data manipulation.
The JDBC drivers support the followingoracle.sql.* classes for BLOBs, CLOBs, NCLOBs, and BFILEs:
oracle.sql.BLOB
oracle.sql.CLOB
oracle.sql.NCLOB
oracle.sql.BFILE
Theoracle.sql.BLOB,oracle.sql.CLOB, and oracle.sql.NCLOB classes implement thejava.sql.Blob,java.sql.Clob, andjava.sql.NClob interfaces, respectively. In contrast,BFILE is an Oracle extension, without a correspondingjava.sql interface.
Instances of these classes contain only the locators for these data types, not the data. After accessing the locators, you must perform some additional steps to access the data.
Note:
If you want to create a newLOB, then use the factory methods fromoracle.jdbc.OracleConnection interface.This section describes how to read and write data to and from BLOBs, CLOBs, and NCLOBs in Oracle Database, using LOB locators. This section covers the following topics:
Standard as well as Oracle-specific getter and setter methods are available for retrieving or passing LOB locators from or to the database. This section covers the following topics:
Given a standard JDBC result set or callable statement that includes BLOB, CLOB, or NCLOBlocators, you can access the locators by using standard getter methods. You can use the standardgetBlob,getClob, andgetNClob methods, which returnjava.sql.Blob,Clob, andNClob objects, respectively.
Note:
All the standard and Oracle-specific getter methods discussed here take either anint column index or aString column name as input.If you retrieve or cast the result set or the callable statement toOracleResultSet orOracleCallableStatement, then you can use Oracle extensions, as follows:
You can usegetBLOB,getCLOB, andgetNCLOB, which returnoracle.sql.BLOB,CLOB, andNCLOB objects, respectively.
You can also use thegetOracleObject method, which returns anoracle.sql.Datum object, and cast the output appropriately.
Example: Getting BLOB, CLOB, and NCLOB Locators from a Result Set
Assumethe database has a table calledlob_table with a column for a BLOB locator,blob_col, a column for a CLOB locator,clob_col, and a column for a NCLOB locator,nclob_col. This example assumes that you have already created theStatement object,stmt.
First, select the LOB locators into a standard result set, then get the LOB data into appropriate Java classes:
// Select LOB locator into standard result set.ResultSet rs = stmt.executeQuery ("SELECT blob_col, clob_col, nclob_col FROM lob_table");while (rs.next()){ // Get LOB locators into Java container classes. java.sql.Blob blob = (java.sql.Blob)rs.getObject(1); java.sql.Clob clob = (java.sql.Clob)rs.getObject(2); java.sql.NClob nclob = (java.sql.NClob)rs.getObject(3); (...process...)}The output is cast tojava.sql.Blob,java.sql.Clob, andjava.sql.NClob. As an alternative, you can cast the output tooracle.sql.BLOB,oracle.sql.CLOB, andoracle.sql.NCLOB to take advantage of extended functionality offered by theoracle.sql.* classes. For example, you can rewrite the preceding code to get the LOB locators as:
// Get LOB locators into Java container classes. oracle.sql.BLOB blob = (BLOB)rs.getObject(1); oracle.sql.CLOB clob = (CLOB)rs.getObject(2); oracle.sql.NCLOB nclob = (NCLOB)rs.getObject(3); (...process...)Example: Getting a CLOB Locator from a Callable Statement
Thecallable statement methods for retrieving LOBs are identical to the result set methods.
For example, if you have anOracleCallableStatement instance,ocs, that calls a functionfunc that has a CLOB output parameter, then set up the callable statement as in the following example.
This example registersOracleTypes.CLOB as the type code of the output parameter.
OracleCallableStatement ocs = (OracleCallableStatement)conn.prepareCall("{? = call func()}");ocs.registerOutParameter(1, OracleTypes.CLOB);ocs.execute();oracle.sql.CLOB clob = ocs.getCLOB(1);Given a standard JDBC prepared statement or callable statement, you can use standard setter methods to pass LOB locators. These methods are defined as follows:
public void setBlob(int index, Blob value);public void setClob(int index, Clob value);public void setNClob(int index, NClob value);
Note:
If you pass aBLOB to a PL/SQL procedure, then the BLOB must be no bigger than 32K - 7. If you pass a BLOB that exceeds this limit, then you will receive aSQLException exception.Given an Oracle-specificOraclePreparedStatement orOracleCallableStatement, then you can use Oracle extensions as follows:
UsesetBLOB,setCLOB, andsetNClob, which takeoracle.sql.BLOB,CLOB andNCLOB locators as input, respectively.
Use thesetOracleObject method, which simply specifies anoracle.sql.Datum input.
Example: Passing a BLOB Locator to a Prepared Statement
If youhave anOraclePreparedStatement objectops and a BLOB namedmy_blob, then write the BLOB to the database as follows:
OraclePreparedStatement ops = (OraclePreparedStatement)conn.prepareStatement ("INSERT INTO blob_table VALUES(?)"); ops.setBLOB(1, my_blob);ops.execute();Example: Passing a CLOB Locator to a Callable Statement
If youhave anOracleCallableStatement objectocs and a CLOB namedmy_clob, then input the CLOB to the stored procedureproc as follows:
OracleCallableStatement ocs = (OracleCallableStatement)conn.prepareCall("{call proc(?))}");ocs.setClob(1, my_clob);ocs.execute();Example: Passing an NCLOB Locator to a Callable Statement
If youhave anOracleCallableStatement objectocs and an NCLOB namedmy_nclob, then input the NCLOB to the stored procedureproc as follows:
OracleCallableStatement ocs = (OracleCallableStatement)conn.prepareCall("{call proc(?))}");ocs.setNClob(1, my_nclob);ocs.execute();Once you have aLOB locator, you can use JDBC methods toread and write theLOB data.LOB data is materialized as a Java array or stream. UnlikeLONG andLONG RAW data, you can access theLOB data at any time during the life of the connection.
To read and write theLOB data, use the methods in thejava.sql.BLOB,java.sql.CLOB, andjava.sql.NCLOB class, as appropriate. These classes provide functionality such as reading from theLOB into an input stream, writing from an output stream into aLOB, determining the length of aLOB, and closing aLOB.
Notes:
To writeLOB data, the application must acquire a write lock on theLOB object. One way to accomplish this is through aSELECT FOR UPDATE. Also, you must disable auto-commit mode.To read and writeLOB data, you can use these methods:
To read from aBLOB, use thegetBinaryStream method of anjava.sql.BLOB object to retrieve the entireBLOB as an input stream. This returns ajava.io.InputStream object.
As with anyInputStream object, use one of the overloadedread methods to read theLOB data and use theclose method when you finish.
To write to aBLOB, use thesetBinaryStream method of anjava.sql.BLOB object to retrieve theBLOB as an output stream. This returns ajava.io.OutputStream object to be written back to theBLOB.
As with anyOutputStream object, use one of the overloadedwrite methods to update the LOB data and use theclose method when you finish.
To read from a CLOB, use thegetAsciiStream orgetCharacterStream method of anjava.sql.CLOB object to retrieve the entire CLOB as an input stream. ThegetAsciiStream method returns an ASCII input stream in ajava.io.InputStream object. ThegetCharacterStream method returns aUnicode input stream in ajava.io.Reader object.
As with anyInputStream orReader object, use one of the overloadedread methods to read the LOB data and use theclose method when you finish.
You can also use thegetSubString method ofjava.sql.CLOB object to retrieve a subset of the CLOB as a character string of typejava.lang.String.
To write to a CLOB, use thesetAsciiStream orsetCharacterStream method of anjava.sql.CLOB object to retrieve the CLOB as an output stream to be written back to the CLOB. ThesetAsciiStream method returns an ASCII output stream in ajava.io.OutputStream object. ThesetCharacterStream method returns a Unicode output stream in ajava.io.Writer object.
As with anyStream orWriter object, use one of the overloadedwrite methods to update theLOB data and use theflush andclose methods when you finish.
To read from an NCLOB, use thegetAsciiStream orgetCharacterStream method of anjava.sql.NCLOB object to retrieve the entire NCLOB as an input stream. ThegetAsciiStream method returns an ASCII input stream in ajava.io.InputStream object. ThegetCharacterStream method returns aUnicode input stream in ajava.io.Reader object.
As with anyInputStream orReader object, use one of the overloadedread methods to read the LOB data and use theclose method when you finish.
You can also use thegetSubString method ofjava.sql.NCLOB object to retrieve a subset of the NCLOB as a character string of typejava.lang.String.
To write to an NCLOB, use thesetAsciiStream orsetCharacterStream method of anoracle.sql.NCLOB object to retrieve the NCLOB as an output stream to be written back to the NCLOB. ThesetAsciiStream method returns an ASCII output stream in ajava.io.OutputStream object. ThesetCharacterStream method returns a Unicode output stream in ajava.io.Writer object.
As with anyStream orWriter object, use one of the overloadedwrite methods to update theLOB data and use theflush andclose methods when you finish.
Notes:
The stream write methods described in this section write directly to the database when you write to the output stream. You donot need to run anUPDATE to write the data. However, you need to callclose orflush to ensure all changes are written. CLOBs and BLOBs are transaction controlled. After writing to either, you must commit the transaction for the changes to be permanent. BFILEs are not transaction controlled. Once you write to them the changes are permanent, even if the transaction is rolled back, unless the external file system does something else.
When writing to or reading from a CLOB or an NCLOB, the JDBC drivers perform all character set conversions for you.
When reading aLOB using any of thegetXXX methods described in the section, the returned stream fetches blocks of data from the database needed. The entire LOB is not fetched all at once, which makes it practical to read very large LOBs.
Use thegetBinaryStreammethod of theoracle.sql.BLOB class to read BLOB data. ThegetBinaryStream method provides access to the BLOB data through a binary stream.
The following example uses thegetBinaryStream method to read BLOB data through a byte stream and then reads the byte stream into a byte array, returning the number of bytes read, as well.
// Read BLOB data from BLOB locator.InputStream byte_stream = my_blob.getBinaryStream(1L);byte [] byte_array = new byte [10];int bytes_read = byte_stream.read(byte_array);...
The following example uses thegetCharacterStream method to readCLOB data into a Unicode character stream. It then reads the character stream into a character array, returning the number of characters read, as well.
// Read CLOB data from CLOB locator into Reader char stream.Reader char_stream = my_clob.getCharacterStream(1L);char [] char_array = new char [10];int chars_read = char_stream.read (char_array, 0, 10);...
The following example uses thegetCharacterStream method to readNCLOB data into a Unicode character stream. It then reads the character stream into a character array, returning the number of characters read, as well.
// Read NCLOB data from NCLOB locator into Reader char stream.Reader char_stream = my_nclob.getCharacterStream(1L);char [] char_array = new char [10];int chars_read = char_stream.read (char_array, 0, 10);...
The next example uses thegetAsciiStream method of theoracle.sql.NCLOB class to read NCLOB data through an ASCII character stream. It then reads the ASCII stream into a byte array, returning the number of bytes read, as well.
// Read NCLOB data from NCLOB locator into Input ASCII character streamInputstream asciiChar_stream = my_nclob.getAsciiStream(1L); byte[] asciiChar_array = new byte[10]; int asciiChar_read = asciiChar_stream.read(asciiChar_array,0,10);
Use thesetBinaryOutputStream methodof anoracle.sql.BLOB object to write BLOB data.
The following example reads a vector of data into a byte array, then uses thesetBinaryOutputStream method to write an array of character data to a BLOB.
java.io.OutputStream outstream;// read data into a byte array byte[] data = {0, 1, 2, 3, 4, 5, 6, 7, 8, 9};// write the array of binary data to a Bloboutstream = ((BLOB)my_blob).setBinaryOutputStream(1L);outstream.write(data);...Use thesetCharacterStream methodor thesetAsciiStream method to write data to a CLOB. ThesetCharacterStream method returns aUnicode output stream. ThesetAsciiStream method returns an ASCII output stream.
The following example reads a vector of data into a character array, then uses thesetCharacterStream method to write the array of character data to a CLOB.
java.io.Writer writer;// read data into a character arraychar[] data = {'0','1','2','3','4','5','6','7','8','9'};// write the array of character data to a Clobwriter = ((CLOB)my_clob).setCharacterStream();writer.write(data);writer.flush();writer.close();...The next example reads a vector of data into a byte array, then uses thesetAsciiStream method to write the array of ASCII data to a CLOB.
java.io.OutputStream out;// read data into a byte arraybyte[] data = {'0','1','2','3','4','5','6','7','8','9'};// write the array of ascii data to a CLOB out = clob.setAsciiStream();out.write(data);out.flush();out.close();Use thesetCharacterStream methodor thesetAsciiStream method to write data to an NCLOB. ThesetCharacterStream method returns aUnicode output stream. ThesetAsciiStream method returns an ASCII output stream.
The following example reads a vector of data into a character array, then uses thesetCharacterStream method to write the array of character data to an NCLOB.
java.io.Writer writer;// read data into a character arraychar[] data = {'0','1','2','3','4','5','6','7','8','9'};// write the array of character data to an NClobwriter = ((NCLOB)my_nclob).setCharacterStream();writer.write(data);writer.flush();writer.close();...The next example reads a vector of data into a byte array, then uses thesetAsciiStream method to write the array of ASCII data to an NCLOB.
java.io.OutputStream out;// read data into a byte arraybyte[] data = {'0','1','2','3','4','5','6','7','8','9'};// write the array of ascii data to a NClob out = nclob.setAsciiStream();out.write(data);out.flush();out.close();Create and populate aBLOB,CLOB, orNCLOB column in a table by using SQL statements.
Note:
You cannot construct a new BLOB, CLOB, or NCLOB locator in your application with a Javanew statement. You must create the locator through a SQL operation, and then select it into your application or use the factory methods fromoracle.jdbc.OracleConnection interface.Create a BLOB, CLOB, or NCLOB column in a table with the SQLCREATE TABLE statement, then populate the LOB. This includes creating the LOB entry in the table, obtaining the LOB locator, and then copying the data into the LOB.
Creating a BLOB, CLOB, or NCLOB Column in a New Table
To create a BLOB, CLOB, or NCLOB column in a new table, run the SQLCREATE TABLE statement. The following example code creates aBLOB column in a new table. This example assumes that you have already created yourConnection objectconn andStatement objectstmt:
String cmd = "CREATE TABLE my_blob_table (x VARCHAR2 (30), c BLOB)";stmt.execute (cmd);
In this example, theVARCHAR2 column designates a row number, such as 1 or 2, and theBLOB column stores the locator of the BLOB data.
Populating a BLOB, CLOB, or NCLOB Column in a New Table
This example demonstrates how to populate a BLOB, CLOB, or NCLOB column by reading data from a stream. These steps assume that you have already created yourConnection objectconn andStatement objectstmt. The tablemy_blob_table is the table that was created in the previous section.
The following example writes thejohn.gif file to a BLOB:
Begin by using SQL statements to create the BLOB entry in the table. Use theempty_blob function to create the BLOB locator.
stmt.execute ("INSERT INTO my_blob_table VALUES ('row1', empty_blob())");Get the BLOB locator from the table.
BLOB blob;cmd = "SELECT * FROM my_blob_table WHERE X='row1' FOR UPDATE";ResultSet rset = stmt.executeQuery(cmd);rset.next();BLOB blob = ((OracleResultSet)rset).getBLOB(2);
Note:
You must disable auto-commit mode.Declare a file handler for thejohn.gif file, then print the length of the file. This value will be used later to ensure that the entire file is read into the BLOB. Next, create aFileInputStream object to read the contents of the file, and anOutputStream object to retrieve the BLOB as a stream.
File binaryFile = new File("john.gif");System.out.println("john.gif length = " + binaryFile.length());FileInputStream instream = new FileInputStream(binaryFile);OutputStream outstream = blob.setBinaryStream(1L);CallgetBufferSize to retrieve the ideal buffer size to use in writing to the BLOB, then create thebuffer byte array.
int size = blob.getBufferSize();byte[] buffer = new byte[size];int length = -1;
Use theread method to read the file to the byte arraybuffer, then use thewrite method to write it to the BLOB. When you finish, close the input and output streams and commit the changes.
while ((length = instream.read(buffer)) != -1) outstream.write(buffer, 0, length);instream.close();outstream.close();conn.commit();
Once your data is in the BLOB, CLOB, or NCLOB, you can manipulate the data.
Once you have yourBLOB, CLOB, or NCLOB locator in a table, you can access and manipulate the data to which it points. To access and manipulate the data, you first must select their locators from a result set or from a callable statement.
After you select the locators, you can retrieve the BLOB, CLOB, or NCLOB data. After retrieving the BLOB, CLOB, or NCLOB data, you can manipulate it however you want.
This example is a continuation of the example in the previous section. It uses the SQLSELECT statement to select the BLOB locator from the tablemy_blob_table into a result set. The result of the data manipulation is to print the length of the BLOB in bytes.
// Select the blob - what we are really doing here// is getting the blob locator into a result set BLOB blob;cmd = "SELECT * FROM my_blob_table";ResultSet rset = stmt.executeQuery (cmd);// Get the blob data - cast to OracleResult set to // retrieve the data in oracle.sql format String index = ((OracleResultSet)rset).getString(1);blob = ((OracleResultSet)rset).getBLOB(2);// get the length of the blobint length = blob.length();// print the length of the blobSystem.out.println("blob length" + length);// read the blob into a byte array // then print the blob from the arraybyte bytes[] = blob.getBytes(1, length);blob.printBytes(bytes, length);The data interface for LOBs provides a streamlined mechanism for writing and reading the entire LOB contents. It is simpler to code and faster in many cases. It does not provide the random access capability or access beyond 2147483648 elements as do the standardjava.sql.Blob andjava.sql.Clob interfaces and the Oracle extensions,oracle.sql.BLOB,oracle.sql.BFILE, andoracle.sql.CLOB.
In Oracle Database 11g release 1 (11.1), thesetBytes,setBinaryStream,setString,setCharacterStream, andsetAsciiStream methods ofPreparedStatement are extended forBLOB andCLOB parameters.
For the JDBC Oracle Call Interface (OCI) and Thin drivers there is no limitation on the size of thebyte array orString and no limit on the length specified for the stream functions except the limits imposed by the Java language, which is that array sizes are limited to positive Javaint or 2147483648 elements.
For the server-side internal driver there is currently a limitation of 4000 bytes for operations on SQL statements, such as anINSERT statement. The limitation does not apply for PL/SQL statements. There is a simple workaround for anINSERT statement, which is to wrap it in a PL/SQL block, as follows:
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:
For SQL statements:
setBytes switches tosetBinaryStream for data larger than 2000 bytes
setString switches tosetCharacterStream for data larger than 32766 characters
PL/SQL statements
setBytes switches tosetBinaryStream for data larger than 2000 bytes and tosetBytesForBlob for data larger that 32766 bytes
setString switches tosetStringForClob for string data larger than 32766 bytes in the database character set
setNClob orsetObject is used for large national character set type data. If thesetObject method is used , the target data type must be specified asTypes.NCHAR,Types.NCLOB,Types.NVARCHAR, orTypes.LONGNVARCHAR.
This will have impact on some programs, which formerly gotORA-17157 errors for attempts to usesetString forString values larger than 32766 characters. Now, depending on the type of the target parameter an error may occur while the application 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.
Oracle Database 10g release 1 (10.1) has theSetBigStringTryClob connection property. Setting this property causes the standardsetString method to switch tosetStringForClob method for large data. This property is no longer used or needed. ThesetBytesForBlob andsetStringForClob methods create temporary LOBs, which are automatically freed when the statement is executed or closed before execution.
However, when a PL/SQL procedure or function is embedded in a SQL statement, data less than 4 KB is bound asString, which is the standard. When data is greater than 4KB, the driver binds the data as aString as for any SQL statement. This will throw an error. The workaround is to usesetClob orsetCharacterStream instead ofsetString orsetStringForClob. You can also create a callable statement.
ThegetBytes,getBinaryStream,getSting,getCharacterStream, andgetAsciiStream methods ofResultSet andCallableStatement are extended to work withBLOB,CLOB, andBFILE columns orOUT parameters. These methods will work for any LOB of length less than 2147483648. This operates entirely on the client-side and will work with any supported version of the database, that is, Oracle Database 8.1.7 and later.
BLOB,BFILE, orCLOB data can be read and written using the same streaming mechanism as forLONG RAW andLONG data. To read, usedefineColumnType(nn, Types.LONGVARBINARY) ordefineColumnType(nn,Types.LONGVARCHAR) on the column. This produces a direct stream on the data as if it were aLONG RAW orLONG column. This technique is limited to Oracle Database 10g release 1 (10.1) and later.
CallableSatement and IN OUT Parameter
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 anIN OUT CLOB parameter of a stored procedure and you wish to usesetString for setting the value for this parameter. For anyIN andOUT 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 usesetString for the input parameter and register theOUT parameter asTypes.VARCHAR and usegetString for the output parameter.
A better solution is to change the stored procedure to have separateIN andOUT 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. Theclob_proc procedure can be wrapped with a Java string to use for theprepareCall statement, as follows:
"DECLARE c_temp; BEGIN c_temp := ?; clob_proc( c_temp); ? := c_temp; END;"
In either case you may usesetString on the first parameter andregisterOutParameter withTypes.CLOB on the second.
Please be aware of the effect on the performance of the Java memory management system due to creation of very largebyte array orString. Please 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.
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. Inserting a temporary LOB may be preferable for some situations. For example, if the LOB data is relatively small so that 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 create a temporary LOB with thestatic methodcreateTemporary(Connection, boolean, int). This method is defined in both theoracle.sql.BLOB andoracle.sql.CLOB classes. You free a temporary LOB with thefreeTemporary method.
public static BLOB createTemporary(Connection conn, boolean isCached, int duration);public static CLOB createTemporary(Connection conn, boolean isCached, int duration);
The duration must be eitherDURATION_SESSION orDURATION_CALL as defined in theoracle.sql.BLOB ororacle.sql.CLOB class. In client applications,DURATION_SESSION is appropriate. In Java stored procedures, you can use eitherDURATION_SESSION orDURATION_CALL, which ever is appropriate.
You can test whether a LOB is temporary by calling theisTemporary method. If the LOB was created by calling thecreateTemporary method, then theisTemporary method returnstrue, else it returnsfalse.
You can free a temporary LOB by calling thefreeTemporary method. Free any temporary LOBs before ending the session or call. Otherwise, the storage used by the temporary LOB will not be reclaimed.
Notes:
Failure to free a temporary LOB will result in the storage used by that LOB in the database being unavailable. Frequent failure to free temporary LOBs will result in filling up temporary table space with unavailable LOB storage.
When fetching data from aReultSet with columns that are temporary LOBs, usegetClob orgetBlob instead ofgetString orgetBytes. Also invokefreeTemporary to free the temporary LOBs.
Creating Temporary NCLOBs in JDK 1.5
You create temporary national character large objects (NCLOBs) using a variant of thecreateTemporary method.
CLOB.createTemporary (Connection conn, boolean cache, int duration, short form);
Theform argument specifies whether the created LOB is a CLOB or an NCLOB. Ifform equalsoracle.jdbc.OraclePreparedStatement.FORM_NCHAR, then the method creates an NCLOB. Ifform equalsoracle.jdbc.OraclePreparedStatement.FORM_CHAR, then the method creates a CLOB.
Creating Temporary NCLOBs in JDK 1.6
JDBC 4.0 supports NCLOBs directly. You can use the standard factory method ofjava.sql.Connection interface to create an NCLOB.
You do not have to open and close your LOBs. You may choose to open and close them for performance reasons.
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.
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 theclose method. However, this means that any domain indexes on the LOB will not be valid in-between the Open/Close calls.
You open a LOB by calling theopen oropen(int) method. You may then read and write the LOB without any triggers associated with that LOB firing. When you are done accessing the LOB, close the LOB by calling theclose method. When you close the LOB, any triggers associated with the LOB will fire. You can see if a LOB is open or closed by calling theisOpen method. If you open the LOB by calling theopen(int) method, the value of the argument must be eitherMODE_READONLY orMODE_READWRITE, as defined in theoracle.sql.BLOB andoracle.sql.CLOB classes. If you open the LOB withMODE_READONLY, any attempt to write to the LOB will result in a SQL exception.
Note:
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.This section describes how to read data to BFILEs, using file locators. This section covers the following topics:
Getter and setter methods are available for retrieving or passing BFILE locators from or to the database.
Given a standard JDBC result set or callable statement object that includesBFILE locators, you can access the locators by using the standard result setgetObject method. This method returns anoracle.sql.BFILE object.
You can also access the locators by casting your result set toOracleResultSet or your callable statement toOracleCallableStatement and using thegetOracleObject orgetBFILE method.
Notes:
In theOracleResultSet andOracleCallableStatement classes,getBFILE andgetBfile both returnoracle.sql.BFILE. There is nojava.sql interface for BFILEs.
If usinggetObject orgetOracleObject, remember to cast the output, as necessary.
Example: Getting a BFILE locator from a Result Set
Assume that the database has a table calledbfile_table with a single column for the BFILE locatorbfile_col. This example assumes that you have already created yourStatement objectstmt.
Select the BFILE locator into a standard result set. If you cast the result set toOracleResultSet, then you can usegetBFILE to get the BFILE locator, as follows:
// Select the BFILE locator into a result setResultSet rs = stmt.executeQuery("SELECT bfile_col FROM bfile_table"); while (rs.next()) { oracle.sql.BFILE my_bfile = ((OracleResultSet)rs).getBFILE(1); }Note that as an alternative, you can usegetObject to return the BFILE locator. In this case, becausegetObject returns ajava.lang.Object, cast the results toBFILE. For example:
oracle.sql.BFILE my_bfile = (BFILE)rs.getObject(1);
Example: Getting a BFILE Locator from a Callable Statement
Assumeyou have anOracleCallableStatement objectocs that calls a functionfunc that has aBFILE output parameter. The following code example sets up the callable statement, registers the output parameter asOracleTypes.BFILE, runs the statement, and retrieves the BFILE locator:
OracleCallableStatement ocs = (OracleCallableStatement)conn.prepareCall("{? = call func()}");ocs.registerOutParameter(1, OracleTypes.BFILE);ocs.execute();oracle.sql.BFILE bfile = ocs.getBFILE(1);To pass a BFILE locator to aprepared statement or callable statement, you can do one of the following:
Cast the statement toOraclePreparedStatement orOracleCallableStatement, and use thesetOracleObject orsetBFILE method.
These methods take the parameter index and anoracle.sql.BFILE object as input.
Example: Passing a BFILE Locator to a Prepared Statement
Assume you want to insert a BFILE locator into a table, and you have anOraclePreparedStatement objectops to insert data into a table. The first column is a string, the second column is a BFILE, and you have a validoracle.sql.BFILE object,bfile. Write the BFILE to the database, as follows:
OraclePreparedStatement ops = (OraclePreparedStatement)conn.prepareStatement ("INSERT INTO my_bfile_table VALUES (?,?)");ops.setString(1,"one");ops.setBFILE(2, bfile);ops.execute();Example: Passing a BFILE Locator to a Callable Statement
Passing a BFILE locator to a callable statement is similar to passing it to a prepared statement. In this case, the BFILE locator is passed to themyGetFileLength procedure, which returns the BFILE length as a numeric value.
OracleCallableStatement cstmt = (OracleCallableStatement)conn.prepareCall ("begin ? := myGetFileLength (?); end;");try{ cstmt.registerOutParameter (1, Types.NUMERIC); cstmt.setBFILE (2, bfile); cstmt.execute (); return cstmt.getLong (1);}To readBFILE data, you must first get the BFILE locator. You can get the locator from either a callable statement or a result set. Once you obtain the locator, you can call a number of methods on the BFILE without opening it. For example, you can use theoracle.sql.BFILE methodsfileExists() andisFileOpen() to determine whether the BFILE exists and if it is open. However, if you want to read and manipulate the data, then you must open and close the BFILE, as follows:
Use theopenFile method of theoracle.sql.BFILE class to open a BFILE.
When you are done, use thecloseFile method of theBFILE class.
BFILE data is through a Java stream. To read from a BFILE, use thegetBinaryStream method of anoracle.sql.BFILE object to access the file as an input stream. This returns ajava.io.InputStream object.
As with anyInputStream object, use one of the overloadedread methods to read the file data and use theclose method when you finish.
Notes:
BFILEs are read-only. They reside in the OS (operating system) File System and can be written to only using OS tools and commands.
You can create a BFILE. However, you cannot create an OS file that a BFILE would refer to. Those are created only externally.
The following example uses thegetBinaryStream method of anoracle.sql.BFILE object to read BFILE data into a byte stream and then read the byte stream into a byte array. The example assumes that the BFILE has already been opened.
// Read BFILE data from a BFILE locatorInputstream in = bfile.getBinaryStream();byte[] byte_array = new byte{10};int byte_read = in.read(byte_array);This section discusses how to create aBFILE column in a table with SQL operations and specify the location where the BFILE resides. The examples in this section assume that you have already created yourConnection objectconn andStatement objectstmt.
Creating a BFILE Column in a New Table
To work with BFILE data, create aBFILE column in a table, and specify the location of the BFILE. To specify the location of the BFILE, use the SQLCREATE DIRECTORY...AS statement to specify an alias for the directory where the BFILE resides. In this example, the directory alias istest_dir and the BFILE resides in the/home/work directory.
String cmd;cmd = "CREATE DIRECTORY test_dir AS '/home/work'";stmt.execute (cmd);
Use the SQLCREATETABLE statement to create a table containing aBFILE column. In this example, the name of the table ismy_bfile_table.
// Create a table containing a BFILE field cmd = "CREATE TABLE my_bfile_table (x varchar2 (30), b bfile)";stmt.execute (cmd);
In this example, theVARCHAR2 column designates a row number and theBFILE column stores the locator of the BFILE data.
Use the SQLINSERT INTO...VALUES statement to populate theVARCHAR2 andBFILE fields. TheBFILE column is populated with the locator to the BFILE data. To populate theBFILE column, use thebfilename function to specify the directory alias and the name of the BFILE file.
cmd ="INSERT INTO my_bfile_table VALUES ('one', bfilename(test_dir, 'file1.data'))";stmt.execute (cmd);cmd ="INSERT INTO my_bfile_table VALUES ('two', bfilename(test_dir, 'jdbcTest.data'))";stmt.execute (cmd);In this example, the name of the directory alias istest_dir. The locator of the BFILEfile1.data is loaded into theBFILE column on rowone, and the locator of theBFILEjdbcTest.data is loaded into thebfile column on rowtwo.
As an alternative, you may want to create the row for the row number and BFILE locator now, but wait until later to insert the locator. In this case, insert the row number into the table andnull as a place holder for the BFILE locator.
cmd ="INSERT INTO my_bfile_table VALUES ('three', null)";stmt.execute(cmd);Here,three is inserted into the row number column andnull is inserted as the place holder. Later in your program, insert the BFILE locator into the table by using a prepared statement.
First get a valid BFILE locator into thebfile object:
rs = stmt.executeQuery("SELECT b FROM my_bfile_table WHERE x='two'");rs.next();oracle.sql.BFILE bfile = ((OracleResultSet)rs).getBFILE(1);Then, create your prepared statement. Note that because this example uses thesetBFILE method to identify the BFILE, the prepared statement must be cast toOraclePreparedStatement:
OraclePreparedStatement ops = (OraclePreparedStatement)conn.prepareStatement (UPDATE my_bfile_table SET b=? WHERE x = 'three');ops.setBFILE(1, bfile);ops.execute();
Now rowtwo and rowthree contain the same BFILE.
Once you have the BFILE locators available in a table, you can access and manipulate the BFILE data.
Once you have theBFILE locator in a table, you can access and manipulate the data to which it points. To access and manipulate the data, you must first select its locator from a result set or a callable statement.
The following code continues the example from the preceding section, getting the locator of the BFILE from rowtwo of a table into a result set. The result set is cast toOracleResultSet so thatoracle.sql.* methods can be used on it. Several of the methods applied to the BFILE, such asgetDirAlias andgetName, do not require you to open the BFILE. Methods that manipulate the BFILE data, such as reading, getting the length, and displaying,do require you to open the BFILE.
When you finish manipulating the BFILE data, you must close the BFILE.
// select the bfile locatorcmd = "SELECT * FROM my_bfile_table WHERE x = 'two'";rset = stmt.executeQuery (cmd);if (rset.next ()) BFILE bfile = ((OracleResultSet)rset).getBFILE (2);// for these methods, you do not have to open the bfileprintln("getDirAlias() = " + bfile.getDirAlias());println("getName() = " + bfile.getName());println("fileExists() = " + bfile.fileExists());println("isFileOpen() = " + bfile.isFileOpen());// now open the bfile to get the databfile.openFile();// get the BFILE data as a binary streamInputStream in = bfile.getBinaryStream();int length ;// read the bfile data in 6-byte chunksbyte[] buf = new byte[6];while ((length = in.read(buf)) != -1){ // append and display the bfile data in 6-byte chunks StringBuffer sb = new StringBuffer(length); for (int i=0; i<length; i++) sb.append( (char)buf[i] ); System.out.println(sb.toString());}// we are done working with the input stream. Close it. in.close();// we are done working with the BFILE. Close it. bfile.closeFile();In Oracle Database 11g Release 1 (11.1), Oracle SecureFiles, a completely redesigned storage for LOBs, provide the following capabilities:
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, read prefetching, new caching modes, and vectored IO.
High performance space management.
These features are implemented in the database and are transparenly available to JDBC programs through the existing APIs.
The newsetLobOptions andgetLobOptions APIs are described in the PL/SQL Packages and Types Reference, and may be accessed from JDBC through callable statements.