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