recompile invalid

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;

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.

Jiri Received on Thu Nov 01 2001 - 03:57:30 CST