Avatar of chinmaya224
chinmaya224Flag for India asked on

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
Oracle Database

Avatar of undefined
Last Comment
chinmaya224

8/22/2022 - Mon
johnsone

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
ASKER
chinmaya224

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 ?
ASKER CERTIFIED SOLUTION
johnsone

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
chinmaya224

Thanks for your clarification ...
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy