Re: recompiling store proc..

From: eugen <eugennyffeler_at_netscape.net>
Date: Tue, 18 Apr 2000 22:40:06 +0200
Message-ID: <38FCC826.C015F6E0_at_netscape.net>

I'm not shure if the dbms_utility.compile_schema() was intorduced in Oracle 7.3.
If not use a small PL/SQL Programm which makes more or less following (please check
the syntax because im doing it without any db and manual)

declare
  v_msg varchar2(2000);
  v_crs integer;
begin
  v_crs := dbms_sql.open_cursor();
  for crs in (select owner, object_type, object_name

                 from dba_objects
                 where object_type in ('PACKAGE', 'PROCEDURE', 'FUNCTION',
'PACKAGE BODY')
                 and status <> 'VALID')

  loop
    v_msg := 'alter '|| crs.object_type||' '|| crs.owner||'.'||object_name|| ' compile';

   dbms_sql.parse(v_crs, v_msg, dbms_sql.native);   end loop;
  dbms_sql.close_cursor(v_crs);
END;
/

Let it run under a DBA account.

hth
eugen

"Sébastien Jean" wrote:

> Is there a way to recompile 150 store proc on the Oracle server 7.3?..
>
> instead of selected (one by one) all the stored proc, and then right click
> dans select Quick edit,.. and then... Recompile??????
>
> thanks a lot!
Received on Tue Apr 18 2000 - 22:40:06 CEST