ALTER SESSION

Use ALTER SESSION to set and clear session-level configuration parameter values for the current session.

Syntax

ALTER SESSION {
    SET [PARAMETER] parameter‑name=value [,…]
    | CLEAR [PARAMETER] parameter‑name[,…]
    | SET UDPARAMETER [ FOR libname ] key=value [,…]
    | CLEAR UDPARAMETER [ FOR libname ] key [,…]
}

Parameters

SET [PARAMETER] Sets one or more configuration parameters to the specified value. For a list of parameters that you can set, query the SESSION_PARAMETERS table.
CLEAR [PARAMETER [FOR]] Clears one or more specified configuration parameters .
SET UDPARAMETER [FOR libname]

Sets a user-defined session parameter, to be used in combination with a UDx. You can scope a parameter to a library using the FOR keyword, as in the following example. If you specify a library name, then only that library can access the parameter's value. Use this restriction to protect parameters that hold sensitive data such as credentials.

=> ALTER SESSION SET UDPARAMETER FOR securelib username='alice';

Key size values:

  • Set from client side: 128 characters
  • Set from UDx side: Unlimited
CLEAR UDPARAMETER [FOR libname] Clears one or more user-defined session parameters.

Privileges

None

Examples

Set and clear a parameter

  • Force all UDxes that support fenced mode to run in fenced mode, even if their definition specified is NOT FENCED:

    => ALTER SESSION SET ForceUDxFencedMode = 1;

  • Clear ForceUDxFencedMode at the session level. The value now reflects the default value (0).

    => ALTER SESSION CLEAR ForceUDxFencedMode;

Set and clear a user-defined parameter

  • Set the value of RowCount, in the MyLibrary library to 25.

    => ALTER SESSION SET UDPARAMETER FOR MyLibrary RowCount = 25;

  • Clear RowCount at the session level. The value now reflects the default value (0).

    => ALTER SESSION CLEAR UDPARAMETER FOR MyLibrary RowCount;

  • Clear all user-defined parameters used by the MyLibrary library.

    => ALTER SESSION CLEAR UDPARAMETERS FOR MyLibrary;