I would like to use sqlplus in development projects, and I would like it to be easy to use.

First off, I want command-history and tab-completion. The easiest approach is to use rlwrap which uses the GNU readline library as a wrapper to command-line programs such as sqlplus.

If you install rlwrap, you can then set an alias in your .bashrc (or similar)

alias sqlplus='rlwrap sqlplus'

That’s it, sqlplus will now have GNU readline compatible command-line history and editing! If you wish to access sqlplus directly without rlwrap (for loading scripts, etc), simply unalias, i.e.,

$ \sqlplus

I would also like intuitive, terse, and easy-to-remember commands to perform basic tasks like inspecting tables and schemas, viewing and editing packages of stored procedures, etc.

I started a github repository [https://github.com/timwarnock/sqlpath] of utility scripts to make this easier.

Set your $SQLPATH environment variable to the directory containing these scripts, which allow for the following operations.

foo@DB> @show databases

SCHEMA
----------------
FOO
SPAM

2 rows selected.

Change to the SPAM schema,

foo@DB> 
foo@DB> @use spam
spam@DB> 

List all tables with ‘abc’ anywhere in the table name,

spam@DB> @show tables abc

TABLE_NAME
----------------
ABC_USERS
ABC_GROUPS
FOO_ABC_MAP

3 rows selected.

List all packages with ‘abc’ anywhere in the package name,

spam@DB> @show packages abc

NAME
----------------
PROCS_ABC
ABC_DATE

2 rows selected.

View the source code of a package of stored procedures,

spam@DB> @show code abc_date

TEXT
---------------------------------------------------------------------------------------
PACKAGE             "ABC_DATE" AS
  FUNCTION DATE_ID(IN_DATE DATE) RETURN NUMBER RESULT_CACHE;
END;
PACKAGE BODY  "ABC_DATE" AS

  FUNCTION DATE_ID(IN_DATE DATE) RETURN NUMBER RESULT_CACHE
  IS
    ID NUMBER;
  BEGIN
    SELECT D.ID INTO ID FROM DATES_DIM D WHERE SQL_DATE = TRUNC(IN_DATE);
    RETURN ID;
  END;
END;

13 rows selected.

View the explain plan output (also consider ‘set autotrace on’)

spam@DB> explain plan for SELECT D.ID FROM DATES_DIM D WHERE SQL_DATE = '07-feb-12';

Explained.

spam@DB> @explain

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------
Plan hash value: 1171817064

------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name             | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                  |     1 |    13 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| DATES_DIM        |     1 |    13 |     2   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | DATESDIMSQLDTUNQ |     1 |       |     1   (0)| 00:00:01 |
------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("SQL_DATE"='07-feb-12')

Edit a package in vim and automatically recompile

spam@DB> @editcode abc_date
... 
... opens ABC_DATE.sql in vim
... 

Package created.

Package body created.

spam@DB> 

Please see the github repository [https://github.com/timwarnock/sqlpath] for periodic updates.