RESETTABLE Clause

The RESETTABLE clause is used to mark whether a PL/SQL unit's state can be discarded by the session when the unit deems it safe to do so.

If the state of a RESETTABLE PL/SQL unit is discarded, subsequent reference to the unit in the same session will result in the unit executing its initialization. The use of RESETTABLE can be beneficial to those who use SQL Patch by avoiding ORA-04068, an error raised when the existing state of a package has been discarded and can otherwise occur when packages change during datapatch.

The RESETTABLE clause can appear in the following statements:

• CREATE PACKAGE Statement

• CREATE PACKAGE BODY Statement

Topics

• Syntax

• Semantics

• Usage Notes

• Examples

Syntax

resettable_clause ::=

Description of the illustration resettable_clause.eps

Semantics

resettable_clause

RESETTABLE

A package or package body is resettable if the RESETTABLE clause appears in the package specification or the definition of the package body, respectively.

The RESETTABLE clause can be specified upon creation of a package and a package body. The clause is set independently on the package specification and body, meaning the property will not necessarily match. Any combination of settings between package specification and package body are valid.

In order to update the RESETTABLE property on a package, use the CREATE OR REPLACE PACKAGE [BODY] RESETTABLE syntax. The ALTER PACKAGE [BODY] syntax cannot be used to change the property.

The RESETTABLE clause cannot be used in combination with the SERIALLY_REUSABLE pragma.

Usage Notes

You must ensure that the state of the unit is, in fact, RESETTABLE when specifying the property. For example, in general, packages that contain instruments that need to follow an open-close mechanism (and they don't immediately complete the cycle) are not safe to mark RESETTABLE, as discarding the state before the mechanism is closed could cause a memory leak. On the other hand, packages that contain information that can be obtained again during reinstantiation are safe to mark as RESETTABLE.

If changes are made to a package that is marked RESETTABLE, you must consider if the change will mean the package is no longer resettable.

Examples

Example 14-36 Creating a RESETTABLE Package Body

CREATE OR REPLACE PACKAGE res_pkg RESETTABLE AS
    FUNCTION get_current_user
    RETURN VARCHAR2;
END;
/

CREATE OR REPLACE PACKAGE BODY res_pkg RESETTABLE AS 
    
    NAME    VARCHAR2(50);
    ID      PLS_INTEGER;
    MAX_L CONSTANT PLS_INTEGER := ORA_MAX_NAME_LEN;

    FUNCTION get_current_user RETURN VARCHAR2 
    IS
    BEGIN
        RETURN DBMS_ASSERT.enquote_name(SYS_CONTEXT('USERENV', 
                                            'CURRENT_USER'), FALSE);
    END get_current_user;
END;
/