Oracle 动态编译数据库对象

编译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;
/