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
"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:
Your response was quite helpful and I started implementing transparent
compression/decompression logic. Everything was working untilupdtae,
when
I started doing update on view, I get following error message:
ORA-01733: virtual column not allowed here ORA-06512: at line 13
I defined view as:
create or replace view ltr_body as select ltr_id, LTR_DESC_TXT,
blob_decompress(LTR_BODY_TXT) ltr_body_txt, ADD_DT, ADD_OPER_ID, UPDT_DT, UPDT_OPER_ID from ltr_body_orig
I can do select,insert.delete but not update. Any help will be apprecaited.
Prem Received on Sun May 09 2004 - 16:21:33 CDT