Oracle PL/SQL Packages – Complete Explanation with Examples

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

A Package in Oracle PL/SQL is a database object that groups related PL/SQL components together into a single unit.

A package can contain:

  • Procedures
  • Functions
  • Variables
  • Constants
  • Cursors
  • Exceptions
  • Types
  • Records

In simple words, a package is like a container that holds multiple related procedures, functions, variables, and other PL/SQL objects.

Real-time example

Imagine you are developing a banking application.

A banking application may need several operations:

  • Create a bank account.
  • Deposit money.
  • Withdraw money.
  • Check account balance.
  • Transfer money.

Instead of creating separate, unrelated procedures, we can group all these operations into one package called BANK_PKG.

BANK_PKG – Package

Package Specification

Public declarations

  • CREATE_ACCOUNT
  • DEPOSIT_AMOUNT
  • WITHDRAW_AMOUNT
  • CHECK_BALANCE

Package Body

Actual implementations

  • CREATE_ACCOUNT logic
  • DEPOSIT_AMOUNT logic
  • WITHDRAW_AMOUNT logic
  • CHECK_BALANCE logic

An Oracle package has a mandatory specification and, when needed, a body:

  1. Package Specification
  2. Package Body

The package specification is mandatory, but the package body is optional if the package contains only declarations that do not need implementation, such as variables, constants, types, or exceptions.

2. Why Do We Use Packages in Oracle?

We use packages to organize related PL/SQL code and make applications easier to develop, maintain, and manage.

Let's understand with an example.

Without using packages

Suppose we have an employee management application.

We create separate procedures:

SQL / PL/SQL
CREATE OR REPLACE PROCEDURE ADD_EMPLOYEE
AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Employee Added');
END;
/
SQL / PL/SQL
CREATE OR REPLACE PROCEDURE UPDATE_EMPLOYEE
AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Employee Updated');
END;
/
SQL / PL/SQL
CREATE OR REPLACE PROCEDURE DELETE_EMPLOYEE
AS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Employee Deleted');
END;
/

Here, all procedures are created independently.

As the application grows, managing hundreds of independent procedures becomes difficult.

Using packages

We can group all employee-related procedures into one package called EMP_PKG.

SQL / PL/SQL
CREATE OR REPLACE PACKAGE EMP_PKG
AS
    PROCEDURE ADD_EMPLOYEE;

    PROCEDURE UPDATE_EMPLOYEE;

    PROCEDURE DELETE_EMPLOYEE;

END EMP_PKG;
/

Now we can implement all procedures inside the package body.

SQL / PL/SQL
CREATE OR REPLACE PACKAGE BODY EMP_PKG
AS

    PROCEDURE ADD_EMPLOYEE
    AS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Employee Added');
    END ADD_EMPLOYEE;


    PROCEDURE UPDATE_EMPLOYEE
    AS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Employee Updated');
    END UPDATE_EMPLOYEE;


    PROCEDURE DELETE_EMPLOYEE
    AS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Employee Deleted');
    END DELETE_EMPLOYEE;

END EMP_PKG;
/

Now we can call any procedure using the package name.

SQL / PL/SQL
BEGIN
    EMP_PKG.ADD_EMPLOYEE;

    EMP_PKG.UPDATE_EMPLOYEE;

    EMP_PKG.DELETE_EMPLOYEE;
END;
/

Output:

Expected output
Employee Added
Employee Updated
Employee Deleted

This makes our code organized and easier to maintain.

3. What Are the Advantages of Packages?

Oracle PL/SQL packages provide several advantages.

1. Modularity

Packages allow us to group related procedures and functions into one logical unit.

Example:

  • EMP_PKG – Employee-related operations.
  • DEPT_PKG – Department-related operations.
  • SALARY_PKG – Salary-related operations.

This makes the application easier to understand.

2. Code Reusability

We can create procedures and functions inside a package and reuse them wherever required.

Example:

SQL / PL/SQL
EMP_PKG.ADD_EMPLOYEE;

We can call this procedure from different PL/SQL blocks, procedures, and other programs, provided we have the required privileges.

3. Better Performance

When a package is called for the first time in a session, Oracle loads the package into memory.

