a query abaixo mostra os objetos inválidos no banco...
Selecionar tudo
SELECT DISTINCT u.object_name,
decode(u.object_type,'PACKAGE BODY','PACKAGE',u.object_type) object_type,
u.created
FROM user_objects u
WHERE u.status = 'INVALID'
AND u.object_type IN ('PROCEDURE','FUNCTION','PACKAGE BODY','PACKAGE')
ORDER BY u.created
e esta próxima compila os objetos inválidos....
Selecionar tudo
DECLARE
CURSOR c_obj IS
SELECT DISTINCT u.object_name,
decode(u.object_type,'PACKAGE BODY','PACKAGE',u.object_type) object_type,
u.created
FROM user_objects u
WHERE u.status = 'INVALID'
AND u.object_type IN ('PROCEDURE','FUNCTION','PACKAGE BODY','PACKAGE')
ORDER BY u.created;
r_obj NUMBER;
BEGIN
FOR r_obj IN c_obj LOOP
BEGIN
EXECUTE IMMEDIATE 'alter '||r_obj.object_type||' '||r_obj.object_name||' compile';
dbms_output.put_line(r_obj.object_type||' '||r_obj.object_name||': COMPILADO!' );
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line('Erro Compilando '||r_obj.object_type||' '||r_obj.object_name);
END;
END LOOP;
END;
[]´s