How to set database options using the SET OPTION statement

Set database options by using the SET OPTION statement. It has the following general syntax:

SET [ EXISTING ] [ TEMPORARY ] OPTION
[ userid. | PUBLIC. ]option-name = [ option-value ]

You can specify a user ID or a role name to set the option for that user or grantees of that role only. You can also specify the PUBLIC role name, which sets the options for grantees of the PUBLIC role. By default, new users are granted the PUBLIC role. If no user ID or role is specified, the option change is applied to the currently logged in user ID that issued the SET OPTION statement.

Any option, whether user-defined or not, must have a PUBLIC setting before a user-specific value can be assigned. The database server does not support setting TEMPORARY values for user-defined options.

If option-value is omitted, the specified option value reverts to the value of that option in the PUBLIC role. If a TEMPORARY option is removed, the option value reverts to the permanent setting.

Caution

Do not change option values while a cursor is open. Changing the option values while a cursor is open can lead to inconsistent results within the cursor. For example, changing the date_format option while a cursor is open can result in some rows being returned in the old format and some rows returned in the new format. To ensure that the rows in the result set are computed consistently using the new option value, open the cursor after the option value is changed.

Note

In databases that use a Turkish collation or are case-sensitive, executing a query on the SYSOPTION system view or a query similar to the following might not match any rows if the option name is used with the wrong case:

SELECT * FROM sa_conn_properties( ) WHERE propname = 'BLOCKING';

Example

The following statement applies an option change to the user DBA, if DBA is the user that issues it:

SET OPTION blocking_timeout = 3;

The following statement applies a change to the PUBLIC role. You must have the SET ANY SECURITY OPTION system privilege to set the login_mode option.

SET OPTION PUBLIC.login_mode = 'Standard';

For example, the following statement resets the ansi_blanks option to its default value:

SET OPTION ansi_blanks =;

How to set database options using the SET OPTION statement

Caution

Note

Example

See also