|
281
|
1 ==============================
|
|
247
|
2 SQLPython's extra capabilities
|
|
|
3 ==============================
|
|
|
4
|
|
|
5 For the most part, SQLPython simply duplicates SQL\*Plus's capabilites.
|
|
|
6
|
|
279
|
7 Neatened output
|
|
|
8 ===============
|
|
|
9
|
|
|
10 When printing query results, sqlpython economizes on screen space by allocating
|
|
|
11 only the width each column actually needs.
|
|
|
12
|
|
|
13 Smart prompt
|
|
|
14 ============
|
|
|
15
|
|
|
16 sqlpython automatically uses `username`@`instance`> as its prompt, helping
|
|
|
17 avoid wrong-instance and wrong-user errors.
|
|
|
18
|
|
|
19 Tab completion
|
|
|
20 ==============
|
|
|
21
|
|
|
22 When typing SQL commands, hitting `<TAB>` after entering part of an object
|
|
|
23 or column name brings up a list of appropriate possibilities or, if there
|
|
|
24 is only one possibility, fills in the rest of the name. This feature is
|
|
|
25 not yet very reliable, but can save typing.
|
|
|
26
|
|
|
27 Scripting
|
|
|
28 =========
|
|
|
29
|
|
|
30 Like SQL\*Plus, sqlpython can run scripts (text files with series of SQL and
|
|
|
31 sqlpython commands) with `@/path/to/script.sql` or (for online scripts)
|
|
|
32 `@http://scripthost/scriptlibrary/script.sql`.
|
|
|
33
|
|
|
34 History
|
|
|
35 =======
|
|
|
36
|
|
|
37 The up- and down-arrow keys allow you to scroll through the lines entered so far
|
|
|
38 in your sqlpython session.
|
|
|
39
|
|
|
40 Commands are also entered into a command history.
|
|
|
41
|
|
286
|
42 history *or* hi
|
|
|
43 List entire command history
|
|
279
|
44
|
|
286
|
45 list *or* li
|
|
|
46 List only last command
|
|
279
|
47
|
|
286
|
48 hi `<N>`
|
|
|
49 List command number <N> from history.
|
|
279
|
50
|
|
286
|
51 hi `<N>-`, hi `-<N>`
|
|
|
52 List commands from <N> onward, or up to <N>
|
|
279
|
53
|
|
286
|
54 hi `<str>`
|
|
|
55 Lists commands that include the string <str>
|
|
279
|
56
|
|
286
|
57 hi `/<regex>/`
|
|
|
58 Lists commands that match the regular expression <regex>
|
|
279
|
59
|
|
286
|
60 run, r, *or* `\\g`
|
|
|
61 Run the most recent command again
|
|
279
|
62
|
|
286
|
63 run `<N>`
|
|
|
64 Run command <N>
|
|
279
|
65
|
|
286
|
66 run `<str>`, run `/<regex>/`
|
|
|
67 Run command matching <str> or <regex> (as for `history`) -
|
|
|
68 if multiple items would match, run most recent
|
|
279
|
69
|
|
281
|
70 Special I/O destinations
|
|
|
71 ========================
|
|
|
72
|
|
|
73 Much as in a UNIX shell, you can follow a command with a special output destination.
|
|
|
74
|
|
|
75 `> {filename}` sends the output to a file. This is more convenient than SQL\*Plus's
|
|
|
76 SPOOL {filename}... SPOOL OFF (though you can use those as well).
|
|
|
77
|
|
|
78 `>` alone (no filename) sends the output to the paste buffer.
|
|
|
79
|
|
|
80 `|` pipes the output to an operating-system command.
|
|
|
81
|
|
|
82 When `< {filename}` is included in your command, it is replaced with the contents of
|
|
|
83 {filename} before the command is run.
|
|
|
84
|
|
|
85 Examples::
|
|
|
86
|
|
283
|
87 Need examples!!!!
|
|
|
88
|
|
281
|
89 Special output formats
|
|
|
90 ======================
|
|
|
91
|
|
|
92 By replacing the `;` that terminates a SELECT statement with a backslash-character
|
|
|
93 sequence, you can get output in a number of useful formats. The `terminators`
|
|
283
|
94 command lists them, for your convenience.
|
|
281
|
95
|
|
283
|
96 ========== ======================== ================================
|
|
|
97 terminator format Useful for
|
|
|
98 ========== ======================== ================================
|
|
|
99 ; standard Oracle format
|
|
|
100 \\c CSV (with headings) sending to spreadsheets
|
|
|
101 \\C CSV (no headings)
|
|
|
102 \\g list wide output with linewraps
|
|
|
103 \\G aligned list
|
|
|
104 \\h HTML table web reports
|
|
|
105 \\i INSERT statements copying to other instances
|
|
|
106 \\j JSON
|
|
|
107 \\s CSV (with headings)
|
|
|
108 \\S CSV (no headings)
|
|
|
109 \\t transposed "narrow" tables like v$database
|
|
|
110 \\x XML
|
|
|
111 \\l line plot, with markers
|
|
|
112 \\L scatter plot (no lines)
|
|
|
113 \\b bar graph
|
|
|
114 \\p pie chart
|
|
|
115 ========== ======================== ================================
|
|
281
|
116
|
|
|
117 Most of these output formats are even more useful when combined with special output
|
|
|
118 destinations. For example, `SELECT * FROM party\h > /var/www/party_report.html`
|
|
|
119 could create an HTML report in the webserver's documents directory, ready to serve.
|
|
|
120
|
|
247
|
121 UNIX-like commands
|
|
|
122 ==================
|
|
|
123
|
|
279
|
124 Many sqlpython commands allow you to act as though the database objects
|
|
|
125 were files in a UNIX filesystem. Many of the commands also accept flags
|
|
|
126 to modify their behavior.
|
|
|
127
|
|
286
|
128 ls `{object type/object name, with wildcards}`
|
|
281
|
129 Lists objects from the data dictionaries, as though they were in a
|
|
|
130 *object_type*/*object_name* directory structure. Thus, `ls view/\*`
|
|
|
131 lists all the user's views. Calling with no argument is equivalent
|
|
|
132 to `ls *`.
|
|
|
133
|
|
|
134 Options::
|
|
|
135
|
|
|
136 -l, --long long descriptions
|
|
|
137 -a, --all all schemas' objects (otherwise, you only get your own)
|
|
|
138 -t, --timesort Sort by last_ddl_time
|
|
|
139 -r, --reverse Reverse order while sorting
|
|
247
|
140
|
|
281
|
141 `ls -lt *;10` lists the ten items with the most recent last_ddl_time;
|
|
|
142 this can be a good way to answer the question, "What was I working on?"
|
|
|
143
|
|
286
|
144 cat `{remainder of query}`
|
|
|
145 Shorthand for "SELECT * FROM". Can be combined with anything else
|
|
|
146 that fits into a SELECT statement (WHERE, ORDER BY, etc.)
|
|
281
|
147
|
|
286
|
148 grep `{target}` `{table}` `[{table2,...}]`
|
|
|
149 Equivalent to SELECT * FROM {table} WHERE *any column* LIKE '%{target}%'.
|
|
|
150 Useful when you don't know, don't remember, or don't care which column
|
|
|
151 a value may be found in.
|
|
281
|
152
|
|
|
153 Options::
|
|
|
154
|
|
|
155 -i, --ignore-case Case-insensitive search
|
|
247
|
156
|
|
281
|
157 find -c {target}, find -t {column}
|
|
|
158 Lists all tables or columns whose names contain {target}. More convenient than
|
|
|
159 querying user_tab_columns/all_tab_columns or user_tables/all_tables.
|
|
|
160 Options::
|
|
283
|
161
|
|
281
|
162 -a Find all objects (not just my own)
|
|
|
163
|
|
286
|
164 Data dictionary exploration
|
|
|
165 ===========================
|
|
|
166
|
|
|
167 refs `{table_name}`
|
|
|
168 Lists all foreign key constraints on the table or referring to the table.
|
|
|
169
|
|
|
170 deps `{object_name}`
|
|
|
171 Lists all objects dependent upon the named object.
|
|
|
172
|
|
|
173 comments `{table_name}`
|
|
|
174 Prints comments on a table and its columns.
|
|
|
175
|
|
|
176 PL/SQL source code
|
|
|
177 ==================
|
|
|
178
|
|
|
179 pull {object_name}
|
|
|
180 Displays the PL/SQL source code for {object_name}.
|
|
|
181
|
|
|
182 Options:
|
|
|
183 -d, --dump dump results to files (object_type/object_name.sql)
|
|
|
184 -f, --full get dependent objects as well
|
|
|
185 -a, --all all schemas' objects
|
|
|
186
|
|
|
187 bzr, git, hg `{object_name}`
|
|
|
188 Dump source code to files, as `pull -f`, but also creates or commits to a
|
|
|
189 repository of the appropriate distributed version control system
|
|
|
190 (Bazaar, Git, or Mercurial, respectively).
|
|
|
191
|
|
|
192 find `{target}`
|
|
|
193 Lists all PL/SQL objects whose source code contains the {target} string.
|
|
|
194 Always case-insensitive.
|
|
|
195 Options::
|
|
|
196
|
|
|
197 -a Search all PL/SQL objects (not just my own)
|
|
|
198
|
|
247
|
199 PostgreSQL-like shortcuts
|
|
|
200 =========================
|
|
|
201
|
|
283
|
202 psql, the command-line client for the open-source database `PostgreSQL <http://www.postgresql.org/>`_ uses a number
|
|
|
203 of backslash-character sequences as convenient shortcuts. sqlpython steals many of
|
|
|
204 them.
|
|
|
205
|
|
|
206 ===== ===================
|
|
247
|
207 \\c connect
|
|
|
208 \\d desc
|
|
|
209 \\e edit
|
|
|
210 \\g run
|
|
|
211 \\h help
|
|
|
212 \\i load
|
|
|
213 \\o spool
|
|
|
214 \\p list
|
|
|
215 \\q quit
|
|
|
216 \\w save
|
|
|
217 \\db _dir_tablespaces
|
|
|
218 \\dd comments
|
|
|
219 \\dn _dir_schemas
|
|
|
220 \\dt _dir_tables
|
|
|
221 \\dv _dir_views
|
|
|
222 \\di _dir_indexes
|
|
|
223 \\? help psql
|
|
283
|
224 ===== ===================
|
|
281
|
225
|
|
|
226 Bind variables
|
|
|
227 ==============
|
|
|
228
|
|
|
229 Bind variables work in sqlpython as they do in SQL\*Plus, but they are set dynamically; there
|
|
|
230 is no need to declare them before use. The syntax for setting them is more permissive than
|
|
|
231 in SQL\*Plus; all these are recognized::
|
|
|
232
|
|
|
233 exec :mybind := 'value'
|
|
|
234 exec :mybind = 'value'
|
|
|
235 :mybind := 'value'
|
|
|
236 :mybind = 'value'
|
|
|
237
|
|
|
238 The current values of all bind variables can be viewed with the `print` command.
|
|
|
239
|
|
|
240 The `bind` command creates and populates bind variables for the final row of the most recent
|
|
|
241 SELECT statement executed; each column name is used as a bind variable, which is filled with
|
|
|
242 the value. `bind -r {rownumber}` does the same, but fills from row {rownumber} instead of
|
|
|
243 from the final row (row numbers begin at 0 for this command).
|
|
|
244
|
|
|
245 When the `autobind` sqlpython parameter is True, a `bind` statement is issued automatically
|
|
|
246 after every query that returns exactly one row.
|
|
|
247
|
|
|
248 Bind variables are available from within Python as a dictionary named `binds` (see Python).
|
|
|
249
|
|
|
250 Substitution variables
|
|
|
251 ======================
|
|
|
252
|
|
|
253 Substitution variables ("&" variables) work much as they do in SQL\*Plus. As in SQL\*Plus,
|
|
|
254 the `scan` parameter determines whether queries are scanned to replace substitution
|
|
|
255 variables. Unlike SQL\*Plus, sqlpython knows how annoying it is to hit a substitution
|
|
|
256 variable you didn't expect, so entering "SET SCAN OFF" when prompted for a substitution
|
|
|
257 variable actually aborts the substitution process.
|
|
|
258
|
|
247
|
259 Wild SQL
|
|
|
260 ========
|
|
|
261
|
|
|
262 Wild SQL is a nonstandard SQL feature that must be enabled with `set wildsql on`. When it is
|
|
282
|
263 enabled, column names in a SELECT statement do not need to be explicitly typed; they can be
|
|
|
264 specified with special Wild SQL symbols: wildcards (`*`, `%`, `_`); column numbers (`#{N}`);
|
|
|
265 and NOT-style exclusion (`!`). The symbols can even be combined.
|
|
247
|
266
|
|
282
|
267 ::
|
|
247
|
268
|
|
282
|
269 jrrt@orcl> cat party
|
|
|
270
|
|
|
271 NAME STR INT WIS DEX CON CHA
|
|
|
272 ------- --- --- --- --- --- ---
|
|
|
273 Frodo 8 14 16 15 14 16
|
|
|
274 Gimli 17 12 10 11 17 11
|
|
|
275 Legolas 13 15 14 18 15 17
|
|
|
276 Sam 11 9 14 11 16 13
|
|
|
277
|
|
|
278 4 rows selected.
|
|
|
279
|
|
|
280 jrrt@orcl> set wild on
|
|
|
281 wildsql - was: False
|
|
|
282 now: True
|
|
|
283 jrrt@orcl> select *i* from party;
|
|
|
284
|
|
|
285 INT WIS
|
|
|
286 --- ---
|
|
|
287 14 16
|
|
|
288 12 10
|
|
|
289 15 14
|
|
|
290 9 14
|
|
|
291
|
|
|
292 4 rows selected.
|
|
|
293
|
|
|
294 jrrt@orcl> select #1, #5 from party;
|
|
|
295
|
|
|
296 NAME DEX
|
|
|
297 ------- ---
|
|
|
298 Frodo 15
|
|
|
299 Gimli 11
|
|
|
300 Legolas 18
|
|
|
301 Sam 11
|
|
|
302
|
|
|
303 4 rows selected.
|
|
|
304
|
|
|
305 jrrt@orcl> select !str from party;
|
|
|
306
|
|
|
307 NAME INT WIS DEX CON CHA
|
|
|
308 ------- --- --- --- --- ---
|
|
|
309 Frodo 14 16 15 14 16
|
|
|
310 Gimli 12 10 11 17 11
|
|
|
311 Legolas 15 14 18 15 17
|
|
|
312 Sam 9 14 11 16 13
|
|
|
313
|
|
|
314 4 rows selected.
|
|
|
315
|
|
|
316 jrrt@orcl> select n*, !#3, !c* from party;
|
|
|
317
|
|
|
318 NAME STR WIS DEX
|
|
|
319 ------- --- --- ---
|
|
|
320 Frodo 8 16 15
|
|
|
321 Gimli 17 10 11
|
|
|
322 Legolas 13 14 18
|
|
|
323 Sam 11 14 11
|
|
|
324
|
|
|
325 4 rows selected.
|
|
247
|
326
|
|
282
|
327 Wild SQL symbols only work in the first SELECT statement in a query; they do not work in
|
|
|
328 subqueries, subsequent UNIONed queries, etc.
|
|
|
329
|
|
|
330 Python
|
|
|
331 ======
|
|
|
332
|
|
|
333 The `py` command allows the user to execute Python commands, either one-at-a-time (with
|
|
|
334 `py {command}`) or in an interactive environment (beginning with a bare `py` statement,
|
|
294
|
335 and continuing until Ctrl-D, `quit()`, or `exit()` is entered).
|
|
282
|
336
|
|
|
337 A history of result sets from each query is exposed to the python session as the list `r`;
|
|
294
|
338 the most recent result set is `r[-1]`. Each row can be references as a tuple, or as an
|
|
|
339 object with an attribute for each column.
|
|
|
340
|
|
|
341 Bind variables are exposed as the dictionary `binds`. Each row from each result set has
|
|
|
342 a .bind() method that fills a bind varible for each column with that row's value.
|
|
282
|
343
|
|
|
344 Resultsets in `r` are read-only, but `binds` can be written as well as read, and will
|
|
|
345 be working bind variables in the SQL environment.
|
|
|
346
|
|
294
|
347 SQL and sqlpython commands can be issued from the Python environment with `sql("{your SQL}")`.
|
|
|
348
|
|
|
349 All variables are retained each time the python environment is entered (whether interactively,
|
|
|
350 or with one-line `py` statements).
|
|
282
|
351 ::
|
|
247
|
352
|
|
294
|
353 0:testschema@orcl> select title, author from play;
|
|
282
|
354
|
|
|
355 TITLE AUTHOR
|
|
|
356 --------------- -----------
|
|
|
357 Timon of Athens Shakespeare
|
|
|
358 Twelfth Night Shakespeare
|
|
|
359 The Tempest Shakespeare
|
|
|
360 Agamemnon Aeschylus
|
|
|
361
|
|
|
362 4 rows selected.
|
|
|
363
|
|
294
|
364 0:testschema@orcl> py import urllib
|
|
|
365 0:testschema@orcl> py current_season = urllib.urlopen('http://cincyshakes.com/').read()
|
|
|
366 0:testschema@orcl> py
|
|
|
367 Python 2.5.2 (r252:60911, Jul 31 2008, 17:28:52)
|
|
|
368 [GCC 4.2.3 (Ubuntu 4.2.3-2ubuntu7)] on linux2
|
|
|
369 Type "help", "copyright", "credits" or "license" for more information.
|
|
|
370 (mysqlpy)
|
|
|
371
|
|
|
372 py <command>: Executes a Python command.
|
|
|
373 py: Enters interactive Python mode; end with `Ctrl-D`, `quit()`, or 'exit`.
|
|
|
374 Past SELECT results are exposed as list `r`;
|
|
|
375 most recent resultset is `r[-1]`.
|
|
|
376 SQL bind, substitution variables are exposed as `binds`, `substs`.
|
|
|
377 SQL and sqlpython commands can be issued with sql("your non-python command here").
|
|
|
378
|
|
282
|
379 >>> r[-1]
|
|
|
380 [('Timon of Athens', 'Shakespeare'), ('Twelfth Night', 'Shakespeare'), ('The Tempest', 'Shakespeare'), ('Agamemnon', 'Aeschylus')]
|
|
294
|
381 >>> r[-1][0][0]
|
|
|
382 'Timon of Athens'
|
|
282
|
383 >>> for row in r[-1]:
|
|
294
|
384 ... print "%s, by %s" % (row.title, row.author)
|
|
|
385 ...
|
|
|
386 Timon of Athens, by Shakespeare
|
|
|
387 Twelfth Night, by Shakespeare
|
|
|
388 The Tempest, by Shakespeare
|
|
|
389 Agamemnon, by Aeschylus
|
|
282
|
390 >>> [row.title for row in r[-1] if row.title in current_season]
|
|
|
391 ['Timon of Athens', 'Twelfth Night']
|
|
294
|
392 >>> binds['author'] = 'Shakespeare'
|
|
|
393 >>> query = "SELECT title FROM play WHERE author = :author"
|
|
|
394 >>> sql(query)
|
|
|
395
|
|
|
396 TITLE
|
|
|
397 ---------------
|
|
|
398 Timon of Athens
|
|
|
399 Twelfth Night
|
|
|
400 The Tempest
|
|
|
401
|
|
|
402 3 rows selected.
|
|
|
403
|
|
|
404 >>> r[-1]
|
|
|
405 [('Timon of Athens',), ('Twelfth Night',), ('The Tempest',)]
|
|
|
406 >>> r[-1][0]
|
|
|
407 ('Timon of Athens',)
|
|
|
408 >>> r[-1][0].bind()
|
|
|
409 >>> binds['title']
|
|
|
410 'Timon of Athens'
|
|
|
411 >>> quit()
|
|
|
412 0:testschema@orcl> select title, author from play where title = :title;
|
|
282
|
413
|
|
|
414 TITLE AUTHOR
|
|
|
415 --------------- -----------
|
|
|
416 Timon of Athens Shakespeare
|
|
|
417
|
|
|
418 1 row selected.
|
|
294
|
419
|
|
281
|
420 Parameters
|
|
|
421 ==========
|
|
|
422
|
|
|
423 Several parameters control the behavior of sqlpython itself.
|
|
|
424
|
|
283
|
425 ===================== =================================================== ===============
|
|
|
426 parameter effect default
|
|
|
427 ===================== =================================================== ===============
|
|
|
428 autobind When True, single-row queries automatically `bind` False
|
|
|
429 commit_on_exit Automatically commits work at end of session True
|
|
|
430 continuation_prompt Prompt for second line and onward of long statement >
|
|
|
431 default_file_name The file opened by `edit`, if not specified afiedt.buf
|
|
|
432 echo Echo command entered before executing False
|
|
|
433 editor Text editor invoked by `edit`. varies
|
|
294
|
434 heading Print column names along with results True
|
|
283
|
435 maxfetch Maximum number of rows to return from any query 1000
|
|
|
436 maxtselctrows Maximum # of rows from a tselect or \\n query 10
|
|
|
437 prompt Probably unwise to change user@instance>
|
|
|
438 scan Interpret & as indicating substitution variables True
|
|
|
439 serveroutput Print DBMS_OUTPUT.PUT_LINE results True
|
|
|
440 sql_echo Print text of "behind-the-scenes" queries False
|
|
|
441 timeout In seconds 30
|
|
|
442 timing Print time for each command to execute False
|
|
|
443 wildsql Accept *, %, #, and ! in column names False
|
|
|
444 ===================== =================================================== ===============
|
|
281
|
445
|
|
|
446 The user can change these with the `set {paramname} {new-value}` statement.
|
|
283
|
447 The True/False parameters accept new values permissively, recognizing "True", "False",
|
|
|
448 "T", "F", "yes", "no", "on", "off", etc.
|
|
281
|
449
|
|
283
|
450 `set` and `show` both list the current values of the sqlpython parameters. They
|
|
|
451 also recognize any abbreviated parameter name, so long as it is long enough to be
|
|
|
452 unique. That is, `show maxf` is recognized as `show maxfetch`, but `show max` is
|
|
|
453 too short to distinguish between `maxfetch` and `maxtselctrows`.
|
|
|
454
|
|
281
|
455 `show parameter {param}` shows current Oracle parameters (from v$parameter), as it does
|
|
|
456 in SQL\*Plus.
|
|
|
457
|
|
|
458 Tuning
|
|
|
459 ======
|
|
|
460
|
|
|
461 In sqlpython, `explain {SQL ID}` shows the execution plan for the SQL statement with the
|
|
|
462 given ID. If SQL ID is omitted, it defaults to the most recent SQL executed.
|
|
|
463 (This is not necessarily the last statement `EXPLAIN PLAN` was issued against.)
|
|
|
464
|
|
|
465 Other specialized sqlpython tuning commands include:
|
|
|
466
|
|
|
467 load
|
|
|
468 Displays OS load on cluster nodes (10gRAC)
|
|
|
469
|
|
|
470 longops
|
|
|
471 Displays long-running operations
|
|
|
472
|
|
|
473 sessinfo
|
|
|
474 Reports session info for the given sid, extended to RAC with gv$
|
|
|
475
|
|
|
476 top, top9i
|
|
|
477 Displays active sessions
|
|
|
478
|
|
|
479
|
|
|
480
|
|
|
481
|
|
|
482
|
