Mercurial > sqlpython
annotate docs/source/comparison.rst @ 352:8e341308ea45
find working
| author | catherine@cordelia |
|---|---|
| date | Sat, 25 Apr 2009 06:54:44 -0400 |
| parents | 80a1976decf2 |
| children |
| rev | line source |
|---|---|
| 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 | |
|
342
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
306
diff
changeset
|
479 0:jrrt@orcl> connect -a scott/tiger@orcl |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
306
diff
changeset
|
480 1:scott@orcl> select * from dept; |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
306
diff
changeset
|
481 |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
306
diff
changeset
|
482 DEPTNO DNAME LOC |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
306
diff
changeset
|
483 ------ ---------- -------- |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
306
diff
changeset
|
484 10 ACCOUNTING NEW YORK |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
306
diff
changeset
|
485 20 RESEARCH DALLAS |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
306
diff
changeset
|
486 30 SALES CHICAGO |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
306
diff
changeset
|
487 40 OPERATIONS BOSTON |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
306
diff
changeset
|
488 |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
306
diff
changeset
|
489 4 rows selected. |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
306
diff
changeset
|
490 |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
306
diff
changeset
|
491 1:scott@orcl> 0:select * from party; |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
306
diff
changeset
|
492 |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
306
diff
changeset
|
493 NAME STR INT WIS DEX CON CHA |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
306
diff
changeset
|
494 ------- --- --- --- --- --- --- |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
306
diff
changeset
|
495 Frodo 8 14 16 15 14 16 |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
306
diff
changeset
|
496 Gimli 17 12 10 11 17 11 |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
306
diff
changeset
|
497 Legolas 13 15 14 18 15 17 |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
306
diff
changeset
|
498 Sam 11 9 14 11 16 13 |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
306
diff
changeset
|
499 |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
306
diff
changeset
|
500 4 rows selected. |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
306
diff
changeset
|
501 |
|
80a1976decf2
no changes i hope
Catherine Devlin <catherine.devlin@gmail.com>
parents:
306
diff
changeset
|
502 1:scott@orcl> 0: |
| 286 | 503 |
| 504 psql-like shortcuts | |
| 505 ------------------- | |
| 506 | |
| 507 *YASQL, sqlpython* | |
| 508 | |
| 509 Several backslash-character command shortcuts have been copied from psql, the command-line tool | |
| 510 for the PostgreSQL open-source database, into YASQL and sqlpython. | |
| 511 | |
| 512 ==== ===================================== | |
| 513 \\c connect (sqlpython) | |
| 514 \\c clear buffer (YASQL) | |
| 515 \\d describe (sqlpython) | |
| 516 \\e edit | |
| 517 \\g run (sqlpython) | |
| 518 \\h help (sqlpython) | |
| 519 \\i load (sqlpython) | |
| 520 \\l list (YASQL) | |
| 521 \\o spool (sqlpython) | |
| 522 \\p list | |
| 523 \\q quit | |
| 524 \\w save | |
| 525 \\db directory of tablespaces (sqlpython) | |
| 526 \\dd comments (sqlpython) | |
| 527 \\dn directory of schemas (sqlpython) | |
| 528 \\dt directory of tables | |
| 529 \\dv directory of views | |
| 530 \\di directory of indexes | |
| 531 ==== ===================================== | |
| 532 | |
| 533 Enhanced bind variables | |
| 534 ----------------------- | |
| 535 | |
| 536 *sqlpython* | |
| 537 | |
| 538 Senora and sqlpython support SQL\*Plus-style bind variables. sqlpython also has an optional, easygoing | |
| 539 syntax for defining bind variables and permits them to be dynamically defined (no declaration required):: | |
| 540 | |
| 541 jrrt@orcl> :name = 'Legolas' | |
| 542 jrrt@orcl> print | |
| 543 :name = Legolas | |
| 544 | |
| 545 sqlpython's `bind` command creates and populates bind variables for each | |
| 546 column of the row most recently returned from a query. The optional `autobind` | |
| 547 setting automatically does this after every query that returns a single row. | |
| 548 | |
| 549 :: | |
| 550 | |
| 551 jrrt@orcl> select name, str, int from party where name = :name; | |
| 552 | |
| 553 NAME STR INT | |
| 554 ------- --- --- | |
| 555 Legolas 13 15 | |
| 556 | |
| 557 1 row selected. | |
| 558 | |
| 559 jrrt@orcl> print | |
| 560 :1 = Legolas | |
| 561 :2 = 13 | |
| 562 :3 = 15 | |
| 563 :int = 15 | |
| 564 :name = Legolas | |
| 565 :str = 13 | |
| 566 | |
| 567 Tuning | |
| 568 ------ | |
| 569 | |
| 570 *senora, sqlpython* | |
| 571 | |
| 572 Each program provides an assortment of commands to provide performance-related data. | |
| 573 There are too many commands for detailed description, but a partial list will supply | |
| 574 some idea for the capabilities. | |
| 575 | |
| 576 Senora tuning commands | |
| 577 ~~~~~~~~~~~~~~~~~~~~~~ | |
| 578 | |
| 579 ============== ====== ========================================================== | |
| 580 command abbrev effect | |
| 581 ============== ====== ========================================================== | |
| 582 analyzeObject ana analyze tables or indexes | |
| 583 cacheStats cst Display memory statisticts | |
| 584 compareStat coms compare statistics of two schemas | |
| 585 cstatement cs lists current SQL statements, by session | |
| 586 cstatement -S cs -S lists current SQL statements with their execution stats | |
| 587 FkeyIndexes fki analyze the existance of foreign key indexes | |
| 588 hwm get high watermark info on analyzed tables (experimental) | |
| 589 jobs print job information | |
| 590 kept show kept (pinned) code | |
| 591 kill kill a session | |
| 592 locks show sessions and the objects the are waiting for | |
| 593 logs print redo log activity | |
| 594 profile estimate current execution times | |
| 595 ps print session information | |
| 596 resize resize datafiles | |
| 597 rollSegs print rollback info | |
| 598 show parameter sp show init.ora parameter | |
| 599 space show tablespace and file stats | |
| 600 stat print session statistics | |
| 601 validate vi xvalidate structure an index | |
| 602 waits show what sessions are waiting for | |
| 603 xqueries xq show most expensive quieries | |
| 604 ============== ====== ========================================================== | |
| 605 | |
| 606 Before senora tuning commands can be run, `register Tuning` must be executed in the session. | |
| 607 | |
| 608 sqlython tuning commands | |
| 609 ~~~~~~~~~~~~~~~~~~~~~~~~ | |
| 610 | |
| 611 ======== ===================================================== | |
| 612 load prints the OS load on all cluster nodes (10g RAC) | |
| 613 longops prints from gv$session_longops | |
| 614 sessinfo prints session information. Parameter: sid | |
| 615 sql prints the sql text from the cache. Parameter: sql_id | |
| 616 top List top active sessions | |
| 617 top9i 9i (and single instance) version of top | |
| 618 ======== ===================================================== | |
| 619 | |
| 620 Wildcards in column names | |
| 621 ------------------------- | |
| 622 | |
| 623 *YASQL, sqlpython* | |
| 624 | |
| 625 YASQL supports `\*` wildcards in column names, if there is a ~/.yasqlrc or /etc/yasql.conf file containing | |
| 626 `column_wildcards=1`. | |
| 627 | |
| 628 sqlpython's wildcards are more powerful. When the option wildsql is set to ON, | |
| 629 sqlpython will accept these in the column list of a SELECT statement: | |
| 630 | |
| 631 * Wildcards (`\*` or `%`, and `?`) | |
| 632 | |
| 633 * Column numbers (`#1, #2`) | |
| 634 | |
| 635 * !, meaning NOT. `!str` means "all columns except STR". | |
| 636 | |
| 637 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;`. | |
| 638 | |
| 639 ==================== | |
| 640 Drawbacks and quirks | |
| 641 ==================== | |
| 642 | |
| 643 No tool reproduces all the features of SQL\*Plus (though gqlplus comes very, very close). These are the drawbacks most likely to be noticed. | |
| 644 | |
| 645 Smart prompt | |
| 646 ------------ | |
| 647 | |
| 648 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. | |
| 649 | |
| 650 PL/SQL | |
| 651 ------ | |
| 652 | |
| 653 Gqlplus, Senora, and sqlpython can run all PL/SQL as SQL\*Plus does. | |
| 654 | |
| 655 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 | |
| 656 in `REMARK BEGIN` and `REMARK END` statements | |
| 657 | |
| 658 Executing shell commands | |
| 659 ------------------------ | |
| 660 | |
| 661 In SQL\*Plus, `host {command}` or `!{command}` run {command} on the operating system. | |
| 662 These work in all the alternative tools as well, except that senora recognizes only | |
| 663 `host` (since, in senora, `!` is used for re-running commands from the history instead.) | |
| 664 | |
| 665 Error messages | |
| 666 -------------- | |
| 667 | |
| 668 Error messages in some programs are less friendly than in SQL*\Plus. For instance, senora | |
| 669 responds to most errors with "what ?":: | |
| 670 | |
| 671 0:jrrt@orcl> grep; | |
| 672 what ? "grep;" ? | |
| 673 | |
| 674 sqlpython passes Python or Oracle errors it encounters up to the user, but it cannot | |
| 675 highlight the place in a SQL command where a syntax error took place. | |
| 676 | |
| 677 When compiling PL/SQL objects (functions, packages, procedures) that contain errors, | |
| 678 the tools vary in the quality of information they return. | |
| 679 | |
| 288 | 680 * Senora reports that a compilation error has occured, and `show errors` works as expected. |
| 286 | 681 |
| 288 | 682 * 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 | 683 |
| 288 | 684 * sqlpython reports errors immediately upon compilation. |
| 286 | 685 |
| 686 Note that, in senora and YASQL, abbreviated forms of `show errors`, like `sho err`, are not recognized. | |
| 687 | |
| 688 Startup | |
| 689 ------- | |
| 690 | |
| 691 Only SQL\*Plus can be used to login to a closed database. gqlplus hangs during the attempt. | |
| 692 The other tools simply report that the database is closed and cannot connect. | |
| 693 | |
| 694 Text file editing | |
| 695 ----------------- | |
| 696 | |
| 697 In senora and YASQL, the `ed` and `\\e` commands (respectively) can be used to edit | |
| 698 text files using the environment's default editor. Unlike SQL\*Plus, however, they | |
| 699 do not edit the *most recently issued command* (the buffer), and the resulting file | |
| 700 is not automatically run. sqlpython's `ed`, without an argument, edits the SQL buffer | |
| 701 as SQL\*Plus does, and runs the result when the editor is closed. You may also | |
| 702 `ed {filename}` to edit a specific text file, `ed {N}` to load command number {N} | |
| 703 from the command history into the buffer and edit it. Finally, if your EDITOR | |
| 704 environment is not set, sqlpython tries to find a more user-friendly editing program | |
| 705 on your machine than UNIX ed. | |
| 706 | |
| 707 Hints | |
| 708 ----- | |
| 709 | |
| 710 sqlpython removes all comments from commands at an early stage of parsing, and thus | |
| 711 will not honor execution hints embedded within SQL statements. | |
| 712 | |
| 713 Variables | |
| 714 --------- | |
| 715 | |
| 716 YASQL does not support bind variables (`:` variables) or substitution variables (`&` variables). | |
| 717 The other tools support them, and sqlpython has enhanced bind variables (see above). | |
| 718 | |
| 719 Parsing speed | |
| 720 ------------- | |
| 721 | |
| 722 sqlpython parses each line of a SQL command as it is entered to determine whether the command | |
| 723 is finished yet. This produces noticable waits as a query approaches ten lines of SQL, and | |
| 724 downright annoying as it grows beyond that. To avoid it, you can prefix long queries with | |
| 725 `REMARK BEGIN` and end them with `REMARK END`, promising sqlpython that you will only enclose a | |
| 726 single query and freeing it from the need to parse until `REMARK END` is reached. | |
| 727 | |
| 728 Maturity | |
| 729 -------- | |
| 730 | |
| 731 All the alternate tools are produced by individuals or very small groups, so it is not | |
| 732 uncommon to encounter bugs. If you encounter one, you can e-mail the author; | |
| 733 for sqlpython in particular, error reports filed in the project's bug tracker at | |
| 734 http://trac-hg.assembla.com/sqlpython are appreciated. Since the projects are | |
| 735 open-source, and most of them are written in the easy-to-use languages Perl and Python, | |
| 736 you can always fix bugs yourself and improve the products for everyone. | |
| 737 | |
| 738 ============ | |
| 739 Installation | |
| 740 ============ | |
| 741 | |
| 742 First, make sure you have Oracle client software on your machine! gqlplus simply | |
| 743 wraps SQL\*Plus, and wenora, YASQL, and | |
| 744 sqlpython depend on | |
| 745 Perl or Python modules that access the Oracle OCI, so none of them will work in the absence of | |
| 746 an Oracle client. | |
| 747 | |
| 748 You will also need Perl (for senora or YASQL) or Python (for sqlpython). Virtually all UNIX | |
| 749 systems will have these languages installed, and frankly, every computer *should*. Free installers for | |
| 750 all common platforms are available from the languages' websites at http://www.perl.org/ | |
| 751 and http://python.org/. | |
| 752 | |
| 753 gqlplus | |
| 754 ------- | |
| 755 | |
| 756 Gqlplus is distributed as a gzipped tarball, downloadable from http://gqlplus.sourceforge.net/, | |
| 757 containing the C source code and a precompiled binary | |
| 758 for Linux. To use on Linux, simply expand the tarball and run Linux/gqlplus; you may want to place | |
| 759 the file or a link to it somewhere in your PATH. On other systems, you will a C compiler such | |
| 760 as `gcc`; run `./configure` and `make` as directed in the project's `README` file. | |
| 761 | |
| 762 Senora | |
| 763 ------ | |
| 764 | |
| 765 Unix | |
| 766 ~~~~ | |
| 767 | |
| 768 Senora is distributed as a gzipped tarball, downloadable from http://senora.sourceforge.net/ | |
| 769 | |
| 770 Senora depends on the DBI and DBD::Oracle packages. Perl's `cpan` tool is a fairly convenient way | |
| 771 to install them:: | |
| 772 | |
| 773 $ cpan | |
| 774 | |
| 304 | 775 cpan> install DBI Term::ReadLine::Perl |
| 286 | 776 cpan> force install DBD::Oracle |
| 777 | |
| 778 If this is your first time running `cpan`, it will ask you many configuration questions | |
| 779 on startup. Accepting the default answers is generally correct. | |
| 780 Running under `sudo`, `cpan` may not find your $ORACLE_HOME; running as `root` avoids this problem. | |
| 781 | |
| 782 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`. | |
| 783 | |
| 784 Windows | |
| 785 ~~~~~~~ | |
| 786 | |
| 787 http://senora.sourceforge.net/ also has a "Senora for Windows" download. Download it, unzip, and | |
| 788 run `SenoraForWindows/senora.exe`. You may wish to create a shortcut for your convenience. | |
| 789 | |
| 790 YASQL | |
| 791 ----- | |
| 792 | |
| 793 Like senora, YASQL depends on the DBI and DBD::Oracle packages. Several other Perl packages | |
| 794 are recommended and can also be installed through `cpan`:: | |
| 795 | |
| 796 cpan> install DBI | |
| 797 cpan> force install DBD::Oracle | |
| 304 | 798 cpan> install Term::ReadKey Text::CSV_XS Time::HiRes Term::ReadLine::Perl |
| 286 | 799 |
| 800 Download yasql-1.83.tar.gz from http://sourceforge.net/projects/yasql/, unzip and untar it, and | |
| 801 cd into yasql-1.83. Run `./configure`, `make`, and (as root) `make install`. | |
| 802 This will place the yasql executable into /usr/local/bin, so that you can run it from anywhere | |
| 803 by simply typing `yasql`. | |
| 804 | |
| 805 sqlpython | |
| 806 --------- | |
| 807 | |
| 808 Some UNIX machines come shipped with Python but without python-dev, which you should install | |
| 809 from your distribution's package repository if you don't have it already. Downloads from http://python.org | |
| 810 (including the Windows installer) include python-dev out of the box. | |
| 811 | |
| 812 The easiest way to install sqlpython is with `easy_install`, a popular Python package installation | |
| 813 tool. You can get easy_install by installing python-setuptools from your Linux distribution's repository, | |
| 814 or by downloading directly from http://peak.telecommunity.com/DevCenter/EasyInstall. | |
| 815 | |
| 816 Once you have easy_install, simply type (as root, or using `sudo`):: | |
| 817 | |
| 818 $ easy_install -UZ sqlpython | |
| 819 | |
| 820 The -UZ flags, though optional, will update your sqlpython installation, if necessary, and | |
| 821 unzip the code so that you can modify it. | |
| 822 | |
| 305 | 823 To generate graphs using sqlpython's `\\b`, `\\g`, and `\\l` terminators, you will need to install `pylab` (or `matplotlib`):: |
| 286 | 824 |
| 825 $ easy_install pylab | |
| 826 | |
| 305 | 827 $ apt-get install python-matplotlib |
| 828 | |
| 304 | 829 Alternately, a Windows installer is available at http://pypi.python.org/pypi/sqlpython, though easy_install |
| 830 works on Windows as well. | |
| 831 | |
| 286 | 832 =================== |
| 833 Extending the tools | |
| 834 =================== | |
| 835 | |
| 836 All the alternative tools are open-source, so you have the right to view the source | |
| 837 code, make your own modifications, contribute your modifications back to the original | |
| 838 project, and even distribute your own modified version. YASQL, senora, and sqlpython | |
| 839 are especially easy to modify since they are written in dynamic languages, which are | |
| 840 easy to program in and require no compilation step. | |
| 841 | |
| 842 When you are ready to begin customizing or improving your favorite tool, imitation is | |
| 843 the key; look in the source code files of the tool for the code corresponding to a | |
| 844 simple command, copy and rename it, then work on small modifications until the new | |
| 845 functionality meets your needs. | |
| 846 | |
| 847 As a specific example, we will add new commands to sqlpython. | |
| 848 | |
| 849 `easy_install -UZ sqlpython` places sqlpython's source code files into your Python site-packages | |
| 850 directory, probably someplace like `/usr/lib/python2.5/site-packages/`. You can edit the files there | |
| 851 (you may need to change their ownership from `root` to yourself first). Any method in | |
| 852 `mysqlpy` whose name begins with `do_` will be registered as a sqlpython | |
| 853 command the next time sqlpython is started. The simplest possible new command could | |
| 854 be written as:: | |
| 855 | |
| 856 def do_hello(self, arg): | |
| 857 print 'Hello, World!' | |
| 858 | |
| 859 Now, typing `hello [arguments]` in a sqlpython session calls `do_hello`. The | |
| 860 optional arguments will be assigned to `arg`, but in this case they are not used. | |
| 861 Let's see a more useful function, one that will | |
| 862 | |
| 288 | 863 * Make use of the argument string |
| 286 | 864 |
| 288 | 865 * Provide online documentation |
| 286 | 866 |
| 288 | 867 * Use a flag to optionally modify the command's behavior |
| 286 | 868 |
| 288 | 869 * Send output to file, paste buffer, or pipe when `>` or `|` is used. |
| 286 | 870 |
| 871 :: | |
| 872 | |
| 873 @options([make_option('-u', '--uppercase', action='store_true', | |
| 874 help='use ALL CAPS')]) | |
| 875 def do_greet(self, arg, opts): | |
| 876 'Provides a personalized greeting.' | |
| 877 result = 'Hello %s!\n' % arg | |
| 878 if opts.uppercase: | |
| 879 result = result.upper() | |
| 880 self.stdout.write(result) | |
| 881 | |
| 882 Now we run sqlpython and try the new command:: | |
| 883 | |
| 884 jrrt@orcl> greet | |
| 885 Hello ! | |
| 886 jrrt@orcl> greet Larry E. | |
| 887 Hello Larry E.! | |
| 888 jrrt@orcl> help greet | |
| 889 Provides a personalized greeting. | |
| 890 Usage: greet [options] arg | |
| 891 | |
| 892 Options: | |
| 893 -h, --help show this help message and exit | |
| 894 -u, --uppercase use ALL CAPS | |
| 895 | |
| 896 jrrt@orcl> greet -u World | |
| 897 HELLO WORLD! | |
| 898 | |
| 899 Senora v0.6 includes a command, `lregister`, that will automatically create a new senora plugin from | |
| 900 SQL commands in a file. This will make an incredibly easy way to make your own senora | |
| 901 commands for your favorite tasks. | |
| 902 | |
| 903 ================= | |
| 904 Cross-RDBMS tools | |
| 905 ================= | |
| 906 | |
| 907 All the tools reviewed so far have been designed purely for Oracle. If you work with | |
| 908 multiple RDBMS platforms, however, you may be interested in a SQL tool that is compatible | |
| 909 with all of them, which opens up a whole new set of possible programs. | |
| 910 | |
| 911 One such possibility is sqlcmd (http://www.clapper.org/software/python/sqlcmd), | |
| 912 an open-source Python program available without charge on all platforms. It | |
| 913 operates seamlessly across Oracle, MySQL, PostreSQL, SQLite, and MS SQL Server. | |
| 914 | |
| 915 Its functionality is rather basic compared to the Oracle-specific tools, and there | |
| 916 are conventions that will be unfamiliar to those immersed in an Oracle world. For | |
| 917 example, use of stored procedures is not supported at all, column and table names | |
| 918 are case-sensitive, and database connection details are specified with an unfamiliar | |
| 919 format (`sqlcmd -d orcl,oracle,localhost,scott,tiger` in place of `sqlplus scott/tiger@orcl`). Nonetheless, it and similar | |
| 920 tools are viable options when cross-RDBMS compatibility is a key requirement. | |
| 921 | |
| 922 ======= | |
| 923 Summary | |
| 924 ======= | |
| 925 | |
| 926 All the tools offer capabilities that will make command-line interaction with Oracle easier and | |
| 927 more powerful, and you should experiment to find out which one(s) suit you best. If you want as | |
| 928 little change as possible, gqlplus provides a few extra features while being almost completely | |
| 929 transparent and SQL\*Plus-compatible. Sqlpython has the broadest set of features and is | |
| 930 being developed most actively. YASQL and senora may be good alternatives if you want some | |
| 931 of the features unique to those programs, such as senora's rich set of tuning commands, | |
| 932 or if you prefer Perl for writing your own improvements. | |
| 933 | |
| 934 There will always be a place for SQL\*Plus, but alternative tools | |
| 935 can replace and improve upon some of the work you have been doing both with SQL\*Plus | |
| 936 and with GUI tools. No matter which programs you use, you will get more powerful, | |
| 937 enjoyable commmand-line experiences and gain new appreciation for what open-source | |
| 938 development can bring to an Oracle environment. | |
| 939 | |
| 940 ===== | |
| 941 Links | |
| 942 ===== | |
| 943 | |
| 944 * http://gqlplus.sourceforge.net/ | |
| 945 | |
| 946 * http://senora.sourceforge.net/ | |
| 947 | |
| 948 * http://sourceforge.net/projects/yasql/ | |
| 949 | |
| 950 * http://pypi.python.org/pypi/sqlpython | |
| 951 | |
| 952 * http://www.clapper.org/software/python/sqlcmd | |
| 953 | |
| 954 * http://catherinedevlin.blogspot.com/ | |
| 955 | |
| 956 ================ | |
| 957 Acknowledgements | |
| 958 ================ | |
| 959 | |
| 960 Thanks to all the authors of the open-source tools reviewed here, but especially to Martin Drautzburg, | |
| 961 author of senora, for important corrections and additions. |