Invalid Dependent Object

[Sharma]

Ques 1:
If I Alter table structure, change the data size in ADDRESS table of column Zip from Varchar2(5) to Varchar2(9).

Is it going to make the dependent object Invalid. Say, stored procedure "SP_SENDMAIL" which contains select from ADDRESS table, will this Stored procedure become invalid. Also, anyother stored procedure which call SP_SENDMAIL also becomes invalid.

If it becomes invalid, Do I have to recreate these stored procedure with "CREATE OR REPLACE" command.

Ques 2:
Also, What about If I Drop the table ADDRESS and ReCreate it with changed structure (Changed column definition)

[padmam]

1)Yes you have to recompile the stored procedure
check the validity of the

SELECT OBJECT_NAME,STATUS FROM DBA_OBJECTS WHERE OBJECT_TYPE='XXX';

where xxx='PROCEDURE' or 'TABLE' etc.

If the output says the status as INVALID.The status column has two values VALID and INVALID.

just compile the procedure again.

2)If you have dropped also you have to again recompile

=======More detail Exp===================

If a schema object referenced in a SQL statement and the object is later modified in any way,the shared SQL area becomes invalidated(Marked as invalid) and the statement must be reparsed the next time it is executed and therefore reloaded.

For Ex a table ,sequence,view or a synonym is re-created or altered or dropped,or a package or procedure is recompiled ,all dependent shared SQL areas are invalidated.
=====================================
you can also go and check if you have OEM

Performance manager===>MEMORY===>Library Cache Details

Cheers

Padmam

[Edited by padmam on 04-19-2001 at 11:19 AM]

[Sharma]

Padmam,

Let me enhance my Ques.

My database is not used when changing the objects, I am in a maintenance window, So no part of SGA is used by anyone. Now,

Changing the table structure or dropping the table and recreating it with new structure,
Does any Stored procedure which uses this table beocmes Invalid, Do I have to Recreate Store procedure using "CREATE OR REPLACE" command.

Same way, If I change the Store procedure, Do I have to recreate the procedure which calls it.

Thanks.

[rsuri]

Yes you dependant procedures will be Invalidated when you change the structure of the underlying objects.
This is a one way process so they will NOT automatically become VALID when you recreate that changed object.

You don't have to recreate the procedure; the only thing you have to do is recompile --

ALTER PROCEDURE MyProc COMPILE;

You may want to check the status of all objects that are INVALID and recompile them as well.

- Rajeev

[rsuri]

Also as per the Oracle8i documentation --

"Oracle first recompiles objects upon which the procedure depends, if any of those objects are invalid"

So if you know the top level procedure in the dependency chart then compiling that may itself recompile the underlying objects.

- Rajeev