Re: Stored objects compilation

From: Yann Chevriaux <chevriaux_at_theleme.com>
Date: Wed, 29 Dec 1999 15:18:34 +0100
Message-ID: <84d547$s55$1@wanadoo.fr>

Many thanks Alain,

  There is what I did, you can use it .. and ameliorate !

I just compile any INVALID objects until no one is validated. (some objects can be really INVALID !!)

I don't use DBMS_UTILITY.COMPILE_ALL because this procedure don't compile VIEWS. Yann.

DECLARE
 cursor c_invalid is
  select 'alter '||object_type||' '||object_name||' compile' stat     from obj
    where object_type in ('FUNCTION','PROCEDURE','PACKAGE','TRIGGER','VIEW')     and status='INVALID';
  cursorHandle INTEGER;
 lineCount integer;
 linePrevious integer;
BEGIN

• Loop until nothing new successfully compilated or nothing left to compile

 linePrevious := 0;
 loop
   lineCount := 0;
   for v in c_invalid loop

     dbms_output.put_line(v.stat);
     cursorHandle := DBMS_SQL.OPEN_CURSOR;

    DBMS_SQL.PARSE(cursorHandle, v.stat, DBMS_SQL.V7);     DBMS_SQL.CLOSE_CURSOR(cursorHandle);     lineCount := lineCount+1;
  end loop;
  if lineCount=0 or linePrevious=lineCount then     dbms_output.put_line('lines: '||to_char(lineCount)||' previous: '||to_char(linePrevious));

    exit;
  end if;
  linePrevious := lineCount;
 end loop;

EXCEPTION
  WHEN OTHERS THEN
   if DBMS_SQL.IS_OPEN(cursorHandle) then     DBMS_SQL.CLOSE_CURSOR(cursorHandle);    end if;
  if c_invalid%isopen then
    close c_invalid;
   end if;
  dbms_output.put_line('ERREUR');
 raise;
END; Alain Martinez a écrit dans le message <3869FFF2.9747E7CB_at_steria.fr>...
>Je ne connais pas cette commande.
>Par contre tu peux te générer les commandes de compil' automatiquement :
>select 'alter procedure '||object_name||' compile;' from user_objects
>where object_type = 'PROCEDURE' and status='INVALID';
>Et pareil pour les fonctions.
>
>
>Yann Chevriaux a écrit :
>
>> Hi.
>> I've read in 'Oracle7 Server Release 7.3 Utilities' that some 'COMPILE
ALL'
>> command should exist.
>> Well, I just need something like that to recompile over hundreds objects
I
>> got from an import.
>>
>> But I can't find any further doccumentation on such a command.
>>
>> Could any one help me?
>>
>> Many thanks
>>
>> Yann.
>
Received on Wed Dec 29 1999 - 08:18:34 CST