troubleshooting Question

Need clarification on PL/SQL Package

Avatar of chinmaya224
chinmaya224Flag for India asked on
Oracle Database
4 Comments1 Solution389 ViewsLast Modified:
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
Join the community to see this answer!
Join our exclusive community to see this answer & millions of others.
Unlock 1 Answer and 4 Comments.
Join the Community
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 1 Answer and 4 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros