>>> [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).
