Package in Oracle

Packages in oracle:-

Scenario:-

When you want to relate some procedures and function with each other’s or you want to declare global variables you can use packages. It will hold variables, procedures and functions in one body.

What are packages in oracle?

A package is a schema object that groups logically related PL/SQL types, items, and subprograms. Packages usually have two parts, a specification and a body, although sometimes the body is unnecessary. The specification (spec for short) is the interface to your applications; it declares the types, variables, constants, exceptions, cursors, and subprograms available for use. The body as whole defines cursors and subprograms, and so implements the spec.

The concept of global, public and private object in package:-

As discuss above, to create package we need to create two parts, part 1 is package specification and part 2 is package body. Object’s declared in package specification are called global object. Objects declared in the package body and not inside any procedures or functions in the package body are called public objects. Objects in the package body inside the procedures or functions are called private objects. For example:-

How to create a packge?

Syntax of the package is :-

Package have two parts, Package Specification and Package Body. Below is Specification:-

Syntax for package specification:-

CREATE [OR REPLACE] PACKAGE PACKAGE_NAME

   [AUTHID {CURRENT_USER | DEFINER}]

   {IS | AS}

   [GLOBAL VARIABLE DECLARATION]

   [CURSOR_SPEC ...]

   [FUNCTION_SPEC ...]

   [PROCEDURE_SPEC ...]

   [CALL_SPEC ...]

   [PRAGMA RESTRICT_REFERENCES (ASSERTIONS) ...]

END [PACKAGE_NAME];

You do not required to mention package SPECIFICATION keyword while create package specification. Oracle by default will consider it as a specification.

Syntax for the package body:-

[CREATE [OR REPLACE] PACKAGE BODY PACKAGE_NAME {IS | AS}

      [PUBLIC VARIABLE DECLARATION]

      [CURSOR_BODY ...]

   [FUNCTION_SPEC ...]

   [PROCEDURE_SPEC ...]

   [CALL_SPEC ...]

[BEGIN

   SEQUENCE_OF_STATEMENTS]

PROCEDURE/ FUNCTION BODY [PRIVATE VARIABLE DECLARATION]

END [PACKAGE_NAME];]

While creating package body you need to specify BODY keyword.

You cannot use public and private variables outside the package body. However you can use global variable even outside the body.

There can be a package specification without package body but there can not be package body without package specification.

Let’s look at the example of package:-

Below is the simple package specification:-

Below is the package body of packaged SAMPLE created above:-

We have created SAMPLE package. Now let’s see how it can be executed.

Since there are two subprograms inside the package one is procedure and other is function. Both procedure and function have to be executed in a different way. Like if you want to call procedure from package you would need to execute it in a different way and procedure in a different way.

Let’s try to execute procedure from the package SAMPLE :-

Let’s try to execute function from package SAMPLE :-

You can execute function from a packge in a  different way’s. like in a plsql block or by using execute dbms_output.put_line.

If you want to drop package, you can drop by using drop command:-

Package has two part package specification and package body. If you no longer required package body, you can drop it or you can drop whole package with body.

Droping package body only:-

DROP PACKAGE BODY PACKAGE_NAME;

In our case if we want to drop package body then we can drop it like:-

DROP PACKAGE BODY SAMPLE;

If I don’t want whole package then I can drop whole package like:-

DROP PACKAGE PACKAGE_NAME

In our case we can drop package like:-

DROP PACKAGE SAMPLE;



Creating function