How to run a Package in PL/SQL? & Why use Oracle Packages?

Package in PL/SQL

In Oracle programming, PL/SQL packages are essential items that facilitate the creation of modular applications. With their ability to organise related code pieces and provide important advantages like data permanence and information concealment, they constitute the foundation of application development initiatives.

Packages: Modular Programming Collection

An encapsulated group of related variables, functions, procedures, and other software objects kept together in the database is called a PL/SQL package. These structures are frequently called programs or programming structures. Packages work together like functionality, functioning as a single programming unit.

The following items can be combined into a package:

1. Procedures and Functions: These are the subprograms that house complex application logic and business rules. You can enhance SQL capability by calling functions from within queries.
2. Variables and Constants: These can be specified outside of any particular function or procedure, within the package structure itself.
3. Cursors and Exceptions: Explicit cursor declarations and specially tailored exceptions that are pertinent to the grouped functionality can be included in packages.
4. Types: Type definitions, such as record types or collection types (such nested tables or associative arrays), can be found in packages and used throughout the application code.

Programs within applications can achieve uniformity and are easier to manage since packages provide these items a named scope.

Specification & Body: Defining Structure

A package must be created using two independent statements, the package specification and the package body, which define the package’s structure.

Package Specification (CREATE PACKAGE)

All of the package’s externally usable components are listed in the package specification, also known as the package header. It is required in order to create a package.

The CREATE PACKAGE statement is used to create the specification. It serves as an API, or public interface. It contains public procedures, functions, variables, constants, cursors, and exceptions that users with package EXECUTE privileges can access.

The specifications for procedures and functions only include the header (or signature), which provides the module type, name, and parameter list (and a RETURN clause if a function). Implementation logic is concealed. using a cursor specified, the SQL query can be concealed using merely the RETURN clause.

A sample syntax:

create [or replace] package [user.] package [authid {definer | current_user} ] {is | as} package specification;

Package Body (CREATE PACKAGE BODY)

The implementation of the public objects specified in the package specification is contained in the package body. The generate CREATE PACKAGE BODY statement is used to generate the body.

Every function and procedure specified in the specification must be defined in the package body. It might also include other components that aren’t included in the package specification, including functions, procedures, variables, cursors, constants, or exceptions. Because these internal components are private and can only be accessed through calls made from within the same package body, implementation concealment and controlled internal logic are made possible.

The package body structure might have its own declaration, execution (initialisation), and exception sections and frequently resembles a method definition. When a package component is first executed, the execution section also referred to as the initialisation section automatically runs just once per session.

Here is an example of syntax:

create [or replace] package body [user.] package body {is | as} package body;

The package specification and the package body should have the same name. Large applications require less recompilation when the package body specification (implementation) is changed because the database does not invalidate dependent schema objects.

Advantages of Packages

Information hiding (encapsulation), session durability, and modularity are the main benefits of package development.

Modularity and Code Reuse

By compiling comparable functionality, packages encourage modularity and make code easier to locate and maintain. A procedure inside a package is the best technique to condense a number of related tasks into a single programming unit when they need to be completed. Program pieces can be reused and reasoning is made simpler using this modular approach.

Additionally, using packages can result in better performance:

• Centralized Execution: By moving processing away from the client, complex business rules can be handled by the database server, potentially significantly enhancing performance in client-server or three-tier systems.
• Reduced Recompilation: By putting sophisticated implementation in the body and public elements in the specification, modifications to the package body (the implementation) minimise the requirement to recompile dependent code by not invalidating external programs that solely rely on the specification.
• Optimized Memory: Oracle’s memory management maximises the availability of code contained within packages.

Information Hiding (Encapsulation)

Removing extraneous details from visibility so that developers can concentrate on essential interactions is known as information hiding. Packages make this possible by rigorously limiting access through both public and private components.

• Hiding Logic: It is not possible to directly reference private objects specified solely in the package body from outside the package. This is essential for separating sensitive or volatile components of an application, like secret calculation formulas or data structures that change regularly.
• Data Encapsulation (Table API): To manage all SQL actions (INSERT, UPDATE, DELETE, and SELECT) against certain tables or entities, developers might offer a procedural interface (a Table API). Instead of depending on individual developers to regularly generate and maintain complex SQL statements, this guarantees that complex transactions are handled consistently and optimally by established, tested procedures.
• Security: “Gatekeepers” can be packages. The package performs DML actions on underlying tables without direct table rights. The WRAP utility to safeguard confidential information before dissemination.

Session Persistence

For the duration of the current Oracle session (connection), package data structures variables and constants declared at the package level, independent of any subprogram remain in effect. Within the session, they behave as globals.

