LLI Wiki — My Digital Knowledge Base

Oracle: date format, NLS_LANG and SQL*Plus output formatting

Cheat sheet for NLS_DATE_FORMAT, NLS_LANG, NLS parameters and SQL*Plus output formatting.

A cheat sheet to set the date format, the language (NLS) and the output layout in SQL*Plus and RMAN.

Date format

Environment variable (for example before starting RMAN):

rem Windows
set NLS_DATE_FORMAT=yyyy/mm/dd hh24:mi:ss
rman target /
# Linux
export NLS_DATE_FORMAT="YYYY-MM-DD HH24:MI:SS"

In SQL*Plus, for the session:

alter session set nls_date_format = 'YYYY-MM-DD HH24:MI:SS';
alter session set nls_date_format = 'DD-MON-YYYY HH24:MI:SS';

In a query:

select TO_CHAR(timestamp, 'DD-MON-YYYY HH24:MI:SS') from ...;

SQL*Plus console layout

set lines 256
set wrap off
SET LINESIZE 32000;
SET PAGESIZE 40000;
SET LONG 50000;
set trimout on
set tab off
set trimspool on
set serveroutput on
set longchunksize 20000000
set colsep " | "

Column layout

COLUMN LAST_NAME        HEADING 'LAST NAME'
COLUMN SALARY           HEADING 'MONTHLY SALARY'
COLUMN COMMISSION_PCT   HEADING COMMISSION

column column_name format a30
column fee format 999999999.99

Columns used by show parameter / show spparameter:

COLUMN SID_COL_PLUS_SHOW_SPPARAM    ON FORMAT a8  HEADING SID
COLUMN VALUE_COL_PLUS_SHOW_SPPARAM  ON FORMAT a28 HEADING VALUE
COLUMN NAME_COL_PLUS_SHOW_SPPARAM   ON FORMAT a29 HEADING NAME
COLUMN value_col_plus_show_param    ON FORMAT a30 HEADING VALUE
COLUMN name_col_plus_show_param     ON FORMAT a36 HEADING NAME

Language: NLS_LANG

Windows: the value is stored in the registry under the Oracle client home key (NLS_LANG value, for example FRENCH_FRANCE.WE8MSWIN1252), or in the session environment variable:

set NLS_LANG=FRENCH_FRANCE.WE8PC850

Linux:

export NLS_LANG='AMERICAN'
export NLS_LANG='english_united kingdom.we8iso8859p1'
echo $NLS_LANG

Valid NLS_LANG formats (<language>_<territory>.<character set>, each part is optional):

NLS_LANG=.WE8ISO8859P1
NLS_LANG=_GERMANY
NLS_LANG=AMERICAN
NLS_LANG=ITALIAN_.WE8MSWIN1252
NLS_LANG=_BELGIUM.US7ASCII

Change the session language:

ALTER SESSION SET NLS_LANGUAGE = 'AMERICAN';

Check NLS settings

select * from NLS_DATABASE_PARAMETERS;
select * from NLS_SESSION_PARAMETERS;
select sys_context('USERENV','LANGUAGE') from dual;

SELECT * FROM NLS_DATABASE_PARAMETERS WHERE PARAMETER LIKE '%CHARACTERSET';

Example output for the database character set:

PARAMETER           VALUE
NLS_CHARACTERSET    AL32UTF8
Mastodon LLIMastodon SocialFosstodon