Compressing data in a lob column in Oracle9i

I had read about the compression feature added in Oracle9i, i.e., a table can be compressed. However, when I found an application to use compression, I was terribly disappointed that Oracle compression does not work on LOB data types.

Basically I have a packaged application (code supplied by third party). There is a table which contains blob column and I want to compress this column. Since it is a packaged appalication, all I can do is to add triggers if they do not cause side effects.

I was thinking of adding a trigger on insert/update which will compress the data stored in LOB column. Will it work? My question is - are there any functions available in PL/SQL for compressing a character string. Also I don't know when LOB data is stored in database (using OCI - I know packaged application uses OCI), does all lob data in a blob come as a unit in a trigger (even though OCI may send it in parts).

If I am not mistaken, Oracle 9i provides triggers on select (FGA). I presume I can uncompress the LOB data inside select trigger. Once again, is there a function avaiable in
PL/SQL for uncompressing. I have never used select trigger only read about them.

IF there is no function for compress/uncompress in PL/SQL are there functions avaiable in Java since Java can be used in triggers.

Thansk a lot,

Prem Received on Wed Apr 28 2004 - 21:16:07 CDT