Oracle PL/SQL/System Packages/DBMS SESSION
This script illustrates DBMS_SESSION.RESET_PACKAGE.
<source lang="sql">
SQL> SQL> SQL> CREATE TABLE MyTable (
2 num_col NUMBER, 3 char_col VARCHAR2(60) 4 );
Table created. SQL> SQL> set serveroutput on SQL> SQL> CREATE OR REPLACE PROCEDURE InsertTemp(
2 p_NumCol MyTable.num_col%TYPE, 3 p_CharCol MyTable.char_col%TYPE) AS 4 BEGIN 5 INSERT INTO MyTable VALUES (p_NumCol, p_CharCol); 6 END InsertTemp; 7 /
Procedure created. SQL> show errors No errors. SQL>
First create a package with some initial state.
SQL> CREATE OR REPLACE PACKAGE MyPackage AS
2 v_NumVar NUMBER := 10; 3 v_StringVar VARCHAR2(50) := "Hello World!"; 4 END MyPackage; 5 /
Package created. SQL> show errors No errors. SQL> SQL> BEGIN
2 InsertTemp(1, "Initial values:"); 3 InsertTemp(2, "v_NumVar = " || MyPackage.v_NumVar); 4 InsertTemp(3, "v_StringVar = " || MyPackage.v_StringVar); 5 END; 6 /
PL/SQL procedure successfully completed. SQL> SQL> BEGIN
2 MyPackage.v_NumVar := 20; 3 MyPackage.v_StringVar := "Goodbye World!"; 4 InsertTemp(4, "Changed values:"); 5 InsertTemp(5, "v_NumVar = " || MyPackage.v_NumVar); 6 InsertTemp(6, "v_StringVar = " || MyPackage.v_StringVar); 7 8 DBMS_SESSION.RESET_PACKAGE; 9 10 InsertTemp(7, "After RESET_PACKAGE:"); 11 InsertTemp(8, "v_NumVar = " || MyPackage.v_NumVar); 12 InsertTemp(9, "v_StringVar = " || MyPackage.v_StringVar); 13 END; 14 /
PL/SQL procedure successfully completed. SQL> SQL> BEGIN
2 InsertTemp(10, "After RESET_PACKAGE and call:"); 3 InsertTemp(11, "v_NumVar = " || MyPackage.v_NumVar); 4 InsertTemp(12, "v_StringVar = " || MyPackage.v_StringVar); 5 END; 6 /
PL/SQL procedure successfully completed. SQL> SQL> SELECT char_col
2 FROM MyTable 3 ORDER BY num_col;
CHAR_COL
Initial values: v_NumVar = 10 v_StringVar = Hello World! Changed values: v_NumVar = 20 v_StringVar = Goodbye World! After RESET_PACKAGE: v_NumVar = 20 v_StringVar = Goodbye World! After RESET_PACKAGE and call: v_NumVar = 10 CHAR_COL
v_StringVar = Hello World! 12 rows selected. SQL> SQL>
</source>