PL SQL Tutorial

PL SQL Packages

PL SQL Packages are grouping of Variables , Stored procedures and Functions together or Package is a schema object, which lets you to group related data structures, sub-routines into a single unit. Packages has 2 parts

  • Package Specification
  • Package Body

Package Specification has declarations. Package Body has actual definitions of stored procedures and functions.

Package Data: Variables( cursors, collection objects) declared outside of the functions or procedures,in package specification or package body is called as Package Data. These are also called as a Global Variables, Scope of these Variables are within session

Package Intialization:


    This PRAGMA, Tells the PL/SQL Runtime engine, that package data shuould not be persist between references. This is used to reduce per-user memory requirements when the package data is required only for duration of the call not for the session.

Package specification and body example

Package Specification

create or replace package  Pkg_Temp 

function toCelsius ( f IN number ) return number;
function toFahrenheit (C IN number) return number;
end Pkg_Temp;

Package Body
create or replace package BODY Pkg_Temp 

function toCelsius ( f IN number ) return number
c number;

c:= (((f-32)/9)*5);
return c;
end toCelsius;

function toFahrenheit (C IN number) return number

F number;
 F:= (((C/5)*9)+32);
 return F;
end toFahrenheit;

end Pkg_Temp;
Calling Package Functions
SQL>select PKG_TEMP.toFahrenheit(45),PKG_TEMP.toCelsius(109) from dual;
PKG_TEMP.TOFahrenheit(45) PKG_TEMP.TOCelsius(109)
------------------------- -----------------------
		      113	       42.7777778