Problem
You want to execute some code each time a particular PL/SQL package is instantiated in a session.
Solution
Create an initialization block for the package in question. By doing so, you will have the ability to execute code each time the package is initialized. The following example shows the same package that was constructed in this article. However, this time the package contains an initialization block.
CREATE OR REPLACE PACKAGE BODY process_employee_time IS
PROCEDURE grant_raises (pct_increase IN NUMBER,
upper_bound IN NUMBER) as
CURSOR emp_cur is
SELECT employee_id, first_name, last_name
FROM employees;
BEGIN
-- loop through each record in the employees table
FOR emp_rec IN emp_cur LOOP
DBMS_OUTPUT.PUT_LINE(emp_rec.first_name || ' ' || emp_rec.last_name);
increase_wage(emp_rec.employee_id, pct_increase, upper_bound);
END LOOP;
END grant_raises;
PROCEDURE increase_wage (empno_in IN NUMBER,
pct_increase IN NUMBER,
upper_bound IN NUMBER) AS
emp_count NUMBER := 0;
emp_sal employees.salary%TYPE;
Results VARCHAR2(50);
BEGIN
SELECT count(*)
INTO emp_count
FROM employees
WHERE employee_id = empno_in;
IF emp_count > 0 THEN
-- IF EMPLOYEE FOUND, THEN OBTAIN RECORD
SELECT salary
INTO emp_sal
FROM employees
WHERE employee_id = empno_in;
IF emp_sal < upper_bound AND
round(emp_sal + (emp_sal * pct_increase), 2) < upper_bound THEN
UPDATE employees
SET salary = round(salary + (salary * pct_increase),2)
WHERE employee_id = empno_in;
results := 'SUCCESSFUL INCREASE';
ELSE
results := 'EMPLOYEE MAKES TOO MUCH, DECREASE RAISE PERCENTAGE';
END IF;
ELSE
Results := 'NO EMPLOYEE FOUND';
END IF;
DBMS_OUTPUT.PUT_LINE(results);
END increase_wage;
BEGIN
DBMS_OUTPUT.PUT_LINE('EXECUTING THE INITIALIZATION BLOCK');
END;
The initialization block in this example is the last code block within the package body. In this case, that block lies in the final three lines.
How It Works
The initialization block for the package in the solution displays a line of text to indicate that the initialization block has been executed. The initialization block will execute once per session, the first time the package is used in that session. If you were to create this package in your session and invoke one of its members, you would see the message print. Although an initialization message is not very useful, there are several good reasons to use an initialization block. One such reason is to perform a query to obtain some data for the session.