Unfortumately, while this will deal with queries very nicely, inserts that
attempt to insert into that column in the view will raise error:
ORA-01733: virtual column not allowed here
And it's not a trivial exercise to write "instead of" triggers for generic
inserts and updates; so unless the code is already using nothing but
(typically inefficient) stored procedures for all DML then the view trick
is not likely to be viable.
Regards
Jonathan Lewis
On Tue, 23 Mar 2021 at 18:43, Adric Norris <landstander668@xxxxxxxxx> wrote:
If forced to make it visible as NUMBER(22), then I'd suggest leveraging a
view. Something like:
alter table my_table
modify tweaked_column number(27,5) check (tweaked_column =
trunc(tweaked_column));
alter table my_table
rename to my_table_old;
create view my_table as
select col1, col2, ..., cast(tweaked_column as number(22))
tweaked_column, ... from my_table_old;
Nice and quick*, with minimal undo/redo generation. Albeit with the caveat
that the table is briefly unavailable when the switcheroo takes place.
