annotate docs/source/comparison.rst @ 288:e7578e7ff9dd

sliced pull almost working...
author catherine@dellzilla
date Fri, 20 Mar 2009 17:23:54 -0400
parents abb4c6524113
children f7daca26cc00 98975ce340ac
rev   line source
286
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
1 This paper is a comparative guide to sqlpython and
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
2 three other open-source SQL\*Plus alternatives (gqlplus, Senora, YASQL).
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
3
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
4 ******************************************************
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
5 Long Live the Command Line: SQL\*Plus and Alternatives
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
6 ******************************************************
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
7
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
8 `COLLABORATE 09 <http://www.ioug.org/collaborate09/>`_
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
9
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
10 Talk #405 - Database track
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
11
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
12 Thursday, May 7, 2009, 11:00 AM - 12:00 PM
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
13
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
14 Orange County Convention Center West
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
15
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
16 Orlando, FL
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
17
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
18 ============
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
19 Introduction
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
20 ============
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
21
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
22 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.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
23
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
24 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.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
25
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
26 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
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
27 JDeveloper can also be used for ad-hoc access to Oracle tables and data.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
28
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
29 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.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
30 Typing a familiar SQL or SQL\*Plus command is often faster than navigating a GUI's menus.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
31 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.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
32
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
33 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.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
34
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
35 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.)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
36
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
37 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.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
38
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
39 For purposes of this paper, "UNIX" implies UNIX, Linux, and similar POSIX-based operating
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
40 systems - essentially anything but Windows. Curly braces (`{}`) denote non-literal values,
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
41 as in `{username}/{password}`.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
42
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
43 =====
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
44 Tools
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
45 =====
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
46
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
47 SQL\*Plus
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
48 ---------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
49
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
50 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.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
51
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
52 gqlplus
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
53 -------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
54
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
55 This tool is essentially a thin wrapper around SQL\*Plus, passing all input
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
56 directly to SQL\*Plus. It preserves virtually all SQL\*Plus' capabilities
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
57 and feel. Gqlplus was written in C by Ljubomir J. Buturovic and available under the GNU General
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
58 Public License. Version 1.12 was released in December 2006. It
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
59 is available from its SourceForge page at http://gqlplus.sourceforge.net/.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
60
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
61 senora
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
62 ------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
63
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
64 Senora - an acronym for Shell ENvironment for ORAcle - is a Perl-based tool was created by
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
65 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
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
66 Artistic License in April 2003. A few of the capabilities
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
67 described here apply only to the 0.6 release, planned for early 2009; they are labelled as such in the text.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
68 Senora is available from its SourceForge page at http://senora.sourceforge.net/.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
69
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
70 Senora uses a plugin architecture; some of its commands are not available
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
71 in any given senora session until the plugin is activated with the `register` command. For example, senora's
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
72 tuning-related commands will not be available until `register Tuning` is executed in the session.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
73 A startup file, `login.sra`, can be used to automatically register all desired plugins on startup.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
74
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
75 YASQL
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
76 -----
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
77
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
78 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/.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
79
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
80 sqlpython
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
81 ---------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
82
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
83 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
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
84 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
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
85
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
86 ====================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
87 Windows Availability
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
88 ====================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
89
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
90 ========= ===
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
91 gqlplus no
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
92 senora yes
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
93 YASQL no
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
94 sqlpython yes
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
95 ========= ===
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
96
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
97 Since gqlplus and YASQL were written in C and Perl, respectively, it should be possible,
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
98 in principle, to compile and run them on Windows. No precompiled versions of them are
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
99 available, however, and no Windows installation procedures have been documented.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
100
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
101 All the tools are available for all UNIX environments.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
102
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
103 ============
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
104 Enhancements
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
105 ============
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
106
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
107 Smart prompt
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
108 ------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
109
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
110 *SQL\*Plus (10g and higer), senora, YASQL, sqlpython*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
111
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
112 `SQL>`, the standard SQL\*Plus prompt, provides no useful information. The prompt can be changed with SQL\*Plus' `SET SQLPROMPT` command.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
113
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
114 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
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
115 being used, like `me@production>`. To get this prompt in SQL\*Plus, issue `SET SQLPROMPT "_user'@'_connect_identifier> "`.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
116
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
117 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.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
118
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
119 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.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
120
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
121 Senora, YASQL, and sqlpython provide this type of smart prompt out of the box.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
122
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
123 Tab completion
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
124 --------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
125
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
126 *gqlplus, YASQL, sqlpython*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
127
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
128 Tab completion saves typing and avoids spelling errors by filling in the
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
129 remainder of a command or object name when
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
130 the first few letters are entered and the TAB key is hit. If there are multiple valid
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
131 endings to an incomplete word, each potential ending.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
132
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
133 Editor choice
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
134 -------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
135
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
136 *all*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
137
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
138 It's important to remember to set the `$EDITOR` environment variable before starting any
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
139 command-line tool under UNIX. All the tools allow textfile editing, but if $EDITOR is
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
140 not set, they will use the system default editor - generally UNIX `ed`, which can be traumatizing.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
141
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
142 sqlpython does not give up so easily if `$EDITOR` is not set, searching for more advanced
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
143 text editors and starting them preferentially.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
144
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
145 Scripting
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
146 ---------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
147
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
148 *all*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
149
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
150 All the tools support running scripts with `@path/to/script.sql`. Establishing a personal
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
151 library of script files is an important part of building your productivity. SQL\*Plus,
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
152 gqlplus, and sqlpython also support running scripts from urls, like
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
153 `@http://host.com/scriptlibrary/myscript.sql`, allowing you to keep your script library
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
154 on the web.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
155
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
156 The convenience of the script library is crucial. If your scripts are hidden away in
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
157 a directory that is difficult to remember and type, you will be tempted to rewrite
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
158 queries instead of using your established scripts. It's good practice to always start
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
159 your SQL tool in the directory that contains your script library; that way, you can
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
160 access your scripts without having to type path names. An alias or shortcut at the
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
161 operating-system level can be useful; for instance, put `alias sql='cd ~/myscriptlibrary; sqlplus'` in your `.bashrc` file.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
162
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
163 Choose your script names carefully, so that you will find them easily in the future.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
164 To view your script library from within your SQL tool, type `host dir` or `host ls`.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
165
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
166 Command history
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
167 ---------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
168
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
169 *SQL\*Plus (Windows, or with rlwrap), gqlplus, senora, YASQL, sqlpython*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
170
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
171 On Windows, the up- and down-arrow keys can be used to scroll through
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
172 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`.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
173 `rlwrap` can also provide cursor-key history to senora, and in fact to any command-line program.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
174
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
175 Gqlplus, YASQL, and sqlpython provide cursor-key command history out of the box.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
176
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
177 In addition, senora and sqlpython have a `history` or `hi` command that gives a numbered list of all commands issued in the session.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
178
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
179 Senora history commands
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
180 ~~~~~~~~~~~~~~~~~~~~~~~
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
181
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
182 ================== ===============================================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
183 hi List all commands issued in this session
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
184 hi {search string} List all commands containing {search string}
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
185 ! rerun the last command
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
186 !!{N} rerun command number N
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
187 !{search string} rerun last command containing {search string}
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
188 ================== ===============================================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
189
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
190 sqlpython history commands
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
191 ~~~~~~~~~~~~~~~~~~~~~~~~~~
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
192
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
193 ========================= ==========================================================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
194 hi List all commands issued in this session
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
195 hi {N} List command number {N}
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
196 hi -{N} List all commands up to command number {N}
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
197 hi {N}- List all commands from command number {N} onward
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
198 hi {search string} List all commands containing {search string}
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
199 hi /{regex}/ List all commands matching regular expression /{regex}/
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
200 r or \\g rerun the last command
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
201 r {N} rerun command number {N}
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
202 r {search string} rerun last command containing {search string}
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
203 r /{regex}/ rerun last command matching {regex}
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
204 ========================= ==========================================================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
205
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
206 Neatened output
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
207 ---------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
208
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
209 *senora, YASQL, sqlpython*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
210
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
211 Senora, YASL, and sqlpython economize on column space when returning query results. This
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
212 can make output much neater, more compact, and easier to read.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
213
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
214 SQL\*Plus::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
215
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
216 SQL> select * from party where name = 'Gimli';
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
217
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
218 NAME STR INT WIS DEX CON CHA
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
219 ---------------------------------------- ---------- ---------- ---------- ---------- ---------- ----------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
220 Gimli 17 12 10 11 17 11
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
221
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
222 YASQL::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
223
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
224 jrrt@orcl> select * from party where name = 'Gimli';
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
225
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
226
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
227 NAME STR INT WIS DEX CON CHA
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
228 ----- ---- ---- ---- ---- ---- ----
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
229 Gimli 17 12 10 11 17 11
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
230
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
231 1 row selected (0.03 seconds)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
232
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
233 UNIX-inspired commands
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
234 ----------------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
235
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
236 *senora, sqlpython*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
237
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
238 "Senora" is an acronym for Shell ENvironment for ORAcle, and many of its special features
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
239 are inspired directly by UNIX shell commands. `sqlpython` duplicates most of these. Also, as
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
240 in Unix, these commands can be altered with flags.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
241
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
242 ls
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
243 `ls` is used to list Oracle objects from the data dictionary. These are reported as though
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
244 they existed in an {object type}/{object name} directory structure::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
245
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
246 0:jrrt@orcl> ls
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
247 Table/Party Index/Xpk_Party
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
248
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
249 0:jrrt@orcl> ls -h
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
250 NAME
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
251 ls - list all objects matching pattern
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
252
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
253 SYNOPSIS
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
254 ls <type/name>
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
255 -a List all objects, even with a dollar
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
256 -l List validity of objects too
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
257 -C List constraints etc too
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
258 -I List indexes etc too
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
259 -i List invalid objects only
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
260
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
261 FILES
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
262 DataDictionary.pm
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
263 0:jrrt@orcl> ls -l Table/*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
264 VALID 27-FEB-09 Table/Party
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
265
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
266
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
267 cat
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
268 A shortcut for `SELECT * FROM`. In fact, you can
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
269 attach `WHERE` clauses, `ORDER BY`, or other SQL just as if you had typed `SELECT * FROM`.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
270
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
271 head [-n N]
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
272 Displays the first N rows (default 10) of a table or view.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
273
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
274 grep *target_text* *table to search* [*table 2 to search*, ...]
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
275 Searches entire tables (all columns) for the desired text::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
276
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
277 0:jrrt@orcl> grep 17 party
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
278
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
279 Name |str|int|wis|dex|con|cha|
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
280 --------------------------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
281 Gimli | 17| 12| 10| 11| 17| 11|
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
282 Legolas| 13| 15| 14| 18| 15| 17|
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
283
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
284 2 rows selected.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
285
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
286 Also like UNIX commands, the operation of many senora and sqlpython commands can be altered by
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
287 using flags, as in `grep -i {target text} {table name}` , where `-i` makes the search case-insensitive.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
288 See `{command} -h` for help on each command, including a list of flags.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
289
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
290 Data dictionary access
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
291 ----------------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
292
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
293 *senora, YASQL, sqlpython*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
294
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
295 Inspecting Oracle objects using the data dictionary requires plenty of typing and an
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
296 excellent memory. The alternative tools provide convenient shortcuts.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
297
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
298 This table shows some approximately equivalent ways to extract object
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
299 information from the data dictionary; actually, the SQL queries
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
300 given require considerable interpretation and usually additional joins to get truly
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
301 useful information, whereas the YASQL/senora/sqlpython commands provide information
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
302 ready-to-use. There are too many possibilities to list, but a sample will give the idea.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
303
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
304 +------------------------+-----------------+-----------------+----------------------+
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
305 |SQL\*Plus |YASQL |senora |sqlpython |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
306 +========================+=================+=================+======================+
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
307 |SELECT table_name |show tables |ls Table/\* |ls table |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
308 |FROM tabs; | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
309 +------------------------+-----------------+-----------------+----------------------+
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
310 |SELECT object_name, | | |ls -l \*a\* |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
311 |status, last_ddl_time | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
312 |FROM all_objects | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
313 |WHERE object_name | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
314 |LIKE 'A%'; | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
315 +------------------------+-----------------+-----------------+----------------------+
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
316 |SELECT * |show constraints |refs {table name}|refs {table name} |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
317 |FROM all_constraints |on {table name} | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
318 |WHERE table_name = | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
319 |'{table name}' | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
320 +------------------------+-----------------+-----------------+----------------------+
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
321 |SELECT * |show indexes | |\\di {table name} |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
322 |FROM user_indexes |on {table name} | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
323 |WHERE table_name = | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
324 |'{table name}' | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
325 +------------------------+-----------------+-----------------+----------------------+
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
326 |SELECT * | show triggers | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
327 |FROM all_triggers | on {table name} | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
328 |WHERE table_name = | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
329 |'{table name}' | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
330 +------------------------+-----------------+-----------------+----------------------+
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
331 |DBMS_METADATA.GET_DDL( | |pull |pull |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
332 |'{object_type}', | |{object_name} |{object_name} |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
333 |'{object_name}') | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
334 +------------------------+-----------------+-----------------+----------------------+
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
335 |SELECT * FROM | |desc -l | comments {table name}|
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
336 |all_tab_comments | |{table_name} | or \\dd {table name} |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
337 |WHERE table_name = | |(available in | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
338 |'{table name}'; | |v0.6) | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
339 |SELECT * FROM | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
340 |all_col_comments | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
341 |WHERE table_name = | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
342 |'{table name}'; | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
343 +------------------------+-----------------+-----------------+----------------------+
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
344 |SELECT * | |find {target} | find -a {target} |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
345 |FROM all_source | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
346 |WHERE text LIKE | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
347 |'%{target}%'; | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
348 +------------------------+-----------------+-----------------+----------------------+
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
349 |SELECT * | | |find -ac {column name}|
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
350 |FROM all_tab_columns | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
351 |WHERE column_name LIKE | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
352 |'%{column name}%' | | | |
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
353 +------------------------+-----------------+-----------------+----------------------+
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
354
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
355 In sqlpython, the command `ls -tl;10` will list the most recent ten objects by their
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
356 last DDL time - which can be a handy way to answer the question, "What was I working
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
357 on here?" (`ls -tl` is similarly useful in the Unix shell.)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
358
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
359 convenient viewing of explain plans
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
360 -----------------------------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
361
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
362 *senora, YASQL, sqlpython*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
363
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
364 In senora, `xplain {text of query to explain}` is the equivalent of issuing
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
365 `EXPLAIN PLAN FOR {query}` in SQL\*Plus, then querying the PLAN table with
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
366 spacing inserted for neat formatting.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
367
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
368 In YASQL, `show plan` displays the last PLAN table entry, neatly formatted.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
369 (Issuing `EXPLAIN PLAN` first is up to you.)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
370
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
371 In sqlpython, `explain {SQL ID}` shows the execution plan for the SQL statement with the
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
372 given ID. If SQL ID is omitted, it defaults to the most recent SQL executed.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
373 (This is not necessarily the last statement `EXPLAIN PLAN` was issued against.)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
374
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
375 Special I/O destinations
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
376 ------------------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
377
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
378 *YASQL, sqlpython*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
379
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
380 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::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
381
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
382 jrrt@orcl> select name from party; | sed 's/Legolas/Elfie-poo/'
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
383
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
384 4 rows selected.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
385
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
386 NAME
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
387 -------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
388 Frodo
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
389 Gimli
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
390 Elfie-poo
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
391 Sam
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
392
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
393 You can use `> {filename}` to route output into a file.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
394
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
395 In sqlpython, if the filename is omitted after a `>`, the output is simply redirected into the
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
396 paste buffer, and can then be pasted into a document, email, etc. An external program called
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
397 `xclip` is necessary to make this work under Unix, and can be installed from your package
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
398 repository.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
399
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
400 In YASQL, `<` can be used to import data directly from a CSV file into a table::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
401
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
402 jrrt@orcl> insert into party values (:1, :2, :3, :4, :5, :6, :7); < newmembers.csv
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
403
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
404 In sqlpython, `< {filename}` simply replaces `< {filename}` with the content of {filename},
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
405 then executes the resulting command.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
406
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
407 Special output formats
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
408 ----------------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
409
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
410 *SQL\*Plus, YASQL, sqlpython*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
411
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
412 SQL\*Plus can produce HTML tables instead of plain text using the `SET MARKUP ON` command.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
413 Using this, a query could be published as a webpage entirely from within a SQL\*Plus
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
414 session::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
415
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
416 > set markup html on
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
417 > spool /var/www/myreport.html
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
418 > select * from mytable;
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
419 > spool off
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
420 > set markup html off
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
421
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
422 YASQL and sqlpython have much more extensive, and convenient, output formatting options.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
423 By simply replacing the ";" terminating a query with a special backslash sequence, you can
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
424 get output in a variety of alternate formats.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
425
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
426 === ===================================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
427 \\b bar graph (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
428 \\c CSV (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
429 \\c CSV (no column names) (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
430 \\G list (aligned)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
431 \\g list (not aligned)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
432 \\h HTML table (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
433 \\i as SQL `insert` statements
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
434 \\j JSON (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
435 \\l line plot, with markers (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
436 \\l line plot, no markers (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
437 \\p pie chart (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
438 \\s CSV
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
439 \\S CSV (no column names)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
440 \\t transposed (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
441 \\x XML (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
442 === ===================================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
443
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
444 Thus, the sqlpython way to produce and web-publish a report fits on a single line::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
445
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
446 > select * from party\h > /var/www/party.html
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
447
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
448 List (`\\g`) output can be especially useful in reading tables with many columns without confusing
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
449 line wrapping. Transposed (`\\t`) output is ideal for reading many-columns/few-rows tables
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
450 like V$DATABASE. Generating plots directly from queries is just fun.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
451
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
452 Row output limits
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
453 -----------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
454
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
455 *YASQL, sqlpython*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
456
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
457 Follow the terminator in a query (usually ";") with a positive integer, and the query will only
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
458 output up to that number of rows::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
459
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
460 jrrt@orcl> select * from party;2
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
461
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
462 NAME STR INT WIS DEX CON CHA
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
463 ----- --- --- --- --- --- ---
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
464 Frodo 8 14 16 15 14 16
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
465 Gimli 17 12 10 11 17 11
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
466
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
467 2 rows selected.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
468
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
469 Multiple sessions
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
470 -----------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
471
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
472 *senora, sqlpython*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
473
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
474 When `connect -a {username@instance}` is used to create a new connection, senora does not close the
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
475 old connection. It keeps both connections alive, and switching between them is far more convenient
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
476 than creating new connections::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
477
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
478 Enter sample here
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
479
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
480 psql-like shortcuts
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
481 -------------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
482
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
483 *YASQL, sqlpython*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
484
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
485 Several backslash-character command shortcuts have been copied from psql, the command-line tool
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
486 for the PostgreSQL open-source database, into YASQL and sqlpython.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
487
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
488 ==== =====================================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
489 \\c connect (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
490 \\c clear buffer (YASQL)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
491 \\d describe (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
492 \\e edit
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
493 \\g run (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
494 \\h help (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
495 \\i load (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
496 \\l list (YASQL)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
497 \\o spool (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
498 \\p list
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
499 \\q quit
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
500 \\w save
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
501 \\db directory of tablespaces (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
502 \\dd comments (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
503 \\dn directory of schemas (sqlpython)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
504 \\dt directory of tables
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
505 \\dv directory of views
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
506 \\di directory of indexes
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
507 ==== =====================================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
508
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
509 Enhanced bind variables
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
510 -----------------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
511
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
512 *sqlpython*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
513
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
514 Senora and sqlpython support SQL\*Plus-style bind variables. sqlpython also has an optional, easygoing
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
515 syntax for defining bind variables and permits them to be dynamically defined (no declaration required)::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
516
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
517 jrrt@orcl> :name = 'Legolas'
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
518 jrrt@orcl> print
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
519 :name = Legolas
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
520
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
521 sqlpython's `bind` command creates and populates bind variables for each
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
522 column of the row most recently returned from a query. The optional `autobind`
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
523 setting automatically does this after every query that returns a single row.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
524
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
525 ::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
526
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
527 jrrt@orcl> select name, str, int from party where name = :name;
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
528
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
529 NAME STR INT
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
530 ------- --- ---
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
531 Legolas 13 15
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
532
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
533 1 row selected.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
534
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
535 jrrt@orcl> print
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
536 :1 = Legolas
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
537 :2 = 13
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
538 :3 = 15
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
539 :int = 15
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
540 :name = Legolas
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
541 :str = 13
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
542
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
543 Tuning
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
544 ------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
545
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
546 *senora, sqlpython*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
547
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
548 Each program provides an assortment of commands to provide performance-related data.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
549 There are too many commands for detailed description, but a partial list will supply
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
550 some idea for the capabilities.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
551
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
552 Senora tuning commands
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
553 ~~~~~~~~~~~~~~~~~~~~~~
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
554
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
555 ============== ====== ==========================================================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
556 command abbrev effect
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
557 ============== ====== ==========================================================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
558 analyzeObject ana analyze tables or indexes
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
559 cacheStats cst Display memory statisticts
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
560 compareStat coms compare statistics of two schemas
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
561 cstatement cs lists current SQL statements, by session
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
562 cstatement -S cs -S lists current SQL statements with their execution stats
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
563 FkeyIndexes fki analyze the existance of foreign key indexes
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
564 hwm get high watermark info on analyzed tables (experimental)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
565 jobs print job information
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
566 kept show kept (pinned) code
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
567 kill kill a session
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
568 locks show sessions and the objects the are waiting for
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
569 logs print redo log activity
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
570 profile estimate current execution times
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
571 ps print session information
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
572 resize resize datafiles
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
573 rollSegs print rollback info
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
574 show parameter sp show init.ora parameter
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
575 space show tablespace and file stats
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
576 stat print session statistics
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
577 validate vi xvalidate structure an index
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
578 waits show what sessions are waiting for
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
579 xqueries xq show most expensive quieries
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
580 ============== ====== ==========================================================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
581
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
582 Before senora tuning commands can be run, `register Tuning` must be executed in the session.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
583
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
584 sqlython tuning commands
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
585 ~~~~~~~~~~~~~~~~~~~~~~~~
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
586
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
587 ======== =====================================================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
588 load prints the OS load on all cluster nodes (10g RAC)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
589 longops prints from gv$session_longops
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
590 sessinfo prints session information. Parameter: sid
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
591 sql prints the sql text from the cache. Parameter: sql_id
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
592 top List top active sessions
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
593 top9i 9i (and single instance) version of top
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
594 ======== =====================================================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
595
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
596 Wildcards in column names
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
597 -------------------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
598
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
599 *YASQL, sqlpython*
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
600
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
601 YASQL supports `\*` wildcards in column names, if there is a ~/.yasqlrc or /etc/yasql.conf file containing
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
602 `column_wildcards=1`.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
603
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
604 sqlpython's wildcards are more powerful. When the option wildsql is set to ON,
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
605 sqlpython will accept these in the column list of a SELECT statement:
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
606
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
607 * Wildcards (`\*` or `%`, and `?`)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
608
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
609 * Column numbers (`#1, #2`)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
610
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
611 * !, meaning NOT. `!str` means "all columns except STR".
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
612
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
613 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;`.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
614
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
615 ====================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
616 Drawbacks and quirks
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
617 ====================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
618
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
619 No tool reproduces all the features of SQL\*Plus (though gqlplus comes very, very close). These are the drawbacks most likely to be noticed.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
620
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
621 Smart prompt
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
622 ------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
623
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
624 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.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
625
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
626 PL/SQL
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
627 ------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
628
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
629 Gqlplus, Senora, and sqlpython can run all PL/SQL as SQL\*Plus does.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
630
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
631 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
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
632 in `REMARK BEGIN` and `REMARK END` statements
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
633
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
634 Executing shell commands
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
635 ------------------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
636
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
637 In SQL\*Plus, `host {command}` or `!{command}` run {command} on the operating system.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
638 These work in all the alternative tools as well, except that senora recognizes only
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
639 `host` (since, in senora, `!` is used for re-running commands from the history instead.)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
640
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
641 Error messages
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
642 --------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
643
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
644 Error messages in some programs are less friendly than in SQL*\Plus. For instance, senora
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
645 responds to most errors with "what ?"::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
646
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
647 0:jrrt@orcl> grep;
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
648 what ? "grep;" ?
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
649
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
650 sqlpython passes Python or Oracle errors it encounters up to the user, but it cannot
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
651 highlight the place in a SQL command where a syntax error took place.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
652
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
653 When compiling PL/SQL objects (functions, packages, procedures) that contain errors,
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
654 the tools vary in the quality of information they return.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
655
288
e7578e7ff9dd sliced pull almost working...
catherine@dellzilla
parents: 286
diff changeset
656 * Senora reports that a compilation error has occured, and `show errors` works as expected.
286
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
657
288
e7578e7ff9dd sliced pull almost working...
catherine@dellzilla
parents: 286
diff changeset
658 * YASQL reports that a compilation error has occured. `show errors` in YASQL lists the compilation errors for *all* invalid PL/SQL objects in the schema.
286
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
659
288
e7578e7ff9dd sliced pull almost working...
catherine@dellzilla
parents: 286
diff changeset
660 * sqlpython reports errors immediately upon compilation.
286
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
661
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
662 Note that, in senora and YASQL, abbreviated forms of `show errors`, like `sho err`, are not recognized.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
663
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
664 Startup
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
665 -------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
666
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
667 Only SQL\*Plus can be used to login to a closed database. gqlplus hangs during the attempt.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
668 The other tools simply report that the database is closed and cannot connect.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
669
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
670 Text file editing
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
671 -----------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
672
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
673 In senora and YASQL, the `ed` and `\\e` commands (respectively) can be used to edit
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
674 text files using the environment's default editor. Unlike SQL\*Plus, however, they
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
675 do not edit the *most recently issued command* (the buffer), and the resulting file
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
676 is not automatically run. sqlpython's `ed`, without an argument, edits the SQL buffer
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
677 as SQL\*Plus does, and runs the result when the editor is closed. You may also
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
678 `ed {filename}` to edit a specific text file, `ed {N}` to load command number {N}
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
679 from the command history into the buffer and edit it. Finally, if your EDITOR
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
680 environment is not set, sqlpython tries to find a more user-friendly editing program
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
681 on your machine than UNIX ed.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
682
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
683 Hints
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
684 -----
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
685
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
686 sqlpython removes all comments from commands at an early stage of parsing, and thus
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
687 will not honor execution hints embedded within SQL statements.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
688
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
689 Variables
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
690 ---------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
691
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
692 YASQL does not support bind variables (`:` variables) or substitution variables (`&` variables).
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
693 The other tools support them, and sqlpython has enhanced bind variables (see above).
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
694
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
695 Parsing speed
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
696 -------------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
697
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
698 sqlpython parses each line of a SQL command as it is entered to determine whether the command
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
699 is finished yet. This produces noticable waits as a query approaches ten lines of SQL, and
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
700 downright annoying as it grows beyond that. To avoid it, you can prefix long queries with
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
701 `REMARK BEGIN` and end them with `REMARK END`, promising sqlpython that you will only enclose a
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
702 single query and freeing it from the need to parse until `REMARK END` is reached.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
703
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
704 Maturity
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
705 --------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
706
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
707 All the alternate tools are produced by individuals or very small groups, so it is not
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
708 uncommon to encounter bugs. If you encounter one, you can e-mail the author;
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
709 for sqlpython in particular, error reports filed in the project's bug tracker at
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
710 http://trac-hg.assembla.com/sqlpython are appreciated. Since the projects are
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
711 open-source, and most of them are written in the easy-to-use languages Perl and Python,
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
712 you can always fix bugs yourself and improve the products for everyone.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
713
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
714 ============
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
715 Installation
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
716 ============
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
717
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
718 First, make sure you have Oracle client software on your machine! gqlplus simply
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
719 wraps SQL\*Plus, and wenora, YASQL, and
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
720 sqlpython depend on
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
721 Perl or Python modules that access the Oracle OCI, so none of them will work in the absence of
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
722 an Oracle client.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
723
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
724 You will also need Perl (for senora or YASQL) or Python (for sqlpython). Virtually all UNIX
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
725 systems will have these languages installed, and frankly, every computer *should*. Free installers for
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
726 all common platforms are available from the languages' websites at http://www.perl.org/
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
727 and http://python.org/.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
728
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
729 gqlplus
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
730 -------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
731
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
732 Gqlplus is distributed as a gzipped tarball, downloadable from http://gqlplus.sourceforge.net/,
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
733 containing the C source code and a precompiled binary
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
734 for Linux. To use on Linux, simply expand the tarball and run Linux/gqlplus; you may want to place
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
735 the file or a link to it somewhere in your PATH. On other systems, you will a C compiler such
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
736 as `gcc`; run `./configure` and `make` as directed in the project's `README` file.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
737
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
738 Senora
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
739 ------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
740
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
741 Unix
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
742 ~~~~
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
743
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
744 Senora is distributed as a gzipped tarball, downloadable from http://senora.sourceforge.net/
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
745
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
746 Senora depends on the DBI and DBD::Oracle packages. Perl's `cpan` tool is a fairly convenient way
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
747 to install them::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
748
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
749 $ cpan
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
750
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
751 cpan> install DBI
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
752 cpan> force install DBD::Oracle
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
753
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
754 If this is your first time running `cpan`, it will ask you many configuration questions
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
755 on startup. Accepting the default answers is generally correct.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
756 Running under `sudo`, `cpan` may not find your $ORACLE_HOME; running as `root` avoids this problem.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
757
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
758 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`.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
759
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
760 Windows
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
761 ~~~~~~~
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
762
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
763 http://senora.sourceforge.net/ also has a "Senora for Windows" download. Download it, unzip, and
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
764 run `SenoraForWindows/senora.exe`. You may wish to create a shortcut for your convenience.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
765
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
766 YASQL
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
767 -----
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
768
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
769 Like senora, YASQL depends on the DBI and DBD::Oracle packages. Several other Perl packages
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
770 are recommended and can also be installed through `cpan`::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
771
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
772 cpan> install DBI
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
773 cpan> force install DBD::Oracle
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
774 cpan> install Term::ReadKey Text::CSV_XS Time::HiRes
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
775
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
776 Download yasql-1.83.tar.gz from http://sourceforge.net/projects/yasql/, unzip and untar it, and
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
777 cd into yasql-1.83. Run `./configure`, `make`, and (as root) `make install`.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
778 This will place the yasql executable into /usr/local/bin, so that you can run it from anywhere
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
779 by simply typing `yasql`.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
780
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
781 sqlpython
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
782 ---------
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
783
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
784 Some UNIX machines come shipped with Python but without python-dev, which you should install
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
785 from your distribution's package repository if you don't have it already. Downloads from http://python.org
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
786 (including the Windows installer) include python-dev out of the box.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
787
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
788 The easiest way to install sqlpython is with `easy_install`, a popular Python package installation
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
789 tool. You can get easy_install by installing python-setuptools from your Linux distribution's repository,
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
790 or by downloading directly from http://peak.telecommunity.com/DevCenter/EasyInstall.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
791
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
792 Once you have easy_install, simply type (as root, or using `sudo`)::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
793
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
794 $ easy_install -UZ sqlpython
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
795
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
796 The -UZ flags, though optional, will update your sqlpython installation, if necessary, and
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
797 unzip the code so that you can modify it.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
798
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
799 To generate graphs using sqlpython's `\\b`, `\\g`, and `\\l` terminators, you will need to install `pylab`::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
800
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
801 $ easy_install pylab
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
802
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
803 ===================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
804 Extending the tools
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
805 ===================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
806
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
807 All the alternative tools are open-source, so you have the right to view the source
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
808 code, make your own modifications, contribute your modifications back to the original
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
809 project, and even distribute your own modified version. YASQL, senora, and sqlpython
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
810 are especially easy to modify since they are written in dynamic languages, which are
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
811 easy to program in and require no compilation step.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
812
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
813 When you are ready to begin customizing or improving your favorite tool, imitation is
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
814 the key; look in the source code files of the tool for the code corresponding to a
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
815 simple command, copy and rename it, then work on small modifications until the new
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
816 functionality meets your needs.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
817
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
818 As a specific example, we will add new commands to sqlpython.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
819
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
820 `easy_install -UZ sqlpython` places sqlpython's source code files into your Python site-packages
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
821 directory, probably someplace like `/usr/lib/python2.5/site-packages/`. You can edit the files there
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
822 (you may need to change their ownership from `root` to yourself first). Any method in
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
823 `mysqlpy` whose name begins with `do_` will be registered as a sqlpython
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
824 command the next time sqlpython is started. The simplest possible new command could
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
825 be written as::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
826
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
827 def do_hello(self, arg):
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
828 print 'Hello, World!'
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
829
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
830 Now, typing `hello [arguments]` in a sqlpython session calls `do_hello`. The
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
831 optional arguments will be assigned to `arg`, but in this case they are not used.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
832 Let's see a more useful function, one that will
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
833
288
e7578e7ff9dd sliced pull almost working...
catherine@dellzilla
parents: 286
diff changeset
834 * Make use of the argument string
286
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
835
288
e7578e7ff9dd sliced pull almost working...
catherine@dellzilla
parents: 286
diff changeset
836 * Provide online documentation
286
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
837
288
e7578e7ff9dd sliced pull almost working...
catherine@dellzilla
parents: 286
diff changeset
838 * Use a flag to optionally modify the command's behavior
286
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
839
288
e7578e7ff9dd sliced pull almost working...
catherine@dellzilla
parents: 286
diff changeset
840 * Send output to file, paste buffer, or pipe when `>` or `|` is used.
286
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
841
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
842 ::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
843
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
844 @options([make_option('-u', '--uppercase', action='store_true',
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
845 help='use ALL CAPS')])
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
846 def do_greet(self, arg, opts):
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
847 'Provides a personalized greeting.'
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
848 result = 'Hello %s!\n' % arg
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
849 if opts.uppercase:
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
850 result = result.upper()
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
851 self.stdout.write(result)
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
852
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
853 Now we run sqlpython and try the new command::
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
854
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
855 jrrt@orcl> greet
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
856 Hello !
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
857 jrrt@orcl> greet Larry E.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
858 Hello Larry E.!
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
859 jrrt@orcl> help greet
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
860 Provides a personalized greeting.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
861 Usage: greet [options] arg
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
862
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
863 Options:
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
864 -h, --help show this help message and exit
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
865 -u, --uppercase use ALL CAPS
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
866
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
867 jrrt@orcl> greet -u World
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
868 HELLO WORLD!
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
869
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
870 Senora v0.6 includes a command, `lregister`, that will automatically create a new senora plugin from
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
871 SQL commands in a file. This will make an incredibly easy way to make your own senora
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
872 commands for your favorite tasks.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
873
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
874 =================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
875 Cross-RDBMS tools
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
876 =================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
877
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
878 All the tools reviewed so far have been designed purely for Oracle. If you work with
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
879 multiple RDBMS platforms, however, you may be interested in a SQL tool that is compatible
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
880 with all of them, which opens up a whole new set of possible programs.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
881
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
882 One such possibility is sqlcmd (http://www.clapper.org/software/python/sqlcmd),
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
883 an open-source Python program available without charge on all platforms. It
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
884 operates seamlessly across Oracle, MySQL, PostreSQL, SQLite, and MS SQL Server.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
885
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
886 Its functionality is rather basic compared to the Oracle-specific tools, and there
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
887 are conventions that will be unfamiliar to those immersed in an Oracle world. For
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
888 example, use of stored procedures is not supported at all, column and table names
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
889 are case-sensitive, and database connection details are specified with an unfamiliar
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
890 format (`sqlcmd -d orcl,oracle,localhost,scott,tiger` in place of `sqlplus scott/tiger@orcl`). Nonetheless, it and similar
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
891 tools are viable options when cross-RDBMS compatibility is a key requirement.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
892
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
893 =======
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
894 Summary
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
895 =======
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
896
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
897 All the tools offer capabilities that will make command-line interaction with Oracle easier and
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
898 more powerful, and you should experiment to find out which one(s) suit you best. If you want as
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
899 little change as possible, gqlplus provides a few extra features while being almost completely
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
900 transparent and SQL\*Plus-compatible. Sqlpython has the broadest set of features and is
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
901 being developed most actively. YASQL and senora may be good alternatives if you want some
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
902 of the features unique to those programs, such as senora's rich set of tuning commands,
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
903 or if you prefer Perl for writing your own improvements.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
904
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
905 There will always be a place for SQL\*Plus, but alternative tools
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
906 can replace and improve upon some of the work you have been doing both with SQL\*Plus
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
907 and with GUI tools. No matter which programs you use, you will get more powerful,
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
908 enjoyable commmand-line experiences and gain new appreciation for what open-source
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
909 development can bring to an Oracle environment.
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
910
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
911 =====
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
912 Links
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
913 =====
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
914
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
915 * http://gqlplus.sourceforge.net/
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
916
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
917 * http://senora.sourceforge.net/
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
918
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
919 * http://sourceforge.net/projects/yasql/
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
920
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
921 * http://pypi.python.org/pypi/sqlpython
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
922
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
923 * http://www.clapper.org/software/python/sqlcmd
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
924
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
925 * http://catherinedevlin.blogspot.com/
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
926
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
927 ================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
928 Acknowledgements
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
929 ================
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
930
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
931 Thanks to all the authors of the open-source tools reviewed here, but especially to Martin Drautzburg,
abb4c6524113 adding ioug paper
catherine@dellzilla
parents:
diff changeset
932 author of senora, for important corrections and additions.