PL/SQL Packages: Structure, Benefits, and Real-World Use

TAKEAWAYS:

• Packages encapsulate PL/SQL code (procedures, functions, variables, exceptions) into a single reusable unit, improving modularity and security.
• Two main parts: A specification (public interface) and a body (private implementation), allowing controlled access to data and logic.
• Advantages: Code reusability, performance optimization (shared variables), and easier maintenance compared to anonymous blocks.
• Real-world use: Banks use packages for transaction validation (e.g., Nabil Bank’s loan approval logic), eSewa for payment processing, and NTC for billing calculations.
• Parameter modes (IN, OUT, IN OUT) define how data flows between a package and calling program—critical for dynamic queries and updates.
• Exam focus: Define packages, draw a package structure diagram, write a package with procedures/functions, and explain why packages are preferred over triggers for complex logic.

1. What Are PL/SQL Packages?

A PL/SQL package is a schema object that groups related PL/SQL types, variables, constants, cursors, procedures, functions, and exceptions into a single logical unit. Think of it as a library in programming languages like C or Java:

• Encapsulation: Hides implementation details (e.g., internal variables) while exposing only what’s needed.
• Reusability: Avoids rewriting the same code across applications.
• Performance: Shared variables (e.g., connection pools) reduce redundant computations.

Why Use Packages?

2. Package Structure: Specification vs. Body

A package has two parts:

1. Package Specification (PACKAGE):
   • Declares public items (procedures, functions, variables) visible to users.
   • Acts as an API (Application Programming Interface).
   • Syntax:

     CREATE OR REPLACE PACKAGE package_name AS
       -- Public declarations (variables, procedures, functions)
       PROCEDURE public_procedure(param1 IN type);
       FUNCTION public_function(param1 IN type) RETURN type;
       VARIABLE public_var TYPE;
     END package_name;
     

classDiagram
    class PackageSpec {
        +PROCEDURE validate_transaction(account_num IN NUMBER, amount IN NUMBER)
        +FUNCTION calculate_interest(principal IN NUMBER, rate IN NUMBER) RETURN NUMBER
        +MAX_LOAN_AMOUNT CONSTANT NUMBER
        +g_total_transactions NUMBER (hidden)
    }
    class PackageBody {
        -g_total_transactions NUMBER
        -PROCEDURE check_balance(account_num IN NUMBER)
        +PROCEDURE validate_transaction(account_num IN NUMBER, amount IN NUMBER)
        +FUNCTION calculate_interest(principal IN NUMBER, rate IN NUMBER) RETURN NUMBER
    }
    PackageSpec --> PackageBody : "Exposes Public Interface"
    PackageBody --> PackageSpec : "Implements Logic"
    PackageBody : "Private Section (Hidden)"
    PackageSpec : "Public Section (API)"

2. Package Body (PACKAGE BODY):
   • Contains the implementation of public items + private items.
   • Syntax:

     CREATE OR REPLACE PACKAGE BODY package_name AS
       -- Private declarations (hidden from users)
       PROCEDURE private_helper(param1 IN type) IS ...
       BEGIN
         -- Logic
       END;
     
       -- Implementation of public items
       PROCEDURE public_procedure(param1 IN type) IS
       BEGIN
         private_helper(param1); -- Can call private items
       END;
     END package_name;
     

Example: A BANK_TRANSACTION Package

Specification (bank_pkg.spec):

CREATE OR REPLACE PACKAGE bank_pkg AS
   -- Public variables (shared across sessions)
   MAX_LOAN_AMOUNT CONSTANT NUMBER := 1000000;

   -- Public procedure
   PROCEDURE validate_transaction(
      account_num IN NUMBER,
      amount IN NUMBER
   );

   -- Public function
   FUNCTION calculate_interest(
      principal IN NUMBER,
      rate IN NUMBER
   ) RETURN NUMBER;
END bank_pkg;

Body (bank_pkg.body):

