PL/SQL Packages

Introduction to Packages

• A package is a way to group related PL/SQL objects (functions, procedures, types, variables, cursors, exceptions, etc.) together.

• Helps in organizing objects within a schema, making them easier to find and maintain.

• Prevents clutter and improves manageability, especially in large projects where multiple teams work on the same schema.

Why Use Packages?

(i) Logical Grouping of Objects

• Related subprograms and data structures can be grouped together.

• Makes it easier to find, modify, and maintain objects.

• Supports encapsulation, allowing some objects to be private and only accessible within the package.

(ii) Performance Optimization (Main Benefit)

• Without Packages:

  • Each function, procedure, or type is stored in database storage.

  • When called, it is loaded into the Program Global Area (PGA), which is private for each user.

  • This leads to excessive memory usage, as each user loads their own copy into their respective PGAs.

• With Packages:

  • The first time an object from a package is called, the entire package is loaded into the System Global Area (SGA) (shared memory).

  • Subsequent calls by any user run from the SGA, avoiding repetitive loading into PGAs.

  • Benefits:

    • Reduces unnecessary memory consumption.

    • Minimizes I/O operations, improving performance significantly.

    • Avoids redundant object loading, leading to faster execution.

Key Features of Packages

(i) Public and Private Members

• Objects in the package specification are public and accessible to other users.

• Objects in the package body are private and cannot be accessed directly.

• This ensures encapsulation and security of implementation details.

(ii) Persistent Variables and Cursors

• Package variables exist throughout a session once declared.

• This enables the creation of session-specific global variables.

• Unlike normal PL/SQL variables, these do not reset after each procedure call.

(iii) Code Reusability & Overloading

• Overloading subprograms (defining multiple procedures/functions with the same name but different parameters) is more efficient in packages.

• Frequently used logic can be centralized, making maintenance easier.

(iv) Security and Encryption

• Package body can be encrypted to prevent unauthorized access to the code.

• Only the package specification is visible to other users.

Structure of a Package

A package consists of two parts:

1. Package Specification (Spec) - Declares the objects available for use.

2. Package Body - Contains the implementation of the declared objects.

This separation helps manage public and private objects in Oracle since Oracle does not have public/private identifiers by default.

Package Specification

• Declares functions, procedures, variables, cursors, etc.

• Makes them public and accessible to other users.

• No BEGIN-END block for procedures or functions.

• Objects declared here are visible to others.

• If a package spec exists without subprograms, no package body is needed.

CREATE OR REPLACE PACKAGE emp AS
    v_salary_increase_rate NUMBER := 0.057;
    CURSOR cur_emps IS SELECT * FROM employees;
    PROCEDURE increase_salaries;
    FUNCTION get_avg_sal(p_dept_id INT) RETURN NUMBER;
END emp;

Package Body

• Implements functions and procedures declared in the specification.

• Can contain additional private objects (not declared in the spec).

• The package body must have the same name as the package spec.

• Objects not declared in the spec are private and can only be used inside the package body.

CREATE OR REPLACE PACKAGE BODY emp AS
    PROCEDURE increase_salaries IS
    BEGIN
        FOR r1 IN cur_emps LOOP
            UPDATE employees_copy
            SET salary = salary + salary * v_salary_increase_rate;
        END LOOP;
    END increase_salaries;

    FUNCTION get_avg_sal(p_dept_id INT) RETURN NUMBER IS
        v_avg_sal NUMBER := 0;
    BEGIN
        SELECT AVG(salary) INTO v_avg_sal
        FROM employees_copy
        WHERE department_id = p_dept_id;
        RETURN v_avg_sal;
    END get_avg_sal;
END emp;

Illegal Object Reference and Forward Declaration

Illegal Variable Usage

• Variables can be declared in both the package specification and package body.

• Variables declared in the package spec can be used anywhere without issue.

• In the package body, variables must be declared before they are used, and their position in the body matters.

• Variables cannot be declared after the first subprogram declaration within the package body.

Illegal Subprogram Reference

• Subprograms (functions/procedures) in a package body also have usage restrictions.

• Public subprograms (declared in the package spec) can be used anywhere.

• Private subprograms (declared only in the package body) cannot be called before they are declared in the text order.

• PL/SQL does not allow forward referencing, meaning a subprogram cannot call another subprogram that is declared after it in the body.

