Search notes:

Oracle: NLS related data dictionary views

create table tq84_nls_numeric_characters (
  id   varchar2(10),
  num  number
);


alter session set nls_numeric_characters = '.,';

insert into tq84_nls_numeric_characters values ('a', '11.11');

-- ORA-01722: invalid number
-- insert into tq84_nls_numeric_characters values ('b', '22,22');

alter session set nls_numeric_characters = ',.';

-- ORA-01722: invalid number
-- insert into tq84_nls_numeric_characters values ('a', '22.22');
insert into tq84_nls_numeric_characters values ('a', '22,22');

select * from tq84_nls_numeric_characters;
-- ID                NUM
-- ---------- ----------
-- a               11,11
-- a               22,22

alter session set nls_numeric_characters = '.,';
select * from tq84_nls_numeric_characters;
-- ID                NUM
-- ---------- ----------
-- a               11.11
-- a               22.22



drop table tq84_nls_numeric_characters purge;
Github repository Oracle-Patterns, path: /Globalization/nls_numeric_characters.sql

NLS_DATABASE_PARAMETERS

select
  lower(parameter) parameter,
  lower(value    ) value
from
  nls_database_parameters
order by
  parameter;
Github repository Oracle-Patterns, path: /Installed/data-dictionary/nls/database_parameters/show.sql
nls_database_parameters selects from x$props.
See also the view database_parameters.

NLS_INSTANCE_PARAMETERS

select
  lower(parameter) parameter,
  lower(value    ) value
from
  nls_instance_parameters
order by
  parameter;
Github repository Oracle-Patterns, path: /Installed/data-dictionary/nls/instance_parameters/show.sql
nls_instance_parameters selects from v$system_parameter.

NLS_SESSION_PARAMETERS

select
  lower(parameter) parameter,
  lower(value    ) value
from
  nls_session_parameters
order by
  parameter;
Github repository Oracle-Patterns, path: /Installed/data-dictionary/nls/session_parameters/show.sql
nls_session_parameters selects from v$nls_parameters.

Left joining all three views

select
  lower(db.parameter  )  param_name,
  lower(max(db.value ))  database_value,
  lower(max(it.value ))  instance_value,
  lower(max(se.value ))  session_value
from
  nls_database_parameters db                                left join
  nls_instance_parameters it on db.parameter = it.parameter left join
  nls_session_parameters  se on db.parameter = se.parameter
group by
  db.parameter
order by
  db.parameter;
Github repository Oracle-Patterns, path: /Installed/data-dictionary/nls/session_parameters/compare-with-db-and-instance.sql

See also

NLS related init parameters
data dictionary

Index