Tips&Tricks: How to handle Oracle LONG datatype while working with legacy systems?
Posted by Jai on February 4, 2010
This post share some techniques you can try to handle Oracle LONG data type while working with legacy systems. While recently working on integrating one of the legacy system with latest technologies (JPA/Hibernate) we came across some of the challenges in doing the same. Here we will see how to find way around for one of such problem, Oracle LONG data type, or how to get rid of it.
Getting rid of Oracle LONG data type: Depending upon the nature of data you store in the column, you can plan to change the column type
LONG->VARCHAR (If you have limited data, 4000)
LONG->CLOB (for bigger data size)
You can use the below Stored Procedure to convert the column type:
DECLARE CURSOR c IS SELECT ID_TEMP, LONG_DATA_COLUMN FROM TEMP; rc c%ROWTYPE; BEGIN OPEN c; ALTER TABLE TEMP ADD TEMP_COLUMN VARCHAR2(4000); LOOP FETCH c INTO rc; EXIT WHEN c%NOTFOUND; UPDATE TEMP SET TEMP_COLUMN = rc.LONG_DATA_COLUMN WHERE ID_TEMP = rc.ID_TEMP; END LOOP; UPDATE TEMP SET LONG_DATA_COLUMN = NULL; ALTER TABLE TEMP MODIFY(LONG_DATA_COLUMN VARCHAR2(4000)); UPDATE TEMP SET LONG_DATA_COLUMN = TEMP_COLUMN; ALTER TABLE TEMP DROP COLUMN TEMP_COLUMN; COMMIT; END; /
Getting way around the LONG data type: The problem with using columns with LONG data type while mapping with Hibernate is that you will get the following exception:
java.sql.SQLException: Stream has already been closed
Few solutions/suggestions are to use:
Column Order: Use Native SQL query and set the column order right. Put the LONG data type column in the end of the select query while loading the data.
SELECT <column1>,<column2>...<LONG_DATA_COLUMN> FROM <TABLE_NAME>
The problem here is that you can not set the column order on JPA/Hibernate (at least I could not figure out 😦 ).
Load/Update using JDBC template: Loading the column value separately using native SQL query and set that in the entity. Do the same while persisting the entity also.
SELECT <LONG_DATA_COLUMN> FROM <TABLE_NAME> UPDATE <TABLE_NAME> set <LONG_DATA_COLUMN> = <value>
Keep the entity load/update operations in the same transaction.
I tried around couple of more tricks around to change the Dialect type etc. but no luck yet to directly map the type.
Feel free to share if you have come across any other good enough solution/suggestion.