You Asked
Hi Tom, How are You?
I was looking for a simple script to compile invalid objects. I found one in the archive, but it seemed to complex. So I tried to simplify, but no sucess.
There it is:
############################################################
--compile all invalid objects for a given schema
declare
strCmd varchar2(250);
intCmd integer;
begin
for cInvalidTriggers in
(
SELECT DECODE( object_type, 'PACKAGE BODY',
'ALTER PACKAGE ' || owner||'.'||object_name || ' COMPILE BODY;',
'ALTER ' || object_type || ' ' || owner||'.'||object_name || ' COMPILE;' ) as cmd_txt
FROM sys.dba_objects a, sys.order_object_by_dependency b
WHERE a.object_id = b.object_id(+)
AND status = 'INVALID'
AND owner=upper('&OWNER')
AND object_type IN ( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'VIEW' )
ORDER BY dlevel desc, object_type, object_name
)
loop
--getting the commando do be executed (compile the object)
strCmd := cInvalidTriggers.cmd_txt;
begin
-- executing...
execute immediate strCmd;
exception when others then dbms_output.put_line('ERRO: ' || SqlErrM);
end;
end loop;
end;
############################################################
But when I run, I receive the error:
ERROR at line 22:
ORA-06550: line 22, column 19:
PLS-00103: Encountered the symbol "IMMEDIATE" when expecting one of the following:
:= . ( @ % ;
I don't know what is going on. In fact I can't run *ANY* execute immediate (same error).
Could you help me? Could you also draw some comments about this simplified version of the compiling script?
Just one more: I've read, in one of your answers (classes fit better!) that each compiled object, compiles also its dependencies. Is it valid only for 8.1.7 and above? Because it seems not working for me!
Thanks in advance!
regards!
LZ
ps: really sorry for last time! My e-mail was misspelled!
and Tom said...
execute immediate is new with 8i (new being "relative" as 8i is pretty old itself)
create or replace procedure execute_immediate( p_sql in varchar2 )
is
cursor_name pls_integer default dbms_sql.open_cursor;
ignore pls_integer;
BEGIN
dbms_sql.parse(cursor_name, p_sql, dbms_sql.native);
ignore := dbms_sql.execute(cursor_name);
dbms_sql.close_cursor(cursor_name);
END;
/
works in 8.0.
I wouldn't even bother doing this tho, not worth it. That and you'll find that query to be one long one (if not in 8.0, it'll be in 8i and up for sure).
You might want to use my method of:
1) pick any invalid object you haven't compiled yet
2) compile it
3) remember you tried to compile it and goto 1)
you don't need to do them in any order.
the dependency thing works in all releases. It compiles objects IT IS DEPENDENT on (goes "up" the chain if you will). It does not compile objects that are DEPENDENT on it!
consider:
ops$tkyte@ORA806.WORLD> create or replace procedure p1
2 as
3 begin
4 null;
5 end;
6 /
Procedure created.
ops$tkyte@ORA806.WORLD> create or replace procedure p2
2 as
3 begin
4 p1;
5 end;
6 /
Procedure created.
ops$tkyte@ORA806.WORLD> create or replace procedure p3
2 as
3 begin
4 p2;
5 end;
6 /
Procedure created.
ops$tkyte@ORA806.WORLD>
ops$tkyte@ORA806.WORLD> alter procedure p1 compile
2 /
Procedure altered.
so, p3 depends on p2 depends on p1... compiling p1 invalidates p2 and p3
ops$tkyte@ORA806.WORLD> @invalid
ops$tkyte@ORA806.WORLD> break on object_type skip 1
ops$tkyte@ORA806.WORLD> column status format a10
ops$tkyte@ORA806.WORLD> select object_type, object_name, status
2 from user_objects
3 where status = 'INVALID'
4 order by object_type, object_name
5 /
OBJECT_TYPE OBJECT_NAME STATUS
--------------- ------------------------------ ----------
PROCEDURE P2 INVALID
P3 INVALID
ops$tkyte@ORA806.WORLD>
ops$tkyte@ORA806.WORLD> alter procedure p3 compile
2 /
Procedure altered.
ops$tkyte@ORA806.WORLD> @invalid
ops$tkyte@ORA806.WORLD> break on object_type skip 1
ops$tkyte@ORA806.WORLD> column status format a10
ops$tkyte@ORA806.WORLD> select object_type, object_name, status
2 from user_objects
3 where status = 'INVALID'
4 order by object_type, object_name
5 /
no rows selected
but compiling p3 recompiled p2 automagically -- p3 cannot compile if p2 is invalid, p2 cannot compile if p1 is invalid. If all three were invalid -- compiling p3 would have validated them ALL!
ops$tkyte@ORA806.WORLD>
ops$tkyte@ORA806.WORLD> alter procedure p1 compile
2 /
Procedure altered.
ops$tkyte@ORA806.WORLD> @invalid
ops$tkyte@ORA806.WORLD> break on object_type skip 1
ops$tkyte@ORA806.WORLD> column status format a10
ops$tkyte@ORA806.WORLD> select object_type, object_name, status
2 from user_objects
3 where status = 'INVALID'
4 order by object_type, object_name
5 /
OBJECT_TYPE OBJECT_NAME STATUS
--------------- ------------------------------ ----------
PROCEDURE P2 INVALID
P3 INVALID
ops$tkyte@ORA806.WORLD>
ops$tkyte@ORA806.WORLD> alter procedure p2 compile
2 /
Procedure altered.
ops$tkyte@ORA806.WORLD> @invalid
ops$tkyte@ORA806.WORLD> break on object_type skip 1
ops$tkyte@ORA806.WORLD> column status format a10
ops$tkyte@ORA806.WORLD> select object_type, object_name, status
2 from user_objects
3 where status = 'INVALID'
4 order by object_type, object_name
5 /
OBJECT_TYPE OBJECT_NAME STATUS
--------------- ------------------------------ ----------
PROCEDURE P3 INVALID
ops$tkyte@ORA806.WORLD>
but compiling p2 won't touch p3 -- doesn't need to so it doesn't
Rating
(1 rating)
Is this answer out of date? If it is, please let us know via a Comment