기존 WL6.1에서는
oracle.sql.CLOB
사용하였으나
WL81에서는
WebLogic Server Support Pattern |
More Support Patterns |
CLOB/BLOB Data Type Handling Issues |
Problem Description
WebLogic Server versions that support the JDBC 2.0 specification support the use of BLOB (Binary Large Object) and CLOB (Character Large Object) data types. These data types were made available with the release of Oracle version 8 and above. Sybase and MS SQL Server, databases do not natively support these data types, however certain Sybase and SQL Server drivers (for example Datadirect) provide both read and write support for BLOB/CLOB data types for Sybase and MS SQL Server. Users face many problems when they use incorrect programming techniques with the above data types. Also, changes in the database driver APIs when migrating from one WebLogic Server version to another leads to incompatible code. A number of cases related to these issues have been raised.
Problem Troubleshooting
This pattern provides some common methodologies that can be followed to troubleshoot CLOB/BLOB problems using Oracle database.
Quick Links:
Why Does the Problem Occur?
Recommended Programming Techniques
Many times due to incorrect programming techniques customers face problems while inserting or retrieving CLOB/BLOB objects from the database. The CLOB/BLOB data insertion and retrieval happens differently with Oracle and WebLogic database drivers.
WLS 8.1:
Standard Programming Technique to insert, update and retrieve rows with Clob/Blob using WebLogic and Oracle drivers are mentioned below.
How to Insert/Update BLOB Data types
Oracle Driver
To access the BEA supported methods, the BLOB object java.sql.Blob needs to be typecast to weblogic.jdbc.vendor.oracle.OracleThinBlob interface. Sample programming is shown below.
java.sql Objects Initialization
PreparedStatement pstmtInsert = null; PreparedStatement pstmtSelect = null; java.sql.Blob myRegularBlob = null; java.io.OutputStream os = null; Connection myConnect = null; |
BLOB Field Initialization
When you first insert a row containing a BLOB data type, you must insert the row with an "empty" BLOB before the field can be updated with real data. You can insert an empty BLOB with the Oracle EMPTY_BLOB()
function.
myConnect = getConnection(); String insertSQL = "insert into myBlobTable values (?, EMPTY_BLOB())"; pstmtInsert = myConnect.prepareStatement(insertSQL); pstmtInsert.setString(1, String.valueOf(i)); pstmtInsert.executeUpdate(); |
Write to BLOB Field
In the following try catch block, you get the BLOB locator and access the Oracle BLOB extension for writing.
try { // get our BLOB locator.. String selectSQL="select pk, myBlobColumn from myBlobTable where pk=? for update"; pstmtSelect = myConnect.prepareStatement(selectSQL); pstmtSelect.setString(1, String.valueOf(i)); rs = pstmtSelect.executeQuery(); while (rs.next()) { System.out.println("PK for update is " + rs.getString("PK")); myRegularBlob = rs.getBlob("myBlobColumn"); } // Access the underlying Oracle extension functionality for // writing. Cast to the OracleThinBlob interface to access // the Oracle method. os = ((OracleThinBlob)myRegularBlob).getBinaryOutputStream(); os.write(bytes); os.flush(); … } catch (SQLException sqe) { System.out.println("ERROR(general SQE): " + sqe.getMessage()); } |
WebLogic Driver
Since the WebLogic driver uses WebLogic wrapper classes, no typecasting is required when using this driver. If typecasting is used, ClassCastException will be thrown
The actual exception that occurs is on the line
“os = ((OracleThinBlob)myRegularBlob).getBinaryOutputStream();" |
is
ava.lang.ClassCastException: weblogic.jdbc.rmi.SerialOracleBlob_weblogic_jdbc_rmi_internal_OracleTBlobStub_weblogic_jdbc_rmi_ internal_ OracleTBlobImpl_weblogic_jdbc_wrapper_Blob_weblogic_jdbc_base_BaseBlob_814_WLStub |
To resolve this issue, the above code needs to be modified.
Replace
os = ((OracleThinBlob)myRegularBlob).getBinaryOutputStream(); |
with
os = myRegularBlob.setBinaryStream(1); |
How to Read BLOB Data Types
The following code snippet depicts how to correctly read a BLOB data type for both Oracle and WebLogic drivers.
try{ myConnect = getConnection(); String selectSQL = "select myBlobColumn from myTable1 where pk = ?"; pstmtSelect = myConnect.prepareStatement(selectSQL); pstmtSelect.setString(1, String.valueOf(i)); ResultSet rs = null; byte[] inBytes = new byte[256]; rs = pstmtSelect.executeQuery(); while (rs.next()) { myRegularBlob = rs.getBlob("myBlobColumn"); java.io.InputStream readis = myRegularBlob.getBinaryStream(); for (int k=0 ; k < 256 ; k++) { inBytes[k] = (byte) readis.read(); System.out.println("output [" + k + "] = " + inBytes[k]); } } catch (SQLException sqe) { System.out.println("ERROR(general SQE): " + sqe.getMessage()); } |
How to Insert/Update CLOB Data Types
Oracle Driver
Initializing a CLOB Field
The code is very similar to the BLOB example.
Replace
String insertSQL = "insert into myBlobTable values (?, EMPTY_BLOB())"; |
With
String insertSQL = "insert into myClobTable values (?, EMPTY_CLOB())"; |
In the try catch block, you get the CLOB locator and access the Oracle CLOB extension as follows using the same procedure as BLOB. getClob
method is used instead of getBlob
and getAsciiOutputStream
instead of getBinaryOutputStream
.
An example is shown below.
Replace
os = ((OracleThinBlob)myRegularBlob).getBinaryOutputStream(); |
With
os = ((OracleThinClob)myRegularClob).getAsciiOutputStream(); |
WebLogic Driver
The code is very similar to the BLOB example with some changes to avoid a ClassCastException, as shown below.
Replace
os = ((OracleThinClob)myRegularClob).getAsciiOutputStream(); |
With
os = myRegularClob.setAsciiStream(1); |
How to Read Clob Data Types
The procedure is the same as the BLOB example, except that you need to use getClob
instead of getBlob
and getAsciiStream
instead of getBinaryStream
.
WLS 7.0 and WLS 6.1:
The implementation of the BLOB and CLOB APIs in WLS 8.1 also works in WLS 7.0 and WLS 6.1.
When and How to Use the API (weblogic.jdbc.common.OracleBlob) or the API (weblogic.jdbc.vendor.oracle.OracleThinBlob):
The earlier versions of WLS always went through the RMI interface, even if local. This was changed due to customer complaints and because it significantly improved performance to avoid RMI whenever possible. Now you get different behaviors depending on whether or not your code is in an RMI client or on the server, and by service pack, and by what driver you are using.
To access the BEA supported methods you need to typecast java.sql.Blob
either to the weblogic.jdbc.vendor.oracle.OracleThinBlob
as in WLS 8.1 example or the weblogic.jdbc.common.OracleBlob
interface.
You need code something like this:
java.io.OutputStream getOS(java.sql.Blob lob) throws SQLException { if (lob instanceof oracle.sql.BLOB) { // Oracle thin/oci driver non-rmi return ((oracle.sql.BLOB)lob).getBinaryOutputStream(); } else if (lob instanceof weblogic.jdbc.vendor.oracle.OracleThinBlob) { // Oracle thin/oci driver via rmi return ((weblogic.jdbc.vendor.oracle.OracleThinBlob)lob).getBinaryOutputStream(); } else if (lob instanceof weblogic.jdbc.common.OracleBlob) { // WebLogic Type 2 Driver for Oracle return ((weblogic.jdbc.common.OracleBlob) lob).getBinaryOutputStream(); } else { // New Weblogic type 4 driver for Oracle or any non-Oracle driver return ((java.sql.Blob) lob).setBinaryStream(1); } } |
Your code can be smaller if you don't use the full variety of drivers.
Note that as in WLS 8.1 in case of WebLogic Type 4 drivers, you don't need to use the Oracle extension getBinaryOutputStream()
. You can use the standard setBinaryStream(1)
. In general, if you can avoid using an extension, do so.
Clarification:
WLS 6.1 SP5 was the service pack that returned an oracle.sql.BLOB
, which was inconsistent with earlier versions of WLS 6.1. This was changed in WLS 6.1 SP6.
With the exception of WLS 5.1 (which is End-of-life as of 2/1/2004) and WLS 6.1 SP5 (which was an error), you will always get weblogic.jdbc.vendor.oracle.OracleThinBlob
when going through WLS.
To get oracle.sql.BLOB
, you need to use connections directly.
The reasons are different for various releases/service packs.
<>WLS 6.1,WLS 6.1 SP1-SP4, WLS 7.0, WLS 7.0 SP1-SP2 - going through RMI
WLS 6.1 SP6+, WLS 7.0 SP3+ - consistency with earlier service packs
WLS 8.1 - dynamic wrappers only work with interfaces, not classes, and there is no interface for oracle.sql.BLOB
.
This discussion also applies to CLOB usage.
Migrating Applications Due To WebLogic Server Upgrade
When migrating applications from WebLogic Server 5.1 to WebLogic Server 6.1 or to WebLogic Server 7.0 and using Oracle 8.1.7 as the database server, the following problem appears while accessing CLOB/BLOB data types.
In WebLogic Server 5.1 the sample code shown below obtains a return object of type oracle.sql.BLOB
.
try { // get our BLOB locator… String selectSQL = "select pk, myBlobColumn from myTable1 where pk=? for update"; pstmtSelect = myConnect.prepareStatement(selectSQL); pstmtSelect.setString(1, String.valueOf(i)); rs = pstmtSelect.executeQuery(); while (rs.next()) { myRegularBlob = rs.getBlob("myBlobColumn"); } // WLS 5.1-type code where we type-casted the database returned Blob object // to oracle.sql.BLOB os = ((oracle.sql.BLOB)myRegularBlob).getBinaryOutputStream(); os.write(bytes); os.flush(); myConnect.commit(); |
When the same code is migrated to WLS 6.1 or to WLS 7.0 you receive a ClassCastException. The actual exception on the line
“os = ((oracle.sql.BLOB)myRegularBlob).getBinaryOutputStream();” |
is
java.lang.ClassCastException: weblogic.jdbc.rmi.SerialOracleBlob |
Using Oracle objects resulted in problems and so wrappers were added back in WLS 6.1. This URL is a link to the documentation that explains the correct way to access the BLOB object, http://e-docs.bea.com/wls/docs70/oracle/advanced.html#1158571.
In summary, change the cast from oracle.sql.BLOB
to
weblogic.jdbc.common.OracleBlob
and all should work.
Similar to BLOBs, with CLOBs you will get the same ClassCastException (java.lang.ClassCastException: weblogic.jdbc.rmi.SerialOracleClob
) if the following typecast is used:
os = ((oracle.sql.CLOB)myRegularClob).getAsciiOutputStream(); |
To avoid the ClassCastException, the above line of code should be replaced with
os = ((weblogic.jdbc.common.OracleClob)myRegularClob).getAsciiOutputStream(); |
Reading CLOBs is exactly same way as BLOBs.
Database Driver
Another cause of the CLOB/BLOB problem could be a JDBC driver problem. In order to isolate whether the problem is a driver problem or WebLogic connection pool problem, you may try the following if you have a reproducible test case.
- Get connection from the driver directly.
In your test case, get JDBC connections directly from the driver and bypass WebLogic datasource. Use the application code snippet to insert/update/retrieve/delete records from database containing CLOB/BLOB columns. Sample code to create a Byte array to use as CLOB/BLOB data to write to database is as follows:
//Creating Blob to write byte[] bytes = new byte[256]; for (int i = 0; i < bytes.length; i++) { bytes[i] = 1; } |
- Use reflection to find out the actual class name and hierarchy of the BLOB/CLOB object returned from the database. Use sample code as follows:
System.out.println("The Blob classname is " + myRegularBlob.getClass()); Class[] classes = myRegularBlob.getClass().getInterfaces(); System.out.println("The Class has " + classes.length + " interfaces" ); for (int m = 0; m < classes.length; m++) { Class intrface = classes[m]; System.out.println("An interface is " + intrface.toString()); } System.out.println("The superclass is " + myRegularBlob.getClass().getSuperclass().toString()); System.out.println("The superclass of that is " + myRegularBlob.getClass().getSuperclass().getSuperclass().toString()); |
You will get a typical response as follows:
The Blob classname is class weblogic.jdbc.rmi.SerialOracleBlob_weblogic_jdbc_rmi_internal_ OracleTBlobStub_weblogic_jdbc_rmi_internal_OracleTBlobImpl_weblogic_jdbc_wrapper_Blob_oracle_sql_BLOB_814_WLStub The Class has 6 interfaces An interface is interface java.io.Serializable An interface is interface weblogic.utils.wrapper.Wrapper An interface is interface weblogic.rmi.extensions.server.StubDelegateInfo An interface is interface weblogic.rmi.internal.StubInfoIntf An interface is interface weblogic.jdbc.rmi.internal.OracleTBlobImpl_weblogic_jdbc_wrapper_Blob_oracle_sql_BLOB_RemoteInterface An interface is interface weblogic.jdbc.rmi.internal.OracleTBlob The superclass is class weblogic.jdbc.rmi.SerialOracleBlob The superclass of that is class weblogic.jdbc.wrapper.JDBCWrapperImpl |
The next step is to investigate the classes involved and check their APIs to find out why the exception is occurring.
- Try different JDBC drivers. You may try a JDBC driver from a different vendor or an updated version of driver to see whether the problem still occurs. You may use metadata to verify that a correct driver is used. Sample code is like this:
DatabaseMetaData dmd = myConnect.getMetaData(); System.out.println("JDBC Driver Name is " + dmd.getDriverName()); System.out.println("JDBC Driver Version is " + dmd.getDriverVersion()); |
Known WebLogic Server Issues
You can periodically review the Release Notes for your version of WLS for more information on Known Issues or Resolved Issues in Service Packs and browse for JDBC-related issues. For your convenience, see the following:
In this section we will examine some known WebLogic Server problems that involve CLOB/BLOB data types.
- WLS (ALL) - Currently, Container Managed Persistence does not support the java.sql.Blob data type. The primary reason for this appears to be due to the lack of a standard facility for updating BLOBs. Alternatives include using:
- LONG_RAW data type instead of a BLOB data type
- Bean Managed Persistence (BMP) and writing proprietary JDBC code to update the BLOB.
- WLS (ALL) - If you use a prepared statement to update a CLOB and the new value is shorter than the previous value, the CLOB will retain the characters that were not specifically replaced during the update. For example, if the current value of a CLOB is
abcdefghij
and you update the CLOB using a prepared statement withzxyw
, the value in the CLOB is updated tozxywefghij
. To correct the values updated with a prepared statement, you should use thedbms_lob.trim
procedure to remove the excess characters left after the update. See the Oracle documentation for more information about thedbms_lob.trim
procedure. Here is a link that contains sample code on how to use trim:
http://www.cs.umb.edu/cs634/ora9idocs/appdev.920/a96591/adl03prg.htm - WLS 8.1 SP2 - Except for DB2 UDB 8.1, CLOB data types are limited to 32K characters because of DRDA listener limitations.
- WLS 8.1 SP2 - The WebLogic Type 4 JDBC drivers allow
PreparedStatement.setXXX
andResultSet.getXXX
methods on BLOB/CLOB data types, in addition to what is described in the JDBC specification. The supported conversions are generally the same as those for LONGVARBINARY/LONGVARCHAR, except where limited by database support. - WLS (ALL) - BLOBs and CLOBs in Oracle behave differently than other data types in regards to transactional boundaries (statements issued before an SQL commit or rollback statement) in that a BLOB or CLOB will become inactive as soon as a transaction is committed. If AutoCommit is set to TRUE, the transaction will be automatically committed after each command issued on the connection, including SELECT statements. For this reason you will need to set AutoCommit to false if you need to have a BLOB or CLOB available across multiple SQL statements. You will then need to manually commit (or rollback) the transactions at the appropriate time.
To set AutoCommit to false, use the following line of code:
conn.setAutoCommit(false);
// where conn is our connection object - WLS 6.1 - When using a non-transaction-enabled DataSource, a "java.sql.SQLException: ORA-01002" error will occur when creating a CMP entity EJB which has a field mapped to Oracle CLOB/BLOB.
The EJB container inserts a row which has CLOB/BLOB columns as follows:- Insert into the table initializing the LOB using EMPTY_BLOB() or EMPTY_CLOB().
- Retrieve the LOB locator using select for update statement.
- Modify the LOB.
Workaround:
You need to use a transactional DataSource (TxDataSource) when using Oracle CLOB/BLOB with CMP. - WLS 6.x - You cannot use BLOBs and CLOBs when using the RMI driver in conjunction with the WebLogic jDriver for Oracle. BLOBs and CLOBs are not serializable and therefore are not supported with the JDBC RMI Driver used with WebLogic Server 6.x.
Known Oracle Thin Driver Issue
The 9.2.0.1 and 9.2.0.2 versions of the Oracle Thin driver do not allow you to work with a CLOB in tables that also contain a long. When you retrieve a CLOB from the table and call clob.length(), you will get a SQL protocol violation.
Workaround: In this scenario, you can read the LONG column before calling clob.length()
.
This issue is fixed in version 9.2.0.3 and 10G.
Need Further Help?
If you have followed the pattern, but still require additional help, you can:
- Query AskBEA at http://support.bea.com/ using "clob" or "blob", as an example, to discover other published solutions.
- Ask a more detailed question on one of BEA's newsgroups at http://forums.bea.com/
If this does not resolve your issue and you have a valid Support Contract, you can open a Support Case by logging in at: http://support.bea.com/
External Resources
Search for known oracle bugs with CLOBs and BLOBs at:
https://metalink.oracle.com/metalink/plsql/ml2_gui.startup
FeedbackClick Support Pattern Feedback to rate this Support Pattern and comment on such things as:
|
DISCLAIMER NOTICE: BEA Systems, Inc. provides the technical tips and patches on this Website for your use under the terms of BEA's maintenance and support agreement with you. While you may use this information and code in connection with software you have licensed from BEA, BEA makes no warranty of any kind, express or implied, regarding the technical tips and patches. Any trademarks referenced in this document are the property of their respective owners. Consult your product manuals for complete trademark information. |
'JAVA' 카테고리의 다른 글
가비지 컬렉션, Garbage Collection (0) | 2008.03.03 |
---|---|
디자인 패턴 (0) | 2007.10.09 |
자바 강좌 링크 (0) | 2007.03.13 |
데이터베이스 프로그래밍에서 기본적으로 지켜야할 사항 (0) | 2007.01.12 |
MarshalException (0) | 2006.10.19 |