Subsequent calls can reuse the loaded package state and avoid repeatedly loading individual program units.

This can improve performance, especially when multiple package components are used.

4. Information Hiding

Packages support information hiding.

We can declare some procedures in the package body without exposing them in the package specification.

These procedures are private and cannot be directly called from outside the package.

Example:

SQL / PL/SQL
CREATE OR REPLACE PACKAGE EMP_PKG
AS
    PROCEDURE ADD_EMPLOYEE;
END EMP_PKG;
/

Package body:

SQL / PL/SQL
CREATE OR REPLACE PACKAGE BODY EMP_PKG
AS

    PROCEDURE VALIDATE_EMPLOYEE
    AS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Validating Employee');
    END VALIDATE_EMPLOYEE;


    PROCEDURE ADD_EMPLOYEE
    AS
    BEGIN
        VALIDATE_EMPLOYEE;

        DBMS_OUTPUT.PUT_LINE('Employee Added');
    END ADD_EMPLOYEE;

END EMP_PKG;
/

Here:

  • ADD_EMPLOYEE is public because it is declared in the specification.
  • VALIDATE_EMPLOYEE is private because it is declared only in the body.

Outside users can call EMP_PKG.ADD_EMPLOYEE, but cannot directly call EMP_PKG.VALIDATE_EMPLOYEE.

5. Easy Maintenance

If we want to change the internal implementation of a procedure, we can modify the package body without changing the package specification, provided its public interface remains compatible.

6. Better Security

We can provide users access to the package without granting direct access to the underlying tables.

For example, a user can execute:

SQL / PL/SQL
EMP_PKG.ADD_EMPLOYEE;

without necessarily having direct INSERT privileges on the employee table.

The package can perform the required operations using its owner's privileges when created with the appropriate rights model.

7. Global Variables

Packages can maintain variables that can be accessed by multiple procedures and functions within the same database session.

Example:

SQL / PL/SQL
CREATE OR REPLACE PACKAGE TEST_PKG
AS
    v_count NUMBER := 0;

    PROCEDURE INCREMENT_COUNT;
END TEST_PKG;
/

Package body:

SQL / PL/SQL
CREATE OR REPLACE PACKAGE BODY TEST_PKG
AS
    PROCEDURE INCREMENT_COUNT
    AS
    BEGIN
        v_count := v_count + 1;

        DBMS_OUTPUT.PUT_LINE(v_count);
    END INCREMENT_COUNT;
END TEST_PKG;
/

Calling:

SQL / PL/SQL
BEGIN
    TEST_PKG.INCREMENT_COUNT;
    TEST_PKG.INCREMENT_COUNT;
    TEST_PKG.INCREMENT_COUNT;
END;
/

Output:

Expected output
1
2
3

The package variable retains its value between calls in the same session.

4. What Are the Disadvantages of Packages?

Although packages provide many advantages, they also have some disadvantages.

1. Compilation Dependencies

If we modify the package specification, dependent objects may become invalid and require recompilation.

For example, if another procedure depends on EMP_PKG, changing the package specification can affect that procedure.

2. Package Body Can Become Large

If we put too many procedures and functions into a single package, the package body can become very large and difficult to maintain.

Solution: Divide functionality into smaller, meaningful packages.

3. Package Variables Consume Memory

Package-level variables maintain session-specific state.

If a package contains many large variables, they can consume additional memory in database sessions.

4. Package State Can Cause Issues

If multiple procedures depend on package-level variables, the values may change during execution.

This can make debugging difficult.

Also, package state is maintained separately for each database session.

5. Recompilation Impact

Changes to a package specification can invalidate dependent objects.

This can affect application availability until the dependent objects are recompiled.

6. Debugging Can Be Difficult

When a package contains hundreds of procedures and functions, finding the source of an issue can become challenging.

Conclusion: Packages are very useful, but we should design them properly and avoid putting unrelated functionality into one package.

5. What Are the Main Components of an Oracle Package?

An Oracle package has two main components:

  1. Package Specification
  2. Package Body

Let's understand each component.

ComponentPurpose
Package SpecificationDeclares public components
Package BodyContains implementation of procedures and functions

6. What Is a Package Specification?

A Package Specification is the public interface of a package.

