Overview of Packages in Oracle

Alex Onsman

Answered on 2nd Aug, 2018

Packages are SQL procedures, functions, variables, statements etc. that are grouped into a single unit. Many different applications can share the contents of a package, as it is stored in the database.

Parts of a Package

The following are the parts of a package in Oracle:

Package Specification

The package specifications contains information about all the procedures, functions, variables, constants etc. stored inside it. It has the declaration of all the components but not the code.

All the objects that are in the specification are known as public objects. If there is any object that is not available in the specification but is coded in the body, then it is known as a private object.

Syntax of Package specification is:

CREATE [OR REPLACE] PACKAGE name_of_package
    IS | AS
    [declaration_of_variable ...]
    [declaration_of_constant ...]
    [declaration_of_exception ...] 
    [cursor_specification ...]
    [PROCEDURE [Schema..] name_of_procedure
        [ (parameter {IN,OUT,IN OUT} datatype [,parameter]) ]
    ]
    [FUNCTION [Schema..] name_of_function
        [ (parameter {IN,OUT,IN OUT} datatype [,parameter]) ]
        RETURN return_datatype
    ]
END [name_of_package];

Package Definition or Package Body

The package body contains the code for all the public objects that were declared in the package specification as well as the private objects.

The package body can be created by using the CREATE PACKAGE BODY STATEMENT.

Syntax of Package body is:

CREATE [OR REPLACE] PACKAGE BODY name_of_package
    IS | AS
    [declaration_of_private_variable ...]
    [declaration_of_private_constant ...]
    BEGIN
        [initialization_statement]
        [PROCEDURE [Schema..] name_of_procedure
            [ (parameter [,parameter]) ]
            IS | AS
                declaration_of_variables;
                declaration_of_constants;                  
            BEGIN
                statement(s);
            EXCEPTION
                WHEN ...
            END
        ]
        [FUNCTION  [Schema..] name_of_function
                [ (parameter [,parameter]) ]
                RETURN return_datatype
            IS | AS
                 declaration_of_variables;
                 declaration_of_constants;         
            BEGIN
                statement(s);
            EXCEPTION
                WHEN ...
            END
        ]
    [EXCEPTION 
        WHEN built-in_exception_name1 THEN
            User defined statement (action) will be taken;
    ]   
END;

Example

Let us first create a table named STUDENTS:

CREATE TABLE STUDENTS( 
   ID INT NOT NULL, 
   NAME VARCHAR (25) NOT NULL, 
   AGE INT NOT NULL, 
   ADDRESS CHAR (30), 
   FEES DECIMAL (18, 2),        
   PRIMARY KEY (ID) 
);  
Table Created

We will now insert the values:

INSERT INTO STUDENTS(ID,NAME,AGE,ADDRESS,FEES) 
VALUES (1, Tom, 22,'Ohio', 17000 );  

INSERT INTO STUDENTS(ID,NAME,AGE,ADDRESS,FEES) 
VALUES (2, Jack, 20, 'Washington', 25000 );  

INSERT INTO STUDENTS(ID,NAME,AGE,ADDRESS,FEES) 
VALUES (3, Amy, 25, 'Boston', 30000 );
  
INSERT INTO STUDENTS(ID,NAME,AGE,ADDRESS,FEES) 
VALUES (4, Anne, 18, 'Texas', 27000 ); 

Let us now assign the above values to PL/SQL variables:

DECLARE 
   s_id students.id%type := 2; 
   s_name students.name%type; 
   s_addr students.address%type; 
   s_fees students.fees%type; 
BEGIN 
   SELECT name, fees INTO s_name, s_fees 
   FROM students 
   WHERE id = s_id;  
   dbms_output.put_line 
   (‘Student ' ||s_name || ' from ' || s_addr || ' pays ' || s_fees); 
END; 
/  

Now the output would be:

Student Jack from Washington pays 25000  
PL/SQL procedure completed successfully

Now let’s create a package:

CREATE OR REPLACE PACKAGE BODY stu_fees AS  

   PROCEDURE find_fees(s_id students.id%TYPE) IS 
   s_fees students.fees%TYPE; 
   BEGIN 
      SELECT fees INTO s_fees 
      FROM students 
      WHERE id = s_id; 
      dbms_output.put_line('Fees = '|| s_fees); 
   END find_sal; 
END stu_fees; 

On executing the above code, the following result would be visible:

Package body created.

Uses of Packages

The following are the uses of Packages:

Added functionality

The package objects can be shared by all the running subprograms. They remain for the whole session and allow data access without needing to store the data in the database.

Modularity

The packages are a prime example of modularity. They store all types of objects such as procedures, functions, variables, statements into a neat package. This makes the information easy to understand and easily readable.

Easier Application Design

The packages are divided into Package specification and definition. This means that the type declaration for the packages can be done at leisure and the code can be written in the definition as and when required.

Better Performance

The first time any subprogram requires the package, it is loaded completely into the memory. For the next times, the package is already available for the other subprograms.

Hidden Implementation Details

The details for the object declarations is available in the package specification while the implementation details are hidden in the package definition.This makes the package easier to handle and use.