Oracle: how to "unalter" a session parameter?

Question

I'm doing a case-insensitive query with

alter session set NLS_COMP=LINGUISTIC;
alter session set NLS_SORT=BINARY_CI;

Is there a way to easily capture the session state prior to altering it so that I can restore the session to its original state?

Did you try a query on `v$parameter2` (see https://community.oracle.com/message/3577293)? — Andreas Fester, Sep 12 '14 at 06:53

score 4 · Accepted Answer · answered Sep 12 '14 at 06:59

4

You can obtain the current values using:

select * 
from nls_session_parameters;

before you change your session. To restore it, you just use the saved values.

I am not aware of any statement that resets the session parameters to the default value.

answered Sep 12 '14 at 06:59

1

This is the best way to do it as far as I know. But it seems like there *should* be a better way. For example, the `reset` syntax is undocumented for `session` but does appear to work. For example, `alter session reset nls_sort;` will reset it. But it will reset it to the *Oracle* default, not the previous *system* default. – Jon Heller Sep 12 '14 at 09:28

score 0 · Answer 2 · answered Sep 12 '14 at 06:59

The NLS parameters are exposed through a series of views, starting with NLS_. In your case you need NLS_SESSION_PARAMETERS. There are equivalent views for Instance and Database.

This is neater than using v$parameter, although that view does allow us to tell whether a paarmeter is changed from the default value.

score 0 · Answer 3 · edited May 23 '17 at 10:24

0

You can get the value of a given session parameter by:

SELECT value
FROM   nls_session_parameters
WHERE  parameter = 'NLS_SORT'; -- replace NLS_SORT with parameter of your choice

This answer demonstrates other means of doing a case insensitive search.

Using UPPER()/LOWER() functions with a function based index.
Regular expressions: REGEXP_LIKE()

edited May 23 '17 at 10:24

Community

1
1

answered Sep 12 '14 at 07:06

sampathsris

21,564
12
71
98

Lalit Kumar B · Answer 4 · 2015-05-04T11:44:18.527

You can see the parameter values initially :

SQL> SHOW PARAMETER NLS_SORT;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_sort                             string      BINARY

SQL> SHOW PARAMETER NLS_COMP;

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
nls_comp                             string      BINARY

And then set the session parameter accordingly :

SQL> alter session set nls_comp='LINGUISTIC';

Session altered

SQL> alter session set nls_sort='BINARY_CI';

Session altered

In PL/SQL, you can do the following to fetch the parameter value and store it in session variable :

SQL> DECLARE
  2    VAR_NLS_SORT VARCHAR2(10);
  3    var_nls_comp VARCHAR2(10);
  4  BEGIN
  5    SELECT VALUE
  6       INTO VAR_NLS_SORT
  7      FROM NLS_SESSION_PARAMETERS
  8     WHERE PARAMETER = 'NLS_SORT';
  9    SELECT VALUE
 10       INTO VAR_NLS_COMP
 11      FROM NLS_SESSION_PARAMETERS
 12     WHERE PARAMETER = 'NLS_COMP';
 13    DBMS_OUTPUT.PUT_LINE('NLS_SORT = '||VAR_NLS_SORT);
 14    DBMS_OUTPUT.PUT_LINE('NLS_COMP = '||VAR_NLS_COMP);
 15  END;
 16  /
NLS_SORT = BINARY
NLS_COMP = BINARY

PL/SQL procedure successfully completed.

For more information, you can have a look at Oracle – Case Insensitive Sorts & Compares

Oracle: how to "unalter" a session parameter?

4 Answers4