Query/Procedure to recompile all views and triggers

[jonbrown]

I need to construct a procedure to recompile all views and triggers in an oracle 8i database.
I plan to use a cursor to loop through the USER_VIEWS table, get the view_name and substitute it in the following statement: ALTER VIEW :viewname COMPILE

The problem is that the procedure compilation falis when calling the ALTER.

Does anybody have any suggestions?

Thanks

 

[KeepItSimpleEngr]

Hi....

DBMS_UTILITY.COMPILE_SCHEMA (schema VARCHAR2);

will recompile the schema.

See...

http://otn.oracle.com/docs/products...17_doc/appdev.817/a76936/dbms_ut2.htm#1002085

Hope this helps. Good luck.

 

[rtefft]

I have found that COMPILE_SCHEMA can crash when certain modules are inter-related. Apparently it uses a CONNECT_ BY...PRIOR recursive query to build a module hierarchy, and some combinations cause "ORA-01436 CONNECT BY loop in user data". To get around this, I build a simple PL/SQL widget to make 3 passes through the objects, compiling any invalid ones as it goes. On the final pass, it will list any which are still invalid. I've found it works well enough, though I'm sure it's not perfect. Hope it helps.

Rich

CREATE OR REPLACE procedure COMPILE_SCHEMA is
  --
  --  COMPILE_SCHEMA   7/17/01  R. Tefft
  --
  --  This procedure compiles all invalid PL/SQL objects in the schema.
  --  A common approach is to use a hierarchical query to compile them
  --  in order of dependencies, but this can error out if any mutually-dependent
  --  objects are found.  Instead, we just the blunt-force approach and make
  --  3 passes, re-checking for invalid objects again with each pass.
  --
  cursor OBJECTS is
  select object_name, object_type, status
  from USER_OBJECTS
  where object_type in ('PACKAGE', 'PACKAGE BODY','VIEW','PROCEDURE','FUNCTION','TRIGGER')
    and object_name != 'COMPILE_SCHEMA'
    and status = 'INVALID'
  order by object_name;
  --
  cmd            varchar2(300);
  msg            varchar2(2000);
  COMPILE_ERROR  exception;
  pragma EXCEPTION_INIT (COMPILE_ERROR, -24344);
begin
  dbms_output.enable(1000000);
  --
  --  We will make 3 passes, to cover dependencies.
  --
  for x in 1..3 loop
    --
    dbms_output.put_line('Pass '||ltrim(to_char(x))||'...');
    --
    for o in objects loop
      if (o.object_type in ('PACKAGE', 'PACKAGE BODY')) then
        cmd := 'alter package '||o.object_name||' compile package';
      else
        cmd := 'alter '||o.object_type||' '||o.object_name||' compile';
      end if;
      begin
        execute immediate cmd;
        msg := rpad('Compiled '||lower(o.object_type),25)||o.object_name;
      exception 
        when COMPILE_ERROR then
          msg := rpad('Compiled '||lower(o.object_type),25)||rpad(o.object_name,35)||' COMPILE ERROR';        
        when OTHERS then
          msg := rpad('Compiled '||lower(o.object_type),25)||rpad(o.object_name,35)||sqlerrm;
      end;
      dbms_output.put_line(rtrim(msg));
    end loop;
  end loop;
end COMPILE_SCHEMA;
/

____________________________
Rich Tefft
PL/SQL Programmer

 

[gansen]

Try this
in sqlplus do

set heading off
set feedback off
set pagesize 0
spool alter_view
select 'alter view ', view_name, ' compile',';'
from user_views;
spool off
@alter_view.lst

do the same for user_triggers