CREATE OR REPLACE PACKAGE BODY bank_pkg AS
   -- Private variable (visible only within the package)
   g_total_transactions NUMBER := 0;

   -- Private procedure (helper)
   PROCEDURE check_balance(account_num IN NUMBER) IS
      bal NUMBER;
   BEGIN
      SELECT balance INTO bal FROM accounts WHERE account_no = account_num;
      IF bal < 0 THEN
         RAISE_APPLICATION_ERROR(-20001, 'Insufficient funds');
      END IF;
   END;

   -- Public procedure implementation
   PROCEDURE validate_transaction(account_num IN NUMBER, amount IN NUMBER) IS
   BEGIN
      check_balance(account_num); -- Calls private procedure
      g_total_transactions := g_total_transactions + 1;
      DBMS_OUTPUT.PUT_LINE('Transaction validated for account: ' || account_num);
   END;

   -- Public function implementation
   FUNCTION calculate_interest(principal IN NUMBER, rate IN NUMBER) RETURN NUMBER IS
   BEGIN
      RETURN principal * rate / 100;
   END;
END bank_pkg;

Mermaid Diagram: Package Structure

classDiagram
    class PackageSpec {
        +PROCEDURE validate_transaction()
        +FUNCTION calculate_interest() RETURN NUMBER
        +MAX_LOAN_AMOUNT CONSTANT NUMBER
    }
    class PackageBody {
        -g_total_transactions NUMBER
        -PROCEDURE check_balance()
        +PROCEDURE validate_transaction()
        +FUNCTION calculate_interest()
    }
    PackageSpec --> PackageBody : "Exposes"
    PackageBody --> PackageSpec : "Implements"

3. Key Components of Packages

A. Variables and Constants

• Shared across sessions: Useful for tracking system-wide metrics (e.g., g_total_transactions in the example above).
• Constants: Immutable values (e.g., MAX_LOAN_AMOUNT).
• Example:

  PACKAGE BODY bank_pkg AS
     g_customer_count NUMBER := 0; -- Shared variable
     MINIMUM_DEPOSIT CONSTANT NUMBER := 500; -- Constant
  END;
  

B. Procedures and Functions

• Procedures: Perform actions (e.g., validate_transaction).
• Functions: Return values (e.g., calculate_interest).
• Parameter Modes:
  • IN: Input only (default).
  • OUT: Output only (used to return values).
  • IN OUT: Both input and output.

Example with OUT Parameter:

PROCEDURE get_customer_balance(
   account_num IN NUMBER,
   balance OUT NUMBER
) IS
BEGIN
   SELECT balance INTO balance FROM accounts WHERE account_no = account_num;
END;

C. Cursors

• Strongly typed: Cursors declared in packages can be reused without redeclaration.
• Example:

  PACKAGE BODY bank_pkg AS
     CURSOR emp_cursor IS SELECT * FROM employees;
  END;
  

D. Exceptions

• Predefined: Use RAISE (e.g., RAISE TOO_MANY_ROWS).
• User-defined: Declare in the specification and handle in the body.

  PACKAGE bank_pkg AS
     INVALID_ACCOUNT EXCEPTION;
  END;
  
  PACKAGE BODY bank_pkg AS
     PROCEDURE validate_transaction(...) IS
     BEGIN
        -- Logic
        EXCEPTION
           WHEN NO_DATA_FOUND THEN
              RAISE INVALID_ACCOUNT;
     END;
  END;
  

4. Advantages of Packages

1. Modularity: Break complex logic into manageable units.
2. Performance:
   • Shared variables reduce redundant database queries.
   • Compiled once and cached (faster execution).
3. Security:
   • Hide sensitive logic in private sections.
   • Control access via public interfaces.
4. Reusability: Call the same package from multiple applications (e.g., a UTILITY_PKG for common tasks).
5. Maintainability: Update logic in one place (the package body) without changing calling code.

5. When to Use Packages vs. Other PL/SQL Constructs

6. Real-World Applications

In the Real World

1. eSewa (Nepal):

   • Package Use: The PAYMENT_PROCESSING_PKG handles transaction validation, fraud checks, and balance updates.
   • How: Shared variables track system-wide metrics (e.g., g_total_transactions), while private procedures validate user inputs before processing payments.

2. Nabil Bank (Loan Approval):

   • Package Use: LOAN_APPROVAL_PKG encapsulates credit score checks, interest calculations, and EMI (Equated Monthly Installment) computations.
   • Example Workflow:
     • A customer applies for a loan via the bank’s app.
     • The app calls LOAN_APPROVAL_PKG.validate_loan(applicant_id, amount).
     • The package checks credit score (private procedure), calculates interest (public function), and updates shared variables (e.g., g_approved_loans).

