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′;