RE: Invalid views

From: Kevin Lange <kgel_at_ppoone.com>
Date: Thu, 14 Jun 2001 09:12:00 -0700
Message-ID: <F001.00329BB9.20010614091647@fatcity.com>

Joe;
For those of us not on
8.1.7 ......

Besides .. I had no idea that
was there.

  <FONT face=Tahoma
  size=2>-----Original Message-----From: JOE TESTA   [mailto:JTESTA_at_longaberger.com]Sent: Thursday, June 14, 2001 10:55   AMTo: ORACLE-L_at_fatcity.com; kgel_at_ppoone.comSubject: RE:   Invalid views
  Why not just use(8.1.7 thing)

  $ORACLE_HOME/rdbms/admin/utlrp.sql

  joe

  >>> kgel_at_ppoone.com 06/14/01 11:47AM >>>Perfect   timing. I was putting togeter some recompile scripts for work.Here   is a modified one that can be used for views.   With a few changes   itcan be used to recompile any invalid object.1. Create a   cursor on the Catalog Table ALL_OBJECTS where OBJECT_TYPE ='VIEW' and   STATUS = 'INVALID'.2. Step thru that Cursor and use the DBMS_SQL calls to   compile. create or replace Procedure Recompile_Views   Is    Cursor cur_objects

  Is      Select 
  owner      
  ,             
  object_name      From 
  dba_objects      Where object_type = 
  'VIEW'        and status = 
  'INVALID';    current_cursor

  Integer;    dummy_var
  Integer;    cur_objects_rec
  cur_objects%ROWTYPE;
  Begin
  dbms_output.enable(20000);      For
  cur_objects_rec In cur_objects
  Loopdbms_output.put_line(cur_objects_rec.owner||'.'||cur_objects_rec.object_name);          Begin          current_cursor
  :=
  DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE (current_cursor, 'ALTER   VIEW'||cur_objects_rec.owner||'.'||cur_objects_rec.object_name||'compile',DBMS_SQL.NATIVE);            dummy_var := DBMS_SQL.EXECUTE
  (current_cursor);
  DBMS_SQL.CLOSE_CURSOR(current_cursor);

  Exception          When 
  Others          
  Then

  DBMS_SQL.CLOSE_CURSOR(current_cursor);              dbms_output.put_line('Unable to
  compile'||cur_objects_rec.owner||'.'||cur_objects_rec.object_name);          End;      End Loop;
  End;This code does work, you just have to make sure you have the   correctauthority on DBA_OBJECTS.   Make sure the compile is   granted select directlyon DBA_OBJECTS by   SYS.Kevin-----Original Message-----Sent: Thursday,   June 14, 2001 2:36 AMTo: Multiple recipients of list   ORACLE-LHallo all you DBA'sCan anyone help me with   this?I would like to check if some views ar einvalid and then if there   are Iwould like them to be compiled immediately .How can I write this   in a pl/sql-procedure?Please give me some good examples, all you   experts!Roland Sköldblom-- Please see the official   ORACLE-L FAQ: http://www.orafaq.com--   Author: INET: Roland.Skoldblom_at_ica.seFat City Network   Services    -- (858) 538-5051 FAX: (858) 538-5051San   Diego, California        -- Public Internet   access / Mailing

  Lists--------------------------------------------------------------------To

REMOVE yourself from this mailing list, send an E-Mail messageto: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and inthe message BODY, include a line containing: UNSUB ORACLE-L(or the name of mailing list you want to be removed from). You mayalso send the HELP command for other information (like subscribing).--Please see the official ORACLE-L FAQ: <A
href="http://www.orafaq.com">http://www.orafaq.com--Author: Kevin Lange INET: kgel_at_ppoone.comFat City Network Services -- (858) 538-5051 FAX: (858) 538-5051San Diego, California -- Public Internet access / Mailing

  Lists--------------------------------------------------------------------To