Re: updtaing through a virtual column of a view

premmehrotra_at_hotmail.com (Prem K Mehrotra) wrote in message news:<43441e77.0405091409.73cd2504_at_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
Received on Sun May 09 2004 - 20:19:48 CDT