When it comes to storing static session data, this durability is extremely valuable. It is possible to retrieve a piece of data (such as a configuration setting or a frequently accessed reference table) once during package initialisation and store it in a package variable if it is static during a user session. Performance is significantly increased by using subsequent package calls to obtain this data from memory (PGA) rather than running repetitive database queries.

In order to populate these persistent variables, the initialisation section, which is run when the package is initially invoked during a session, can execute setup code that is arbitrary in complexity.

Example: Modular Logging with Session Persistence

We will demonstrate these concepts using a modular logging solution. This example requires creating a table, defining a sequence, and implementing a package that utilizes a private session-persistent variable (g_first_call_time) to track when the package was first used in the session, showcasing initialization and persistence.

Create Table and Sequence (DDL and DML)

We first create the table where the log entries will be stored and a sequence to generate primary key IDs.

-- Create table to store log entries
CREATE TABLE STAFF_LOG (
    log_id        NUMBER(10) PRIMARY KEY,
    log_date      DATE,
    username      VARCHAR2(30),
    action_desc   VARCHAR2(255)
);

-- Insert values (sample data is not relevant until the package runs, 
-- but a sequence is helpful for automated PK generation)
CREATE SEQUENCE staff_log_seq 
START WITH 1 
INCREMENT BY 1;

Package Specification (Declaring Public Element)

The specification declares the public procedure Log_Action. This is the only element external users need to know about to interface with the logging module.

CREATE OR REPLACE PACKAGE Logger_Pkg AS
    -- Public procedure declaration (header only)
    PROCEDURE Log_Action (p_action_in IN VARCHAR2);
END Logger_Pkg;
/
-- Output: Package created.

Package Body (Defining Implementation and Persistence)

The package body defines the implementation of Log_Action and declares private, session-persistent variables (g_first_call_time, g_log_count). The initialization section (BEGIN ... END Logger_Pkg) runs once per session, setting the initial value of g_first_call_time.

CREATE OR REPLACE PACKAGE BODY Logger_Pkg AS
    -- Private session-persistent variables declared in the body 
    g_first_call_time DATE; 
    g_log_count NUMBER := 0; 
    
    PROCEDURE Log_Action (p_action_in IN VARCHAR2) IS
    BEGIN
        -- Encapsulating DML (Information Hiding)
        INSERT INTO STAFF_LOG 
        VALUES (staff_log_seq.NEXTVAL, SYSDATE, SYS_CONTEXT('USERENV', 'SESSION_USER'), p_action_in);
        
        -- Modifying persistent data
        g_log_count := g_log_count + 1;
        
        DBMS_OUTPUT.PUT_LINE('Log count for session: ' || g_log_count);
        
        -- Demonstrating use of persistent data
        IF g_log_count = 1 THEN
            DBMS_OUTPUT.PUT_LINE('Package initialized at: ' || 
                                 TO_CHAR(g_first_call_time, 'HH24:MI:SS'));
        ELSE
            DBMS_OUTPUT.PUT_LINE('First call time (persistent): ' || 
                                 TO_CHAR(g_first_call_time, 'HH24:MI:SS'));
        END IF;
    END Log_Action;

-- Package Initialization Section (runs once per session) 
BEGIN
    g_first_call_time := SYSDATE; 
    DBMS_OUTPUT.PUT_LINE('--- Logger_Pkg initialized for this session. ---'); 
END Logger_Pkg;
/
-- Output: Package body created.

Example Execution and Output

To view the output, we must enable SERVEROUTPUT. We call the procedure multiple times within the same session using the EXECUTE command.

SET SERVEROUTPUT ON;

-- First Execution: Triggers Package Initialization and Persistence 
EXEC Logger_Pkg.Log_Action('User logged in.'); 

-- Output from First Execution:
--- Logger_Pkg initialized for this session. ---
Log count for session: 1
Package initialized at: 10:30:00 (Example Time)
PL/SQL procedure successfully completed.

-- Second Execution: Initialization is skipped, persistent data is retained 
EXEC Logger_Pkg.Log_Action('Updated user profile.'); 

-- Output from Second Execution:
Log count for session: 2
First call time (persistent): 10:30:00 (Example Time, unchanged from initialization)
PL/SQL procedure successfully completed.

-- Verify Data (DML was encapsulated and executed) 
SELECT log_id, log_date, username, action_desc 
FROM STAFF_LOG;

Output of SELECT statement (Example data):

This execution demonstrates modularity (calling a single named unit), encapsulation (hiding the DML logic and table structure inside the body), and session persistence (the g_first_call_time variable retained its value set during the first call, confirming initialization runs only once).