It tells users which procedures, functions, variables, cursors, types, and exceptions are available to use from outside the package.

In simple words:

Package Specification tells WHAT a package can do.

It contains declarations, not the actual implementation of procedures and functions.

Syntax

SQL / PL/SQL
CREATE OR REPLACE PACKAGE package_name
AS

    -- Variable declarations

    -- Constant declarations

    -- Procedure declarations

    -- Function declarations

    -- Cursor declarations

END package_name;
/

Example

Let's create an employee package specification.

SQL / PL/SQL
CREATE OR REPLACE PACKAGE EMP_PKG
AS

    PROCEDURE ADD_EMPLOYEE;

    PROCEDURE UPDATE_EMPLOYEE;

    PROCEDURE DELETE_EMPLOYEE;

    FUNCTION GET_EMPLOYEE_COUNT
    RETURN NUMBER;

END EMP_PKG;
/

Explanation:

  • EMP_PKG is the package name.
  • ADD_EMPLOYEE is a public procedure.
  • UPDATE_EMPLOYEE is a public procedure.
  • DELETE_EMPLOYEE is a public procedure.
  • GET_EMPLOYEE_COUNT is a public function.
  • RETURN NUMBER specifies the function's return datatype.

Important points:

  • Package specification is mandatory.
  • It contains public declarations.
  • It does not contain the implementation of procedures and functions.
  • Other programs can access its public components.
  • We can create a package specification without creating a package body if no implementation is required.

7. What Is a Package Body?

A Package Body contains the actual implementation of the procedures and functions declared in the package specification.

In simple words:

Package Body tells HOW a package performs its operations.

It contains:

  • Procedure implementations.
  • Function implementations.
  • Private procedures and functions.
  • Private variables and cursors.
  • Initialization section (optional).

Syntax

SQL / PL/SQL
CREATE OR REPLACE PACKAGE BODY package_name
AS

    -- Procedure implementation

    -- Function implementation

    -- Private variables

    -- Private procedures

BEGIN

    -- Initialization statements

END package_name;
/

The initialization section is optional and executes when the package is first instantiated in a session.

Example

Let's create the package body for EMP_PKG.

SQL / PL/SQL
CREATE OR REPLACE PACKAGE BODY EMP_PKG
AS

    PROCEDURE ADD_EMPLOYEE
    AS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Employee Added');
    END ADD_EMPLOYEE;


    PROCEDURE UPDATE_EMPLOYEE
    AS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Employee Updated');
    END UPDATE_EMPLOYEE;


    PROCEDURE DELETE_EMPLOYEE
    AS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Employee Deleted');
    END DELETE_EMPLOYEE;


    FUNCTION GET_EMPLOYEE_COUNT
    RETURN NUMBER
    AS
    BEGIN
        RETURN 100;
    END GET_EMPLOYEE_COUNT;

END EMP_PKG;
/

Explanation:

  • ADD_EMPLOYEE prints Employee Added.
  • UPDATE_EMPLOYEE prints Employee Updated.
  • DELETE_EMPLOYEE prints Employee Deleted.
  • GET_EMPLOYEE_COUNT returns 100.

Here, we are providing the actual implementation of all public components.

8. Difference Between Package Specification and Package Body

This is one of the most frequently asked Oracle PL/SQL interview questions.

Package SpecificationPackage Body
It is the public interface of a package.It contains the implementation of a package.
It tells WHAT a package does.It tells HOW a package works.
It is mandatory.It is optional in certain cases.
Contains public declarations.Contains implementations and private declarations.
Does not implement procedures and functions.Implements procedures and functions.
Can be accessed by external programs through public components.Private components cannot be accessed directly from outside.
Changing it can invalidate dependent objects.Body-only changes generally do not invalidate dependents that rely on the specification.
Created using CREATE PACKAGE.Created using CREATE PACKAGE BODY.

Simple real-world example

Think about a television remote control.

Package Specification

You see buttons such as:

  • Power
  • Volume
  • Channel

Package Body

Internal implementation:

  • How power turns on
  • How volume changes
  • How channels change

You know which buttons are available, but you don't need to know the internal circuitry to use them.

Similarly:

  • Specification = What operations are available.
  • Body = How those operations are implemented.