3. NTC (Electricity Billing):

   • Package Use: BILLING_PKG manages consumer data, usage calculations, and late-fee applications.
   • Shared Variable: g_peak_consumption tracks the highest usage in the grid to optimize power distribution.

7. Worked Example: Daraz Order Processing

Scenario: Daraz uses PL/SQL packages to manage order processing. Write a package to:

1. Validate an order (check stock and customer credit).
2. Update inventory and generate a receipt.

Solution: Specification (daraz_pkg.spec):

CREATE OR REPLACE PACKAGE daraz_pkg AS
   -- Public procedure to process an order
   PROCEDURE process_order(
      order_id IN NUMBER,
      product_id IN NUMBER,
      quantity IN NUMBER,
      customer_id IN NUMBER,
      order_status OUT VARCHAR2
   );

   -- Public function to check stock
   FUNCTION check_stock(product_id IN NUMBER) RETURN NUMBER;
END daraz_pkg;

Body (daraz_pkg.body):

CREATE OR REPLACE PACKAGE BODY daraz_pkg AS
   -- Private variable to track failed orders
   g_failed_orders NUMBER := 0;

   -- Private procedure to validate customer credit
   PROCEDURE validate_credit(customer_id IN NUMBER) IS
      credit_limit NUMBER;
   BEGIN
      SELECT credit_limit FROM customers WHERE customer_id = customer_id;
      IF credit_limit < (SELECT total_amount FROM orders WHERE customer_id = customer_id) THEN
         RAISE_APPLICATION_ERROR(-20002, 'Credit limit exceeded');
      END IF;
   END;

   -- Public function to check stock
   FUNCTION check_stock(product_id IN NUMBER) RETURN NUMBER IS
      stock_quantity NUMBER;
   BEGIN
      SELECT quantity FROM products WHERE product_id = product_id INTO stock_quantity;
      RETURN stock_quantity;
   END;

   -- Public procedure to process order
   PROCEDURE process_order(
      order_id IN NUMBER,
      product_id IN NUMBER,
      quantity IN NUMBER,
      customer_id IN NUMBER,
      order_status OUT VARCHAR2
   ) IS
      stock_available NUMBER;
   BEGIN
      -- Validate credit (private call)
      validate_credit(customer_id);

      -- Check stock
      stock_available := check_stock(product_id);
      IF stock_available < quantity THEN
         order_status := 'FAILED: Insufficient stock';
         g_failed_orders := g_failed_orders + 1;
         RETURN;
      END IF;

      -- Update inventory and generate receipt
      UPDATE products SET quantity = quantity - quantity WHERE product_id = product_id;
      INSERT INTO orders VALUES (order_id, customer_id, product_id, quantity, SYSDATE, 'PROCESSED');
      order_status := 'SUCCESS';
   END;
END daraz_pkg;

Mermaid Diagram: Daraz Order Processing Flow

sequenceDiagram
    participant User
    participant DarazApp
    participant daraz_pkg
    participant Database

    User->>DarazApp: Places order (product_id=101, quantity=2)
    DarazApp->>daraz_pkg: process_order(1, 101, 2, 1001, order_status)
    daraz_pkg->>daraz_pkg: validate_credit(1001) (private)
    daraz_pkg->>Database: Check customer credit
    Database-->>daraz_pkg: credit_limit=5000
    daraz_pkg->>daraz_pkg: check_stock(101) (public)
    daraz_pkg->>Database: SELECT quantity FROM products WHERE product_id=101
    Database-->>daraz_pkg: stock_quantity=5
    daraz_pkg->>Database: UPDATE products SET quantity=3 WHERE product_id=101
    daraz_pkg->>Database: INSERT INTO orders(...)
    daraz_pkg-->>DarazApp: order_status="SUCCESS"
    DarazApp->>User: Show receipt

8. Common Pitfalls and Best Practices

A. Pitfalls

1. Overloading Packages:
   • Avoid putting unrelated procedures/functions in one package. Example: Don’t mix bank_pkg with hr_pkg logic.
2. Uninitialized Variables:
   • Shared variables must be initialized in the package body (e.g., g_total_transactions NUMBER := 0).
3. Circular Dependencies:
   • Package A calling Package B, which calls Package A → leads to compilation errors.

