In this post I will try to show the session's persistence of Package Data, what are the issues with the Default behaviour and what can be done to alter this behaviour.
Session: When ever a Client connects from a machine to a Database server, a session is set up between the server and the Client, now this session is responsible for any communication between the server and the client.
If the same user logs in from different client/machine then a separate session will be created.
In Oracle the data for a particular session resides in the PGA.
What is meant by Package Data persistence for a Session?
The Default behaviour for a Package Data is once the variables in the Packages are assigned then they will hold their values until the session end or Disconnects.
Lets look at examples to understand the issue better.
Note: All code mentioned here can be executed in the HR schema provided with Oracle installation 11g.
Create a new session using Sql/Developer
CREATE OR REPLACE PACKAGE DEMO1
AS
employee_name VARCHAR2(256);
PROCEDURE EMPLOYEE_SALARY(e_name IN VARCHAR2);
END DEMO1;
CREATE OR REPLACE PACKAGE BODY DEMO1
AS
PROCEDURE EMPLOYEE_SALARY(e_name IN VARCHAR2)
AS
BEGIN
SELECT last_name into employee_name FROM EMPLOYEES WHERE first_name=e_name;
END;
END;
This is a simple package which is going to give the last name of the employee when the first name of the employee is used a Parameter in the EMPLOYEE_SALARY procedure.
Execute the below block now:
SET SERVEROUTPUT ON ;
BEGIN
DEMO1.EMPLOYEE_SALARY('Neena');
dbms_output.put_line('The name of the employee is '||DEMO1.employee_name);
END;
output of the block is :
anonymous block completed
The name of the employee is Kochhar
Now the DEMO1.employee_name variable is assigned this value and until this value is over written with another call with a different parameter value, the value will persist for the session.
So if you only execute the below block:
SET SERVEROUTPUT ON ;
BEGIN
dbms_output.put_line('The name of the employee is '||DEMO1.employee_name);
END;
Still the output will be :
anonymous block completed
The name of the employee is Kochhar
Now execute the below block from a different session, so for this open the SQL PLUS :
You will see that for this session, employee_name variable is not yet assigned any value as this is a new session.
This data persistence at session level is useful, but it can easily cause memory issues as the session variables will remain in the PGA for the whole session and eat up the memory area and will decrease the performance.
In order to overcome this behaviour we can make Oracle Package data to persist only for a specific call within the session using a PRAGMA SERIALLY_REUSABLE.
Lets look at one example for this :
CREATE OR REPLACE PACKAGE DEMO2
AS
PRAGMA SERIALLY_REUSABLE;
employee_name VARCHAR2(256);
PROCEDURE EMPLOYEE_SALARY(e_name IN VARCHAR2);
END DEMO2;
CREATE OR REPLACE PACKAGE BODY DEMO2
AS
PRAGMA SERIALLY_REUSABLE;
PROCEDURE EMPLOYEE_SALARY(e_name IN VARCHAR2)
AS
BEGIN
SELECT last_name into employee_name FROM EMPLOYEES WHERE first_name=e_name;
END;
END;
SET SERVEROUTPUT ON ;
BEGIN
DEMO2.EMPLOYEE_SALARY('Nancy');
dbms_output.put_line('The name of the employee is '||DEMO2.employee_name);
END;
Output for this is
anonymous block completed
The name of the employee is Greenberg
Execute the second block:
SET SERVEROUTPUT ON ;
BEGIN
dbms_output.put_line('The name of the employee is '||DEMO2.employee_name);
END;
Output is :
anonymous block completed
The name of the employee is
As compared to the previous case now the data persist only for a specific call and not for the whole session!!!!!
Hope this helps............
