The RESET_PACKAGE procedure clears all package variables in a session.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the MySQL mode.
Syntax
DBMS_SESSION.RESET_PACKAGE;
Limitations
In remote call scenarios, limited by the remote synchronization design for package variables, RESET_PACKAGE cannot be synchronized to the remote end. As a result, an error is reported in the logs of the OBServer node, and the ##__OB_RESET_ALL_PACKAGE_BY_DBMS_SESSION_RESET_PACKAGE__ variable cannot be parsed. Consequently, subsequent execution results are returned to the local device.
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)