Simon Krenger

Ok, here is a quick trick that I just found out about earlier this week. I am currently in an Oracle Database 11g: Administration 11 course and learn many new things about database administration.

One thing you will definitely encounter when working with Linux is that it is not possible to scroll back though your SQL*Plus history with the arrow keys. If you try to do so, the following happens:

SQL> select owner, count(1) from dba_tables group by owner;

OWNER				 COUNT(1)
------------------------------ ----------
[..]
SYS				      673
SIMON				       88


7 rows selected.

SQL> ^[[A     " - rest of line ignored.
SQL> command "
SQL> 

Argh! Some kind of wild character sequence appears on the command line. A quick search shows that this is a common problem. One thing that regularly happens is that you enter a SELECT statement that returns too many rows. Because the terminal buffer only contains a certain number of rows, your query disappears and you have to retype it.

There are workarounds such as rlwrap or GQLPlus that work quite well. However, you have to be careful not to break any scripts that rely on the quirks of SQL*Plus.

As an alternative, you can simply type l (lowercase L) and SQL*Plus will show the last statement:

SQL> l
  1* select owner, count(1) from dba_tables group by owner
SQL> 

Now you can copy the statement, paste it and edit it to fit your needs. Another way to edit your last statement is to use the systems editor. To do this, define the _editor value (either during your session or in your glogin.sql) like this:

define _editor=vim

Then, to reuse your query, type (which stands for editor):

ed

And you will be presented with your last query in VIM. Edit your query, save it (:wq) and run your last query again by typing

/

And that is how to reuse your last query in SQL*Plus on Linux.