Creating PL/SQL Packages

  1. Packages Overview

PL/SQL Packages
a.Group logically related components :

  • PL/SQL Types
  • Variables,Structures and Exceptions.
  • Subprograms – Procedures and Functions

b. Consists of two parts:

  • A Specification
  • A Body
    c. Enables the Oracle Server to read multiple objects into memory at once.

For Example a HR Package can contain hiring,firing procedures commission and bonus calculation functions.

Packages

  1. Group Procedures and Functions
  2. Define the Package Specification
  3. Define the Package Body

— PL/SQL Package —

CREATE TABLE tb_sample(sno NUMBER,name VARCHAR2(20))
/

CREATE OR REPLACE PACKAGE pkg_pack IS
PROCEDURE p_insert(a NUMBER,b VARCHAR2);
PROCEDURE p_update(a NUMBER,b VARCHAR2);
PROCEDURE p_delete(a NUMBER);
END pkg_pack;
/

CREATE OR REPLACE PACKAGE BODY pkg_pack IS
PROCEDURE p_insert(a NUMBER,b VARCHAR2)
IS
BEGIN
INSERT INTO tb_sample VALUES(a,b);
DBMS_OUTPUT.PUT_LINE(‘Records Inserted’);
COMMIT;
END p_insert;
PROCEDURE p_update(a NUMBER,b VARCHAR2)
IS
BEGIN
UPDATE tb_sample SET name = b WHERE sno=a;
DBMS_OUTPUT.PUT_LINE(‘Records Updated’);
COMMIT;
END p_update;
PROCEDURE p_delete(A NUMBER)
IS
BEGIN
DELETE FROM tb_sample WHERE sno = A;
DBMS_OUTPUT.PUT_LINE(‘Records Deleted’);
COMMIT;
END p_delete;
END pkg_pack;
/

DESC pkg_pack;

EXECUTE pkg_pack.p_insert(1,’King’);

SELECT * FROM tb_sample
/

EXECUTE pkg_pack.p_update(1,’Rahul King’);

SELECT * FROM tb_sample
/

EXECUTE pkg_pack.p_delete(1);

SELECT * FROM tb_sample
/

— Execute the Package from a PL/SQL Block —

DECLARE
v_a NUMBER;
v_b VARCHAR2(50);
v_choice VARCHAR2(4);
v_error EXCEPTION;
BEGIN
v_choice:=UPPER(‘&chc’);
IF v_choice NOT IN(‘I’,’U’,’D’) THEN
raise v_error;
END IF;
IF v_choice = ‘I’ THEN
pkg_pack.p_insert(1,’K1′);
ELSIF v_choice = ‘U’ THEN
pkg_pack.p_update(1,’K2′);
ELSIF v_choice = ‘D’ THEN
pkg_pack.p_delete(1);
END IF;
EXCEPTION
WHEN v_error THEN
raise_application_error(-20000,’Invalid Choice’);
END;
/

DROP TABLE tb_sample PURGE
/

— Using a Cursor inside a PL/SQL Package —

CREATE OR REPLACE PACKAGE pkg_util
IS
CURSOR c_emp IS
SELECT * FROM emp;
r_emp c_emp%ROWTYPE;
END pkg_util;
/

CREATE OR REPLACE PACKAGE pkg_util_pkg
IS
PROCEDURE p_print_emp;
END pkg_util_pkg;
/

CREATE OR REPLACE PACKAGE BODY pkg_util_pkg
IS
PROCEDURE p_print_emp
IS
BEGIN
OPEN pkg_util.c_emp;
LOOP
FETCH pkg_util.c_emp INTO pkg_util.r_emp;
EXIT WHEN pkg_util.c_emp%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(pkg_util.r_emp.ename);
END LOOP;
CLOSE pkg_util.c_emp;
END p_print_emp;
END pkg_util_pkg;
/

EXEC pkg_util_pkg.p_print_emp;

  1. Overloading Concept in Packages