9. What Is the Syntax for Creating a Package?

To create a complete package, we generally follow two steps.

Step 1: Create Package Specification

SQL / PL/SQL
CREATE OR REPLACE PACKAGE package_name
AS

    PROCEDURE procedure_name;

    FUNCTION function_name
    RETURN datatype;

END package_name;
/

Step 2: Create Package Body

SQL / PL/SQL
CREATE OR REPLACE PACKAGE BODY package_name
AS

    PROCEDURE procedure_name
    AS
    BEGIN
        -- Statements
        NULL;
    END procedure_name;


    FUNCTION function_name
    RETURN datatype
    AS
    BEGIN
        -- Statements
        RETURN value;
    END function_name;

END package_name;
/

Remember: The package specification must be created before the package body.

10. How Do We Create a Package Specification?

Let's create a complete example step by step.

Our requirement is to create an employee package with the following operations:

  1. Insert an employee.
  2. Update an employee.
  3. Delete an employee.
  4. Get an employee salary.

Step 1: Create an employee table

SQL / PL/SQL
CREATE TABLE EMPLOYEE
(
    EMP_ID NUMBER PRIMARY KEY,
    EMP_NAME VARCHAR2(100),
    SALARY NUMBER
);

Step 2: Create Package Specification

SQL / PL/SQL
CREATE OR REPLACE PACKAGE EMP_PKG
AS

    PROCEDURE ADD_EMPLOYEE
    (
        P_ID NUMBER,
        P_NAME VARCHAR2,
        P_SALARY NUMBER
    );


    PROCEDURE UPDATE_EMPLOYEE
    (
        P_ID NUMBER,
        P_SALARY NUMBER
    );


    PROCEDURE DELETE_EMPLOYEE
    (
        P_ID NUMBER
    );


    FUNCTION GET_SALARY
    (
        P_ID NUMBER
    )
    RETURN NUMBER;

END EMP_PKG;
/

Here, we have declared four public components.

Notice that we have only declared the procedures and function. We have not written their actual SQL statements.

Step 3: Verify Package Specification

SQL / PL/SQL
SELECT OBJECT_NAME,
       OBJECT_TYPE,
       STATUS
FROM USER_OBJECTS
WHERE OBJECT_NAME = 'EMP_PKG';

Expected result:

OBJECT_NAMEOBJECT_TYPESTATUS
EMP_PKGPACKAGEVALID

At this point, we have created only the package specification.

Step 4: Create Package Body

Now we provide the implementation.

SQL / PL/SQL
CREATE OR REPLACE PACKAGE BODY EMP_PKG
AS

    PROCEDURE ADD_EMPLOYEE
    (
        P_ID NUMBER,
        P_NAME VARCHAR2,
        P_SALARY NUMBER
    )
    AS
    BEGIN

        INSERT INTO EMPLOYEE
        VALUES (P_ID, P_NAME, P_SALARY);

        DBMS_OUTPUT.PUT_LINE('Employee Inserted');

    END ADD_EMPLOYEE;


    PROCEDURE UPDATE_EMPLOYEE
    (
        P_ID NUMBER,
        P_SALARY NUMBER
    )
    AS
    BEGIN

        UPDATE EMPLOYEE
        SET SALARY = P_SALARY
        WHERE EMP_ID = P_ID;

        DBMS_OUTPUT.PUT_LINE('Employee Updated');

    END UPDATE_EMPLOYEE;


    PROCEDURE DELETE_EMPLOYEE
    (
        P_ID NUMBER
    )
    AS
    BEGIN

        DELETE FROM EMPLOYEE
        WHERE EMP_ID = P_ID;

        DBMS_OUTPUT.PUT_LINE('Employee Deleted');

    END DELETE_EMPLOYEE;


    FUNCTION GET_SALARY
    (
        P_ID NUMBER
    )
    RETURN NUMBER
    AS
        V_SALARY NUMBER;
    BEGIN

        SELECT SALARY
        INTO V_SALARY
        FROM EMPLOYEE
        WHERE EMP_ID = P_ID;

        RETURN V_SALARY;

    END GET_SALARY;

END EMP_PKG;
/

Step 5: Execute Package Procedures

Insert an employee:

