Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Compressing data in a lob column in Oracle9i

Re: Compressing data in a lob column in Oracle9i

From: Anurag Varma <>
Date: Thu, 29 Apr 2004 04:08:33 GMT
Message-ID: <5d%jc.126839$>

"Prem K Mehrotra" <> wrote in message
> 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

No function for compressing/uncompressing in 9i. In 10G however, there is a utl_compress function which does exactly what you are looking for.
So if you can upgrade, it might be worthwhile.

Otherwise, you would have to call your Java/C program from pl/sql. And you should not have to go into FGA to uncompress the data. Just write an uncompress program .. and convert the table into a view which would uncompress the data before selecting it. That same view can have triggers on it to take care of the insert/update/delete also.

Anurag Received on Wed Apr 28 2004 - 23:08:33 CDT

Original text of this message