Inside the Package two procedures having the same name but with different datatypes are called Overloading in Packages.
PL/SQL allows two or more packaged subprograms to have the same name.This option is useful when you want a subprogram to accept similiar sets of parameters that have different datatypes.

CREATE TABLE journal_entries(amount NUMBER,name VARCHAR2(50),trans_date DATE)
/

CREATE OR REPLACE PACKAGE journal_entries_pkg AS
PROCEDURE journalize(p_amount NUMBER,p_trans_date VARCHAR2);
PROCEDURE journalize(p_amount NUMBER,p_name VARCHAR2,p_trans_date DATE);
END journal_entries_pkg;
/

CREATE OR REPLACE PACKAGE BODY journal_entries_pkg AS
PROCEDURE journalize (p_amount NUMBER,p_trans_date VARCHAR2)
IS
BEGIN
INSERT INTO journal_entries(amount,trans_date) VALUES(p_amount,TO_DATE(p_trans_date,’MM/DD/YYYY’));
END journalize;
PROCEDURE journalize(p_amount NUMBER,p_name VARCHAR2,p_trans_date DATE)
IS
BEGIN
INSERT INTO journal_entries VALUES(p_amount,p_name,p_trans_date);
END journalize;
END journal_entries_pkg;
/

BEGIN
BEGIN
journal_entries_pkg.journalize(2,’04-JAN-2012′);
END;
BEGIN
journal_entries_pkg.journalize(1,’03/03/2012′);
END;
END;
/

select * from journal_entries
/

DROP TABLE journal_entries PURGE
/

CREATE OR REPLACE PACKAGE testpack
IS
PROCEDURE TEST1(p_num NUMBER, p_name VARCHAR2);
PROCEDURE TEST1(p_num NUMBER, p_name VARCHAR2,p_num2 NUMBER DEFAULT 1);
END testpack;
/

show error

CREATE OR REPLACE PACKAGE BODY testpack
is
PROCEDURE TEST1(p_num NUMBER, p_name VARCHAR2)
IS
BEGIN
DBMS_OUTPUT.put_line(‘Test1’);
END;
PROCEDURE TEST1(p_num NUMBER, p_name VARCHAR2,p_num2 NUMBER DEFAULT 1)
IS
BEGIN
DBMS_OUTPUT.put_line(‘Test2’);
END;
end testpack;
/
show error

EXEC testpack.test1(10,’k’)

EXEC testpack.test1(10,’k’,3)

  1. Private Procedure inside a Package

CREATE OR REPLACE PACKAGE public_private_pkg
IS
PROCEDURE publicproc;
END public_private_pkg;
/

CREATE OR REPLACE PACKAGE BODY public_private_pkg
IS
PROCEDURE privateProc
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘test’);
END;
PROCEDURE publicProc
IS
BEGIN privateProc; –exec privateProc;
END;
END public_private_pkg;
/

EXECUTE public_private_pkg.publicproc;

— This will error out —
EXECUTE public_private_pkg.privateProc;

SELECT object_name,object_type FROM user_objects
WHERE object_type IN(‘PROCEDURE’,’PACKAGE’)
/

  1. One – Time – Only Procedure

A PL/SQL One – Time – Only Procedure is an anonymous block of code encapsulated within a package. This One – Time – Only Procedure is executed only once when the
package is invoked for the first time in a user session.

One Time Procedure has one END statement instead of two END statements.

CREATE OR REPLACE PACKAGE one_time_proc_pkg
IS
PROCEDURE repeated_proc;
END one_time_proc_pkg;
/

CREATE OR REPLACE PACKAGE BODY one_time_proc_pkg
IS
PROCEDURE repeated_proc
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘This Procedure is defined in Package Spec’);
END repeated_proc;
BEGIN
DBMS_OUTPUT.PUT_LINE(‘This is a One – Time Procedure’);
END one_time_proc_pkg;
/