SQL / PL/SQL
BEGIN

    EMP_PKG.ADD_EMPLOYEE
    (
        101,
        'RAHUL',
        50000
    );

END;
/

Output:

Expected output
Employee Inserted

Step 6: Update Employee Salary

SQL / PL/SQL
BEGIN

    EMP_PKG.UPDATE_EMPLOYEE
    (
        101,
        60000
    );

END;
/

Output:

Expected output
Employee Updated

Step 7: Get Employee Salary

SQL / PL/SQL
DECLARE
    V_SALARY NUMBER;
BEGIN

    V_SALARY := EMP_PKG.GET_SALARY(101);

    DBMS_OUTPUT.PUT_LINE('Salary: ' || V_SALARY);

END;
/

Output:

Expected output
Salary: 60000

Step 8: Delete Employee

SQL / PL/SQL
BEGIN

    EMP_PKG.DELETE_EMPLOYEE(101);

END;
/

Output:

Expected output
Employee Deleted

11. How to Access Procedures and Functions Inside a Package?

We use the following syntax:

For a procedure:

SQL / PL/SQL
package_name.procedure_name;

Example:

SQL / PL/SQL
BEGIN
    EMP_PKG.ADD_EMPLOYEE(101, 'RAHUL', 50000);
END;
/

For a function:

SQL / PL/SQL
package_name.function_name;

Example:

SQL / PL/SQL
DECLARE
    V_SALARY NUMBER;
BEGIN
    V_SALARY := EMP_PKG.GET_SALARY(101);

    DBMS_OUTPUT.PUT_LINE(V_SALARY);
END;
/

We use the package name followed by a dot (.) and then the procedure or function name.

12. Important Interview Points About Oracle Packages

Interview questionAnswer
How many main components does a package have?Two: Specification and Body.
Is package specification mandatory?Yes.
Is package body mandatory?No.
Can we create a package without a body?Yes, if no implementation is needed.
Can a package contain multiple procedures?Yes.
Can a package contain multiple functions?Yes.
Can a package contain variables?Yes.
Can we call a private procedure from outside?No.
Can one procedure call another procedure in the same package?Yes.
Can we access package variables from outside?Yes, if they are declared publicly.
Can a package have an initialization section?Yes, in the package body.
How do we call a package procedure?PACKAGE_NAME.PROCEDURE_NAME.

13. Quick Revision – Remember These Concepts

Oracle PL/SQL Package

A package is a collection of related PL/SQL objects grouped together.

Two main components

  1. Package Specification
  • Public interface.
  • Contains declarations.
  • Tells WHAT the package does.
  • Mandatory.
  1. Package Body
  • Contains implementation.
  • Can contain private components.
  • Tells HOW the package works.
  • Optional in certain cases.

Creation order

  1. Create package specification.
  2. Create package body.
  3. Execute package procedures/functions.

14. Practice Interview Questions

Choose an answer, then expand the explanation to check your understanding.

1. Which component of a package is mandatory?

  • Package Body
  • Package Specification
  • Both are optional
Show answer and explanation

Package Specification — Every package needs a specification. A body is needed when declarations require implementation.

2. Where do we write the actual implementation of a procedure?

  • Package Specification
  • Package Body
  • Both
Show answer and explanation

Package Body — The specification declares the public procedure; the body implements it.

3. How do we call a procedure inside a package?

  • PROCEDURE_NAME.PACKAGE_NAME
  • PACKAGE_NAME.PROCEDURE_NAME
  • PACKAGE_NAME->PROCEDURE_NAME
Show answer and explanation

PACKAGE_NAME.PROCEDURE_NAME — Use a dot between the package name and the procedure name, followed by arguments when required.

4. What is the main purpose of a package specification?

  • To provide the public interface
  • To execute SQL statements
  • To store table data
Show answer and explanation

To provide the public interface — The specification exposes the components callers can use.

5. Can a package body contain private procedures?

  • No
  • Yes
  • Only one
Show answer and explanation

Yes — Procedures declared only inside the body are private to the package.

Practice next: Create packages with procedures, functions, public variables, private procedures, and cursors. Focus on the difference between the specification and body before moving to advanced PL/SQL concepts.

No comments:

Post a Comment