is it true that whenever u perform alter table...u invalid table -elated procedure?

[ngwh]

hi,

I have got someone telling me that whenever we perform any Alter Table.....it is effectively invalidating all procedures related to that table? is this TRUE? as I dont wish to run the risk/trouble so I hope anyone can help to clarify here?

thanks in advance

rgds,
nick

[pando]

yes, nothing to worry since procedures are recompiled automatically when they are called again

[ngwh]

hi pando,

thks for the info...so am i right to say it is almost "transparent" to any oracle user that call a procedure related to that table right? as in no error prompting and so on....almost as per normal ?

thks
nick

[jmodic]

Originally posted by ngwh
hi,

I have got someone telling me that whenever we perform any Alter Table.....it is effectively invalidating all procedures related to that table? is this TRUE?

No, not quite. Only some of the ALTER TABLE comands (basicaly only those which change the table's "visible" structure) indeed cause dependant procedures to be invalidated, but there are many ALTER TABLE clauses that actually have no effect on related procedures..

[ngwh]

ohh really? infact I am going to run Alter Table add partition with a new date sub-range wonder if that would invalidate those procedures???
hope that u can enlighten me further

thks
nick

[abhaysk]

Why dont u try on ur test DB

[pando]

Originally posted by ngwh
ohh really? infact I am going to run Alter Table add partition with a new date sub-range wonder if that would invalidate those procedures???
hope that u can enlighten me further

thks
nick

dont think so, AFIAK procedures, packages, functins are invalid if structure of table changes such as adding/deleting columns, change column definitions