— This will error out since One – Time should be given at the end —
CREATE OR REPLACE PACKAGE BODY one_time_proc_pkg
IS
PROCEDURE repeated_proc
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘This is a One – Time Procedure’);
BEGIN
DBMS_OUTPUT.PUT_LINE(‘This Procedure is defined in Package Spec’);
END repeated_proc;
END one_time_proc_pkg;
/

SET SERVEROUTPUT ON

EXEC one_time_proc_pkg.repeated_proc;

This is executed only once for a particular user session.

— Again i am executing this Procedure —
EXEC one_time_proc_pkg.repeated_proc;

  1. Persistent state of a Packaged Variable

The collection of public and private package variables represents the package state for the current user session.That is, the package state is the set of values stored in all the package variables at a given point in time.In general, the package state exists for the life of the user session.

Package Variables are initialized the first time a package is loaded into memory for a user session. The Package Variables are,by default,unique to each session and hold their values until the user session is terminated. This package state changes when a package subprogram is invoked and its logic modifies the variable state.

— Example to understand the Persistant State of a Package Variable —

CREATE OR REPLACE PACKAGE my_pkg
IS
PROCEDURE do_it;
END my_pkg;
/

CREATE OR REPLACE PACKAGE BODY my_pkg
IS
/* Private Package Data */
TYPE g_rec IS RECORD
(first_name VARCHAR2(50),
last_name VARCHAR2(50)
);
TYPE g_tab_type IS TABLE OF g_rec INDEX BY BINARY_INTEGER;
g_tab g_tab_type;
i BINARY_INTEGER;
PROCEDURE do_it
IS
BEGIN
i:=g_tab.COUNT + 1;
g_tab(i).first_name:=’Eddie’;
g_tab(i).last_name:=’Awad’;
DBMS_OUTPUT.PUT_LINE(‘g_tab_count: ‘||g_tab.COUNT);
END do_it;
END my_pkg;
/

EXEC my_pkg.do_it;

EXEC my_pkg.do_it;

EXEC my_pkg.do_it;

EXIT

EXEC my_pkg.do_it;

Since g_tab is a Package Level Variable,its value persists across multiple package calls in the same session. Once disconnected from the session and connected again,g_tab is again re-initialized.

— Example to understand the Persistant State of a Package Variable with One – Time Procedure —
CREATE OR REPLACE PACKAGE discounts
IS
g_id NUMBER:=7839;
discount_rate NUMBER:=0.00;
PROCEDURE display_price(p_price NUMBER);
END discounts;
/

CREATE OR REPLACE PACKAGE BODY discounts
IS
PROCEDURE display_price(p_price NUMBER)
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Discount Rate inside the Display Price Procedure Beginning’||discount_rate);
DBMS_OUTPUT.PUT_LINE(‘Discounted after calculation ‘||p_price * discount_rate);
END display_price;
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Discount Rate inside One – Time Procedure ‘||discount_rate);
discount_rate:=0.10;
DBMS_OUTPUT.PUT_LINE(‘Discount Rate inside One – Time Procedure with different value to Discount Rate ‘||discount_rate);
END discounts;
/

EXEC discounts.display_price(12);

This Persistent State of the Package Variable can be avoided by using the
following methods :

  1. Usage of SERIALLY_REUSABLE pragma inside the Package Specification and Package Body

CREATE OR REPLACE PACKAGE my_pkg
IS
— Use this avoid the re-initialization of Package Variables —
PRAGMA SERIALLY_REUSABLE;
PROCEDURE do_it;
END my_pkg;
/

CREATE OR REPLACE PACKAGE BODY my_pkg
IS
— Use this avoid the re-initialization of Package Variables —
PRAGMA SERIALLY_REUSABLE;
/* Private Package Data */
TYPE g_rec IS RECORD
(first_name VARCHAR2(50),
last_name VARCHAR2(50)
);
TYPE g_tab_type IS TABLE OF g_rec INDEX BY BINARY_INTEGER;
g_tab g_tab_type;
i BINARY_INTEGER;
PROCEDURE do_it
IS
BEGIN
i:=g_tab.COUNT + 1;
g_tab(i).first_name:=’Eddie’;
g_tab(i).last_name:=’Awad’;
DBMS_OUTPUT.PUT_LINE(‘g_tab_count: ‘||g_tab.COUNT);
END do_it;
END my_pkg;
/

