Originally posted by aarroyob
Hi all,

I little question for your. You know sometimes in the database are invalid objects, and when you make alter .... compile; this objects continue invalid.

My question:

Does anybody know where Oracle store the sql code definition for this invalid objects?

Thanks and best regards

Angel
PROCEDURE RECOMPILE IS
cur Number;
I Number;
sqlpac_cur integer;
begin
I := 0;
loop -- infinite
select curnt(*) into cur
from User_Objects
where object_type in ('FUNCTION','PROCEDURE','TRIGGER','PACKAGE BODY','PACKAGE','VIEW')
and object_name!='RECOMPILE'
and STATUS = 'INVALID';
If cur = 0 OR I > 10 then return; end if;
I := I +1;
for FPCur in (select distinct object_name,object_type
from user_objects
where object_type in ('FUNCTION','PROCEDURE','TRIGGER','PACKAGE BODY','PACKAGE','VIEW')
and object_name!='RECOMPILE'
and STATUS = 'INVALID'
order by object_type desc
) Loop
begin
sqlpac_cur:=dbms_sql.open_cursor;
if FPCur.Object_type = 'PACKAGE BODY' then FPCur.Object_type := 'PACKAGE'; end if;
dbms_sql.parse(sqlpac_cur,'alter '||FPCur.Object_type||' '||FPCur.object_name||' compile',dbms_sql.v7);
dbms_output.put_line('processed: '||FPCur.Object_type||' '||FPCur.object_name);
dbms_sql.close_cursor(sqlpac_cur);
EXCEPTION
WHEN OTHERS THEN
dbms_sql.close_cursor(sqlpac_cur);
dbms_output.put_line('failed: '||FPCur.object_type||' '||FPCur.object_name||' '||sqlerrm);
end;
end loop;
end loop;

end;