RESET_PACKAGE

DBMS_SESSION.RESET_PACKAGE; 

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)