Re: Re: Column Length modification

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_at_gmail.com> 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.
>
>

--
http://www.freelists.org/webpage/oracle-l