This paper is a comparative guide to sqlpython and three other open-source SQL*Plus alternatives (gqlplus, Senora, YASQL).
Talk #405 - Database track
Thursday, May 7, 2009, 11:00 AM - 12:00 PM
Orange County Convention Center West
Orlando, FL
Common desktop productivity applications, like spreadsheets, word processors, and the Microsoft Access database, make no clear separation between the program’s handling of its underlying data and the interface it provides for user access. Coming from this world, newcomers to relational databases like Oracle are often confused by the distinction between the database itself and the variety of programs that may be used to access it. For my first several months of working with Oracle, “Oracle” and “SQL*Plus” were basically synonyms in my mind.
In reality, there is a great variety of options for accessing data. End users generally use tools that channel their interaction and insulate them from SQL - tools like Oracle Forms, Reports, and Discoverer; homegrown GUI applications; and web-based interfaces, sometimes built on platforms like Oracle Application Express.
Database administrators and developers need more general-purpose tools that let them see and manipulate data and metadata freely and issue ad-hoc SQL statements, exercising any DML and DDL privileges their accounts have been granted. GUI tools have gained much attention since the introduction of Oracle SQL Developer, though Quest Software’s TOAD remains extremely popular due to its rich feature set and vigorous development. Other GUI tools include PL/SQL Developer, TORA, and open-source tools like Squirrel. Oracle Enterprise Manager and JDeveloper can also be used for ad-hoc access to Oracle tables and data.
Nonetheless, command-line interaction through SQL*Plus remains a cornerstone of most Oracle professionals’ work. SQL*Plus is ubiquitous and familiar, running on virtually any system. Typing a familiar SQL or SQL*Plus command is often faster than navigating a GUI’s menus. SQL*Plus gives experienced users a sense of full control of their system. Finally, professionals trying to avoid repetitive motion injuries may find frequent use of a mouse uncomfortable, preferring the ergonomics of a good keyboard.
SQL*Plus still appears much the same as it did years ago, and it’s easy to assume that command-line clients are inherently limited, bare-bones approaches. There have been improvements, however, which many users are unaware of; by educating yourself about SQL*Plus’ newer features, you may find that SQL*Plus is a more powerful tool than you’d realized.
Furthermore, SQL*Plus is not the only command-line client for interacting with Oracle. Over the years, Oracle professionals craving greater capabilities have produced several alternate command-line clients that introduce extra features they found useful, and you may as well. In fact, since all these tools are open-source, you are welcome to add new features to meet your own needs, and to contribute your improvements for incorporation into the tools. All the SQL*Plus alternative tools are free of charge. (SQL*Plus is also available free of charge, since it is packaged with free Oracle products like the Oracle XE client.)
This paper will begin with a short introduction to each of the tools reviewed. It will then present enhanced capabilities, most of which appear in more than one of the tools. Next, it will list the most SQL*Plus capabilities that some or all of the alternative products lack. Next, it will cover installing and extending each tool, and finally, briefly discuss cross-RDBMS SQL client tools.
For purposes of this paper, “UNIX” implies UNIX, Linux, and similar POSIX-based operating systems - essentially anything but Windows. Curly braces ({}) denote non-literal values, as in {username}/{password}.
Oracle’s command-line SQL interface, and possibly the most familiar face of Oracle to the world. We will take the well-known features of SQL*Plus as our baseline, assuming that readers are familiar with them. Some of its lesser-known features, however, will be included in our review of enhanced features.
This tool is essentially a thin wrapper around SQL*Plus, passing all input directly to SQL*Plus. It preserves virtually all SQL*Plus’ capabilities and feel. Gqlplus was written in C by Ljubomir J. Buturovic and available under the GNU General Public License. Version 1.12 was released in December 2006. It is available from its SourceForge page at http://gqlplus.sourceforge.net/.
Senora - an acronym for Shell ENvironment for ORAcle - is a Perl-based tool was created by Martin Drautzburg in 2003. As its name suggests, its focus is on adding features of the UNIX shell to a SQL*Plus-like environment. Version 0.5.1 was released under the Artistic License in April 2003. A few of the capabilities described here apply only to the 0.6 release, planned for early 2009; they are labelled as such in the text. Senora is available from its SourceForge page at http://senora.sourceforge.net/.
Senora uses a plugin architecture; some of its commands are not available in any given senora session until the plugin is activated with the register command. For example, senora’s tuning-related commands will not be available until register Tuning is executed in the session. A startup file, login.sra, can be used to automatically register all desired plugins on startup.
Another Perl program, this one by Nathan Shafer and Balint Kozman. Many of its advanced features center around producing query output in more versatile and convenient forms. Version 1.83 was released under the GNU General Public License in May 2005. It is available from its SourceForge page at http://sourceforge.net/projects/yasql/.
A Python program by Luca Canali and Catherine Devlin (this author). It imitates the features of senora and YASQL as well as introducing many unique features. Active development is ongoing, and version 1.6.1 was released in March 2009. Some defects as of this paper’s writing (March 2009) are likely to be fixed by the time you read it. It is available from the Python Package Index at http://pypi.python.org/pypi/sqlpython
| gqlplus | no |
| senora | yes |
| YASQL | no |
| sqlpython | yes |
Since gqlplus and YASQL were written in C and Perl, respectively, it should be possible, in principle, to compile and run them on Windows. No precompiled versions of them are available, however, and no Windows installation procedures have been documented.
All the tools are available for all UNIX environments.
SQL*Plus (10g and higer), senora, YASQL, sqlpython
SQL>, the standard SQL*Plus prompt, provides no useful information. The prompt can be changed with SQL*Plus’ SET SQLPROMPT command.
As any DBA who has mistakenly issued a command in what they believed was the development instance can tell you, the most useful prompt is one that identifies what username and database is being used, like me@production>. To get this prompt in SQL*Plus, issue SET SQLPROMPT “_user’@’_connect_identifier> “.
It’s important that the prompt be kept up-to-date; if a CONNECT statement is used to connect to a different instance, the prompt must be updated or it will become deceptive. In SQL*Plus, SET SQLPROMPT should be included in $ORACLE_HOME/sqlplus/admin/glogin.sql. SQL*Plus 10g and higher execute the commands in glogin.sql each time a CONNECT statement changes this information.
SQL*Plus 9i and earlier run glogin.sql only when a new session is started, as does gqlpython. In these environments, the “smart prompt” cannot be trusted, since a CONNECT statement may have changed the reality.
Senora, YASQL, and sqlpython provide this type of smart prompt out of the box.
gqlplus, YASQL, sqlpython
Tab completion saves typing and avoids spelling errors by filling in the remainder of a command or object name when the first few letters are entered and the TAB key is hit. If there are multiple valid endings to an incomplete word, each potential ending.
all
It’s important to remember to set the $EDITOR environment variable before starting any command-line tool under UNIX. All the tools allow textfile editing, but if $EDITOR is not set, they will use the system default editor - generally UNIX ed, which can be traumatizing.
sqlpython does not give up so easily if $EDITOR is not set, searching for more advanced text editors and starting them preferentially.
all
All the tools support running scripts with @path/to/script.sql. Establishing a personal library of script files is an important part of building your productivity. SQL*Plus, gqlplus, and sqlpython also support running scripts from urls, like @http://host.com/scriptlibrary/myscript.sql, allowing you to keep your script library on the web.
The convenience of the script library is crucial. If your scripts are hidden away in a directory that is difficult to remember and type, you will be tempted to rewrite queries instead of using your established scripts. It’s good practice to always start your SQL tool in the directory that contains your script library; that way, you can access your scripts without having to type path names. An alias or shortcut at the operating-system level can be useful; for instance, put alias sql=’cd ~/myscriptlibrary; sqlplus’ in your .bashrc file.
Choose your script names carefully, so that you will find them easily in the future. To view your script library from within your SQL tool, type host dir or host ls.
SQL*Plus (Windows, or with rlwrap), gqlplus, senora, YASQL, sqlpython
On Windows, the up- and down-arrow keys can be used to scroll through the history of SQL*Plus commands issued during the session. SQL*Plus on UNIX does not provide this feature. You can restore the feature, however, by installing a free GNU tool called rlwrap, then invoking SQL*Plus under it: rlwrap sqlplus me@instance. rlwrap can also provide cursor-key history to senora, and in fact to any command-line program.
Gqlplus and sqlpytyhon provide cursor-key command history out of the box; Senora and YASQL do also, provided that the Term::ReadLine::Perl module has been installed (see Installation).
In addition, senora and sqlpython have a history or hi command that gives a numbered list of all commands issued in the session.
| hi | List all commands issued in this session |
| hi {search string} | List all commands containing {search string} |
| ! | rerun the last command |
| !!{N} | rerun command number N |
| !{search string} | rerun last command containing {search string} |
| hi | List all commands issued in this session |
| hi {N} | List command number {N} |
| hi -{N} | List all commands up to command number {N} |
| hi {N}- | List all commands from command number {N} onward |
| hi {search string} | List all commands containing {search string} |
| hi /{regex}/ | List all commands matching regular expression /{regex}/ |
| r or \g | rerun the last command |
| r {N} | rerun command number {N} |
| r {search string} | rerun last command containing {search string} |
| r /{regex}/ | rerun last command matching {regex} |
senora, YASQL, sqlpython
Senora, YASL, and sqlpython economize on column space when returning query results. This can make output much neater, more compact, and easier to read.
SQL*Plus:
SQL> select * from party where name = 'Gimli';
NAME STR INT WIS DEX CON CHA
---------------------------------------- ---------- ---------- ---------- ---------- ---------- ----------
Gimli 17 12 10 11 17 11
YASQL:
jrrt@orcl> select * from party where name = 'Gimli';
NAME STR INT WIS DEX CON CHA
----- ---- ---- ---- ---- ---- ----
Gimli 17 12 10 11 17 11
1 row selected (0.03 seconds)
senora, sqlpython
“Senora” is an acronym for Shell ENvironment for ORAcle, and many of its special features are inspired directly by UNIX shell commands. sqlpython duplicates most of these. Also, as in Unix, these commands can be altered with flags.
ls is used to list Oracle objects from the data dictionary. These are reported as though they existed in an {object type}/{object name} directory structure:
0:jrrt@orcl> ls
Table/Party Index/Xpk_Party
0:jrrt@orcl> ls -h
NAME
ls - list all objects matching pattern
SYNOPSIS
ls <type/name>
-a List all objects, even with a dollar
-l List validity of objects too
-C List constraints etc too
-I List indexes etc too
-i List invalid objects only
FILES
DataDictionary.pm
0:jrrt@orcl> ls -l Table/*
VALID 27-FEB-09 Table/Party
Searches entire tables (all columns) for the desired text:
0:jrrt@orcl> grep 17 party
Name |str|int|wis|dex|con|cha|
--------------------------------
Gimli | 17| 12| 10| 11| 17| 11|
Legolas| 13| 15| 14| 18| 15| 17|
2 rows selected.
Also like UNIX commands, the operation of many senora and sqlpython commands can be altered by using flags, as in grep -i {target text} {table name} , where -i makes the search case-insensitive. See {command} -h for help on each command, including a list of flags.
senora, YASQL, sqlpython
Inspecting Oracle objects using the data dictionary requires plenty of typing and an excellent memory. The alternative tools provide convenient shortcuts.
This table shows some approximately equivalent ways to extract object information from the data dictionary; actually, the SQL queries given require considerable interpretation and usually additional joins to get truly useful information, whereas the YASQL/senora/sqlpython commands provide information ready-to-use. There are too many possibilities to list, but a sample will give the idea.
| SQL*Plus | YASQL | senora | sqlpython |
|---|---|---|---|
| SELECT table_name FROM tabs; | show tables | ls Table/* | ls table |
| SELECT object_name, status, last_ddl_time FROM all_objects WHERE object_name LIKE ‘A%’; | ls -l *a* | ||
| SELECT * FROM all_constraints WHERE table_name = ‘{table name}’ | show constraints on {table name} | refs {table name} | refs {table name} |
| SELECT * FROM user_indexes WHERE table_name = ‘{table name}’ | show indexes on {table name} | \di {table name} | |
| SELECT * FROM all_triggers WHERE table_name = ‘{table name}’ | show triggers on {table name} | ||
| DBMS_METADATA.GET_DDL( ‘{object_type}’, ‘{object_name}’) | pull {object_name} | pull {object_name} | |
| SELECT * FROM all_tab_comments WHERE table_name = ‘{table name}’; SELECT * FROM all_col_comments WHERE table_name = ‘{table name}’; | desc -l {table_name} (available in v0.6) | comments {table name} or \dd {table name} | |
| SELECT * FROM all_source WHERE text LIKE ‘%{target}%’; | find {target} | find -a {target} | |
| SELECT * FROM all_tab_columns WHERE column_name LIKE ‘%{column name}%’ | find -ac {column name} |
In sqlpython, the command ls -tl;10 will list the most recent ten objects by their last DDL time - which can be a handy way to answer the question, “What was I working on here?” (ls -tl is similarly useful in the Unix shell.)
senora, YASQL, sqlpython
In senora, xplain {text of query to explain} is the equivalent of issuing EXPLAIN PLAN FOR {query} in SQL*Plus, then querying the PLAN table with spacing inserted for neat formatting.
In YASQL, show plan displays the last PLAN table entry, neatly formatted. (Issuing EXPLAIN PLAN first is up to you.)
In sqlpython, explain {SQL ID} shows the execution plan for the SQL statement with the given ID. If SQL ID is omitted, it defaults to the most recent SQL executed. (This is not necessarily the last statement EXPLAIN PLAN was issued against.)
YASQL, sqlpython
You can use the UNIX pipe (|) to send query output to a UNIX shell command; this will work much like the pipe in the UNIX shell would. An example in sqlpython:
jrrt@orcl> select name from party; | sed 's/Legolas/Elfie-poo/'
4 rows selected.
NAME
-------
Frodo
Gimli
Elfie-poo
Sam
You can use > {filename} to route output into a file.
In sqlpython, if the filename is omitted after a >, the output is simply redirected into the paste buffer, and can then be pasted into a document, email, etc. An external program called xclip is necessary to make this work under Unix, and can be installed from your package repository.
In YASQL, < can be used to import data directly from a CSV file into a table:
jrrt@orcl> insert into party values (:1, :2, :3, :4, :5, :6, :7); < newmembers.csv
In sqlpython, < {filename} simply replaces < {filename} with the content of {filename}, then executes the resulting command.
SQL*Plus, YASQL, sqlpython
SQL*Plus can produce HTML tables instead of plain text using the SET MARKUP ON command. Using this, a query could be published as a webpage entirely from within a SQL*Plus session:
> set markup html on
> spool /var/www/myreport.html
> select * from mytable;
> spool off
> set markup html off
YASQL and sqlpython have much more extensive, and convenient, output formatting options. By simply replacing the “;” terminating a query with a special backslash sequence, you can get output in a variety of alternate formats.
| \b | bar graph (sqlpython) |
| \c | CSV (sqlpython) |
| \c | CSV (no column names) (sqlpython) |
| \G | list (aligned) |
| \g | list (not aligned) |
| \h | HTML table (sqlpython) |
| \i | as SQL insert statements |
| \j | JSON (sqlpython) |
| \l | line plot, with markers (sqlpython) |
| \l | line plot, no markers (sqlpython) |
| \p | pie chart (sqlpython) |
| \s | CSV |
| \S | CSV (no column names) |
| \t | transposed (sqlpython) |
| \x | XML (sqlpython) |
Thus, the sqlpython way to produce and web-publish a report fits on a single line:
> select * from party\h > /var/www/party.html
List (\g) output can be especially useful in reading tables with many columns without confusing line wrapping. Transposed (\t) output is ideal for reading many-columns/few-rows tables like V$DATABASE. Generating plots directly from queries is just fun.
YASQL, sqlpython
Follow the terminator in a query (usually “;”) with a positive integer, and the query will only output up to that number of rows:
jrrt@orcl> select * from party;2
NAME STR INT WIS DEX CON CHA
----- --- --- --- --- --- ---
Frodo 8 14 16 15 14 16
Gimli 17 12 10 11 17 11
2 rows selected.
senora, sqlpython
When connect -a {username@instance} is used to create a new connection, senora does not close the old connection. It keeps both connections alive, and switching between them is far more convenient than creating new connections:
Enter sample here
YASQL, sqlpython
Several backslash-character command shortcuts have been copied from psql, the command-line tool for the PostgreSQL open-source database, into YASQL and sqlpython.
| \c | connect (sqlpython) |
| \c | clear buffer (YASQL) |
| \d | describe (sqlpython) |
| \e | edit |
| \g | run (sqlpython) |
| \h | help (sqlpython) |
| \i | load (sqlpython) |
| \l | list (YASQL) |
| \o | spool (sqlpython) |
| \p | list |
| \q | quit |
| \w | save |
| \db | directory of tablespaces (sqlpython) |
| \dd | comments (sqlpython) |
| \dn | directory of schemas (sqlpython) |
| \dt | directory of tables |
| \dv | directory of views |
| \di | directory of indexes |
sqlpython
Senora and sqlpython support SQL*Plus-style bind variables. sqlpython also has an optional, easygoing syntax for defining bind variables and permits them to be dynamically defined (no declaration required):
jrrt@orcl> :name = 'Legolas'
jrrt@orcl> print
:name = Legolas
sqlpython’s bind command creates and populates bind variables for each column of the row most recently returned from a query. The optional autobind setting automatically does this after every query that returns a single row.
jrrt@orcl> select name, str, int from party where name = :name;
NAME STR INT
------- --- ---
Legolas 13 15
1 row selected.
jrrt@orcl> print
:1 = Legolas
:2 = 13
:3 = 15
:int = 15
:name = Legolas
:str = 13
senora, sqlpython
Each program provides an assortment of commands to provide performance-related data. There are too many commands for detailed description, but a partial list will supply some idea for the capabilities.
| command | abbrev | effect |
|---|---|---|
| analyzeObject | ana | analyze tables or indexes |
| cacheStats | cst | Display memory statisticts |
| compareStat | coms | compare statistics of two schemas |
| cstatement | cs | lists current SQL statements, by session |
| cstatement -S | cs -S | lists current SQL statements with their execution stats |
| FkeyIndexes | fki | analyze the existance of foreign key indexes |
| hwm | get high watermark info on analyzed tables (experimental) | |
| jobs | print job information | |
| kept | show kept (pinned) code | |
| kill | kill a session | |
| locks | show sessions and the objects the are waiting for | |
| logs | print redo log activity | |
| profile | estimate current execution times | |
| ps | print session information | |
| resize | resize datafiles | |
| rollSegs | print rollback info | |
| show parameter | sp | show init.ora parameter |
| space | show tablespace and file stats | |
| stat | print session statistics | |
| validate | vi | xvalidate structure an index |
| waits | show what sessions are waiting for | |
| xqueries | xq | show most expensive quieries |
Before senora tuning commands can be run, register Tuning must be executed in the session.
| load | prints the OS load on all cluster nodes (10g RAC) |
| longops | prints from gv$session_longops |
| sessinfo | prints session information. Parameter: sid |
| sql | prints the sql text from the cache. Parameter: sql_id |
| top | List top active sessions |
| top9i | 9i (and single instance) version of top |
YASQL, sqlpython
YASQL supports * wildcards in column names, if there is a ~/.yasqlrc or /etc/yasql.conf file containing column_wildcards=1.
sqlpython’s wildcards are more powerful. When the option wildsql is set to ON, sqlpython will accept these in the column list of a SELECT statement:
- Wildcards (* or %, and ?)
- Column numbers (#1, #2)
- !, meaning NOT. !str means “all columns except STR”.
These can be combined. SELECT !#2, !c% FROM party means ‘select all columns except column #2 and any column beginning with ‘C’”, and is translated by sqlpython into SELECT name, int, wis, dex FROM party;.
No tool reproduces all the features of SQL*Plus (though gqlplus comes very, very close). These are the drawbacks most likely to be noticed.
Like SQL*Plus, gqlplus invokes glogin.sql when it is started. Unfortunately, gqlplus does not re-invoke it when a new CONNECT statement is issued during a session. Thus, as for SQL*Plus versions xxxx and before, the “smart prompt” can become dangerously deceptive in gqlplus.
Gqlplus, Senora, and sqlpython can run all PL/SQL as SQL*Plus does.
YASQL can run single lines of PL/SQL with exec, and automatically echoes DBMS_OUTPUT.PUT_LINE output, but it does not recognize anonymous SQL BEGIN..END blocks. sqlpython requires that PL/SQL blocks be bracketed in REMARK BEGIN and REMARK END statements
In SQL*Plus, host {command} or !{command} run {command} on the operating system. These work in all the alternative tools as well, except that senora recognizes only host (since, in senora, ! is used for re-running commands from the history instead.)
Error messages in some programs are less friendly than in SQL*Plus. For instance, senora responds to most errors with “what ?”:
0:jrrt@orcl> grep;
what ? "grep;" ?
sqlpython passes Python or Oracle errors it encounters up to the user, but it cannot highlight the place in a SQL command where a syntax error took place.
When compiling PL/SQL objects (functions, packages, procedures) that contain errors, the tools vary in the quality of information they return.
Note that, in senora and YASQL, abbreviated forms of show errors, like sho err, are not recognized.
Only SQL*Plus can be used to login to a closed database. gqlplus hangs during the attempt. The other tools simply report that the database is closed and cannot connect.
In senora and YASQL, the ed and \e commands (respectively) can be used to edit text files using the environment’s default editor. Unlike SQL*Plus, however, they do not edit the most recently issued command (the buffer), and the resulting file is not automatically run. sqlpython’s ed, without an argument, edits the SQL buffer as SQL*Plus does, and runs the result when the editor is closed. You may also ed {filename} to edit a specific text file, ed {N} to load command number {N} from the command history into the buffer and edit it. Finally, if your EDITOR environment is not set, sqlpython tries to find a more user-friendly editing program on your machine than UNIX ed.
sqlpython removes all comments from commands at an early stage of parsing, and thus will not honor execution hints embedded within SQL statements.
YASQL does not support bind variables (: variables) or substitution variables (& variables). The other tools support them, and sqlpython has enhanced bind variables (see above).
sqlpython parses each line of a SQL command as it is entered to determine whether the command is finished yet. This produces noticable waits as a query approaches ten lines of SQL, and downright annoying as it grows beyond that. To avoid it, you can prefix long queries with REMARK BEGIN and end them with REMARK END, promising sqlpython that you will only enclose a single query and freeing it from the need to parse until REMARK END is reached.
All the alternate tools are produced by individuals or very small groups, so it is not uncommon to encounter bugs. If you encounter one, you can e-mail the author; for sqlpython in particular, error reports filed in the project’s bug tracker at http://trac-hg.assembla.com/sqlpython are appreciated. Since the projects are open-source, and most of them are written in the easy-to-use languages Perl and Python, you can always fix bugs yourself and improve the products for everyone.
First, make sure you have Oracle client software on your machine! gqlplus simply wraps SQL*Plus, and wenora, YASQL, and sqlpython depend on Perl or Python modules that access the Oracle OCI, so none of them will work in the absence of an Oracle client.
You will also need Perl (for senora or YASQL) or Python (for sqlpython). Virtually all UNIX systems will have these languages installed, and frankly, every computer should. Free installers for all common platforms are available from the languages’ websites at http://www.perl.org/ and http://python.org/.
Gqlplus is distributed as a gzipped tarball, downloadable from http://gqlplus.sourceforge.net/, containing the C source code and a precompiled binary for Linux. To use on Linux, simply expand the tarball and run Linux/gqlplus; you may want to place the file or a link to it somewhere in your PATH. On other systems, you will a C compiler such as gcc; run ./configure and make as directed in the project’s README file.
Senora is distributed as a gzipped tarball, downloadable from http://senora.sourceforge.net/
Senora depends on the DBI and DBD::Oracle packages. Perl’s cpan tool is a fairly convenient way to install them:
$ cpan
cpan> install DBI Term::ReadLine::Perl
cpan> force install DBD::Oracle
If this is your first time running cpan, it will ask you many configuration questions on startup. Accepting the default answers is generally correct. Running under sudo, cpan may not find your $ORACLE_HOME; running as root avoids this problem.
After installation is complete, download and unzip Senora-0.5.1.tgz from http://senora.sourceforge.net/, cd into senora/core, and run perl Senora.pm.
http://senora.sourceforge.net/ also has a “Senora for Windows” download. Download it, unzip, and run SenoraForWindows/senora.exe. You may wish to create a shortcut for your convenience.
Like senora, YASQL depends on the DBI and DBD::Oracle packages. Several other Perl packages are recommended and can also be installed through cpan:
cpan> install DBI
cpan> force install DBD::Oracle
cpan> install Term::ReadKey Text::CSV_XS Time::HiRes Term::ReadLine::Perl
Download yasql-1.83.tar.gz from http://sourceforge.net/projects/yasql/, unzip and untar it, and cd into yasql-1.83. Run ./configure, make, and (as root) make install. This will place the yasql executable into /usr/local/bin, so that you can run it from anywhere by simply typing yasql.
Some UNIX machines come shipped with Python but without python-dev, which you should install from your distribution’s package repository if you don’t have it already. Downloads from http://python.org (including the Windows installer) include python-dev out of the box.
The easiest way to install sqlpython is with easy_install, a popular Python package installation tool. You can get easy_install by installing python-setuptools from your Linux distribution’s repository, or by downloading directly from http://peak.telecommunity.com/DevCenter/EasyInstall.
Once you have easy_install, simply type (as root, or using sudo):
$ easy_install -UZ sqlpython
The -UZ flags, though optional, will update your sqlpython installation, if necessary, and unzip the code so that you can modify it.
To generate graphs using sqlpython’s \b, \g, and \l terminators, you will need to install pylab (or matplotlib):
$ easy_install pylab
$ apt-get install python-matplotlib
Alternately, a Windows installer is available at http://pypi.python.org/pypi/sqlpython, though easy_install works on Windows as well.
All the alternative tools are open-source, so you have the right to view the source code, make your own modifications, contribute your modifications back to the original project, and even distribute your own modified version. YASQL, senora, and sqlpython are especially easy to modify since they are written in dynamic languages, which are easy to program in and require no compilation step.
When you are ready to begin customizing or improving your favorite tool, imitation is the key; look in the source code files of the tool for the code corresponding to a simple command, copy and rename it, then work on small modifications until the new functionality meets your needs.
As a specific example, we will add new commands to sqlpython.
easy_install -UZ sqlpython places sqlpython’s source code files into your Python site-packages directory, probably someplace like /usr/lib/python2.5/site-packages/. You can edit the files there (you may need to change their ownership from root to yourself first). Any method in mysqlpy whose name begins with do_ will be registered as a sqlpython command the next time sqlpython is started. The simplest possible new command could be written as:
def do_hello(self, arg):
print 'Hello, World!'
Now, typing hello [arguments] in a sqlpython session calls do_hello. The optional arguments will be assigned to arg, but in this case they are not used. Let’s see a more useful function, one that will
@options([make_option('-u', '--uppercase', action='store_true',
help='use ALL CAPS')])
def do_greet(self, arg, opts):
'Provides a personalized greeting.'
result = 'Hello %s!\n' % arg
if opts.uppercase:
result = result.upper()
self.stdout.write(result)
Now we run sqlpython and try the new command:
jrrt@orcl> greet
Hello !
jrrt@orcl> greet Larry E.
Hello Larry E.!
jrrt@orcl> help greet
Provides a personalized greeting.
Usage: greet [options] arg
Options:
-h, --help show this help message and exit
-u, --uppercase use ALL CAPS
jrrt@orcl> greet -u World
HELLO WORLD!
Senora v0.6 includes a command, lregister, that will automatically create a new senora plugin from SQL commands in a file. This will make an incredibly easy way to make your own senora commands for your favorite tasks.
All the tools reviewed so far have been designed purely for Oracle. If you work with multiple RDBMS platforms, however, you may be interested in a SQL tool that is compatible with all of them, which opens up a whole new set of possible programs.
One such possibility is sqlcmd (http://www.clapper.org/software/python/sqlcmd), an open-source Python program available without charge on all platforms. It operates seamlessly across Oracle, MySQL, PostreSQL, SQLite, and MS SQL Server.
Its functionality is rather basic compared to the Oracle-specific tools, and there are conventions that will be unfamiliar to those immersed in an Oracle world. For example, use of stored procedures is not supported at all, column and table names are case-sensitive, and database connection details are specified with an unfamiliar format (sqlcmd -d orcl,oracle,localhost,scott,tiger in place of sqlplus scott/tiger@orcl). Nonetheless, it and similar tools are viable options when cross-RDBMS compatibility is a key requirement.
All the tools offer capabilities that will make command-line interaction with Oracle easier and more powerful, and you should experiment to find out which one(s) suit you best. If you want as little change as possible, gqlplus provides a few extra features while being almost completely transparent and SQL*Plus-compatible. Sqlpython has the broadest set of features and is being developed most actively. YASQL and senora may be good alternatives if you want some of the features unique to those programs, such as senora’s rich set of tuning commands, or if you prefer Perl for writing your own improvements.
There will always be a place for SQL*Plus, but alternative tools can replace and improve upon some of the work you have been doing both with SQL*Plus and with GUI tools. No matter which programs you use, you will get more powerful, enjoyable commmand-line experiences and gain new appreciation for what open-source development can bring to an Oracle environment.
Thanks to all the authors of the open-source tools reviewed here, but especially to Martin Drautzburg, author of senora, for important corrections and additions.