Re: Compressing data in a lob column in Oracle9i

"Anurag Varma" <avdbi_at_hotmail.com> wrote in message news:<5d%jc.126839$e17.65398_at_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

Anurag:

Thanks. I had not thought of view.

Prem Received on Thu Apr 29 2004 - 11:23:44 CDT