next up previous contents
Next: TOra Up: Tools to lessen the Previous: Tools to lessen the   Contents


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 - Prod

auto_commit is OFF, commit_on_exit is ON
benr@cuddle> select COUNT(*) from all_users;


1 row selected (0.01 seconds)

benr@cuddle> select * from all_users;5

--------- ------- -------------------
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   

100 rows selected (0.18 seconds)

benr@cuddle> select * from SYSTEM_PRIVILEGE_MAP; >

173 rows selected (0.10 seconds)

benr@cuddle> !head

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

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:

next up previous contents
Next: TOra Up: Tools to lessen the Previous: Tools to lessen the   Contents