3.7 Senora
Another helpful
DDL::Oracle-based product is Martin
Drautzburg's Senora, an alternative to
Oracle's own SQL*Plus.
3.7.1 Installing Senora
You
can get hold of Senora
here:
- http://sourceforge.net/projects/senora
We downloaded the senora-0.4.tgz tarball:
$ gzip -d senora-0.3.tgz
$ tar xvf senora-0.3.tar
$ cd senora
$ vi README.txt
To access Senora, type in something similar to this statement:
$ perl Senora.pm scott/tiger@orcl
You will now see a doppelganger screen that looks amazingly similar
to something you may have seen somewhere before:
SEN*Ora: Release 0.4.0.0.4 - Production on Mon Dec 31 21:53:26 CET 2001
(c) Copyright 2001 Miracle Exploration. No rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.0.0 - Production
PL/SQL Release 8.1.7.0.0 - Production
...
0:scott@orcl>
3.7.2 Senora and SQL*Plus
The output in the previous section looks
remarkably like SQL*Plus? Why, in the name of the two Larrys, would
we bother changing to something new when SQL*Plus comes with all
versions of Oracle (and is likely to be included at least until Kurt
Vonnegut's Ice-9 has been invented, the whole world
has become an icy lake and even the Oracle database has gone open
source)? Well, Senora author Martin Drautzburg does put forward some
arguments for consideration:
- Extensibility
-
Like most of us, you may love SQL*Plus
to bits, but even those devoted to SQL*Plus have to admit that it
lacks extensibility. You have to get hold of tools like TOAD or
SQL*Navigator to do anything beyond basic SQL*Plus — and even
these tools are impossible to extend. If you don't
want to pay for a commercial product, you just have to hope and pray
that the noncommercial version of TOAD you download every month will
now provide the features you need. And you may not yet be in a
position to write your own tools with Perl/Tk. Senora fills the gap
by giving you much of the browsing and analyzing capabilities of
these tools without the need to acquire any other tool or hand over
your credit card number.
- Plug-ins
-
You can extend Senora by providing your
own plug-ins, or maybe collecting other people's
plug-ins from a growing Senora library. Who said the Napster spirit
was dead? Most of Senora's core functionality is
written as plug-ins providing additional commands to a basic Senora
module. We've defined the main plug-ins in Table 3-3.
- Unix-style options
-
Many appreciate
Senora's Unix-style switches, provided via other
plug-ins directly accessible from the Senora command line. This means
you can have a single script with 10 different switch-dependent
options, rather than 10 different scripts.
- Flexible outputs
-
Senora also attempts to provide more
flexible (and perhaps friendlier) report formatting than SQL*Plus,
with columns tending to be only as wide as maximally necessary and
linebreaks tending to come after blank lines, rather than splitting
headers and columns.
- Legacy scripts
-
Senora can run most existing SQL*Plus scripts, including those using
@ and @@, DEFINE,
ampersands (&), and bind variables.
3.7.3 Running Senora
Let's
take a look at the plug-ins available through Senora (see Table 3-3) and then see how some of them work.
Table 3-3. Senora plug-ins
|
DataDictionary
|
Pulls the code of procedures or lists all objects according to a
pattern:
pull, ls, set ddView
|
|
Bind
|
Declares and prints out bind variables:
print (p), variable (var)
|
|
Sqlplus
|
Provides many SQL*Plus-style cloned commands:
show user (id), describe (desc|d), prompt, head, set server
output (so), exec, define, list (l), spool (spo), column, show
errors
|
|
SessionMgr
|
Connects and disconnects sessions:
disconnect (dis), connect (conn|c), quit (exit|q)
|
|
MainLoop
|
Executes SQL scripts:
set verify, startRel (@@), start (@)
|
|
Tuning
|
Provides many highly useful tuning output commands:
show parameter (sp), ps, kept, xqueries (xq), waits,
cstatement (cs), validate (vi), rollSegs, locks, space, hwm, stat,
xplain, jobs
|
|
PluginMgr
|
Enables help and the addition of further plug-ins:
help (he), register, set pluginCode
|
We particularly like the
ls option provided by the
DataDictionary plug-in, illustrated here:
0:scott@orcl> ls
Table/Bonus Table/Dept
Table/Emp Index/Pk_Dept
Index/Pk_Emp Table/Salgrade
The DataDictionary plug-in is standard with
Senora. To add a new self-documented plug-in, you register it
interactively via the shell command prompt. For example, the
Tuning.pm module is an optional extra plug-in,
and you can use the PluginMgr register command
to set it. You may also need to be connected as a
DBA user when using the tuning options, because many of the
commands — for example, xqseries (xq),
which is illustrated here — access DBA-type
tables:
$ perl Senora.pm system/manager@orcl
...
0:system@orcl> register Tuning
Tuning registered
0:system@orcl> xq
Order by "rds/xl" desc (top 10)
Username|reads|exec|loads|rds/Xl|cmd|statement |
-----------------------------------------------------------------------
SYS |1452 |389 |1 |3.73 |3 |select /*+ index(idl_ub1$ i_idl_u|
: : : : : : ub11) +*/ piece#,length,piece fr:
: : : : : :om idl_ub1$ where obj#=:1 and:
: : : : : : part=:2 and version=:3 order by :
: : : : : : piece# :
...
10 rows selected.
system@orcl>
We like Senora a lot, and we think you will too.
|
A number of other more generic
SQL*Plus-like Perl DBI tools are available. You might want to check
out the following:
- http://www.perldoc.com/perl5.6.1/lib/DBI/Shell.html
-
The dbish
program is a command-line interface for Perl DBI itself; it comes
with the Perl DBI download (so you probably already have it!). The
program has evolved greatly over the years from the original
pmsql script written by Andreas
König. Its current incarnation has benefited from input
from Tim Bunce, Jochen Wiedmann, Adam Marks, and most recently, Tom
Lowery.
- http://dbishell.sourceforge.net
-
Vivek Dasmohapatra's dbishell
database shell program includes specific support for Oracle, MySQL,
Sybase, and PostgreSQL. It also provides a generic driver for every
other DBI database type.
- http://piqt.sourceforge.net
-
Lorance Stinson's Perl Interactive Query Tool (PIQT)
is similar to dbishell, but it has more of a
Lisp-like syntax.
- http://sourceforge.net/projects/dsql
-
Daniel Tamborelli Alvarenga's SQL Query Tool works
for MySQL, Oracle, PostgreSQL, SQL Server, ODBC, and all supported
Perl DBI drivers.
|
|
