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>