B. Best Practices

1. Naming Conventions:
   • Use prefixes (e.g., BANK_, DARAZ_) to avoid naming conflicts.
2. Documentation:
   • Add comments to explain purpose, parameters, and return values.

   -- Validates a transaction and updates shared counters
   PROCEDURE validate_transaction(...) IS ...
   

3. Error Handling:
   • Use user-defined exceptions for domain-specific errors (e.g., INVALID_ACCOUNT).
4. Testing:
   • Test packages thoroughly, especially shared variables and private procedures.

9. Exam Tip

What Examiners Look For

1. Definition:

   • A package is a schema object that groups related PL/SQL code into a specification (public) and body (private).
   • Marks: 1–2 for a concise definition.

2. Structure Diagram:

   • Draw a class diagram or layered model showing:
     • PackageSpec (public items) → PackageBody (private + implementation).
   • Marks: 3–4 for a correct diagram with labels.

3. Syntax:

   • Write both the specification and body for a package with:
     • At least one procedure and one function.
     • One shared variable and one private procedure.
   • Marks: 5–6 for correct syntax and logic.

4. Parameter Modes:

   • Explain IN, OUT, IN OUT with an example (e.g., OUT for returning a balance).
   • Marks: 2–3 for clarity and correctness.

5. Real-World Application:

   • Link packages to eSewa (payments), NTC (billing), or banking (loans).
   • Marks: 2 for relevance and 3 for technical accuracy.

6. Comparison Tables:

   • Compare packages with anonymous blocks, procedures, or triggers in a table.
   • Marks: 3 for a well-structured table.

Sample Exam Question and Answer

Question: "Explain the concept of PL/SQL packages with an example. Differentiate between package specification and package body. [5]."

Model Answer: A PL/SQL package is a database object that encapsulates related PL/SQL types, variables, constants, cursors, procedures, functions, and exceptions into a single unit. It improves modularity, performance, and security by hiding implementation details.

Example: A BANK_TRANSACTION_PKG for loan processing:

-- Specification (public interface)
CREATE OR REPLACE PACKAGE bank_transaction_pkg AS
   PROCEDURE validate_loan(applicant_id IN NUMBER, amount IN NUMBER);
   FUNCTION calculate_emi(principal IN NUMBER, rate IN NUMBER) RETURN NUMBER;
   MAX_LOAN_AMOUNT CONSTANT NUMBER := 5000000;
END;

-- Body (private implementation)
CREATE OR REPLACE PACKAGE BODY bank_transaction_pkg AS
   g_total_loans NUMBER := 0; -- Shared variable

   -- Private procedure
   PROCEDURE check_credit_score(applicant_id IN NUMBER) IS ...
   BEGIN
      -- Logic to check credit score
   END;

   -- Public procedure
   PROCEDURE validate_loan(applicant_id IN NUMBER, amount IN NUMBER) IS
   BEGIN
      check_credit_score(applicant_id); -- Calls private procedure
      g_total_loans := g_total_loans + 1;
   END;

   -- Public function
   FUNCTION calculate_emi(principal IN NUMBER, rate IN NUMBER) RETURN NUMBER IS
   BEGIN
      RETURN principal * rate / 1200; -- Simplified EMI formula
   END;
END;

Differentiation:

Why Packages?

• Reusability: Call validate_loan from multiple applications.
• Performance: Shared variable g_total_loans avoids redundant queries.
• Security: Private procedures (e.g., check_credit_score) are hidden.

In the real world

• eSewa: Uses PL/SQL packages to encapsulate payment validation logic (e.g., validate_transaction procedure) and shared variables (e.g., g_total_transactions) to track system-wide transaction counts across all users. The package structure ensures secure access to sensitive financial data while optimizing performance for high-volume transactions.
• Nabil Bank: Implements PL/SQL packages for loan approval workflows (e.g., calculate_interest function) and shared constants (e.g., MAX_LOAN_AMOUNT) to enforce bank-wide policies. The package body contains private helper procedures (e.g., check_balance) to validate customer accounts before processing requests.
• Daraz (Nepal): Leverages PL/SQL packages for order processing (e.g., update_order_status procedure) and shared cursors (e.g., emp_cursor for employee data) to reduce redundant database queries. The package specification exposes only necessary functions to the application layer, improving security and maintainability.