From kgel@ppoone.com Thu, 14 Jun 2001 09:12:00 -0700 From: Kevin Lange Date: Thu, 14 Jun 2001 09:12:00 -0700 Subject: RE: Invalid views Message-ID: MIME-Version: 1.0 Content-Type: text/plain Joe;   For those of us not on 8.1.7 ......    Besides .. I had no idea that was there. -----Original Message-----From: JOE TESTA [mailto:JTESTA@longaberger.com]Sent: Thursday, June 14, 2001 10:55 AMTo: ORACLE-L@fatcity.com; kgel@ppoone.comSubject: RE: Invalid views Why not just use(8.1.7 thing)   $ORACLE_HOME/rdbms/admin/utlrp.sql   joe         >>> kgel@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@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@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: http://www.orafaq.com--Author: Kevin Lange  INET: kgel@ppoone.comFat 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@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).