How to Create a Procedure Inside a Package in Oracle

When working with Oracle PL/SQL, it is important to understand the correct way to define procedures within packages. In Oracle, every procedure or function that is part of a package must first be declared in the package specification before you can provide its implementation in the package body. Failing to declare the procedure in the package specification will result in errors such as PLS-00302: component must be declared and ORA-06550: invalid PL/SQL block.

Below, I will guide you step by step on how to properly create a procedure inside a package in Oracle.

Example: Creating a Procedure Inside a Package

In this example, we will create a procedure named update_comm that updates the commission in the EMP table. This procedure will accept a parameter indicating the commission percentage to apply to each employee's salary.

Step 1: Declare the Procedure in the Package Specification

First, you declare the procedure in the package specification. The package specification acts as the interface for your package, listing all public procedures and functions.

CREATE OR REPLACE PACKAGE emp_pkg
IS
    PROCEDURE update_comm(i_comm IN emp.comm%TYPE);
END emp_pkg;

Here, the procedure update_comm is declared, with a parameter i_comm that specifies the commission percentage.

Step 2: Define the Procedure in the Package Body

Next, you provide the full implementation of the procedure in the package body. This is where you write the logic for how the procedure works.

CREATE OR REPLACE PACKAGE BODY emp_pkg
IS
    PROCEDURE update_comm(i_comm IN emp.comm%TYPE)
    IS
    BEGIN
        UPDATE emp
           SET comm = sal * NVL(i_comm, 0) / 100;
        COMMIT;
    END update_comm;
END emp_pkg;

In this implementation:

• The procedure updates the comm column in the EMP table.
• The new commission is calculated as a percentage of each employee's salary.
• The NVL function ensures that if i_comm is NULL, it defaults to 0.
• The COMMIT statement saves the changes to the database.

Step 3: Executing the Procedure

Once the package has been created, you can execute the procedure using an anonymous PL/SQL block. For example, to update the commission to 5% of the salary for all employees, use the following code:

BEGIN
    /* Updating commission with 5% of salary */
    emp_pkg.update_comm(5);
END;

Summary

• Always declare your procedures and functions in the package specification.
• Implement the full procedure in the package body.
• If you skip the declaration in the specification, Oracle will raise an error when you try to use the procedure.
• Packages help organize your PL/SQL code and make it reusable and easier to maintain.

By following these steps, you can successfully create and use procedures within packages in Oracle. This approach is essential for building robust, modular, and maintainable PL/SQL applications.