RE: Invalid views

[JOE TESTA]

Why not just use(8.1.7 thing)   $ORACLE_HOME/rdbms/admin/utlrp.sql   joe         >>> [EMAIL PROTECTED] 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 it can 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       Loop dbms_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 correct authority on DBA_OBJECTS.   Make sure the compile is granted select directly on DBA_OBJECTS by SYS. Kevin -----Original Message----- Sent: Thursday, June 14, 2001 2:36 AM To: Multiple recipients of list ORACLE-L Hallo all you DBA's Can anyone help me with this? I would like to check if some views ar einvalid and then if there are I would 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: [EMAIL PROTECTED] Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051 San Diego, California        -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from).  You may also send the HELP command for other information (like subscribing). -- Please see the official ORACLE-L FAQ: http://www.orafaq.com -- Author: Kevin Lange   INET: [EMAIL PROTECTED] Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051 San Diego, California        -- Public Internet access / Mailing Lists -------------------------------------------------------------------- To REMOVE yourself from this mailing list, send an E-Mail message to: [EMAIL PROTECTED] (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from).  You may also send the HELP command for other information (like subscribing).