Compiling Triggers

set echo on
set feedback on
set serveroutput on

/**********************************************************************************************************/
/*                                                                                                        */
/* Procedure:   CompileTrigger                                                                            */
/* Description: This procedure compiles all triggers in the specified schema.                             */
/*              This process only takes place in the server and therefore is usually is mutch faster      */
/*              then other spooling based scripts.                                                        */
/* Version:    	1.3.0                                                                                     */ 
/* Required:	Oracle Server Version 7.3 or higher and the appropriate rights.                           */
/*              GRANT CONNECT TO <user>;                                                                  */
/*              GRANT UNLIMITED TABLESPACE TO <user>;                                                     */
/*              GRANT CREATE PROCEDURE TO <user>;                                                         */
/*              GRANT EXECUTE ON dbms_output TO <user>;                                                   */
/*              GRANT EXECUTE ON dbms_sql TO <user>;                                                      */
/*              GRANT SELECT ON all_triggers TO <user>;                                                   */
/*              GRANT ALTER ANY TRIGGER TO <user>;                                                        */
/*                                                                                                        */
/* Written by: 	Material Dreams                                                                           */
/* EMail:       info@materialdreams.com                                                                   */
/* WWW:         http://www.materialdreams.com/oracle                                                      */
/*                                                                                                        */
/* License:     This script can be freely distributed as long as this header will not be removed and      */
/*              improvements and changes to this script will be reported to the author.                   */
/*                                                                                                        */
/*              Copyright (c) 1995-2004 by Material Dreams. All Rights Reserved.                          */
/*                                                                                                        */
/**********************************************************************************************************/

CREATE OR REPLACE PROCEDURE CompileTrigger(theSchema IN VARCHAR2, theDebugFlag IN INTEGER DEFAULT 0) AS
BEGIN
DECLARE
	v_SqlCmd	VARCHAR2(2000);
	v_Cursor	INTEGER := 0;
	v_RetCode	INTEGER := 0;
	v_Count		INTEGER := 0;

CURSOR C1 IS 
SELECT		'ALTER TRIGGER ' || owner || '.' || trigger_name || ' COMPILE'
FROM		all_triggers
WHERE		table_owner = upper(theSchema)
ORDER BY	owner, trigger_name;

BEGIN

	IF theDebugFlag = 1 THEN
		SYS.DBMS_OUTPUT.ENABLE(1000000);
	END IF;

	v_Cursor := SYS.DBMS_SQL.OPEN_CURSOR;

	OPEN C1;

	LOOP
		FETCH C1 INTO v_SqlCmd;
		EXIT WHEN C1%NOTFOUND;
		IF theDebugFlag = 1 THEN
			SYS.DBMS_OUTPUT.PUT_LINE('Execute -> ' || v_SqlCmd);
		ELSE
   			SYS.DBMS_SQL.PARSE(v_Cursor, v_SqlCmd, DBMS_SQL.V7);
   			v_RetCode := SYS.DBMS_SQL.EXECUTE(v_Cursor);
		END IF;
		v_Count := v_Count + 1;
	END LOOP; 

	CLOSE C1;

	SYS.DBMS_SQL.CLOSE_CURSOR(v_Cursor);

	SYS.DBMS_OUTPUT.PUT_LINE('*** The procedure CompileTrigger has compiled ' || v_Count || ' trigger(s).');

END;

END CompileTrigger;
/