rem ************************************************************* rem * Name : COMPILE.SQL rem * rem * Date: July 15 2001 rem * Purpose: Compile invalid objects rem * rem * Downloaded from www.oradev.com rem ************************************************************* set echo off prompt These objects were invalid: prompt ... select object_name from user_objects where status = 'INVALID' / declare cursor c_not_valid is select uo.* from user_objects uo where uo.status != 'VALID' and object_type IN ('PROCEDURE', 'FUNCTION', 'PACKAGE', 'TRIGGER', 'VIEW', 'PACKAGE BODY') ; cur integer; result integer; vorig_not_valid_aant integer; not_valid_aant integer := 0; alter_string varchar2(200); begin select count(*) into vorig_not_valid_aant from user_objects where status = 'INVALID'; while not_valid_aant <> vorig_not_valid_aant loop begin vorig_not_valid_aant := not_valid_aant; not_valid_aant := 0; for c_rec in c_not_valid loop begin alter_string := 'ALTER '; if c_rec.object_type = 'PACKAGE BODY' then alter_string := alter_string||'PACKAGE'; else alter_string := alter_string||c_rec.object_type; end if; alter_string := alter_string||' '||c_rec.object_name|| ' COMPILE'; if c_rec.object_type = 'PACKAGE BODY' then alter_string := alter_string||' BODY'; end if; cur := DBMS_SQL.OPEN_CURSOR; DBMS_SQL.PARSE(cur, alter_string,DBMS_SQL.V7); result := DBMS_SQL.EXECUTE(cur); DBMS_SQL.CLOSE_CURSOR(cur); exception when others then null; end; not_valid_aant := not_valid_aant + 1; end loop; end; end loop; end; / prompt These objects are still invalid: prompt ... select object_name from user_objects where status = 'INVALID' / rem ************************************************************* rem * End of COMPILE.SQl rem *************************************************************