编译Oracle对象
SET SERVEROUTPUT ON;
-----------------------------------------------
-- 编译数据库对象
-----------------------------------------------
DECLARE
v_sql VARCHAR2(2000);
BEGIN
FOR cur_rec IN (
SELECT
owner,
object_name,
object_type,
DECODE(object_type,'PACKAGE',1,'PACKAGE BODY',2,3) AS recompile_order
FROM dba_objects
WHERE object_type IN ('PACKAGE', 'PACKAGE BODY','FUNCTION')
AND status != 'VALID'
AND OBJECT_NAME LIKE 'XXXX%'
ORDER BY 4
)
LOOP
IF cur_rec.object_type = 'PACKAGE BODY' THEN
v_sql := 'ALTER PACKAGE ' || ' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE BODY';
ELSE
v_sql := 'ALTER ' || cur_rec.object_type || '"' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
END IF;
EXECUTE IMMEDIATE v_sql;
DBMS_OUTPUT.put_line(v_sql);
END LOOP;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
DBMS_OUTPUT.put_line(v_sql);
END;
/
