suhinrasheed
asked on
procedure package recompilation
Hi All
I have a view X and this view selects column 1,2,3 from a table Y.
I have some procedures and packages in my schema which references this view i.e inside those procedures and packages i use the view X.
Now my client wants to alter the structure of the view so that it also takes column 4 from table Y.I know that the the dependent objects i.e procedures and packages referring view X will get invalidated when i change the view structure.
dO i need to do a manual recompile or will Oracle automatically recompile the procedures and packages,the next time when someone invokes it.
My Oracle version is 9.2.0.7
Please reply urgently
I have a view X and this view selects column 1,2,3 from a table Y.
I have some procedures and packages in my schema which references this view i.e inside those procedures and packages i use the view X.
Now my client wants to alter the structure of the view so that it also takes column 4 from table Y.I know that the the dependent objects i.e procedures and packages referring view X will get invalidated when i change the view structure.
dO i need to do a manual recompile or will Oracle automatically recompile the procedures and packages,the next time when someone invokes it.
My Oracle version is 9.2.0.7
Please reply urgently
ASKER
is there any particular script i can write to recompile all invalidated objects in a particular schema
You can use dbms_utility to compile all the invalid objects in a schema:
usage:
EXEC DBMS_UTILITY.compile_schem
usage:
EXEC DBMS_UTILITY.compile_schem
Have a look at this link:
http://www.oracle-base.com/articles/misc/RecompilingInvalidSchemaObjects.php#DBMS_UTILITY.compile_schema
http://www.oracle-base.com/articles/misc/RecompilingInvalidSchemaObjects.php#DBMS_UTILITY.compile_schema
You can use dynamic sql to create a procedure to compile the invalid packages - something like this
declare
sql_stmt varchar2(255);
begin
for n in (select object_name from user_objects where object_type = 'PACKAGE' and status = 'INVALID') loop
sql_stmt := 'alter package '||n.object_name||' compile body';
execute immediate sql_stmt;
end loop;
end;
/
Obviously, you can do the same for procedures and functions as well (without the "body" keyword).
declare
sql_stmt varchar2(255);
begin
for n in (select object_name from user_objects where object_type = 'PACKAGE' and status = 'INVALID') loop
sql_stmt := 'alter package '||n.object_name||' compile body';
execute immediate sql_stmt;
end loop;
end;
/
Obviously, you can do the same for procedures and functions as well (without the "body" keyword).
ASKER
Can anyone tell me a query to identify the dependent objects of a view.
That is i am going to alter the structure of a view and i know that the objects referencing the view will get invalidated,how to easily identify all the objects referring the view
That is i am going to alter the structure of a view and i know that the objects referencing the view will get invalidated,how to easily identify all the objects referring the view
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
But to avoid the compilation delay and any unexpected errors at run time it is always preferred to compile them manually, before the users access them.