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