Thread: alter table alter type CASCADE

One of the biggest problems I have maintaining a database with a lot of
views is that when I want to change a datatype, I have to drop every
view uses the column and every view that uses those views etc...
This turns into a maintenance nightmare.

Is there any intention of adding a CASCADE to alter type which would
automatically update any dependencies with the new datatype? Obviously
it should error out if it wouldn't have let you save one of the views
with the new datatype.

Thanks
Sim

It has been some years since I worked with Oracle, doesn't Oracle recompile the view when the object it references changes in structure?
Send

---

From: thombrown@gmail.com
Date: Wed, 5 May 2010 10:12:34 +0100
Subject: Re: [GENERAL] alter table alter type CASCADE
To: sim@compulab.co.il
CC: pgsql-general@postgresql.org

---

Win a $10,000 shopping spree from Hotmail! Enter now Enter now

What does Oracle do when you've got a view like:

CREATE OR REPLACE VIEW test_view AS
 SELECT test.test_id, test.test_value, date_is_future(test.test_date) as upcoming
   FROM test;

Where date_is_future expects a text field, but you change the test table so that test_date is now varchar(20)?  The function no longer matches the signature.  Does it just prevent it?  And what about when conditions are provided in a WHERE clause which become invalid when the column type changes?

Thom

2010/5/5 Sim Zacks <sim@compulab.co.il>:
> One of the biggest problems I have maintaining a database with a lot of
> views is that when I want to change a datatype, I have to drop every
> view uses the column and every view that uses those views etc...
> This turns into a maintenance nightmare.

Then I would question your approach to maintenance.  In the past when
I've had to deal with this type of thing, all views were created from
a script.  Edit the script, run the script, you're done.  Do it in a
transaction and if there are any errors nothing changes.  Script looks
something like:

begin;
drop view x1;
drop view y1;
... more drop statements
create view x1...
create view y1 ...
commit;

On Wed, May 5, 2010 at 7:31 AM, Thom Brown <thombrown@gmail.com> wrote:
> On 5 May 2010 13:14, Chris Barnes <compuguruchrisbarnes@hotmail.com> wrote:
>>
>> It has been some years since I worked with Oracle, doesn't Oracle
>> recompile the view when the object it references changes in structure?
>> Send
>
> What does Oracle do when you've got a view like:
>
> CREATE OR REPLACE VIEW test_view AS
>  SELECT test.test_id, test.test_value, date_is_future(test.test_date) as
> upcoming
>    FROM test;
>
> Where date_is_future expects a text field, but you change the test table so
> that test_date is now varchar(20)?  The function no longer matches the
> signature.  Does it just prevent it?  And what about when conditions are
> provided in a WHERE clause which become invalid when the column type
> changes?
>

If Oracle can find a function that will make the view valid it will
continue to allow usage of the view; though it is still marked as
needing to be rebuilt from a user perspective any calls to it will
succeed if there is a possible way for the view to still be valid.  If
there is no possible way for any use of the view to succeed then the
calls fail.


--
Peter Hunsberger