Gurus,
I had faced a strange issue and am trying to understand more details or internals of Oracle Package and/or Procedure in Oracle Database.
Issue: One of the custom package was showing in 'valid' status in DBA_OBJECTS but received error when the procedure within the package are called.
Fix: I had to manually compile the package body using alter package XYZ compile body; Once I executed this, everything was working as normal.
Questions:
1. What happens when you compile a package or procedure in Oracle Database?
2. What is storied in Oracle Database and how the package is called in the DB memory?
3. I usually use OH/rdbms/admin/utlrp.sql to compile invalid objects. What is the difference between utlrp.sql and explicitly compiling a package with -alter package XYZ compile body;? Is there any table/view that can show if an object is compiled explicitly or using utlrp?
Appreciate your experience and knowledge in trying to understand in detail about Oracle Packages and compilation. I have been trying to use this a reference: http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/alter_package.htm#LNPLS99998 But more in depth knowledge will help me understand more.
Thanks
Cherrish Vaidiyan