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';
Discussion about this article
| Author | Body |
admin
8/24/2009 2:50 PM
|
Please provide feedback about this article here.
To participate in this discussion Sign up for free membership of 24x7code.
To Signup click on Login , Use create user link & the follow the instructions.
Thank you
|