Jai’s Weblog – Tech, Security & Fun…

Tech, Security & Fun…

  • Jaibeer Malik

    Jaibeer Malik
  • View Jaibeer Malik's profile on LinkedIn
  • Subscribe

  • Feedburner

  • Enter your email address to subscribe to this blog and receive notifications of new posts by email.

    Join 29 other followers

  • Archives

  • Categories

  • Stats

    • 379,210
  • Live Traffic

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.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: