Re: Recompile invalid prcedures -- help needed

From: Tom Zamani <tomz_at_redflex.com.au>
Date: Tue, 4 Jan 2000 10:15:00 +1100
Message-ID: <84racd$o54$1@perki.connect.com.au>

What you have done is compling all the procedures, which is not correct,. You need to compile only the invalid procedure (where status ='INVALID') Objects are dependent on each other so if you compile one some other objects will become invalid.
but if you only comile invalid objects after few times runing you procedure all become valid.

have a look at this procedure.
Tom

PROCEDURE compile_invalid_objects
  iS

     cursor_name INTEGER;
     rows_processed INTEGER;

 BEGIN
 for invalids in (select object_name,object_type from all_objects   where UPPER(owner)='name'
  AND object_TYPE = 'PACKAGE BODY'
  AND STATUS='INVALID')
 LOOP
 cursor_name := dbms_sql.open_cursor;
 --dbms_output.put_line('ALTER PACKAGE '||INVALIDS.OBJECT_NAME||' COMPILE');  dbms_sql.parse(cursor_name,
  'ALTER PACKAGE '||INVALIDS.OBJECT_NAME||' COMPILE',

 dbms_sql.NATIVE);
 rows_processed := dbms_sql.execute(cursor_name);
 dbms_sql.close_cursor(cursor_name);

 end loop;
 EXCEPTION
 WHEN OTHERS THEN
 --null;

     dbms_sql.close_cursor(cursor_name);  END; Dr Yufan Hu <yufan_at_rightiming.com> wrote in message news:84r88a$bjh$1_at_news.rightiming.com...
> Hello,
>
> From Oracle Schema Manager I noticed from time to time that some of the
> stored procedures and Packages became invalid. Although I can recompile
> these invalid procedures from the Schema Manager by click one procedure
> after another, I would like to find out how I can do it through SQL.
> Executing the output of following SQL statement:
>
> select unique 'ALTER PROCEDURE ' || NAME || ' COMPILE;' from user_source
> where TYPE='PROCEDURE';
>
> does not seem to work. It creates more invalide procedures.
>
> Any help greately appreciated.
>
> Yufan
>
Received on Mon Jan 03 2000 - 17:15:00 CST