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.
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 seperate 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