Initialization section of the package

Question

This is the package specification:

CREATE OR REPLACE PACKAGE employee_info
IS
  PROCEDURE p;

  FUNCTION f RETURN BOOLEAN;

END employee_info;

This is the package body:

CREATE OR REPLACE PACKAGE body employee_info
IS
  PROCEDURE p IS
  BEGIN
    dbms_output.put_line('This is procedure');
    dbms_output.put_line(chr(10));
  END;

  FUNCTION f RETURN BOOLEAN IS
  BEGIN
    dbms_output.put_line('This is function ');
    dbms_output.put_line(chr(10));
    RETURN true;
  END;

BEGIN
  dbms_output.put_line('This is the initialization section of package body');
END employee_info;

When should I use this initialization section of the package?

As in the above example, when I first execute the code then only i can see that begin..end of package body is executed and for rest of call it is not executed.

I am executing the procedure and function using below statements:

 execute employee_info.p;

    declare
      p1 boolean;
    begin
      p1 := employee_info.f;
    end;

Husqvik Husqvik · Accepted Answer · 2015-11-20T04:17:01

Package initialization section as the name suggest is executed when package is initialized. This happens when first procedure/function from the package is executed after session is established or after package is (re)compiled. The purpose is to initialize the global state of the package that can be used during session lifetime. All package global variables are kept and you can access them later.

Example:

HUSQVIK@hq_pdb_tcp> CREATE OR REPLACE PACKAGE test_package
  2  IS
  3     PROCEDURE foo;
  4  END;
  5  /

Package created.

HUSQVIK@hq_pdb_tcp> CREATE OR REPLACE PACKAGE BODY test_package
  2  IS
  3     PROCEDURE foo
  4     IS
  5     BEGIN
  6             DBMS_OUTPUT.PUT_LINE('Procedure executed. ');
  7     END;
  8
  9  BEGIN
 10     DBMS_OUTPUT.PUT_LINE('Package initialized. ');
 11  END;
 12  /

Package body created.

HUSQVIK@hq_pdb_tcp> EXEC test_package.foo
Package initialized.
Procedure executed.

PL/SQL procedure successfully completed.

HUSQVIK@hq_pdb_tcp> EXEC test_package.foo
Procedure executed.

PL/SQL procedure successfully completed.

HUSQVIK@hq_pdb_tcp>

You see that after package is compiled the initialization section is executed when procedure foo is executed. The package is initialized now. Any subsequent execution of foo executes only the procedure.

Initialization section of the package

1 Answers