Back to: Oracle Tutorials for Beginners and Professionals
Overloading Subprograms with Procedures in Oracle
In this article, I am going to discuss Overloading Subprograms with Procedures in Oracle with Examples. Please read our previous article where we discussed Recompile Packages in Oracle. This is a new series of articles where we understand working with packages. We will talk about new and advanced concepts in packages. This chapter is very important. Now, let’s start learning about overloading in packages.
What is Overloading Subprogram in Oracle?
The Oracle will let us create many procedures with the same name in the package. We can create a procedure in the package called add_employee. We can repeat this procedure three times and each procedure takes the parameters differently. We have some conditions to maintain overloading.
Why Create Many Procedures Inside the Package in Oracle?
For example, in the enterprise application like Oracle ERP, we have API. This interface is for data migration. Oracle creates many procedures in the same package. This API will determine the procedure according to the situation. All the procedures in the package serve the same package and insert records in the employee table but each procedure takes different parameters. According to the situation and work that needs to be done we can choose a different procedure. This is the main reason we can create many procedures inside the package.
- This enables us to create two or more subprograms with the same name.
- Requires the subprogram’s formal parameters to differ in number, order, or data type family. Enables you to build flexible ways for invoking subprograms with different data.
- Provides a way to extend functionality without loss of existing code i.e. adding new parameters to existing subprograms.
- Provides a way to overload local subprograms package subprograms, and type methods, but not stand-alone subprograms.
In the upcoming article, we will take a real-time example and learn more about overloading packages.
Example to Understand Overloading Subprogram in Oracle:
Now, let’s learn the overloading with an example. So, we are going to create a customer table and create a package with the name overload_proc and add two procedures with the same name. First, let’s go ahead and create a table by executing the below create table statement.
create table customer (cust_id number, name varchar2(100), birthday date );
We are creating the table with three columns cust_id, name, and birthday. Let’s go ahead and create the table.
So, the table is now created. Now, let’s check the Overloading Subprograms in Oracle with different methods.
Method 1:
Let’s create two procedures that differ in parameters. Let’s go ahead and create a package that contains two procedures with the same name.
create or replace package overload_proc is procedure add_cust(p_id number,p_name varchar2,p_bd date); procedure add_cust(p_id number,p_name varchar2); end;
We have created a package with the name overload_proc. This is the package specification which contains two procedure headers. The first procedure takes parameters as p_id, p_name, and birthday. The second procedure takes two parameters p_id and p_name. So, we will use procedure 2 when we require only two values available to use. Oracle will not find any difficulty to understand this concept because each procedure takes parameters so Oracle distinguishes between two parameters. Let’s create the package specification.
So, the package specification is created. Let’s now check the package body.
create or replace package body overload_proc is procedure add_cust(p_id number,p_name varchar2,p_bd date) is begin insert into customer (cust_id,name,birthday) values (p_id,p_name,p_bd); commit; end; procedure add_cust(p_id number,p_name varchar2) is begin insert into customer (cust_id,name) values (p_id,p_name); commit; end; end;
This is the package body. The package body has two procedures with the same name and taking different parameters. The first name procedure will take p_id, p_name, and p_bd and will insert the record in the table customer into the columns cust_id, name, and birthday. The second procedure will take two parameters p_id and p_name as the input. This inserts cust_id and the name into the table customer. Let’s go ahead and compile the package body.
So, the package body is now compiled. Let’s now test the package with a few examples. First, let’s call the package and procedure by adding three parameters by executing the below query.
execute overload_proc.add_cust(1,’jay’,’1-jan-21′);
So, we are adding data to the table customers with three parameters. Let’s run this statement.
The procedure is executed successfully. Let’s check the table customers by executing the below select statement.
select * from customers;
So, the record is inserted now. Let’s now try the other method to execute the other procedure by executing the below statement.
execute overload_proc.add_cuist(2,’sam’);
So, the procedure is also executed. This procedure will add cust_id and name to the table customer. Let’s check the table customers by executing the below select statement.
select * from customers;
So, the two records are inserted now. So, now we understood that the overloading with the same procedure name but different parameters. Let’s try to understand another method of this overloading.
Method 2:
In the second method, we will use the same procedure name with the same number of parameters but differ in type. First, execute the below statements to create the package specification.
create or replace package overload_proc is procedure add_cust(p_id number,p_name varchar2,p_bd date); procedure add_cust(p_id number,p_name varchar2); procedure add_cust(p_name varchar2,p_id number); end;
From the above package specification, we can see there are three procedures with the same names, and procedure 2 and procedure 3 take two parameters as input the order is different. Procedure 2 takes p_id, and p_name as the input parameters whereas Procedure 3 takes p_name and p_id as the input parameters. Let’s compile this package specification.
So, the package specification is now compiled. Let’s look at the package body as follows.
create or replace package body overload_proc is procedure add_cust(p_id number,p_name varchar2,p_bd date) is begin insert into customer (cust_id,name,birthday) values (p_id,p_name,p_bd); commit; end; procedure add_cust(p_id number,p_name varchar2) is begin insert into customer (cust_id,name) values (p_id,p_name); commit; end; procedure add_cust(p_name varchar2,p_id number) is begin insert into customer (name,cust_id) values (p_name,p_id); commit; end; end;
We are using the same package body but we are adding a new procedure where it takes p_name as the first parameter and p_id as the second parameter. This inserts in the same method. Name and then the cust_id. Let’s go ahead and compile this package body.
So, the package body is also compiled. Now, let’s go ahead and check the package by inserting data into it using the procedures. Let’s try to execute procedure 3 as we already tested the first two procedures by executing the below statement.
Execute overload_proc.add_Cust(‘arun’,3);
So, the procedure is now executed successfully. Let’s try to check the table customer by executing the below select statement.
select * from customer;
So, the third procedure is executed successfully and the record is inserted into the table customer.
Method 3:
Procedures with the same number of parameters and same variables and different datatypes. In this case, we will same procedure names with the same parameters but a slight change in the datatypes. Let’s look at the statement and understand more about this.
create or replace package overload_proc is procedure add_cust(p_id number,p_name varchar2,p_bd date); procedure add_cust(p_id integer,p_name varchar2,p_bd date); end;
So, the package contains two procedures with the same name and the same input parameters like p_id, p_name, and p_bd. But the change in the datatypes. The first procedure takes p_id as a number datatype but the second procedure takes p_id as an integer datatype. The remaining parameters are taking the same value. Let’s now compile the package specification.
So, the package specification is now compiled Let’s have a look at the package body. We can see the two packages takes the same parameters as input and p_id is taking the different datatypes. Apart from this, the entire procedure is the same adding the values to the customer table.
create or replace package body overload_proc is procedure add_cust(p_id number,p_name varchar2,p_bd date) is begin insert into customer (cust_id,name,birthday) values (p_id,p_name,p_bd); commit; end; procedure add_cust(p_id integer,p_name varchar2,p_bd date) is begin insert into customer (cust_id,name,birthday) values (p_id,p_name,p_bd); commit; end; end;
Let’s compile the package body.
So, the package body is now compiled. Let’s try to test the package by executing the procedures as follows.
execute overload_proc.add_cust(10.6,’daya’,’1-jan-99′);
This is giving an error because Oracle cannot understand which procedure it should call because p_id is having integer and number as the datatypes.
Oracle tries to call one of the procedures inside the function it cannot call because Oracle cannot identify which procedure needs to be called, So the overloading doesn’t’ work with the same parameters.
In the next article, I am going to discuss Overloading Subprograms with Functions in Oracle with Examples. Here, in this article, I try to explain Overloading Subprograms with Procedures in Oracle with Examples. I hope you enjoy this Overloading Subprogram with Procedures in Oracle article.