Skip to main content

Package in Oracle and it’s Advantage in PL/SQL Program #Diksha Oracle Training Centre

 

Package in Oracle and it’s Advantage in PL/SQL Program.


Hello Everyone,

In my Previous Blogs I Discussed about Stored Procedures, Stored Functions, Cursor etc. Today we will discuss about Oracle Packages. Oracle Package plays very important role in PL/SQL program . Package we can say collection of Procedures , Functions , Cursor etc . In another word we can say A package is a schema object that groups logically related PL/SQL types, variables, and subprograms.

 Packages usually have two parts:

Package Specification and Package Body, Sometimes the body is not required .The specification is the main interface to the package. It declares the types, variables, constants, exceptions, cursors, and subprograms that can be referenced from outside the package. The body mainly defines the queries for the cursors and the code for the subprograms.

PL/SQL Package is very important in Database and as mentioned above it contains multiple procedures , Functions, variables , cursors etc so before creating Oracle Package and see the benefits of Package please see my previous below blog again for Stored procedures , Stored Functions , Cursor etc for your reference so that it’s  easy for you to understand Packages in PL/SQL.

How to Create Package in PL/SQL:

Create Package :  CREATE PACKAGE statement to create the specification for a stored package, which is an encapsulated collection of related procedures, functions, and other program objects stored together in the database. The package specification declares these objects. The package body, specified subsequently, defines these objects. To create a package in your own schema, you must have the CREATE PROCEDURE system privilege. To create a package in another user's schema, we must have the CREATE ANY PROCEDURE system privilege. To embed a CREATE PACKAGE statement inside an Oracle Database precompiled program, you must terminate the statement with the keyword END-EXEC followed by the embedded SQL statement terminator for the specific language.

 

Syntax for creating Package specification :

CREATE [OR REPLACE] PACKAGE <package_name>

IS

<sub_program and public element declaration>

.END <package name>

Here in subprogram and public element declaration we can have multiple procedures, functions, variables, cursor etc .Few characteristics of the Package specification is as below.

The elements which are all declared in the specification can be accessed from outside of the package. Such elements are known as a public element. Package specification is a standalone element means that it can exist alone without package body. Whenever a package has referred an instance of the package is created for that particular session.

Syntax for creating Package Body:

CREATE [OR REPLACE] PACKAGE BODY <package_name>

IS

<global_declaration part>

<Private element definition>

<sub_program and public element definition>

.

<Package Initialization>

END <package_name>

 

Package Body consists of the definition of all the elements that are present in the package specification. It can also have a definition of elements that are not declared in the specification these elements are called private elements and can be called only from inside the package.

Note: Package Body should contain definitions for all the subprograms/cursors that have been declared in the Package specification. It can also have more subprograms or other elements that are not declared in specification. These are called private elements which can hide information. Package Body depends on package specification.

So simply we can say we use package body as a Private which used all the subprograms/variable declared in Package Specification. Also use to hide information from the user, but Package body can’t exist without Package Specification but Package Specification can exist without Package Body.

 

Example of creating one package which contains Procedures in package Specification and manipulating the data of emp of table in package body:

   CREATE OR REPLACE PACKAGE DOTC

AS

    PROCEDURE NEW_EMP (V_EMPNO IN NUMBER , V_ENAME IN VARCHAR2 , V_JOB IN VARCHAR2 , V_SAL IN NUMBER , V_DEPTNO IN NUMBER);

    PROCEDURE EMP_RESIG (V_EMPNO IN NUMBER);

END DOTC;

/

The specification for the DOTC package declares the  public program objects .

 

Note: All of these objects are available to users who have access to the package. After creating the package, we can develop applications that call any of these public procedures or functions or raise any of the public exceptions of the package. But before you can call this package's procedures , you must define procedures  in the package body.

Creation of Package Body which uses procedures in Package Specification:

 CREATE OR REPLACE PACKAGE BODY DOTC

AS 

  PROCEDURE NEW_EMP (V_EMPNO IN NUMBER , V_ENAME IN VARCHAR2 , V_JOB IN VARCHAR2 , V_SAL IN NUMBER , 

V_DEPTNO IN NUMBER)

   IS 

    BEGIN 

      INSERT INTO EMP (EMPNO,ENAME,JOB,SAL,DEPTNO) VALUES(V_EMPNO,V_ENAME,V_JOB,V_SAL,V_DEPTNO);

      END NEW_EMP;

  PROCEDURE EMP_RESIG (V_EMPNO IN NUMBER)

IS 

   BEGIN 

       DELETE FROM EMP WHERE EMPNO=V_EMPNO;

 END  EMP_RESIG;

END DOTC;

 

 

In Package Body we have manipulated the  procedures which we declared in Package Specification. So what we defined in Package specification we can do manipulation in Package Body which benefits programmer to hide the transaction information from the user.

Output of Above package is as below :





Writing one Another Simple Package by using one simple procedure and function.

Package Specification:

create or replace package DOTC is

procedure insert_record (p_ID IN TEST.ID%TYPE,

                         P_NAME IN TEST.NAME%TYPE,

                         P_AMOUNT IN TEST.AMOUNT%TYPE,

                         P_ADDRESS IN  TEST.ADDRESS%TYPE);

function get_record (p_id IN NUMBER) return

TEST%ROWTYPE;

