Learn about PL SQL Package, its advantages, specifications, and structure:
In this article, we will continue with PL/SQL series. In the PL SQL Records tutorial, we have learned about Records and Record Types with programming examples.
Here we shall explore packages in PL/SQL and some important features of packages. Also, we will discuss some of the basic operations on them.
We will also discuss dbms_ouput packages in PL/SQL and its subprograms.
Let’s start with the learnings!!
Table of Contents:
PL SQL Package
A package is an object in the form of a schema that segregates logically connected items, types, and subprograms in PL/SQL. A package consists of two parts: Package specification and Package body or definition.
The package specification is like an interface to the application and is used to declare variables, constants, types, exceptions, cursors, and subprograms. The body of a package implements the subprograms and cursors declared in the package specification.
We can update, debug, or change a package body without touching the package specification (also called an interface to the package).
Advantages Of PL/SQL Package
Advantages of the package are listed below:
- It gives modularity to our code.
- It helps to design the application easily.
- PLSQL Package helps to hide information with the help of public and private items, data types, and subprograms.
- package allows maintaining information over all the transactions without requiring to store it in the database.
- Packages help improve performance of execution.
Package Specification
A package specification is like an interface to the package. It takes care of the declaration of variables, constants, types, exceptions, cursors, and subprograms. Thus it has all the details about the package content but does not have the implementation of the subprograms.
The objects inside a package specification are public objects. All subprograms implemented within a package body are private objects. We can declare multiple global variables, functions, or procedures within the package specification.
A package specification is an independent entity and can exist without a package body.
Code implementation:
CREATE PACKAGE citi AS
FUNCTION p_strng RETURN VARCHAR2;
END citi;
/
The output of the above code:
Package Body
The package body contains the implementation of the cursors and subprograms declared in the package specification. It must be remembered that the subprograms implemented in the package body can be accessed outside the package provided they are declared in the package specification.
The subprogram declared in the package specification and body must be exact. This comparison is done token-by-token of their headers. In case they do not match, PL/SQL throws an exception.
The package body may contain other subprograms that are not declared in the package specification. In that case, those are private to that package body only. CREATE PACKAGE BODY is the keyword used for creating a package body.
Let us take an example to create the package body for the citi package.
Code implementation with package body.
CREATE OR REPLACE PACKAGE BODY citi AS
--function implemented
FUNCTION p_strng RETURN VARCHAR2 IS
BEGIN
RETURN 'Software Testing Help!';
END p_strng;
END citi;
/
The output of the above code should be.
Refer To Package Elements
As we are done with declaring the elements in the package specification and implementing them in the package body, we need to refer to these elements. The package elements consist of the functions, procedures, and variables.
These public elements of the package can be accessed by the package name followed by the element name separated by the dot (.) notation.
Syntax:
package_name.element_name;
Coding implementation for accessing elements:
BEGIN DBMS_OUTPUT.PUT_LINE (citi.p_strng); END;
The output of the above code:
Create A PL SQL Package
If a package is called or referred to in a session, a new instance of that package gets created in PL/SQL. We have the option to initialize the elements of the package or perform any other actions during instance creation with the Package Initialization block of code. This is the executable block of code within the package body after the initialization of the package elements.
Syntax of package creation:
CREATE [OR REPLACE] PACKAGE BODY <package_n> IS < private element definition > < function, procedure and public element definition > BEGIN < package initialization code > END < package_n >
Here, the package_n is the name of the package.
Overloading A Package
There can be multiple subprograms within a package having similar names. This feature is useful if we want to have homogenous parameters with heterogeneous data types. The concept of overloading within the package allows the programmers to mention clearly the type of action they want to perform.
Coding Implementation with procedure overloading. (Package created):
CREATE PACKAGE overloadingprocedure AS
Procedure overl_method (p varchar2);
Procedure overl_method (numbr number);
END overloadingprocedure; /
The output of the above code:
Coding Implementation with procedure overloading. (Package body created)
CREATE OR REPLACE PACKAGE BODY overloadingprocedure AS
--procedure implemented
Procedure overl_method (p varchar2) AS
BEGIN
DBMS_OUTPUT.PUT_LINE ('First Procedure: ' || p);
END;
--procedure implemented
Procedure overl_method (numbr number) AS
BEGIN
DBMS_OUTPUT.PUT_LINE ('Second Procedure: ' || numbr);
END;
END;
/
The output of the above code:
Coding Implementation with procedure overloading. (Package procedure referring)
BEGIN
overloadingprocedure.overl_method ('Software Testing Help');
overloadingprocedure.overl_method (1);
END;
The output of the above code:
PLSQL Package Dependency
The package dependencies in PL/SQL are listed below:
- A package specification is an independent identity.
- Package body is reliant on the package specification.
- A package body can only be compiled separately. However, if a package specification is compiled then the body needs to be compiled again.
- Function or a procedure inside a package body that depends on the private elements should be implemented post declaration of the private elements.
Guidelines for creating a Package in PL SQL:
- A package should be written in generic terms so they can be reused in the future.
- Avoid duplicate characteristics in a package.
- Package specification contains the key to a properly designed application. Complete package specification before creating the package body.
- A package specification should only contain items, subprograms, and types that must be accessible to the package users. It should not have unnecessary implementation information.
- The package specification should contain a limited number of items. It shall save time for recompiling the code as a modification to the package specification requires PL/SQL to recompile all the subprograms that refer the package.
Package Information In PL/SQL
All the relevant details like the source of the package, subprograms, and overloaded items are stored in data definition tables after a package is created.
The list of the data definition tables are as follows:
- USER_PROCEDURES: This table contains subprogram information like the overloaded items, object_id, and so on for the current user.
- ALL_PROCEDURES: This table contains subprogram information like the overloaded items, object_id, and so on for all the users.
- USER_SOURCE: This table contains the information on the object source for the current user.
- ALL_SOURCE: This table contains the information on the object source for all the users.
- ALL_OBJECT: This table contains the information on the package like the creation_date, object_id, and other object detail for all the users.
DBMS Output In PL/SQL
DBMS_OUTPUT package allows the display of the PL/SQL output produced from subprograms and blocks of code. This helps us to debug, test our code, and to send messages.
The put_line procedure produces the output data to a buffer. The information is displayed with the help of the get_line procedure or by configuring SERVEROUTPUT ON in the SQL*Plus.
DBMS_OUTPUT package contains the following subprograms:
| Sl No. | Name | Purposes |
|---|---|---|
| 1 | DBMS_OUTPUT.DISABLE | Confines the message output. |
| 2 | DBMS_OUTPUT.ENABLE (buffer IN INTEGER DEFAULT 20000) | Allows the message output. If the buffer is set to NULL, it represents an unlimited size of the buffer. |
| 3 | DBMS_OUTPUT.GET_LINE (line OUT VARCHAR, status OUT NUMBER) | Fetches a buffered information within a single line. |
| 4 | DBMS_OUTPUT.NEW_LINE | Terminates an end of line marker. |
| 5 | DBMS_OUTPUT.PUT (item IN VARCHAR) | Puts an incomplete line in the buffer. |
| 6 | DBMS_OUTPUT.PUT_LINE (item IN VARCHAR) | Puts a complete line in the buffer. |
Code Implementation:
DECLARE
BEGIN
DBMS_OUTPUT.PUT_LINE ('Software Testing Help!');
END;
The output of the above code:
Frequently Asked Questions And Answers
Q #1) What is package in PL SQL?
Answer: A package is an object schema that categories logically related variables, constants, cursors, subprograms, and types.
Q #2) What are the benefits of PL SQL packages?
Answer: The benefits of the package are listed below:
- With packages, it becomes easy to design applications.
- With packages, we can achieve encapsulation.
- The packages help to get better performance in terms of execution speed.
- The code becomes more modular with packages.
Q #3) Can we create a package body without specification?
Answer: Yes, we can create a package body without package specification.
Q #4) What is Dbms_output Put_line in PL SQL?
Answer: The dbms_output put_line is a procedure in Oracle which enables us to write information in a flat file or in the PL/SQL output screen.
Q #5) What is Dbms_output?
Answer: The dbms_output is a default package that allows us to display debugging information, output and to send messages from the subprograms, triggers, packages, and PL/SQL block of code
Conclusion
In this tutorial, we have discussed in detail some basic concepts about PL SQL Packages that are essential to use them in real applications.
We have covered the following topics listed below:
- Packages and their structure.
- Different features of packages.
- DBMS Output.
- Subprograms of DBMS_OUTPUT package.
<< PREV Tutorial | NEXT Tutorial>>