Need clarification on PL/SQL Package

I have some doubt on PL/SQL Package. Please explain me, need your help for the below.

Please check the below program:
CREATE OR REPLACE PACKAGE valerr
IS
   FUNCTION private_variable RETURN VARCHAR2;
END valerr;
/

CREATE OR REPLACE PACKAGE BODY valerr
IS
   g_private   VARCHAR2 (1);

   FUNCTION private_variable
      RETURN VARCHAR2
   IS
   BEGIN
      RETURN g_private;
   END private_variable;

   PROCEDURE initialize
   IS
   BEGIN
      DBMS_OUTPUT.put_line ('Before I show you v...');
      g_private := 'abc';
   EXCEPTION
      WHEN OTHERS
      THEN
         DBMS_OUTPUT.put_line ('Trapped the error!');
   END initialize;
BEGIN
   initialize;
END valerr;
/

Now Please explain me on my below points:
1. When i am login through a new session, only specification will be loaded in SGA or both body or specification or specification and Declarative part of body.
2. When the body will be loaded.
3. What is the internal process of Oracle when we are executing a procedure of a package.

Thanks in advance
chinmaya224Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

johnsoneSenior Oracle DBACommented:
Neither the header or the body will be loaded until a function within the package is executed.

http://docs.oracle.com/cd/E11882_01/server.112/e40540/memory.htm#sthref1763
0
chinmaya224Author Commented:
it means that when ever we will call a procedure of a package then only it will be loaded into the memory.

So suppose if i am executing the procedure of a package second time then it will execute the procedure which is already loaded into the memory.

@johnsone - wheather the specification is also loaded/intialized when we execute the Procedure ?
0
johnsoneSenior Oracle DBACommented:
On the second run, it may or may not need to be loaded into memory.  If it has been aged out, it would need to be loaded again, otherwise, it would use what was already there.

Specification gets loaded first to check the package then the body.  I believe that if you are referencing something that only exists in the header they body would not be loaded until needed.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
chinmaya224Author Commented:
Thanks for your clarification ...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.