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