Tuesday, July 17, 2012

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

2 comments:

  1. postingan yang bagus tentang How to Create Packages in Oracle Database using TOAD for Oracle

    ReplyDelete
  2. Thanks Badar you have been a great help (Riaz)

    ReplyDelete

ShareThis

Related Posts Plugin for WordPress, Blogger...