PL/SQL (MOSC)

MOSC Banner

compile invalid object in on go and delete

edited Dec 19, 2012 10:27PM in PL/SQL (MOSC) 12 commentsAnswered
Hello,

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.


SET SERVEROUTPUT ON SIZE 1000000
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';

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center