Using HISTORY in SQL*PLUS 12.2….

One of the biggest things that we couldn’t do in the previous versions of the oracle database was to use the back arrow button-to reexecute the old commands, without typing them. Of course, a workarouns is possible in the form of rlwrap but it’s not comparable to a natively available feature in the SQL*PLUS. Well, until now because now, we have HISTORY option available in 12.2 SQL*PLUS. Let’s take a look.

SQL> /

BANNER                         CON_ID
—————————————- ———-
Oracle Database 12c Enterprise Edition R      0
elease 12.2.0.1.0 – 64bit Production

PL/SQL Release 12.2.0.1.0 – Production          0
CORE    12.2.0.1.0    Production              0
TNS for Linux: Version 12.2.0.1.0 – Prod      0
uction

NLSRTL Version 12.2.0.1.0 – Production          0

SQL> show history
history is OFF
SQL> select * from dual;

D
–
X

SQL> ^[[A                  ” – rest of line ignored.
SQL> 042: unknown command “

So we can see that there is new option of HISTORY in the SQL*PLUS and it is OFF by default. And with this, we obviously can’t use get back to the previously executed commands. So let’s turn the feature on.

SQL> set history on
SQL> select * from dual;

D
–
X

SQL> select sysdate from dual;

SYSDATE
———
09-MAR-17

SQL> show history
history is ON and set to “100”
SQL>

So we have enabled the History and it’s set to 100 statements by deault. We have executed two commands now. Let’s see them.

SQL> history
  1  select * from dual;
  2  select sysdate from dual;
  3  show history

Cool! So now how we execute the 2nd statement?

SQL> history 2 run

SYSDATE
———
09-MAR-17

Nice! And how do we edit this statement and change from Sysdate to user name?

SQL> history 2 edit
And now, if we check the History, we see that the edited statement is available for us to use.

SQL> history
  1  select * from dual;
  2  select sysdate from dual;
  3  show history
  4  select username  from dual;

And for some reason, you want to clear the history information, you can use CLEAR option.

SQL> history clear
SQL> history
SP2-1651: History list is empty.

Cool isn’t it? Well now if you are going to say that it’s still not equivalent of using the back arrow button, yes it’s not but then, it’s better than the hassle of installing an external wrapper downloaed from the internet and of course, miles better than not having any of such facility at all IMO.

Happy weekend and happy testing 12.2 features!