Problem with LONG in Oracle

Question

hi,

i have an old oracle database in which there is a field of type LONG(max size is 2GB)

Now earlier it used to work fine but with time data we started putting was of size much more than 2GB so we started facing trouble.

I can not change the field type from LONG to CLOB since that will create a lot of trouble as innumerable changes will have to be made to the product to deal with the CLOB type.

Dividing data into chunks and then putting is an option but how do we do that??

Can someone suggest a way to handle this . Preferably code or a link to the code.

Answer 1

The problem is, at no point can you pass more than 2GB to a LONG variable. So your application has two choices:

• split the big data into chunks before touching the database
• sending the database a CLOB and letting the database handle it (say with a view and an INSTEAD OF trigger)

Which approach will work best for you depends on the details of your application. Give us more info and we can give you specific advice.

Actually there is a third option. The LONG and LONG RAW data types have been deprecated for well over a decade. Perhaps it is time to move on ...

Answer 2

You may be interested in this article on LONGs by Adrian Billington.