Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

CURSOR to recompile all INVALID objects

631898May 12 2008 — edited May 12 2008
Ok, I know I'm using the atler-compile pre-defined trigger here, and I'm cool with that.
I'm creating an anonymous block to recompile all INVALID objects. I've come up with a basic CURSOR to pull all INVALID objects into the cursor, and then using the alter_compile trigger to recomipile them.

However, I can't figure out how to call them all into the trigger...One of the inputs for the alter_compile trigger is the name of the object...hwo can I get around that?
DECLARE
 CURSOR compile_cur 
  IS
   SELECT object_name, status
    FROM user_objects
     WHERE status='INVALID';
BEGIN
 FOR rec_cur IN compile_cur LOOP
  DBMS_DDL.ALTER_COMPILE('PROCEDURE', 'SYSTEM','BB_JOBTEST');
 END LOOP;
END;
/
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jun 9 2008
Added on May 12 2008
5 comments
1,047 views