Solution: Forward Declaration

• Oracle provides a forward declaration option to resolve this issue.

• Declare the subprogram before it is used, similar to how variables are declared.

• Add the subprogram signature (without the body) at the top of the package.

PACKAGE BODY emp_pkg IS
    FUNCTION get_sal (e_id employees.employee_id%TYPE) RETURN NUMBER;
    -- Declare other variables and subprograms

    FUNCTION get_sal (e_id employees.employee_id%TYPE) RETURN NUMBER IS
        v_sal NUMBER := 0;
    BEGIN
        SELECT salary INTO v_sal FROM employees WHERE employee_id = e_id;
        RETURN v_sal;
    END get_sal;

    PROCEDURE print_test IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Test salary: ' || get_sal(102));
    END print_test;
END emp_pkg;

Package Initialization Block

• When a package component is first called, the entire package is loaded into memory.

• Uninitialized variables default to NULL, so explicit initialization is recommended.

• Oracle allows an additional way to initialize variables and execute code upon package loading.

• A special initialization block can be written inside the package body to execute once per session.

Initialization Block in Package Body

• This block is executed the first time a package is referenced in a session.

• Can be used for:

  • Initializing variables.

  • Logging package usage.

  • Handling session-specific tasks (e.g., clearing temporary tables).

• The initialization block is placed in the package body just before the END keyword.

PACKAGE BODY emp_pkg IS
    --- Subprogram defination

BEGIN
  -- Insert a log entry
  INSERT INTO logs (log_source, log_message, log_date)
  VALUES ('EMP_PKG', 'Package initialized!', SYSDATE);

  -- Initialize variables
  v_salary_increase_rate := 500;
END emp_pkg;

Persistent State of Package

Memory Areas in a Database

1. PGA (Program Global Area) / UGA (User Global Area):

   • Private memory area for each session.

   • Stores objects, variables, and code specific to a single user.

   • Each user has their own PGA, isolated from others.

2. SGA (System Global Area) / Shared Global Area:

   • Shared memory area among all users.

   • Stores shared objects, values, codes, and package subprograms.

How Packages Are Loaded in Memory

• When a package is referenced, it is copied into memory.

• Public objects and variables are copied into PGA.

• Subprograms (procedures & functions) are copied into SGA.

• Changes to variables in PGA affect only the user’s session.

• Changes persist until the session ends or the package is recompiled.

Example Scenario: Persistent Variable State

• Assume a package variable X is initialized to 10.

• User A changes X to 20.

• User B still sees X as 10, since each session has its own copy.

• The value remains 20 for User A until the session ends.

• Recompiling the package refreshes the value for all users.

Constants in PL/SQL Packages

• To make a variable constant, use the constant keyword right after the variable name.

• Once a variable is declared as constant, its value cannot be modified.

• Attempting to change it results in an error: "This variable cannot be used as an assignment target."

CREATE OR REPLACE PACKAGE emp AS
    v_salary_increase constant number := 10;
    CURSOR cur_emps IS SELECT * FROM employees;
    t_emps_type employees%ROWTYPE;
    FUNCTION get_avg_sal(p_dept_id INT) RETURN NUMBER;
END emp;

Serially Reusable Packages in PL/SQL

• Normally, package variables are stored in PGA (session-specific).

• To store variables in SGA (shared across users), use SERIALLY_REUSABLE.

• Syntax: Add PRAGMA SERIALLY_REUSABLE to package specification and body.

• Effect: Variables reset to default values after each server call.

Advantages of SERIALLY_REUSABLE

• Saves memory by preventing duplication of package variables across multiple PGAs.

• Useful when packages are used by many users and state persistence is not required.

Disadvantages of SERIALLY_REUSABLE

• Cannot be accessed from triggers.

• Cannot be used in subprograms called from SQL statements.

Understanding Server Calls:

• Changes persist only during a single server call.

• A server call lasts until the END keyword is reached.

• Once the block ends, values are reset.

Impact on Other Users:

• Changes made by one user do not affect another.

• Each user has their own session-specific values.

CREATE OR REPLACE PACKAGE my_pkg IS
  PRAGMA SERIALLY_REUSABLE;
  v_company_name VARCHAR2(100);
END my_pkg;