What is an Oracle Package

A package is an encapsulated collection of related PL/SQL types, objects, procedures, and other program objects. Packages have a package specification and a package body. Package specification declares constants, variables, types, cursors, exceptions and procedures. Package definition defines cursors and procedures.

Packages provide the advantages of modularity, information hiding, and encapsulation.

Package commands To create a package

CREATE PACKAGE package_name

To create package body

CREATE PACKAGE_BODY package_body_name

To change a package

ALTER PACKAGE package_name options

To delete a package

Drop PACKAGE package_name

To override an existing package

CREATE OR REPLACE package_name

This command would recreate a package if it already exists. It maintains objects and privileges of the older package.

Permissions You require:

• CREATE PROCEDURE system privilege to create a package in your schema.
• you require the CREATE ANY PROCEDURE system privilege to create a package in another user’s schema

Examples

PACKAGE package_name IS
  TYPE ...
  CURSOR ... 
  Procedure ...
END package_name;

PACKAGE BODY package_body_name IS
  CURSOR ...
  PROCEDURE ...
END package_body_name;

Packages cannot be nested.