I have a stored procedure which references a view. A column was dropped by the underlying table and the view was changed so it didn't reference the column anymore. The stored procedure still referenced the view and dropped column. My question is how is the stored procedure not invalid, since it references a column that has been dropped and is not inthe view any longer? We did not get an error until we tried to execute the stored procedure.

Thanks

When a dependent object is dropped, all the related packages (or plans if on z/OS and using plans) are marked as invalid (not the stored procedure itself). Upon first execution of the invalid package, an automatic rebind is attempted to see if the problem has been corrected, and if it has not, then the package is marked as inoperative and no further automatic rebinds take place (and execution error is returned).

So next time, check for invalid packages, not invalid SP's.

I should have said package. I did a rebind on all packages and all were valid. Shouldn't the package in this case have been invalid ?

Are you sure that the column is actually referenced in a Select, Insert, Update, or Delete statement of the SP? If the column no longer existed you should get an error.

Yes, the SP references the column. We get an error message at runtime complaining about the missing column.

If the package associated with the SP is still valid, I would guess that the SP is using dynamic SQL. Is this an SQL SP, or Java, C, etc SP?

Even if it is a SQL SP, is any of the SQL dynamically prepared?

Its a SQL SP. Its declaring a global temp table and then inserting into the temp table. The select statement in the within the insert.

declare global temp table:

insert into temp table
(
select ..... (the column that got dropped is in this select statement)
)

I think you answered your own question.