Section 3.7. Senora | Perl for Oracle Dbas

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

Senora plug-in	Description/commands/aliases
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 (descd), prompt, head, set server output (so), exec , define, list (l), spool (spo), column, show errors
SessionMgr	Connects and disconnects sessions: disconnect (dis), connect (connc), quit (exitq)
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. ^[6] 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:

^[6] If you want to write your own Senora plug-ins, use the Tuning.pm file as a skeleton.

 $ perl Senora.pm  system/manager@orcl  ... 0:system@orcl>  register Tuning  Tuning registered 0:system@orcl>  xq  Order by "rds/xl" desc (top 10)    Usernamereadsexecloadsrds/Xlcmdstatement                         ----------------------------------------------------------------------- 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.

More SQL*Plus Clones

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.