SERIALLY_REUSABLE Package

View All Scripts Login to Run Script

Script Name SERIALLY_REUSABLE Package
Description By default, the state of package-level variables persists in one's session. You assign a value to a variable declared at the package level, PL/SQL "remembers" that value in between references to the package. The same is true for a cursor declared at the package level. It stays open. Use the SERIALLY_REUSABLE pragma to turn off this persistence, forcing re-initialization of the package with each new server call to an element in the package.
Area PL/SQL General / PL/SQL Procedures, Functions, Packages
Contributor Steven Feuerstein
Created Wednesday March 23, 2016

Statement 1

CREATE OR REPLACE PACKAGE serial_package   
AS   
   CURSOR emps_cur   
   IS   
      SELECT * FROM hr.employees;   
END serial_package;

Package created.

Statement 2

Open the Cursor, Don't Close It

BEGIN  
   OPEN serial_package.emps_cur;  
END;

Statement processed.

Statement 3

Failure! The cursor is still open.

Try to Open the Cursor AGAIN....

BEGIN  
   OPEN serial_package.emps_cur;  
END;

ORA-06511: PL/SQL: cursor already open

Statement 4

Now Add SERIALLY_REUSABLE Pragma

CREATE OR REPLACE PACKAGE serial_package   
AS   
   PRAGMA SERIALLY_REUSABLE;   
   
   CURSOR emps_cur   
   IS   
      SELECT * FROM hr.employees;   
END serial_package;

Package created.

Statement 5

Open the Cursor, Don't Close It

BEGIN  
   OPEN serial_package.emps_cur;  
END;

Statement processed.

Statement 6

Open the Cursor AGAIN, No Problem!

BEGIN  
   OPEN serial_package.emps_cur;  
END;

Statement processed.

Additional Information

Database on OTN SQL and PL/SQL Discussion forums
Oracle Database
Download Oracle Database