I had read several articles of internet that compression/decompression
of blob can be implemented transparently (i.e., without modifying
third party code)
using views and instead of triggers for insert/delete/update.

I have a table blobtble( in third party application) which I renamed
to
blobtable_orig:

create table blobtable_orig
(
body_txt blob
);

I want to compress blob when storing in database and uncompress it
when selecting it. My code comes from third party, so I cannot make
any changes except I can modify database schema such as create
triggers and views.
I had created a thread on this sometime ago but now I am starting
another thread to point the specific problem with update.

I defined a vlwe:

create of replace view blobtable as
select uncompress(body_txt) body_txt from blobtable_orig

I create instead of triggers for delete/insert/update, in these
trigggers
I do operations on original table (table_orig) where I can compress
the body_txt
column before making changes to database.

While insert worked just fine, update gives me error:
ORA-01733: virtual column not allowed here
ORA-06512: at line 13

I don't know why insert instead of trigger will not complain where as
update
will complain. Is there a way around since I cannot modify application
code?

Eralier I was thinging of not using views sintead use fine grained
auditing
which implements "select triggers".However, I didnot find any way in
FGA
to modify the value to ve returned to application (as is done in view
using
decompress(...)).


Prem

premmehrotra@hotmail.com (Prem K Mehrotra) wrote in message news:<43441e77.0405091409.73cd2504@posting.google. com>...
> I had read several articles of internet that compression/decompression
> of blob can be implemented transparently (i.e., without modifying
> third party code)
> using views and instead of triggers for insert/delete/update.
>
> I have a table blobtble( in third party application) which I renamed
> to
> blobtable_orig:
>
> create table blobtable_orig
> (
> body_txt blob
> );
>
> I want to compress blob when storing in database and uncompress it
> when selecting it. My code comes from third party, so I cannot make
> any changes except I can modify database schema such as create
> triggers and views.


Sorry, I made a mistake. Have been working too long today. When writing
instead of trigger for update, I changed logic for update, but I still had
insert in the definition of trigger,so there was no instead of trigger
on update. That'swhy insert was working fine and not update.


> I had created a thread on this sometime ago but now I am starting
> another thread to point the specific problem with update.
>
> I defined a vlwe:
>
> create of replace view blobtable as
> select uncompress(body_txt) body_txt from blobtable_orig
>
> I create instead of triggers for delete/insert/update, in these
> trigggers
> I do operations on original table (table_orig) where I can compress
> the body_txt
> column before making changes to database.
>
> While insert worked just fine, update gives me error:
> ORA-01733: virtual column not allowed here
> ORA-06512: at line 13
>
> I don't know why insert instead of trigger will not complain where as
> update
> will complain. Is there a way around since I cannot modify application
> code?
>
> Eralier I was thinging of not using views sintead use fine grained
> auditing
> which implements "select triggers".However, I didnot find any way in
> FGA
> to modify the value to ve returned to application (as is done in view
> using
> decompress(...)).
>
>
> Prem