Compile all PL/SQL objects in a given schema
The following anonymous
block compiles PL/SQL objects in a given schema. It is intended to used with a selection critera in order to limit the objects that will be compiled.
A variant of this code snippet can also be used because calling
dbms_utility.compile_schema from a PL/SQL object in the schema that should be compiled will block.
begin
for obj in (select object_name name, object_type type from user_objects where object_type in ('TYPE', 'PACKAGE', 'PROCEDURE', 'FUNCTION')) loop
begin
execute immediate 'alter ' || obj.type || ' ' || obj.name || ' compile';
exception when others then
if sqlcode in (
- 2311, -- cannot alter with COMPILE option a valid type with type or table dependents
- 6545, -- PL/SQL: compilation error - compilation aborted
-24344 -- success with compilation error
) then
null;
else
dbms_output.put_line(sqlerrm);
dbms_output.put_line('alter ' || obj.type || ' ' || obj.name || ' compile');
end if;
end;
end loop;
end;
/
See also
PL/Scope collects information about
identifiers and SQL statements in PL/SQL source code.