Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: recompile invalid

Re: recompile invalid

From: Sergey M <msu_at_pronto.msk.ru>
Date: Thu, 1 Nov 2001 14:44:44 +0300
Message-ID: <9rrcjm$21aj$1@serv2.vsi.ru>

"Feli" <jife_at_ementor.no> сообщил/сообщила в новостях следующее: news:286d2b8b.0111010157.33b54e76_at_posting.google.com...
> Hello to everybody
> I am having problem that is for sure very known. When I install all my
> db objects (packages, views, triggers) I have troubles with getting
> them in VALID status. I try recompile_invalid procedure:
> DECLARE
> -- col object_name form a30
> CURSOR invalid_obj IS
> select object_name,object_type,created,status from user_objects
> where status='INVALID' order by 2,1;
>
> command VARCHAR2(255);
> err VARCHAR2(300);
> ddl_ok BOOLEAN;
> cursor_id INTEGER;
>
>
> BEGIN
> FOR inv_obj IN invalid_obj LOOP
> BEGIN
> IF inv_obj.object_type = 'TRIGGER' THEN
> command := 'alter trigger '||inv_obj.object_name||' compile';
> ddl_ok := TRUE;
> ELSIF inv_obj.object_type = 'VIEW' THEN
> command := 'alter view '||inv_obj.object_name||' compile';
> ddl_ok := TRUE;
> ELSIF inv_obj.object_type like 'PACKAGE%' THEN
> command := 'alter package '||inv_obj.object_name||' compile';
> ddl_ok := TRUE;
> ELSIF inv_obj.object_type = 'FUNCTION' THEN
> command := 'alter function '||inv_obj.object_name||' compile';
> ddl_ok := TRUE;
> ELSIF inv_obj.object_type = 'PROCEDURE' THEN
> command := 'alter procedure '||inv_obj.object_name||' compile';
> ddl_ok := TRUE;
> ELSE
> ddl_ok := FALSE;
> END IF;
>
> IF ddl_ok = TRUE THEN
> cursor_id := dbms_sql.open_cursor;
> dbms_sql.parse(cursor_id,command,dbms_sql.v7);
> dbms_sql.close_cursor(cursor_id);
> END IF;
>
> EXCEPTION
> /* If an exception is raised, close cursor before exiting. */
> WHEN OTHERS THEN
> DBMS_SQL.CLOSE_CURSOR(cursor_id);
> err := 'Error parsing :'||command||' !!';
> dbms_output.put_line(err);
> -- RAISE;
> END;
> END LOOP;
> EXCEPTION
> WHEN OTHERS THEN
> RAISE;
> END;
> /
>
> col object_name form a30
> select object_name,object_type,created,status from user_objects
> where status='INVALID' order by 2,1;
>
> But the result of it that there are still some invalid objects. When I
> run it once more sometimes there are more, sometimes there are less
> invalid objects in the database. I don't know how to ensure that all
> objects are OK (teoretically they should be). I ask in this group
> before I will start my attempt to manage the db objects installation
> process in a defined order (this would be very complicated, I think) -
> now we just start objects in a random order (alphabeticaly).
> Of course that my views use packages and maybe some packages use views
> (but no circular reference view - package). Is there another possible
> method (built-in) function that could do the same?
>
> Thanks for your help.
>

Package DBMS_UTILITY:
 DBMS_UTILITY.COMPILE_SCHEMA (schema VARCHAR2);

Sergey M. Received on Thu Nov 01 2001 - 05:44:44 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US