60 lines
2.0 KiB
SQL
60 lines
2.0 KiB
SQL
set serveroutput on size 100000;
|
|
declare
|
|
s_object_name VARCHAR2(255);
|
|
s_object_type VARCHAR2(255);
|
|
ssql VARCHAR2(255);
|
|
CURSOR packages_cur
|
|
IS
|
|
select object_name
|
|
,object_type
|
|
from user_objects
|
|
where status='INVALID'
|
|
and object_type in('FUNCTION', 'PACKAGE', 'PACKAGE BODY', 'PROCEDURE', 'TRIGGER','VIEW')
|
|
order by case object_type
|
|
when 'VIEW' then '0'||object_type
|
|
when 'FUNCTION' then '1'||object_type
|
|
when 'PACKAGE' then '2'||object_type
|
|
when 'PACKAGE BODY' then '3'||object_type
|
|
when 'PROCEDURE' then '4'||object_type
|
|
when 'TRIGGER' then '5'||object_type
|
|
else '9'||object_type
|
|
end
|
|
,object_name;
|
|
BEGIN
|
|
dbms_output.enable(1000000);
|
|
for i in 1..3 loop
|
|
OPEN packages_cur;
|
|
LOOP
|
|
FETCH packages_cur
|
|
INTO s_object_name,
|
|
s_object_type;
|
|
EXIT WHEN packages_cur%NOTFOUND;
|
|
begin
|
|
|
|
IF s_object_type = 'PACKAGE BODY'
|
|
THEN
|
|
ssql:='ALTER PACKAGE '||s_object_name||' COMPILE DEBUG BODY';
|
|
execute immediate ssql;
|
|
ELSIF s_object_type <> 'VIEW' and s_object_type <> 'TRIGGER'
|
|
THEN
|
|
ssql:='ALTER '||s_object_type||' '||s_object_name||' COMPILE DEBUG';
|
|
execute immediate ssql;
|
|
ELSE
|
|
ssql:='ALTER '||s_object_type||' '||s_object_name||' COMPILE';
|
|
execute immediate ssql;
|
|
END IF;
|
|
dbms_output.put_line('Eseguito correttamente '||ssql);
|
|
exception
|
|
when others then
|
|
if i = 1 then
|
|
null;
|
|
else
|
|
dbms_output.put_line('Oggetto: '||s_object_type||' '||s_object_name||' Errore: '||sqlerrm);
|
|
end if;
|
|
end;
|
|
END LOOP;
|
|
CLOSE packages_cur;
|
|
end loop;
|
|
end;
|
|
/
|