How to Create a Procedure Inside a Package in Oracle

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • PLSQL
  • 1 min read

In Oracle, you first need to declare the procedure in a package specification and then create the full procedure in the package body. Because if you will try to create procedure only in the package body without its declaration in package specification then you will get the error PLS-00302 component must be declared and ORA-06550 invalid PL/SQL block. So below I am giving an example to how to create a procedure inside a package in Oracle.

In this example, we will create a procedure update_comm to update commission in EMP table. This procedure will take the parameter for commission percentage.

Create a Procedure Inside a Package in Oracle Example

Declare The Procedure in Package Specification

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

Create The Full Procedure in Package Body

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;

Now you can execute this procedure as follows:

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