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>
