http://www.skview.co.kr/upload/blding/영등포0.jpg

영등포 SK LEADERS' VIEW 2 월말 공사진행 현황 Year | 2004 1 2 3 4 5 6 7 8 9 10 11 12 Year | 2005 1 2 3 4 5 6 7 8 9 10 11 12 Year | 2006 1 2 3 4 5 6 7 8 9 10 11 12 Year | 2007 1 2 3 4 5 6 7 8 9 10 11 12 사진을 누르시면 큰 이미지를 보실 수 있습니다. 이 공사는 2004년 04월 착공하여, 2007년 08월 준공 예정 입니다. 2월말 현재 공정율이 81%로 순조롭게 공사가 진행 중입니다. 공사진행율 81% 2월 실적 ■ 건축공사 1. 고층부 - 외장판넬공사 완료 - 내장공사 : 도배공사, 도장공사, 붙박이 가구문짝설치 2. 상가(근린생활시설) - 외벽 석공사 (1층 마무리) - 창호 및 유리설치공사 (1층) - 외부계단 금속공사 3. 주차장 - 지상1층 바닥방수■ 기전공사 - 기계실 공조,위생,가스,소방,자동제어 배관 - 지하1층 오배수 횡주관설치, 지하3층 집수정 펌프설치 - CGV 및 운동시설 공조실 덕트설치 - 5층 운동시설 SP배관, 판매시설 SP배관 잔손보기 - 오피스텔 환기유닛 설치 - ELEV. 1~3,5~7 호기 홀 버튼류 취부 - ELEV. 1~2,9~10 호기 외장공사 - 오피스텔 세대분전반 배선기구 취부, EPS 계량기함 설치 - B3~3F 소방배관 입선, MCC반입

기존 WL6.1에서는

oracle.sql.CLOB

사용하였으나

WL81에서는

WebLogic Server Support Pattern

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

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.

Write to BLOB Field

In the following try catch block, you get the BLOB locator and access the Oracle BLOB extension for writing.

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

is

To resolve this issue, the above code needs to be modified.

Replace

with

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.

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

With

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

With

WebLogic Driver

The code is very similar to the BLOB example with some changes to avoid a ClassCastException, as shown below.

Replace

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:

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.

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

is

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:

To avoid the ClassCastException, the above line of code should be replaced with

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:

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:

You will get a typical response as follows:

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:

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

http://www.mgoon.com/Player/Mgoon_Player.swf?VID=438026&PlayerType=1

자바 강좌 링크

http://cafe.naver.com/ssui.cafe?iframe_url=/ArticleRead.nhn%3Farticleid=32