compile invalid object in on go and delete
Hello,
SET SERVEROUTPUT ON SIZE 1000000
I want write PL/SQL block. that compile all invalid objects from all database users. here i wrote one block. but it automatically goes into exception.
requirement:
PL/SQL block or procedure :
1. recompile of all invalid db objects from all database users.
2. create spool file for rest of invalid objects separately user wise after recompile done.
3. remove all invalid objects from all db users.
BEGIN
FOR cur_rec IN (SELECT owner, object_name, object_type FROM dba_objects WHERE object_type IN ('FUNCTION','PROCEDURE','VIEW') AND status != 'VALID')
LOOP
BEGIN
EXECUTE IMMEDIATE 'ALTER ' || cur_rec.object_type ||' "' || cur_rec.owner || '"."' || cur_rec.object_name || '" COMPILE';
0