stored procedure become invalid

What would cause a stored procedure to become invalid if I did not upgrade, install patchset, or run catolag?

Thanks!

Any change on any object that the procedure is depending on (any table, view, function, etc...) would cuse the procedure to become invalid. But it will recompile automatcaly the first time it is used again, so you can ignore this behavior.

it script generate all objects invalid in your database:

rem *********************************************************
rem * file: comp_all.sql
rem * purpose: compile all database stored objects
rem * to use: log in using the appropriate account then
rem * execute this script using the following syntax:
rem *
rem * SQL> @comp_all
rem *
rem * NOTE: You should not have to run this script more
rem * than once since it uses
rem * order_object_by_dependency table to compile
rem * objects in the proper order. Any
rem * compilation errors generated should be
rem * investigated.
rem *********************************************************
rem ====================== script for 8.1.5 =========================
rem If the above script fails when run on 8.1.5 with:
rem ERROR at line 1:
rem ORA-01436: CONNECT BY loop in user data.
rem This is due to [BUG:895238] fixed in 8.1.7.
rem
rem Use the following modification instead. It will run successfully, rem but it will
rem not order by dependencies:
rem References:
rem ===========
rem [NOTE:1014072.102] SCRIPT TO FIND INVALID OBJECTS IN THE DATABASE
rem
rem ============================================================
set echo off
set heading off
set pagesize 0
set linesize 79
set verify off
set recsep off
spool /tmp/compile_all.sql
select
decode( OBJECT_TYPE, 'PACKAGE BODY',
'alter package ' || OWNER||'.'||OBJECT_NAME || ' compile body;',
'alter ' || OBJECT_TYPE || ' ' || OWNER||'.'||OBJECT_NAME || ' compile;' )
from
dba_objects
where
STATUS = 'INVALID' and OBJECT_TYPE in
( 'PACKAGE BODY', 'PACKAGE', 'FUNCTION', 'PROCEDURE', 'TRIGGER', 'VIEW' )
order by OWNER, OBJECT_TYPE, OBJECT_NAME;
spool off



CZULU
--------
Senior DBA Oracle

Jmodic:

Thank you! I appreciate it!

Lisa

How come sometimes they do not recompile automatically?

Thanks!

Quote:

---

Originally posted by lgaorcl
How come sometimes they do not recompile automatically?

---

It can be either:

a) PL/SQL procedure has errors in it, so it won't compile even if you manually recompile it

b) It did recompile automaticaly, but after that your aplication could couse some other procedure or function to recompile automaticaly which in turn invalidated your first procedure again, according to dependancy tree

c) If neither of the above two options are true in your case, you must provide us some more details about the circumstances in which you encountered that behavior.

I remembered once we had an isue with one of our Delphi applications, which consistantly compaining about an invalid function, but to my amasement the function did not recompile automaticaly when invoked by the aplication (I'm not a Delphi programer, so I have not had a clue about what could be causing this). Only after ve recompiled the function manually the application worked again, but only untill something made that function invalid again. At the end it turned out that in Delphi you can set some switch (or object attribute or something) which prevent automatic recompilation of stored database code. Once we set that switch back to the default setting everithing went back to normal.