Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

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 <avdbi_at_hotmail.com>
Date: Thu, 29 Apr 2004 04:08:33 GMT
Message-ID: <5d%jc.126839$e17.65398@twister.nyroc.rr.com>

"Prem K Mehrotra" <premmehrotra_at_hotmail.com> wrote in message news:43441e77.0404281816.35e301f9_at_posting.google.com...
> 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

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US