WL81에서 BLOB 사용법

기존 WL6.1에서는

oracle.sql.CLOB

사용하였으나

WL81에서는

How helpful was this Support Pattern? Rating: - Please select - 5-Extremely helpful 4-Very helpful 3-Somewhat helpful 2-Not very helpful 1-Not at all helpful Comments:

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

  • Migrating applications due to WebLogic Server upgrade

  • Database Driver

• Known WebLogic Server Issues

• Known Oracle Thin Driver Issue

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);

Top of Page

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()); }

Top of Page

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);

Top of Page

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.

Top of Page

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.

Top of Page

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.

Top of Page

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.

1. 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; }

2. 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.

3. 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());

Top of Page

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:

• WLS 8.1 Release Notes
• WLS 7.0 Release Notes
• WLS 6.1 Release Notes

In this section we will examine some known WebLogic Server problems that involve CLOB/BLOB data types.

1. 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:
   1. LONG_RAW data type instead of a BLOB data type
   2. Bean Managed Persistence (BMP) and writing proprietary JDBC code to update the BLOB.
2. 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 with zxyw, the value in the CLOB is updated to zxywefghij. To correct the values updated with a prepared statement, you should use the dbms_lob.trim procedure to remove the excess characters left after the update. See the Oracle documentation for more information about the dbms_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
3. WLS 8.1 SP2 - Except for DB2 UDB 8.1, CLOB data types are limited to 32K characters because of DRDA listener limitations.
4. WLS 8.1 SP2 - The WebLogic Type 4 JDBC drivers allow PreparedStatement.setXXX and ResultSet.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.
5. 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
6. 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:
   
   1. Insert into the table initializing the LOB using EMPTY_BLOB() or EMPTY_CLOB().
   2. Retrieve the LOB locator using select for update statement.
   3. Modify the LOB.
   Although the connection from a non-transaction-enabled DataSource is in auto-commit mode, Oracle LOB locators that are used to write cannot span the transactions. So the transaction is closed at step 1 and then ORA-1002 occurs at step 2.
   Workaround:
   You need to use a transactional DataSource (TxDataSource) when using Oracle CLOB/BLOB with CMP.
7. 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.

Top of Page

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/

Top of Page

External Resources

Search for known oracle bugs with CLOBs and BLOBs at:

https://metalink.oracle.com/metalink/plsql/ml2_gui.startup

Feedback Click Support Pattern Feedback to rate this Support Pattern and comment on such things as: Were you successful in solving your issue with this pattern? Was there any additional information that could be included in this pattern that would help solve your issue. What other Support patterns would you like to see developed?

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.