In this blog, we’ll discuss the Packages in PL/SQL. Packages are schema objects that groups logically related PL/SQL types, variables, and subprograms.
A package will have two mandatory parts −
• Package specification
• Package body or definition
Package Specification
The specification is that the interface to the package. It just DECLARES the kinds, variables, constants, exceptions, cursors, and subprograms which will be referenced from outside the package. In other words, it contains all information about the content of the package, but excludes the code for the subprograms.
All objects placed within the specification are called public objects. Any subprogram not within the package specification but coded within the package body is named a personal object.
The following code snippet shows a package specification having one procedure. you’ll have many global variables defined and multiple procedures or functions inside a package.
CREATE PACKAGE cust_sal AS
PROCEDURE find_sal(c_id customers.id%type);
END cust_sal;
/
When the above code is executed at the SQL prompt, it produces the subsequent result −
Package created.
Package Body
The package body has the codes for various methods declared within the package specification and other private declarations, which are hidden from the code outside the package.
The CREATE PACKAGE BODY Statement is employed for creating the package body. the subsequent code snippet shows the package body declaration for the cust_sal package created above. I assumed that we have already got CUSTOMERS table created in our database as mentioned within the PL/SQL – Variables chapter.
CREATE OR REPLACE PACKAGE BODY cust_sal AS

PROCEDURE find_sal(c_id customers.id%TYPE) IS
c_sal customers.salary%TYPE;
BEGIN
SELECT salary INTO c_sal
FROM customers
WHERE id = c_id;
dbms_output.put_line(‘Salary: ‘|| c_sal);
END find_sal;
END cust_sal;
/
When the above code is executed at the SQL prompt, it produces the subsequent result −
Package body created.
Using the Package Elements
The package elements (variables, procedures or functions) are accessed with the subsequent syntax −
package_name.element_name;
Consider, we have already got created the above package in our database schema, the subsequent program uses the find_sal method of the cust_sal package −
DECLARE
code customers.id%type := &cc_id;
BEGIN
cust_sal.find_sal(code);
END;
/
When the above code is executed at the SQL prompt, it prompts to enter the customer ID and once you enter an ID, it displays the corresponding salary as follows −
Enter value for cc_id: 1
Salary: 3000

PL/SQL procedure successfully completed.
Example
The following program provides a more complete package. we’ll use the purchasers table stored in our database with the subsequent records −
Select * from customers;

+—-+———-+—–+———–+———-+
| ID | NAME | AGE | ADDRESS | SALARY |
+—-+———-+—–+———–+———-+
| 1 | Ramesh | 32 | Ahmedabad | 3000.00 |
| 2 | Khilan | 25 | Delhi | 3000.00 |
| 3 | kaushik | 23 | Kota | 3000.00 |
| 4 | Chaitali | 25 | Mumbai | 7500.00 |
| 5 | Hardik | 27 | Bhopal | 9500.00 |
| 6 | Komal | 22 | MP | 5500.00 |
+—-+———-+—–+———–+———-+
The Package Specification
CREATE OR REPLACE PACKAGE c_package AS
— Adds a customer
PROCEDURE addCustomer(c_id customers.id%type,
c_name customerS.No.ame%type,
c_age customers.age%type,
c_addr customers.address%type,
c_sal customers.salary%type);

— Removes a customer
PROCEDURE delCustomer(c_id customers.id%TYPE);
–Lists all customers
PROCEDURE listCustomer;

END c_package;
/
When the above code is executed at the SQL prompt, it creates the above package and displays the subsequent result −
Package created.

Creating the Package Body
CREATE OR REPLACE PACKAGE BODY c_package AS 
PROCEDURE addCustomer(c_id customers.id%type, 
c_name customerS.No.ame%type, 
c_age customers.age%type, 
c_addr customers.address%type, 
c_sal customers.salary%type) 
IS 
BEGIN 
INSERT INTO customers (id,name,age,address,salary) 
VALUES(c_id, c_name, c_age, c_addr, c_sal); 
END addCustomer; 

PROCEDURE delCustomer(c_id customers.id%type) IS 
BEGIN 
DELETE FROM customers 
WHERE id = c_id; 
END delCustomer; 

