Packages FAQs

1. What is a package in Oracle PL/SQL?

A package is a group of related PL/SQL constructs (procedures, functions, variables, cursors, types) stored together in the database.

• Promotes modular programming
• Encapsulates related logic
• Supports information hiding via package specification and body

2. What are the components of a package?

1.    Package Specification (PACKAGE)

o   Declares the public interface

o   Lists procedures, functions, cursors, variables, types accessible outside the package

2.    Package Body (PACKAGE BODY)

o   Implements the logic of procedures and functions

o   Can have private elements visible only inside the package

CREATE OR REPLACE PACKAGE emp_pkg IS

   PROCEDURE add_employee(p_name VARCHAR2, p_salary NUMBER);

   FUNCTION get_salary(p_emp_id NUMBER) RETURN NUMBER;

END emp_pkg;

 

CREATE OR REPLACE PACKAGE BODY emp_pkg IS

   PROCEDURE add_employee(p_name VARCHAR2, p_salary NUMBER) IS

   BEGIN

      INSERT INTO employees(name, salary) VALUES (p_name, p_salary);

   END;

 

   FUNCTION get_salary(p_emp_id NUMBER) RETURN NUMBER IS

      v_salary NUMBER;

   BEGIN

      SELECT salary INTO v_salary FROM employees WHERE employee_id = p_emp_id;

      RETURN v_salary;

   END;

END emp_pkg;

3. What are the advantages of using packages?

• Modularity – logically group related code
• Encapsulation – hide implementation details (private elements in body)
• Reusability – can be called by multiple programs
• Easier maintenance – change code in one place
• Performance – package elements are loaded into memory once, reducing repeated parsing
• Global variables – package-level variables maintain session state

4. How do you call procedures and functions from a package?

BEGIN

   emp_pkg.add_employee('John', 5000);

   DBMS_OUTPUT.PUT_LINE(emp_pkg.get_salary(101));

END;

• Use the package name as a prefix

5. Can packages have variables and constants?

Yes.

• Package variables maintain values for the duration of the session
• Constants can be declared in the specification

PACKAGE emp_pkg IS

   g_counter NUMBER := 0;

   c_max_salary CONSTANT NUMBER := 10000;

END emp_pkg;

6. Difference between public and private elements in a package

Feature	Public (Specification)	Private (Body)
Visibility	Outside the package	Only inside package
Declaration	In package spec	In package body
Use case	API for calling programs	Helper logic

7. Can packages improve performance?

• Oracle loads the package into memory on first call, keeping it for the session
• Reduces context switches between SQL and PL/SQL
• Multiple calls to package procedures/functions don’t re-parse the code

8. What are package cursors?

• Cursors can be declared in a package for reusable queries
• Can be public (used outside package) or private (inside body)
• Supports parameterized cursors

PACKAGE emp_pkg IS

   CURSOR emp_cursor(p_dept NUMBER) IS

      SELECT * FROM employees WHERE department_id = p_dept;

END emp_pkg;

9. What are package types?

• Scalar types – NUMBER, VARCHAR2, DATE, etc.
• Composite types – RECORD, TABLE, VARRAY
• Can declare user-defined types in the package for complex data structures

10. Can packages have exceptions?

Yes.

• Exceptions can be declared in the package specification
• Raised and handled inside procedures or functions

PACKAGE emp_pkg IS

   e_invalid_salary EXCEPTION;

END emp_pkg;

11. Common mistakes with packages

• Forgetting to recompile after changing specification or body
• Declaring variables in specification unnecessarily (public vs private)
• Excessive use of package variables leading to state management issues
• Using DML in package functions called from SQL (limits usability)

12. Best practices for packages

• Keep public interface minimal; expose only what’s necessary
• Use private elements for helpers or implementation details
• Avoid hardcoding values; use constants or parameters
• Use package variables carefully to manage session state
• Maintain naming conventions for readability
• Recompile packages after updates to avoid runtime errors