SQL*Plus sucks. You can't really debate the point, it just does. Especially for those of us who have been spoiled with BASH and ZSH's nifty completion and history editing features it's almost unbearable to be stuck in the confines of SQL*Plus. But thanks to some thrifty coders the madness can end by using YaSQL.
YaSQL is Yet Another SQL*Plus replacement. It's a GPL app written in PERL by Jon Nangle and Nathan Shafer and is copyright Ephibian, Inc.
So what is SQL*Plus really missing? History editing is probly the big one. After typing out a long query and then screwing up a line you don't want to cut and paste the whole damned thing back in. PostgreSQL like listing would be kool too to quickly see a list of all tables, indexes, etc. Better output formatting is a must, since SQL*Plus table output is essentially unreadable. And how about bounding! When I look at a big table (or one that i think might be big) it would be nice to only see the first 10 lines, or last 10 lines, or whatever instead of the whole thing, especially when you just want to see acouple rows to craft a better query. Well, good news! YaSQL does all this and more!
Here's an example that'll sell you instantly.
benr@nexus6 ~$ export ORACLE_HOME=/u01/app/oracle/product/10.1.0/db_1
benr@nexus6 ~$ export ORACLE_SID=cuddle
benr@nexus6 ~$ export LD_LIBRARY_PATH=$ORACLE_HOME/lib
benr@nexus6 ~$ yasql benr/oracle
YASQL version 1.81 Copyright (c) 2000-2001 Ephibian, Inc.
$Id: yasql,v 1.81 2002/03/06 21:55:13 nshafer Exp nshafer $
Please type 'help' for usage instructions
Attempting connection to local database
Connected to: Oracle Database 10g Enterprise Edition Release 10.1.0.2.0 - Prod
auto_commit is OFF, commit_on_exit is ON
benr@cuddle> select COUNT(*) from all_users;
COUNT(*)
--------
24
1 row selected (0.01 seconds)
benr@cuddle> select * from all_users;5
USERNAME USER_ID CREATED
--------- ------- -------------------
BENR 58 2004-10-14 01:18:18
SCOTT 57 2004-02-05 13:44:42
MGMT_VIEW 56 2004-02-05 13:37:17
WKPROXY 51 2004-02-05 13:33:46
WKSYS 50 2004-02-05 13:33:46
5 rows selected (0.01 seconds)
benr@cuddle> show all tables;
Table Name Type Owner
------------------------------ ----- -------
AUDIT_ACTIONS TABLE SYS
AW$AWCREATE TABLE SYS
...
WM$VERSION_TABLE TABLE WMSYS
WM$WORKSPACES_TABLE TABLE WMSYS
100 rows selected (0.18 seconds)
benr@cuddle> select * from SYSTEM_PRIVILEGE_MAP; >sys.priv.map.out
173 rows selected (0.10 seconds)
benr@cuddle> !head sys.priv.map.out
PRIVILEGE NAME PROPERTY
--------- ------------------------------ --------
-3 ALTER SYSTEM 0
-4 AUDIT SYSTEM 0
-5 CREATE SESSION 0
-6 ALTER SESSION 0
-7 RESTRICTED SESSION 0
-10 CREATE TABLESPACE 0
-11 ALTER TABLESPACE 0
benr@cuddle>
In this example you can first see a standard SQL statement that counts the rows of the all_users table and finds 24 rows. In the next statement I query for all rows, but append a "5" after semicolon telling YaSQL to only show me the first 5 rows of output. Then I use the "show" YaSQL command to list all the avalible tables to me (remember my rant about not having this ability like PostgreSQL?). And in the last two statements I run a select but redirect output to a file and then use "!" to run a system command to read it from within YaSQL.
Thats just a taste of the power of YaSQL. This is a professional grade interface that is easy to use, flexable, and won't leave you wondering why DBAs seem to enjoy needless suffering. It's anything but "Yet Another".
Grab a copy and make sure to mail the authors and tell them how much you value it. There are lots of SQL*Plus replacements, but trust me: look no further.
You can download YaSQL from its SourceForge project page:
http://sourceforge.net/projects/yasql/