PROCEDURE listCustomer IS 
CURSOR c_customers is 
SELECT name FROM customers; 
TYPE c_list is TABLE of consumers .Name%type; 
name_list c_list := c_list(); 
counter integer :=0; 
BEGIN 
FOR n IN c_customers LOOP 
counter := counter +1; 
name_list.extend; 
name_list(counter) := n.name; 
dbms_output.put_line('Customer(' ||counter|| ')'||name_list(counter)); 
END LOOP; 
END listCustomer;

END c_package; 
/

The above example makes use of the nested table. we’ll discuss the concept of nested table within the next chapter.
When the above code is executed at the SQL prompt, it produces the subsequent result −
Package body created.
Using The Package
The following program uses the methods declared and defined within the package c_package.
DECLARE
code customers.id%type:= 8;
BEGIN
c_package.addcustomer(7, ‘Rajnish’, 25, ‘Chennai’, 3500);
c_package.addcustomer(8, ‘Subham’, 32, ‘Delhi’, 7500);
c_package.listcustomer;
c_package.delcustomer(code);
c_package.listcustomer;
END;
/
When the above code is executed at the SQL prompt, it produces the subsequent result −
Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal
Customer(7): Rajnish
Customer(8): Subham
Customer(1): Ramesh
Customer(2): Khilan
Customer(3): kaushik
Customer(4): Chaitali
Customer(5): Hardik
Customer(6): Komal
Customer(7): Rajnish

PL/SQL procedure successfully completed
So, this brings us to the end of blog. This Tecklearn ‘Packages in PL-SQL’ blog helps you with commonly asked questions if you are looking out for a job in Oracle Pl-SQL. If you wish to learn Oracle PL-SQL and build a career in Database domain, then check out our interactive, Oracle PL-SQL Training, that comes with 24*7 support to guide you throughout your learning period. Please find the link for course details:

Oracle PL SQL Training

Oracle PL-SQL Training

About the Course

Oracle PL/SQL online training course provides you the complete skills needed to create, implement and manage robust database applications using the Oracle Database tools. Our expert instructors will help you to master PL SQL advanced features, from performance to maintainability to the application code architecture. Our best online classes will help you to gain a precise knowledge of PL SQL language, architecture, interactions with the SQL engine, data types, and much more. The entire training is in line with the Oracle PL/SQL certification.

Why Should you take Oracle PL-SQL Training?

• The Average salary of a Senior Oracle PL-SQL Developer is $131,878 per annum – ZipRecuiter.com
• PL-SQL has a market share of 23% globally.
• IBM, TCS, Tech Mahindra, Oracle, Wipro & other MNCs worldwide use Pl-SQL for their database deployments.

What you will Learn in this Course?

Introduction to Oracle SQL
• Database Models
• RDBMS
• Components of SQL
• DataTypes
• DDL-Create, Alter, Rename, Drop, Truncate
Manipulating Data using SQL
• Constraints –Unique, Not Null, Primary Key, Check Constraint, Foreign Key
• DML Commands-Insert, Update, Delete
• Order by Clause
• Group Functions
• SET Operators- Union All, Union, Intersect, Minus
• TCL Commands-Commit, RollBack, Savepoint
Oracle Views and Synonyms
• Types of Views
• Synonyms
• Types of Synonyms
• Indexes
• Types of Indexes
Using Subqueries to Solve Queries
• Subqueries
• Co-Related Subquery
OLAP Functions
• OLAP Features
• Roll Up
• Model Clause
• Dimension Modelling
Conditional Statement
• Block
• Variable Attributes
• Nested Blocks
• Conditional Control Statements
• Iterative Controls (Loop)
Cursor Management
• Types of Cursor
• Writing Explicit cursors
• Explicit cursor functions
• Advance Explicit cursor
• Cursor with parameters
Exception Handling
• Handling Exception
• Handling Exception with PL/SQL Predefined Exceptions,
• User Defined Exceptions
• Non-Predefined Error
• Function for trapping Exception
• Trapping user-defined Exception
Subprogram, Procedure and passing parameters and Advance Package Concepts and functions
• Important Features of Sub-Programs
• Procedure
• Functions
Trigger Management
• Introduction to Triggers
• Types of Triggers
• Compound Triggers
Oracle Job Scheduling
Large Object Functions
• Large object functions – BFILENAME, EMPTY_BLOB, EMPTY_CLOB etc
Important Features of Oracle
Advance level- Scripting

717