|
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
|
|
|
42 `history` or `hi`
|
|
|
43 List entire command history
|
|
|
44
|
|
|
45 `list` or `li`
|
|
|
46 List only last command
|
|
|
47
|
|
|
48 `hi <N>`
|
|
|
49 List command number <N> from history.
|
|
|
50
|
|
|
51 `hi <N>-`, `hi -<N>`
|
|
|
52 List commands from <N> onward, or up to <N>
|
|
|
53
|
|
|
54 `hi <str>`
|
|
|
55 Lists commands that include the string <str>
|
|
|
56
|
|
|
57 `hi /<regex>/`
|
|
|
58 Lists commands that match the regular expression <regex>
|
|
|
59
|
|
|
60 `run`, `r`, or `\\g`
|
|
|
61 Run the most recent command again
|
|
|
62
|
|
|
63 `run <N>`
|
|
|
64 Run command <N>
|
|
|
65
|
|
|
66 `run <str>`, `run /<regex>/`
|
|
|
67 Run command matching <str> or <regex> (as for `history`) -
|
|
|
68 if multiple items would match, run most recent
|
|
|
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
|
|
281
|
128 ls {object type/object name, with wildcards}
|
|
|
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
|
|
|
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.)
|
|
|
147
|
|
|
148 grep {grep {target} {table} [{table2,...}]
|
|
283
|
149 Equivalent to SELECT * FROM {table} WHERE *any column* LIKE '%{target}%'.
|
|
281
|
150 Useful when you don't know, don't remember, or don't care which column
|
|
|
151 a value may be found in.
|
|
|
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
|
|
247
|
164 PostgreSQL-like shortcuts
|
|
|
165 =========================
|
|
|
166
|
|
283
|
167 psql, the command-line client for the open-source database `PostgreSQL <http://www.postgresql.org/>`_ uses a number
|
|
|
168 of backslash-character sequences as convenient shortcuts. sqlpython steals many of
|
|
|
169 them.
|
|
|
170
|
|
|
171 ===== ===================
|
|
247
|
172 \\c connect
|
|
|
173 \\d desc
|
|
|
174 \\e edit
|
|
|
175 \\g run
|
|
|
176 \\h help
|
|
|
177 \\i load
|
|
|
178 \\o spool
|
|
|
179 \\p list
|
|
|
180 \\q quit
|
|
|
181 \\w save
|
|
|
182 \\db _dir_tablespaces
|
|
|
183 \\dd comments
|
|
|
184 \\dn _dir_schemas
|
|
|
185 \\dt _dir_tables
|
|
|
186 \\dv _dir_views
|
|
|
187 \\di _dir_indexes
|
|
|
188 \\? help psql
|
|
283
|
189 ===== ===================
|
|
247
|
190
|
|
281
|
191 PL/SQL source code
|
|
|
192 ==================
|
|
|
193
|
|
|
194 pull {object_name}
|
|
|
195 Displays the PL/SQL source code for {object_name}.
|
|
|
196
|
|
|
197 Options:
|
|
|
198 -d, --dump dump results to files (object_type/object_name.sql)
|
|
|
199 -f, --full get dependent objects as well
|
|
|
200 -a, --all all schemas' objects
|
|
|
201
|
|
|
202 bzr, git, hg {object_name}
|
|
|
203 Dump source code to files, as `pull -f`, but also creates or commits to a
|
|
|
204 repository of the appropriate distributed version control system
|
|
|
205 (Bazaar, Git, or Mercurial, respectively).
|
|
|
206
|
|
|
207 find {target}
|
|
|
208 Lists all PL/SQL objects whose source code contains the {target} string.
|
|
|
209 Always case-insensitive.
|
|
|
210 Options::
|
|
283
|
211
|
|
281
|
212 -a Search all PL/SQL objects (not just my own)
|
|
|
213
|
|
|
214
|
|
|
215 Bind variables
|
|
|
216 ==============
|
|
|
217
|
|
|
218 Bind variables work in sqlpython as they do in SQL\*Plus, but they are set dynamically; there
|
|
|
219 is no need to declare them before use. The syntax for setting them is more permissive than
|
|
|
220 in SQL\*Plus; all these are recognized::
|
|
|
221
|
|
|
222 exec :mybind := 'value'
|
|
|
223 exec :mybind = 'value'
|
|
|
224 :mybind := 'value'
|
|
|
225 :mybind = 'value'
|
|
|
226
|
|
|
227 The current values of all bind variables can be viewed with the `print` command.
|
|
|
228
|
|
|
229 The `bind` command creates and populates bind variables for the final row of the most recent
|
|
|
230 SELECT statement executed; each column name is used as a bind variable, which is filled with
|
|
|
231 the value. `bind -r {rownumber}` does the same, but fills from row {rownumber} instead of
|
|
|
232 from the final row (row numbers begin at 0 for this command).
|
|
|
233
|
|
|
234 When the `autobind` sqlpython parameter is True, a `bind` statement is issued automatically
|
|
|
235 after every query that returns exactly one row.
|
|
|
236
|
|
|
237 Bind variables are available from within Python as a dictionary named `binds` (see Python).
|
|
|
238
|
|
|
239 Substitution variables
|
|
|
240 ======================
|
|
|
241
|
|
|
242 Substitution variables ("&" variables) work much as they do in SQL\*Plus. As in SQL\*Plus,
|
|
|
243 the `scan` parameter determines whether queries are scanned to replace substitution
|
|
|
244 variables. Unlike SQL\*Plus, sqlpython knows how annoying it is to hit a substitution
|
|
|
245 variable you didn't expect, so entering "SET SCAN OFF" when prompted for a substitution
|
|
|
246 variable actually aborts the substitution process.
|
|
|
247
|
|
247
|
248 Wild SQL
|
|
|
249 ========
|
|
|
250
|
|
|
251 Wild SQL is a nonstandard SQL feature that must be enabled with `set wildsql on`. When it is
|
|
282
|
252 enabled, column names in a SELECT statement do not need to be explicitly typed; they can be
|
|
|
253 specified with special Wild SQL symbols: wildcards (`*`, `%`, `_`); column numbers (`#{N}`);
|
|
|
254 and NOT-style exclusion (`!`). The symbols can even be combined.
|
|
247
|
255
|
|
282
|
256 ::
|
|
247
|
257
|
|
282
|
258 jrrt@orcl> cat party
|
|
|
259
|
|
|
260 NAME STR INT WIS DEX CON CHA
|
|
|
261 ------- --- --- --- --- --- ---
|
|
|
262 Frodo 8 14 16 15 14 16
|
|
|
263 Gimli 17 12 10 11 17 11
|
|
|
264 Legolas 13 15 14 18 15 17
|
|
|
265 Sam 11 9 14 11 16 13
|
|
|
266
|
|
|
267 4 rows selected.
|
|
|
268
|
|
|
269 jrrt@orcl> set wild on
|
|
|
270 wildsql - was: False
|
|
|
271 now: True
|
|
|
272 jrrt@orcl> select *i* from party;
|
|
|
273
|
|
|
274 INT WIS
|
|
|
275 --- ---
|
|
|
276 14 16
|
|
|
277 12 10
|
|
|
278 15 14
|
|
|
279 9 14
|
|
|
280
|
|
|
281 4 rows selected.
|
|
|
282
|
|
|
283 jrrt@orcl> select #1, #5 from party;
|
|
|
284
|
|
|
285 NAME DEX
|
|
|
286 ------- ---
|
|
|
287 Frodo 15
|
|
|
288 Gimli 11
|
|
|
289 Legolas 18
|
|
|
290 Sam 11
|
|
|
291
|
|
|
292 4 rows selected.
|
|
|
293
|
|
|
294 jrrt@orcl> select !str from party;
|
|
|
295
|
|
|
296 NAME INT WIS DEX CON CHA
|
|
|
297 ------- --- --- --- --- ---
|
|
|
298 Frodo 14 16 15 14 16
|
|
|
299 Gimli 12 10 11 17 11
|
|
|
300 Legolas 15 14 18 15 17
|
|
|
301 Sam 9 14 11 16 13
|
|
|
302
|
|
|
303 4 rows selected.
|
|
|
304
|
|
|
305 jrrt@orcl> select n*, !#3, !c* from party;
|
|
|
306
|
|
|
307 NAME STR WIS DEX
|
|
|
308 ------- --- --- ---
|
|
|
309 Frodo 8 16 15
|
|
|
310 Gimli 17 10 11
|
|
|
311 Legolas 13 14 18
|
|
|
312 Sam 11 14 11
|
|
|
313
|
|
|
314 4 rows selected.
|
|
247
|
315
|
|
282
|
316 Wild SQL symbols only work in the first SELECT statement in a query; they do not work in
|
|
|
317 subqueries, subsequent UNIONed queries, etc.
|
|
|
318
|
|
|
319 Python
|
|
|
320 ======
|
|
|
321
|
|
|
322 The `py` command allows the user to execute Python commands, either one-at-a-time (with
|
|
|
323 `py {command}`) or in an interactive environment (beginning with a bare `py` statement,
|
|
|
324 and continuing until `end py` is entered).
|
|
|
325
|
|
|
326 A history of result sets from each query is exposed to the python session as the list `r`;
|
|
|
327 the most recent result set is `r[-1]`. Bind variables are exposed as the dictionary `binds`.
|
|
|
328 All variables are retained each time the python environment is entered (whether interactively,
|
|
|
329 or with one-line `py` statements).
|
|
|
330
|
|
|
331 Resultsets in `r` are read-only, but `binds` can be written as well as read, and will
|
|
|
332 be working bind variables in the SQL environment.
|
|
|
333
|
|
|
334 ::
|
|
247
|
335
|
|
282
|
336 0:testschema@eqtest> select title, author from play;
|
|
|
337
|
|
|
338 TITLE AUTHOR
|
|
|
339 --------------- -----------
|
|
|
340 Timon of Athens Shakespeare
|
|
|
341 Twelfth Night Shakespeare
|
|
|
342 The Tempest Shakespeare
|
|
|
343 Agamemnon Aeschylus
|
|
|
344
|
|
|
345 4 rows selected.
|
|
|
346
|
|
|
347 0:testschema@eqtest> py import urllib
|
|
|
348 0:testschema@eqtest> py current_season = urllib.urlopen('http://cincyshakes.com/').read()
|
|
|
349 0:testschema@eqtest> py
|
|
|
350 Now accepting python commands; end with `end py`
|
|
|
351 >>> r[-1]
|
|
|
352 [('Timon of Athens', 'Shakespeare'), ('Twelfth Night', 'Shakespeare'), ('The Tempest', 'Shakespeare'), ('Agamemnon', 'Aeschylus')]
|
|
|
353 >>> for row in r[-1]:
|
|
|
354 ... print '%s by %s' % (row.title, row.author)
|
|
|
355 Timon of Athens by Shakespeare
|
|
|
356 Twelfth Night by Shakespeare
|
|
|
357 The Tempest by Shakespeare
|
|
|
358 Agamemnon by Aeschylus
|
|
|
359 >>> [row.title for row in r[-1] if row.title in current_season]
|
|
|
360 ['Timon of Athens', 'Twelfth Night']
|
|
|
361 >>> binds['nowplaying'] = [row.title for row in r[-1] if row.title in current_season][0]
|
|
|
362 >>> end py
|
|
|
363 0:testschema@eqtest> print
|
|
|
364 :nowplaying = Timon of Athens
|
|
|
365 0:testschema@eqtest> select title, author from play where title = :nowplaying;
|
|
|
366
|
|
|
367 TITLE AUTHOR
|
|
|
368 --------------- -----------
|
|
|
369 Timon of Athens Shakespeare
|
|
|
370
|
|
|
371 1 row selected.
|
|
281
|
372
|
|
|
373 Parameters
|
|
|
374 ==========
|
|
|
375
|
|
|
376 Several parameters control the behavior of sqlpython itself.
|
|
|
377
|
|
283
|
378 ===================== =================================================== ===============
|
|
|
379 parameter effect default
|
|
|
380 ===================== =================================================== ===============
|
|
|
381 autobind When True, single-row queries automatically `bind` False
|
|
|
382 commit_on_exit Automatically commits work at end of session True
|
|
|
383 continuation_prompt Prompt for second line and onward of long statement >
|
|
|
384 default_file_name The file opened by `edit`, if not specified afiedt.buf
|
|
|
385 echo Echo command entered before executing False
|
|
|
386 editor Text editor invoked by `edit`. varies
|
|
|
387 heading Print column names True
|
|
|
388 maxfetch Maximum number of rows to return from any query 1000
|
|
|
389 maxtselctrows Maximum # of rows from a tselect or \\n query 10
|
|
|
390 prompt Probably unwise to change user@instance>
|
|
|
391 scan Interpret & as indicating substitution variables True
|
|
|
392 serveroutput Print DBMS_OUTPUT.PUT_LINE results True
|
|
|
393 sql_echo Print text of "behind-the-scenes" queries False
|
|
|
394 timeout In seconds 30
|
|
|
395 timing Print time for each command to execute False
|
|
|
396 wildsql Accept *, %, #, and ! in column names False
|
|
|
397 ===================== =================================================== ===============
|
|
281
|
398
|
|
|
399 The user can change these with the `set {paramname} {new-value}` statement.
|
|
283
|
400 The True/False parameters accept new values permissively, recognizing "True", "False",
|
|
|
401 "T", "F", "yes", "no", "on", "off", etc.
|
|
281
|
402
|
|
283
|
403 `set` and `show` both list the current values of the sqlpython parameters. They
|
|
|
404 also recognize any abbreviated parameter name, so long as it is long enough to be
|
|
|
405 unique. That is, `show maxf` is recognized as `show maxfetch`, but `show max` is
|
|
|
406 too short to distinguish between `maxfetch` and `maxtselctrows`.
|
|
|
407
|
|
281
|
408 `show parameter {param}` shows current Oracle parameters (from v$parameter), as it does
|
|
|
409 in SQL\*Plus.
|
|
|
410
|
|
|
411 Tuning
|
|
|
412 ======
|
|
|
413
|
|
|
414 In sqlpython, `explain {SQL ID}` shows the execution plan for the SQL statement with the
|
|
|
415 given ID. If SQL ID is omitted, it defaults to the most recent SQL executed.
|
|
|
416 (This is not necessarily the last statement `EXPLAIN PLAN` was issued against.)
|
|
|
417
|
|
|
418 Other specialized sqlpython tuning commands include:
|
|
|
419
|
|
|
420 load
|
|
|
421 Displays OS load on cluster nodes (10gRAC)
|
|
|
422
|
|
|
423 longops
|
|
|
424 Displays long-running operations
|
|
|
425
|
|
|
426 sessinfo
|
|
|
427 Reports session info for the given sid, extended to RAC with gv$
|
|
|
428
|
|
|
429 top, top9i
|
|
|
430 Displays active sessions
|
|
|
431
|
|
|
432
|
|
|
433
|
|
|
434
|
|
|
435
|
