How to Create Packages in Oracle Database using TOAD for Oracle

What are Packages in Oracle Database

A package is a group of procedures, functions, variables and SQL statements created as a single unit. It is used to store together related objects. A package has two parts, Package Specification and Package Body.

Package Specification

Package Specification acts as an interface to the package. Declaration of types, variables, constants, exceptions, cursors and subprograms is done in Package specifications. Package specification does not contain any code.

Package Body

Package body is used to provide implementation for the subprograms, queries for the cursors declared in the package specification.

In this post I will show you how to create Packages in Oracle Database using TOAD. Connect to Oracle Database using TOAD. Click on Database menu and open Schema Browser.

In the Schema Browser open Packages Tab and click on New button to create Package. New PL/SQL Object Create Options screen will appear. Select Object Type as Package, enter the New Object Name, leave the other options and click on OK to create the Package.

Package Specification and Package Body structure created automatically and both open in seprate SQL tabs. In the Package Specification I have created the interfaces of a Function and a Procedure as shown below. Press F5 to compile the Package Specification.

In the Package Body implementation of the Function and Procedure are provided. Press F5 to compile the Package Body.

Package Specification Code

CREATE OR REPLACE PACKAGE mypkg AS FUNCTION ValidateUser(p_usrname varchar2, p_password varchar2) RETURN varchar2; PROCEDURE InsertCustomer (p_cust_id Number, p_cust_name VARCHAR2, p_cust_city VARCHAR2, p_contact_no VARCHAR2); END mypkg; /

Package Body Code

CREATE OR REPLACE PACKAGE BODY MYPKG AS FUNCTION ValidateUser(p_usrname varchar2, p_password varchar2) return varchar2 is usr NUMBER; BEGIN    SELECT COUNT(*) into usr FROM USERS U WHERE U.USER_NAME = p_usrname AND U.USER_PW = p_password; IF usr > 0 THEN return 'User Found'; Else return 'User not Found'; END IF; END ValidateUser; PROCEDURE InsertCustomer (p_cust_id Number, p_cust_name VARCHAR2, p_cust_city VARCHAR2, p_contact_no VARCHAR2) IS BEGIN INSERT INTO CUSTOMER VALUES (p_cust_id, p_cust_name, p_cust_city,p_contact_no); commit;    EXCEPTION      WHEN NO_DATA_FOUND THEN        NULL;      WHEN OTHERS THEN        RAISE; END InsertCustomer; END MYPKG; /

Now your newly created MYPKG Package and its structure is showing under the Package Tab in Schema Browser.

How to Use Package

For accessing the types, subprograms etc from a package we write
Package_name.type_name
Package_name.subprogram_name

For example we want to access InsertCustomer subprogram from MYPKG package we write ( as shown in pic also)

MYPKG.InsertCustomer

Advantages of Packages:

• It allows you to group together related items, types and subprograms as a PL/SQL module.
• When a procedure in a package is called entire package is loaded, though it happens to be expensive first time the response is faster for subsequent calls.
• Package allows us to create types, variable and subprograms that are private or public