EXEC my_pkg.do_it;

Note : The global memory for serially reusable packages is pooled in the System Global Area (SGA) and not allocated to individual users in the User Global Area (UGA). Each time the package is reused,its package level variables are initialized to their default values or to NULL.

2.Execute DBMS_SESSION.RESET_PACKAGE

CREATE OR REPLACE PACKAGE my_pkg
IS
— Use this avoid the re-initialization of Package Variables —
PROCEDURE do_it;
END my_pkg;
/

CREATE OR REPLACE PACKAGE BODY my_pkg
IS
— Use this avoid the re-initialization of Package Variables —
/* Private Package Data */
TYPE g_rec IS RECORD
(first_name VARCHAR2(50),
last_name VARCHAR2(50)
);
TYPE g_tab_type IS TABLE OF g_rec INDEX BY BINARY_INTEGER;
g_tab g_tab_type;
i BINARY_INTEGER;
PROCEDURE do_it
IS
BEGIN
i:=g_tab.COUNT + 1;
g_tab(i).first_name:=’Eddie’;
g_tab(i).last_name:=’Awad’;
DBMS_OUTPUT.PUT_LINE(‘g_tab_count: ‘||g_tab.COUNT);
END do_it;
END my_pkg;
/

EXEC my_pkg.do_it;

EXEC my_pkg.do_it;

EXEC dbms_session.reset_package;
SET SERVEROUTPUT ON

EXEC my_pkg.do_it;

Note: Suppose the same user logs into a different session and executes the package
the package variables gets re-initialized, so the DBMS_SESSION.RESET_PACKAGE frees the memory associated with each of the previously run PL/SQL programs from the current session and clears the current values of any package globals.

3.Execute DBMS_SESSION.MODIFY_PACKAGE_STATE(DBMS_SESSION.REINITIALIZE)

This was introduced in Oracle 9i and provides an equivalent of the
dbms_session.reset_package. Here instead of recreating from the scratch the package memory alone is reused.

EXEC my_pkg.do_it;

EXEC my_pkg.do_it;

EXEC dbms_session.modify_package_state(dbms_session.reinitialize);
SET SERVEROUTPUT ON

  1. Execute ALTER PACKAGE package name COMPILE

Package Data is reinitialized in a session when that package is recompiled.
This was introduced in Oracle 9i and provides an equivalent of the
dbms_session.reset_package. Here instead of recreating from the scratch the package memory alone is reused.

EXEC my_pkg.do_it;

EXEC my_pkg.do_it;

ALTER PACKAGE my_pkg COMPILE
SET SERVEROUTPUT ON

EXEC my_pkg.do_it;

Final Note :
If the requirement is that all the Package Data has to be ‘fresh’ every time the package is used within a session, the SERIALLY_REUSABLE pragma makes a better choice.

  1. Persistent state of a Packaged Cursor

We can keep track of the state of a Package Cursor which persists throughout the User session from the time the user first references the cursor to the time the user disconnects.
It persists across transactions within a session.But it does not persist from session to session for the same user.

— Example of a Persistent state of Packaged Cursor —

CREATE OR REPLACE PACKAGE curs_pkg
IS
PROCEDURE open;
FUNCTION next(n NUMBER:=1)
RETURN BOOLEAN;
PROCEDURE close;
END curs_pkg;
/

CREATE OR REPLACE PACKAGE BODY curs_pkg
IS
CURSOR c IS SELECT empno FROM emp;
PROCEDURE open IS
BEGIN
IF NOT C%ISOPEN THEN
OPEN C;
END IF;
END open;
FUNCTION next(n NUMBER:=1)
RETURN boolean
IS
emp_id emp.empno%TYPE;
BEGIN
FOR count IN 1.. n
LOOP
FETCH c INTO emp_id;
EXIT WHEN c%NOTFOUND;
DBMS_OUTPUT.PUT_LINE(‘Id: ‘||(emp_id));
END LOOP;
RETURN c%FOUND;
END next;
PROCEDURE close
IS
BEGIN
IF c%ISOPEN THEN
CLOSE c;
END IF;
END close;
END curs_pkg;
/

