What is a Package !:

A package is a schema object in which groups logically associated PL/SQL types, subprograms and items. Packages commonly have two parts, a specification and a body, while sometimes the body is unnecessary. A specification is the interface to your applications; Package declares the constants, types, variables, exceptions, subprograms and cursors available for use. The body fully describes cursors and subprograms and so implements the specification.

A package once compiled and stored, gets life in a session when any of the elements of the package is referred in the session.  Then  beyond,  the  variables,  arrays,  cursors  or subprograms described in the package are allocated and loaded in the memory for that session and  any  modification  which  is  done  to  the  elements  are  live  for  the  overall  session  across operations and across all the triggers and subprograms. This is unlike a subprogram where the variables have effect only inside the subprogram and not across programs in the session.

Unlike subprograms, packages cannot be parameterized, called, or nested. Still, the format of a package is same to that of a subprogram as display below.

CREATE PACKAGE name AS        -- specification (visible part)

-- public type and item declarations

-- subprogram specifications

END [name];

CREATE PACKAGE BODY name AS       -- body (hidden part)

-- private type and item declarations

-- subprogram bodies

[BEGIN

-- initialization statements]

END [name];

The specification contains public declarations that are visible to the application. The body contains   implementation   details   and   private   declarations that are hidden from the application. This model is very useful when modular applications are build that would enable a server centric application with all the application associated programs and variables stored as part of the package.

You can replace, debug, or enhance a package body without modification the interface (package specification) to the package body.

To create packages and store them permanently in an Oracle database the CREATE PACKAGE and CREATE PACKAGE BODY statements are used. Those statements can be executed interactively from SQL*Plus or Enterprise Manager.

In the given example below, you package a record and the cursor type and two employment procedures are packaged.  Note that the procedure hire_employee uses the database sequence empno_seq and the function SYSDATE to insert a new employee number and hire date, correspondingly.

CREATE PACKAGE emp_actions AS         -- specification

PROCEDURE hire_employee ( # procedure declaration#

ename  VARCHAR2,

job       VARCHAR2,

mgr      NUMBER,

sal        NUMBER,

comm  NUMBER,

deptno NUMBER);

PROCEDURE fire_employee (emp_id NUMBER); # procedure declaration#

END emp_actions;

CREATE PACKAGE BODY emp_actions AS        -- body

PROCEDURE hire_employee (# procedure declaration #

ename  VARCHAR2,

job       VARCHAR2,

mgr      NUMBER,

sal        NUMBER,

comm  NUMBER,

deptno NUMBER) IS

BEGIN

INSERT INTO emp VALUES (empno_seq.NEXTVAL, ename, job, mgr, SYSDATE, sal, comm, deptno);

END hire_employee;

PROCEDURE fire_employee (emp_id NUMBER) IS # procedure declaration#

BEGIN

DELETE FROM emp WHERE empno = emp_id;

END fire_employee;

END emp_actions;

The above package creates two procedures that are known as hire_employee and fire_employee.   These two procedures can be executed in the following way:

EXEC EMP_ACTIONS.HIRE_EMPLOYEE('SUDHA','MANAGER',7901,10000,200,10);

EXEC EMP_ACTIONS.FIRE_EMPLOYEE(2);# procedure called#

Only the declarations in the package specification are accessible and visible to applications. An Implementation details in the package body are inaccessible and hidden.  Thus, the body (implementation) can be modified without having to recompile the calling programs.