end DOTC;

/

Package Body:

create or replace package BODY DOTC is

procedure insert_record (p_ID IN TEST.ID%TYPE,

                         P_NAME IN TEST.NAME%TYPE,

                         P_AMOUNT IN TEST.AMOUNT%TYPE,

                         P_ADDRESS IN TEST.ADDRESS%TYPE ) IS

PRAGMA AUTONOMOUS_TRANSACTION;

BEGIN

INSERT INTO TEMP VALUES(P_ID,P_NAME,P_AMOUNT,P_ADDRESS);

COMMIT;

END INSERT_RECORD;

FUNCTION GET_RECORD (P_ID IN NUMBER )

RETURN TEST%ROWTYPE

IS

V_TEST TEST%ROWTYPE;

BEGIN

SELECT * INTO V_TEST FROM TEST WHERE ID=P_ID;

RETURN V_TEST;

END GET_RECORD;

BEGIN

DBMS_OUTPUT.PUT_LINE('CONTROL IS GIVEN TO PACKAGE SPECIFICATION');

END DOTC;

/

Output of above Package is as below:

 

 


Advantage of using Packages in PL/SQL.

 

Why we use Package as we can do by using Stored Procedures and functions .  So let’s see the Advantage of package and why Package should use in PL/SQL and it’s benefits:

Information Hiding

With packages, you can specify which types, items, and subprograms are public (visible and accessible) or private (hidden and inaccessible).  So we create Package body to hide the information.

Modularity

Packages let you encapsulate logically related types, items, and subprograms in a named PL/SQL module. Each package is easy to understand, and the interfaces between packages are simple, clear, and well defined.

Easier Application Design

When designing an application, all you need initially is the interface information in the package specification. You can code and compile a specification without its body. Then, stored subprograms that reference the package can be compiled as well. You need not define the package body fully until you are ready to complete the application.

Added Functionality

Packages can be used to add functionality of the programs.

Better Performance

When you call a packaged subprogram for the first time, the whole package is loaded into memory. Later calls to related subprograms in the package require no disk I/O.

Important : We can use procedures, functions etc. in run time by specifying packagename.proc_name  etc. . Now we are able to understand about the Package and its Advantages in PL/SQL program. One thing needs to very clear that Packages Specification can exist without Package Body whereas Package Body can’t exist without Package Specification .

Please read this blog very carefully and also take reference for stored procedure , functions , cursor etc from my previous blog and try to make some different package in which you can use multiple procedures , functions etc. and do the manipulation in Package body and see the benefits of your Program.

Thanks and Please let me know if you have any doubts on Packages.

 

Comments

Popular posts from this blog

SQL and Classification of SQL in Oracle Database #Diksha Oracle Training Centre

  SQL and Classification of SQL in Oracle Database.   SQL is Structured Query Language , which is used for storing, manipulating and retrieving data stored in a relational database .SQL is the standard language for RDBMS. All the Relational Database Management Systems (RDMS) like Oracle, MySQL, Sybase, Informix, IBM DB2 and Microsoft SQL Server use SQL as their standard database language. Oracle is one of the more secured database as compared to other databases. Importance of   SQL : SQL and PL/SQL is a backend process where all data is stored and retrieved in GUI which created either by any programming languages like Java, C++, PHP etc. so we need to have very secure database so that there will be no impact for users. SQL allows users to access data in the relational database management systems. SQL is used to communicate with a database.SQL and PL/SQL allows users to create and drop databases tables , views , stored procedures , functions , packages , trigger etc. SQL allows

Materialized View uses and Benefits in Database #DOTC_Mdb

Materialized View uses and Benefits in Database. Hello Everyone, Today we will discuss about Materialized view as it’s play important role in database. We already discussed about Simple Views and complex views in my previous blog. Before Materialized view go through my previous blog which related to simple view. https://dheeraj60.blogspot.com/2020/05/benefits-of-creating-and-using-view-in.html As we know View is not a database object and not like table which is stored in database, but view can be created from base table a view is an SQL statement that’s stored in the database. This statement, or view, has a name.A view looks and acts a lot like a table. It has columns and rows, and can be included in SELECT queries just like a table. In other word we can say View is a virtual/logical table which is basically used for security purpose. Let’s understand   about   Materialized view : A materialized view is a view that stores the results of the view’s query. Whenever you query the ma

Top 50 Interview Questions On SQL and PL/SQL #DOTC_Mdb

                    Top 50 Interview Questions On SQL and PL/SQL. Today we will Discuss Top 50 interview questions and answers of SQL and PL/SQL which is frequently asked in interview.     Question 1: What is SQL and Classification of SQL? Answer SQL is a Structure Query Language which is vastly used in RDBMS database like Oracle, Sybase, DB2 , Microsoft SQL server etc.   Classification of SQL is as below: DDL (Data Definition Language):  Commands are  create , alter , drop , truncate etc DML (Data Manipulation Language) : Commands are  insert,update and delete . TCL (Transaction Control Language ) : Commands are  Commit , Rollback and Save point. DCL (Data Control Language) : Commands are Grant , Revoke Question 2:    What is meant by Joins? What are the types of join? Answer Joins are basically used to extract/get data from multiple tables using some common columns or conditions and also by using alias to fulfill the condition.   There are various types of Joins as li