This script illustrates DBMS_SESSION.RESET_PACKAGE. : DBMS_SESSION « System Packages « Oracle PL / SQL






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>

 








Related examples in the same category