How To Create Package Body In Oracle

Learn about PL SQL Package, its advantages, specifications, and structure:

In this article, we will continue with PL/SQL series. In thePL 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!!

What You Will Learn:

• PL SQL Package
  • Advantages Of PL/SQL Package
  • Package Specification
  • Package Body
  • Refer To Package Elements
  • Create A PL SQL Package
  • Overloading A Package
  • PLSQL Package Dependency
  • Package Information In PL/SQL
• DBMS Output In PL/SQL
  • Frequently Asked Questions And Answers
• Conclusion
  • Recommended Reading

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:

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>>

How To Create Package Body In Oracle

Source: https://www.softwaretestinghelp.com/pl-sql-packages/

Posted by: watsonaccee1988.blogspot.com

Share this post