The RESET_PACKAGE procedure is used to clear all package variables in the current session.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support this feature.
Syntax
DBMS_SESSION.RESET_PACKAGE;
Limitations and considerations
In a remote call scenario, due to the limitations of the remote synchronization design for package variables, the RESET_PACKAGE procedure cannot synchronize to the remote end. This will result in an error being logged on the OBServer node. Additionally, the variable ##__OB_RESET_ALL_PACKAGE_BY_DBMS_SESSION_RESET_PACKAGE__ cannot be resolved, which will cause the execution results to be returned to the local node.
Examples
CREATE TABLE tab1(col1 INT);
CREATE TABLE tab2(col2 INT);
DELIMITER /
CREATE OR REPLACE PACKAGE pkg IS
a INT := 111;
b INT;
TYPE typ IS TABLE OF INT;
c typ := typ(1,2,3);
CURSOR d IS SELECT * FROM tab1 order by a asc;
PROCEDURE set_var(v1 int, v2 int default 0);
PROCEDURE reset_pkg;
PROCEDURE prin_var(v1 int, v2 int);
PROCEDURE type_test1;
PROCEDURE ext_type;
PROCEDURE type_test2(v1 int);
PROCEDURE fetch_cursor1;
PROCEDURE fetch_cursor2(v1 int);
END;
/
CREATE OR REPLACE PACKAGE BODY pkg IS
a1 INT := 111;
b1 INT := 1994;
PROCEDURE set_var(v1 INT, v2 INT) IS
BEGIN
a := v1;
b := v2;
a1 := v1;
b1 := v2;
END;
PROCEDURE reset_pkg is
BEGIN
-- after reset_package, dbms_output enable is be set to false
-- so we should set dbms_output.enable
-- This behavior is compatible with oracle
DBMS_SESSION.RESET_PACKAGE();
DBMS_OUTPUT.ENABLE;
END;
PROCEDURE prin_var(v1 int, v2 int) IS
b_need_to_null BOOLEAN DEFAULT false;
BEGIN
IF 1994 = v2 THEN
b_need_to_null := true;
END IF;
DBMS_OUTPUT.PUT_LINE('a := ' || v1 || '(expect) , ' || a ||'(real)');
IF b_need_to_null THEN
DBMS_OUTPUT.PUT_LINE('b := ' || '(expect) , ' || b || '(real)');
ELSE
DBMS_OUTPUT.PUT_LINE('b := ' || v2 || '(expect) , ' || b ||'(real)');
END IF;
DBMS_OUTPUT.PUT_LINE('a1 := ' || v1 || '(expect) , ' || a1 ||'(real)');
DBMS_OUTPUT.PUT_LINE('b1 := ' || v2 || '(expect) , ' || b1 ||'(real)');
END;
PROCEDURE type_test1 IS
a typ;
BEGIN
null;
END;
PROCEDURE ext_type IS
BEGIN
c.extend(10);
c(1) := 1111;
c(2) := 2222;
c(3) := 3333;
c(4) := 4444;
c(5) := 5555;
c(6) := 6666;
c(7) := 7777;
c(8) := 8888;
c(9) := 9999;
c(10) := 0000;
END;
PROCEDURE type_test2(v1 INT) IS
BEGIN
IF 1 = v1 THEN
DBMS_OUTPUT.PUT_LINE('c(1) := 1(expect) , ' || c(1) ||'(real)');
DBMS_OUTPUT.PUT_LINE('c(2) := 2(expect) , ' || c(2) ||'(real)');
DBMS_OUTPUT.PUT_LINE('c(3) := 3(expect) , ' || c(3) ||'(real)');
ELSIF 2 = v1 THEN
DBMS_OUTPUT.PUT_LINE('c(4) := 4444(expect) , ' || c(4) ||'(real)');
ELSE
DBMS_OUTPUT.PUT_LINE('c(1) := 1111(expect) , ' || c(1) ||'(real)');
DBMS_OUTPUT.PUT_LINE('c(2) := 2222(expect) , ' || c(2) ||'(real)');
DBMS_OUTPUT.PUT_LINE('c(3) := 3333(expect) , ' || c(3) ||'(real)');
DBMS_OUTPUT.PUT_LINE('c(4) := 4444(expect) , ' || c(4) ||'(real)');
DBMS_OUTPUT.PUT_LINE('c(5) := 5555(expect) , ' || c(5) ||'(real)');
DBMS_OUTPUT.PUT_LINE('c(6) := 6666(expect) , ' || c(6) ||'(real)');
DBMS_OUTPUT.PUT_LINE('c(7) := 7777(expect) , ' || c(7) ||'(real)');
DBMS_OUTPUT.PUT_LINE('c(8) := 8888(expect) , ' || c(8) ||'(real)');
DBMS_OUTPUT.PUT_LINE('c(9) := 9999(expect) , ' || c(9) ||'(real)');
DBMS_OUTPUT.PUT_LINE('c(10) := 0000(expect) , ' || c(10) ||'(real)');
END IF;
END;
PROCEDURE fetch_cursor1 IS
BEGIN
OPEN d FOR SELECT * FROM tab2 ORDER BY a ASC;
END;
PROCEDURE fetch_cursor2(v1 int) IS
val INT;
BEGIN
IF NOT d%isopen THEN
OPEN d;
END IF;
FETCH d INTO val;
DBMS_OUTPUT.PUT_LINE('d := ' || v1 || '(expect) , ' || val ||'(real)');
END;
END;
/
DELIMITER ;
CALL DBMS_SESSION.RESET_PACKAGE();
SET SERVEROUTPUT ON;
CALL pkg.prin_var(111,1994);
CALL pkg.prin_var(111,1994);
CALL pkg.set_var(9999,4444);
CALL pkg.prin_var(9999,4444);
CALL DBMS_SESSION.RESET_PACKAGE();
CALL DBMS_OUTPUT.ENABLE();
CALL pkg.prin_var(111,1994);
a := 111(expect) , 111(real)
b := (expect) , (real)
a1 := 111(expect) , 111(real)
b1 := 1994(expect) , 1994(real)