------------------------------------------------------------------------
--
-- Script for recompile all objects in Current user scheme
--
------------------------------------------------------------------------
declare
COMPILE_WITH_ERROR EXCEPTION;
PRAGMA EXCEPTION_INIT(COMPILE_WITH_ERROR,-24344);

IsExists number;
a_cursor number;
rowprocessed number;
i number;
nCount number;
isInvalid number;
strSQL varchar2(128);
Type ObjectName_Type is table OF varchar2(30) index by binary_integer;
Type ObjectType_Type is table OF varchar2(30) index by binary_integer;
tObjectName ObjectName_Type;
tObjectType ObjectType_Type;
lSQLErrm varchar2(64);
lSQLCode varchar2(32);
cursor crs1 is
select ao.object_name,ao.object_type
from user_objects ao
where ao.STATUS='INVALID'
and ao.object_type in ('PROCEDURE','FUNCTION','PACKAGE BODY','VIEW','TRIGGER')
order by ao.created;
cursor crs_check(iObjName varchar2, iObjType varchar2) is
select count(*) from user_objects
where object_name=iObjName and object_type=iObjType
and status='INVALID';
begin
i := 0;
for c1rec in crs1 loop
i := i + 1;
tObjectName(i) := c1rec.object_name;
tObjectType(i) := c1rec.object_type;
end loop;
nCount := i;
for i in 1..nCount loop
open crs_check(tObjectName(i),tObjectType(i));
fetch crs_check into isInvalid;
if isInvalid > 0 then
a_cursor:=dbms_sql.open_cursor;
if tObjectType(i)='PACKAGE BODY' then
strSQL := 'ALTER PACKAGE '||tObjectName(i)||' COMPILE BODY';
else
strSQL := 'ALTER '||tObjectType(i)||' '||tObjectName(i)||' COMPILE';
end if;
begin
dbms_sql.parse(a_cursor,strSQL,dbms_sql.v7);
exception
when COMPILE_WITH_ERROR then
null;
when others then
lSQLCode := SQLCODE;
lSQLErrm := SQLERRM;
rollback;
raise_application_error(-20000,lSQLCode||', Mesg:'||lSQLErrm||', in sql:'||strSQL,TRUE);
end;
rowprocessed:=dbms_sql.execute(a_cursor);
dbms_sql.close_cursor(a_cursor);
end if;
close crs_check;
end loop;
end;
/