Oracle supplied packages -DBMS_DDL Package
Below is self explanatory example
Scenario of recompilation for a procedure –
drop table tx1;
create table tx1(a number);
insert into tx1 values(1);
create or replace procedure px1
is
s number;
begin
select a into s
from tx1;
end;
select status from user_objects
where object_name=’PX1′;
alter table tx1
add b number;
select status from user_objects
where object_name=’PX1′;
alter procedure px1 compile; OR Exec px1;
select status from user_objects
where object_name=’PX1′;
DBMS_DDL.ALTER_COMPILE(‘object_type’, ‘owner name’, object_name)
Object_type can be a procedure, function, package specification, package body or a trigger only.
create or replace procedure compile_procedure(procedure_name varchar2)
as
begin
DBMS_DDL.ALTER_COMPILE(‘PROCEDURE’,’SCOTT’, procedure_name);
dbms_output.put_line(‘The procedure ‘ || procedure_name || ‘ is recompiled’);
end;
exec compile_procedure(‘PX1’);
select status from user_objects
where object_name=’PX1′;