Free script to recompile
dependent PL/SQL procedures
Wojtek Kaczorowski has published a freeware script that will
traverse the dictionary to locate and recompile all PL/SQL objects
that become invalid after a database change:
http://www.dba-village.com/village/dvp_scripts.ScriptDetails?ScriptIdA=1741
CREATE OR REPLACE PROCEDURE pr_recompile_objects(p_mask user_dependencies.NAME%TYPE DEFAULT '%') IS
v_sql VARCHAR2(100);
v_name VARCHAR2(50);
v_type VARCHAR2(50);
BEGIN
--This order is a must!
FOR cur_main IN (SELECT 'TYPE', 'TRIGGER', 'VIEW', 'PROCEDURE', 'FUNCTION',
'MATERIALIZED VIEW', 'PACKAGE', 'PACKAGE BODY' TYPE
FROM dual)
LOOP
v_type := cur_main.TYPE;
FOR cur IN (SELECT NAME, COUNT(*)
FROM user_dependencies
WHERE TYPE LIKE v_type
AND NAME LIKE p_mask
AND NAME NOT LIKE 'PR_RECOMPILE%' -- to avoid self lock
GROUP BY NAME
ORDER BY 2)
LOOP
v_name := cur.NAME;
BEGIN
v_sql := 'alter ' || v_type || ' ' || v_name || ' compile';
IF v_type = 'PACKAGE BODY'
THEN
v_sql := 'alter package ' || v_name || ' compile body';
END IF;
EXECUTE IMMEDIATE v_sql;
EXCEPTION
WHEN OTHERS THEN
NULL;
/* or log it into some (t_compilance_error_adm) table
INSERT INTO t_compilance_error_adm
(object_name,
object_type)
VALUES
(v_name,
v_type);
COMMIT;
*/
END;
END LOOP;
END LOOP;
END;
/
Reader Comments:
I believe that there is an error in the code.
In the part :
FOR cur_main IN (SELECT 'TYPE', 'TRIGGER', 'VIEW', 'PROCEDURE', 'FUNCTION',
'MATERIALIZED VIEW', 'PACKAGE', 'PACKAGE BODY'
TYPE
FROM dual)
My sugestion is
FOR cur_main IN (SELECT * FROM
(
SELECT 1 TYPE_ORD,
'TYPE' TYPE FROM DUAL
UNION
SELECT 2, 'VIEW'
FROM DUAL
UNION
SELECT 3,
'PROCEDURE' FROM DUAL
UNION
SELECT 4, 'FUNCTION'
FROM DUAL
UNION
SELECT 5, 'PACKAGE'
FROM DUAL
UNION
SELECT 6, 'PACKAGE
BODY' FROM DUAL
)
ORDER BY 1
)
My best regards,
Juliano Bastos
|