To use this Package, we need to perform the following set of activities:

  1. Call the Open Procedure to Open the Cursor.
  2. Call the Next Procedure to fetch one or more specified number of rows. If we
    request more rows than actually exist, the procedure successfully handles the termination.It returns TRUE if more rows need to be processed;otherwise it returns FALSE.
  3. Call the Close Procedure to Close the Cursor, before or at the end of processing the rows.

Sample to Execute this Package

SET SERVEROUTPUT ON
EXECUTE curs_pkg.open;

DECLARE
more BOOLEAN:=curs_pkg.next(3);
BEGIN
IF NOT more THEN
curs_pkg.close;
END IF;
END;
/

  1. Global Variables in a PL/SQL Package

— Example to understand the concept of a Global Variable in PL/SQL Package —

CREATE OR REPLACE PACKAGE test_pkg
IS
global_var NUMBER;
END test_pkg;
/

— Execute this Package from a PL/SQL Block to initialize a value to this Global Variable —
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Staring Routine, Variable: ‘||test_pkg.global_var);
test_pkg.global_var:=5;
DBMS_OUTPUT.PUT_LINE(‘Ending the Routine, Variable: ‘||test_pkg.global_var);
END;
/

— Execute this Package from a PL/SQL Block to initialize a value to this Global Variable —
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Continuing the Variable Value : ‘||test_pkg.global_var);
END;
/

— We can refer this Global Variable inside another Package —
CREATE OR REPLACE PACKAGE test_package
IS
PROCEDURE first_proc;
END test_package;
/

CREATE OR REPLACE PACKAGE BODY test_package
IS
PROCEDURE first_proc
IS
BEGIN
DBMS_OUTPUT.PUT_LINE(‘Global Variable Value is : ‘||test_pkg.global_var);
END first_proc;
END test_package;
/

EXECUTE test_package.first_proc;

Log Out from the Current Session and Login Back again.

SET SERVEROUTPUT ON

BEGIN
DBMS_OUTPUT.PUT_LINE(‘After a New Session : ‘||NVL(test_pkg.global_var,0));
END;
/

Additional Notes

TO_CHAR,TO_DATE,TO_NUMBER and rest of the predefined Procedures and Functions reside under a package
by name STANDARD. This STANDARD package resides under the SYS Schema.
If at all we need to view the contents, then we can issue a command like
DESC sys.STANDARD
/

DBMS_OUTPUT.ENABLE (buffer_size IN INTEGER DEFAULT 20000);

• It is not necessary to call this procedure when you use the SET SERVEROUTPUT option of SQL*Plus.

• If there are multiple calls to ENABLE, then buffer_size is the last of the values specified. The maximum size is 1,000,000, and the minimum is 2,000 when the user specifies buffer_size (NOT NULL).

SET SERVEROUTPUT OFF

BEGIN
DBMS_OUTPUT.PUT_LINE(‘Output 1’);
DBMS_OUTPUT.PUT_LINE(‘Output 2’);
DBMS_OUTPUT.PUT_LINE(‘Output 3’);
DBMS_OUTPUT.ENABLE;
DBMS_OUTPUT.PUT_LINE(‘Output 4’);
END;
/

SET SERVEROUTPUT ON

BEGIN
DBMS_OUTPUT.PUT_LINE(‘Output 5’);
END;
/

Execute the PL/SQL Block multiple times.
Eg. If you execute the Block 3 Times,till you specify the SET SERVEROUTPUT ON
the text will get stored in the memory.
Once i enable the SET SERVEROUTPUT ON and execute the PL/SQL Block the no.of times the Block was executed that no.of times the Output will get displayed.

© 2024 AWS. Learn from Scratch