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:
- Package Specification
- 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:
CREATE OR REPLACE PROCEDURE ADD_EMPLOYEE
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Employee Added');
END;
/
CREATE OR REPLACE PROCEDURE UPDATE_EMPLOYEE
AS
BEGIN
DBMS_OUTPUT.PUT_LINE('Employee Updated');
END;
/
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.
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.
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.
BEGIN
EMP_PKG.ADD_EMPLOYEE;
EMP_PKG.UPDATE_EMPLOYEE;
EMP_PKG.DELETE_EMPLOYEE;
END;
/
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:
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:
CREATE OR REPLACE PACKAGE EMP_PKG
AS
PROCEDURE ADD_EMPLOYEE;
END EMP_PKG;
/
Package body:
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_EMPLOYEEis public because it is declared in the specification.VALIDATE_EMPLOYEEis 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:
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:
CREATE OR REPLACE PACKAGE TEST_PKG
AS
v_count NUMBER := 0;
PROCEDURE INCREMENT_COUNT;
END TEST_PKG;
/
Package body:
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:
BEGIN
TEST_PKG.INCREMENT_COUNT;
TEST_PKG.INCREMENT_COUNT;
TEST_PKG.INCREMENT_COUNT;
END;
/
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:
- Package Specification
- Package Body
Let's understand each component.
| Component | Purpose |
|---|---|
| Package Specification | Declares public components |
| Package Body | Contains 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
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.
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_PKGis the package name.ADD_EMPLOYEEis a public procedure.UPDATE_EMPLOYEEis a public procedure.DELETE_EMPLOYEEis a public procedure.GET_EMPLOYEE_COUNTis a public function.RETURN NUMBERspecifies 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
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.
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_EMPLOYEEprints Employee Added.UPDATE_EMPLOYEEprints Employee Updated.DELETE_EMPLOYEEprints Employee Deleted.GET_EMPLOYEE_COUNTreturns 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 Specification | Package 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
CREATE OR REPLACE PACKAGE package_name
AS
PROCEDURE procedure_name;
FUNCTION function_name
RETURN datatype;
END package_name;
/
Step 2: Create Package Body
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:
- Insert an employee.
- Update an employee.
- Delete an employee.
- Get an employee salary.
Step 1: Create an employee table
CREATE TABLE EMPLOYEE
(
EMP_ID NUMBER PRIMARY KEY,
EMP_NAME VARCHAR2(100),
SALARY NUMBER
);
Step 2: Create Package Specification
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
SELECT OBJECT_NAME,
OBJECT_TYPE,
STATUS
FROM USER_OBJECTS
WHERE OBJECT_NAME = 'EMP_PKG';
Expected result:
| OBJECT_NAME | OBJECT_TYPE | STATUS |
|---|---|---|
| EMP_PKG | PACKAGE | VALID |
At this point, we have created only the package specification.
Step 4: Create Package Body
Now we provide the implementation.
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:
BEGIN
EMP_PKG.ADD_EMPLOYEE
(
101,
'RAHUL',
50000
);
END;
/
Output:
Employee Inserted
Step 6: Update Employee Salary
BEGIN
EMP_PKG.UPDATE_EMPLOYEE
(
101,
60000
);
END;
/
Output:
Employee Updated
Step 7: Get Employee Salary
DECLARE
V_SALARY NUMBER;
BEGIN
V_SALARY := EMP_PKG.GET_SALARY(101);
DBMS_OUTPUT.PUT_LINE('Salary: ' || V_SALARY);
END;
/
Output:
Salary: 60000
Step 8: Delete Employee
BEGIN
EMP_PKG.DELETE_EMPLOYEE(101);
END;
/
Output:
Employee Deleted
11. How to Access Procedures and Functions Inside a Package?
We use the following syntax:
For a procedure:
package_name.procedure_name;
Example:
BEGIN
EMP_PKG.ADD_EMPLOYEE(101, 'RAHUL', 50000);
END;
/
For a function:
package_name.function_name;
Example:
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 question | Answer |
|---|---|
| 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
- Package Specification
- Public interface.
- Contains declarations.
- Tells WHAT the package does.
- Mandatory.
- Package Body
- Contains implementation.
- Can contain private components.
- Tells HOW the package works.
- Optional in certain cases.
Creation order
- Create package specification.
- Create package body.
- 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 BodyPackage SpecificationBoth 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 SpecificationPackage BodyBoth
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_NAMEPACKAGE_NAME.PROCEDURE_NAMEPACKAGE_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 interfaceTo execute SQL statementsTo 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?
NoYesOnly 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