<FONT
size=1>
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: <A
href="
http://www.orafaq.com">
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:
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
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).
Received on Thu Jun 14 2001 - 11:23:35 CDT
