When doing create or replace procedure, all other proc. func. that refer to this proc will become invalid and needs to be recomplied. Could someone explain what this really mean, and give an simple example as to how to actually recompile the other proc and functions? Thanks.
Why not you put the relevent procedures and functions under a package and compile the package. When you do the compilation of procedures individually, then there is a possiblitity that you might loose the dependency. Best is to use the package.
From a "computer science" point of view, PL/SQL is an interpreted language. From what I understand, when Oracle says that the code is "compiled", what they mean is that the text is parsed and goes through lexical and semantic analysis (what big words!). This analysis is primarily to verify referenced objects (either tables, variables or procedures/functions) against the data dictionary, then store that validated code in the shared pool (at least I think it's the shared pool). It does not generate intermediate or target binary executables (such as happens when you compile a non-interpreted language like C, FORTRAN, Pascal, etc.).
IIRC, Oracle will automaticallly attempt to "recompile" procedures/functions that are not "valid" when they are invoked. The main impact is that it takes a finite amount of time, which you don't want to occur at runtime. One way around this to to implement a pseudo "makefile" that will rebuild all of your related procedures/functions in the proper order. You could probably also generate this makefile dynamically by querying the objects with an "invalid" status.
Hope this helps more than confuses.
Thanks for the explaination. So can we say if compilation time is not a major concern, I can just let oracle to do the compilation whenever the invalidated proc/func is called. I need to do alter procedure proc_name compile; manually only when I want to do the complile job before it is called?