Recompiling stored procedures after table chamges 1

[newora]

Could somebody please help me with something that I have just come across and I feel that I am missing a major issue.

I have just changed a database table structure and I forgot that some stored procedures that I have on the database use this modified table.

To be honest I forgot that the stored procedures used this table, but even if I had remembered the only way that I know for the stored procedure to work again is to reload it via a .sql file (using SQL plus) with has the create or replace procedure line at the top.

Is there not any way of recompiling the stored procedure, without using the sql file with the create or replace statement in it.

Thanks for any advise.

 

[SantaMufasa]

Sure, NewOra...Here is my "recompile.sql" that finds all INVALID objects that the current user owns, then creates a script that recompiles the INVALID objects when you are ready:

set pagesize 0
set feedback off
set echo off
spool temp.sql
select 'alter '||object_type||' '||object_name||' compile;'
from user_objects
where status = 'INVALID' and object_type not like '%BODY%'
/
spool off
set feedback on
prompt 
prompt Created script, 'temp.sql'. Run that script to recompile Invalid objects.
prompt

alter PROCEDURE GET_USER_CNT compile;
alter VIEW ORGCHART compile;
alter PROCEDURE SHOWBYROWID compile;
alter FUNCTION STRINGEMPLOYEES compile;
********************************************************************************

Let us know how this works for you.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]

 

[newora]

Thats's absolutely great SantaMufasa, works a treat.

I have not found that in any of the Oracle books that I have.

Regards,

Newora