Oracle PL/SQL/System Packages/DBMS SESSION

This script illustrates DBMS_SESSION.RESET_PACKAGE.

 
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>