Oracle 12.2 – SQL*Plus Command History

Currently I am preparing some slides for future events. I will share some of the topics beforehand. First topic is the one mentioned in the title, with Oracle Database 12.2 we finally get a command history. Though only a tiny new bit this is one of my favorite new features.
Let’s see how this works.

$ sqlplus / as sysdba

SQL*Plus: Release 12.2.0.1.0 Production on Mon May 8 09:49:04 2017

Copyright (c) 1982, 2016, Oracle.  All rights reserved.


Connected to:
Oracle Database 12c Enterprise Edition Release 12.2.0.1.0 - 64bit Production

SQL> history
SP2-1650: History is off, use "SET HIST[ORY] ON" to enable History.

Ok, the feature is inactive by default. We need to enable it by setting it to “ON” or to a number specifying how many statements should be kept in the history.

SQL> set history 50

SQL> select * from dual;
D
-
X
SQL> history
  1  select * from dual;

Let’s see what else is possible with the “history” command:

SQL> help history

 HISTORY
 -------

 Stores, lists, executes, edits of the commands
 entered during the current SQL*Plus session.

 HIST[ORY] [N {RUN | EDIT | DEL[ETE]}] | [CLEAR]

 N is the entry number listed in the history list.
 Use this number to recall, edit or delete the command.

 Example:
 HIST 3 RUN - will run the 3rd entry from the list.

 HIST[ORY] without any option will list all entries in the list.

We can easily run any command by specifying the number from the history or remove a command from the history.

SQL> hist
  1  select * from dual;
  2  select count(*) from dba_objects where status <> 'VALID';

SQL> hist 2 run

  COUNT(*)
----------
         0

SQL> hist 1 del
SQL> hist
  1  select count(*) from dba_objects where status <> 'VALID';

And by the way, the history runs at statement level, not at line level. So this is maybe the most important advantage over OS level command history using arrow-up and arrow-down (natively on Windows or with “rlwrap” on Linux).

SQL> select count(*) from dba_users
  2  where oracle_maintained = 'Y'
  3  and account_status = 'OPEN';

  COUNT(*)
----------
         2

SQL> hist
  1  select count(*) from dba_objects where status <> 'VALID';
  2  select count(*) from dba_users
     where oracle_maintained = 'Y'
     and account_status = 'OPEN';

SQL> hist 2 run

  COUNT(*)
----------
         2

Enjoy the new feature.

SQL*Plus, Character Sets and Windows

Again and again I stumble upon issues regarding Windows, SQL*Plus and character encoding. Let me share my experience with you.

For whatever reason Microsoft decided to use the PC850 character set for the command prompt (cmd) instead of the MSWIN1252 character set. That means, if you install Oracle on Windows and use MSWIN1252 as NLS character set, the “cmd” will destroy charcters when writing or reading from the database. That means one must be very careful when using “cmd” in combination with SQL*Plus. There is a way around that issue that I will describe.

NLS_LANG

You need to set the value for NLS_LANG explicitly before starting SQL*Plus to the proper value which is “WE8PC850” in that case.

C:\>set NLS_LANG=american_america.we8pc850

C:\>sqlplus marco/*****@******

SQL> create table bla (txt varchar2(100));

Tabl created.

SQL> insert into bla values ('NLS850: äöü');

1 row created.

SQL> commit;

Commit complete.

SQL> select * from bla;

TXT
--------------------------------------------------------------------------------
NLS850: äöü

Change Code Page

You may also change the code page that “cmd” is using before starting SQL*Plus:

C:\>chcp
Active code page: 850

C:\>chcp 1252
Active code page: 1252

C:\>sqlplus marco/*****@******

SQL> select * from bla;

TXT
--------------------------------------------------------------------------------
NLS850: äöü

SQL> insert into bla values ('chcp1252: üöäß');

1 row created.

Commit complete.

SQL> select * from bla;

TXT
-----------------------------------------------
NLS850: äöü
chcp1252: üöäß

Be sure to use a font other than “Raster Fonts” for “cmd”. You can change that in the “Properties” dialogue on the “Font” tab.

Change Code Page permanently

If you are not willing to change NLS_LANG or the code page over and over again when using “cmd” you can also change the default code page for “cmd”. Just add the following registry key:

Windows Registry Editor Version 5.00
[HKEY_CURRENT_USER\Console]
"CodePage"=dword:4e4
C:\>chcp
Active code page: 1252.

C:\>sqlplus marco/*****@******

SQL> select * from bla;

TXT
-------------------------------------
NLS850: äöü
chcp1252: üöäß

Identifying the current NLS_LANG setting

Since the Oracle environment on Windows platforms can be defined via registry settings rather than environment variables it is kind of tricky to query the current setting that is in effect. There is a simple trick to query that setting from SQL*Plus:

SQL> @ .[%NLS_LANG%]
SP2-0310: unable to open file ".[AMERICAN_AMERICA.WE8MSWIN1252]"

If you get

SQL> @ .[%NLS_LANG%]
SP2-0310: unable to open file ".[%NLS_LANG%]"

then there is no current setting, neither in the environment nor in the registry.

Additonal Information

You can find a list of valid Windows code pages on MSDN