|
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
|
|
|
87 Special output formats
|
|
|
88 ======================
|
|
|
89
|
|
|
90 By replacing the `;` that terminates a SELECT statement with a backslash-character
|
|
|
91 sequence, you can get output in a number of useful formats. The `terminators`
|
|
|
92 command lists them, for your convenience::
|
|
|
93
|
|
|
94 === ======================== ================================
|
|
|
95 Especially useful for
|
|
|
96 === ======================== ================================
|
|
|
97 ; standard Oracle format
|
|
|
98 \c CSV (with headings) sending to spreadsheets
|
|
|
99 \C CSV (no headings)
|
|
|
100 \g list wide output with linewraps
|
|
|
101 \G aligned list
|
|
|
102 \h HTML table web reports
|
|
|
103 \i INSERT statements copying to other instances
|
|
|
104 \j JSON
|
|
|
105 \s CSV (with headings)
|
|
|
106 \S CSV (no headings)
|
|
|
107 \t transposed "narrow" tables like v$database
|
|
|
108 \x XML
|
|
|
109 \l line plot, with markers
|
|
|
110 \L scatter plot (no lines)
|
|
|
111 \b bar graph
|
|
|
112 \p pie chart
|
|
|
113 === ======================== ================================
|
|
|
114
|
|
|
115 Most of these output formats are even more useful when combined with special output
|
|
|
116 destinations. For example, `SELECT * FROM party\h > /var/www/party_report.html`
|
|
|
117 could create an HTML report in the webserver's documents directory, ready to serve.
|
|
|
118
|
|
247
|
119 UNIX-like commands
|
|
|
120 ==================
|
|
|
121
|
|
279
|
122 Many sqlpython commands allow you to act as though the database objects
|
|
|
123 were files in a UNIX filesystem. Many of the commands also accept flags
|
|
|
124 to modify their behavior.
|
|
|
125
|
|
281
|
126 ls {object type/object name, with wildcards}
|
|
|
127 Lists objects from the data dictionaries, as though they were in a
|
|
|
128 *object_type*/*object_name* directory structure. Thus, `ls view/\*`
|
|
|
129 lists all the user's views. Calling with no argument is equivalent
|
|
|
130 to `ls *`.
|
|
|
131
|
|
|
132 Options::
|
|
|
133
|
|
|
134 -l, --long long descriptions
|
|
|
135 -a, --all all schemas' objects (otherwise, you only get your own)
|
|
|
136 -t, --timesort Sort by last_ddl_time
|
|
|
137 -r, --reverse Reverse order while sorting
|
|
247
|
138
|
|
281
|
139 `ls -lt *;10` lists the ten items with the most recent last_ddl_time;
|
|
|
140 this can be a good way to answer the question, "What was I working on?"
|
|
|
141
|
|
|
142 cat {remainder of query}
|
|
|
143 Shorthand for "SELECT * FROM". Can be combined with anything else
|
|
|
144 that fits into a SELECT statement (WHERE, ORDER BY, etc.)
|
|
|
145
|
|
|
146 grep {grep {target} {table} [{table2,...}]
|
|
|
147 Equivalent to SELECT * FROM {table} WHERE *any column* LIKE '%{target}%'
|
|
|
148 Useful when you don't know, don't remember, or don't care which column
|
|
|
149 a value may be found in.
|
|
|
150
|
|
|
151 Options::
|
|
|
152
|
|
|
153 -i, --ignore-case Case-insensitive search
|
|
247
|
154
|
|
281
|
155 find -c {target}, find -t {column}
|
|
|
156 Lists all tables or columns whose names contain {target}. More convenient than
|
|
|
157 querying user_tab_columns/all_tab_columns or user_tables/all_tables.
|
|
|
158 Options::
|
|
|
159 -a Find all objects (not just my own)
|
|
|
160
|
|
247
|
161 PostgreSQL-like shortcuts
|
|
|
162 =========================
|
|
|
163
|
|
|
164 ----- ------------------
|
|
|
165 z y
|
|
|
166 ----- ------------------
|
|
|
167 \\c connect
|
|
|
168 \\d desc
|
|
|
169 \\e edit
|
|
|
170 \\g run
|
|
|
171 \\h help
|
|
|
172 \\i load
|
|
|
173 \\o spool
|
|
|
174 \\p list
|
|
|
175 \\q quit
|
|
|
176 \\w save
|
|
|
177 \\db _dir_tablespaces
|
|
|
178 \\dd comments
|
|
|
179 \\dn _dir_schemas
|
|
|
180 \\dt _dir_tables
|
|
|
181 \\dv _dir_views
|
|
|
182 \\di _dir_indexes
|
|
|
183 \\? help psql
|
|
|
184 ----- ------------------
|
|
|
185
|
|
281
|
186 PL/SQL source code
|
|
|
187 ==================
|
|
|
188
|
|
|
189 pull {object_name}
|
|
|
190 Displays the PL/SQL source code for {object_name}.
|
|
|
191
|
|
|
192 Options:
|
|
|
193 -d, --dump dump results to files (object_type/object_name.sql)
|
|
|
194 -f, --full get dependent objects as well
|
|
|
195 -a, --all all schemas' objects
|
|
|
196
|
|
|
197 bzr, git, hg {object_name}
|
|
|
198 Dump source code to files, as `pull -f`, but also creates or commits to a
|
|
|
199 repository of the appropriate distributed version control system
|
|
|
200 (Bazaar, Git, or Mercurial, respectively).
|
|
|
201
|
|
|
202 find {target}
|
|
|
203 Lists all PL/SQL objects whose source code contains the {target} string.
|
|
|
204 Always case-insensitive.
|
|
|
205 Options::
|
|
|
206 -a Search all PL/SQL objects (not just my own)
|
|
|
207
|
|
|
208
|
|
|
209 Bind variables
|
|
|
210 ==============
|
|
|
211
|
|
|
212 Bind variables work in sqlpython as they do in SQL\*Plus, but they are set dynamically; there
|
|
|
213 is no need to declare them before use. The syntax for setting them is more permissive than
|
|
|
214 in SQL\*Plus; all these are recognized::
|
|
|
215
|
|
|
216 exec :mybind := 'value'
|
|
|
217 exec :mybind = 'value'
|
|
|
218 :mybind := 'value'
|
|
|
219 :mybind = 'value'
|
|
|
220
|
|
|
221 The current values of all bind variables can be viewed with the `print` command.
|
|
|
222
|
|
|
223 The `bind` command creates and populates bind variables for the final row of the most recent
|
|
|
224 SELECT statement executed; each column name is used as a bind variable, which is filled with
|
|
|
225 the value. `bind -r {rownumber}` does the same, but fills from row {rownumber} instead of
|
|
|
226 from the final row (row numbers begin at 0 for this command).
|
|
|
227
|
|
|
228 When the `autobind` sqlpython parameter is True, a `bind` statement is issued automatically
|
|
|
229 after every query that returns exactly one row.
|
|
|
230
|
|
|
231 Bind variables are available from within Python as a dictionary named `binds` (see Python).
|
|
|
232
|
|
|
233 Substitution variables
|
|
|
234 ======================
|
|
|
235
|
|
|
236 Substitution variables ("&" variables) work much as they do in SQL\*Plus. As in SQL\*Plus,
|
|
|
237 the `scan` parameter determines whether queries are scanned to replace substitution
|
|
|
238 variables. Unlike SQL\*Plus, sqlpython knows how annoying it is to hit a substitution
|
|
|
239 variable you didn't expect, so entering "SET SCAN OFF" when prompted for a substitution
|
|
|
240 variable actually aborts the substitution process.
|
|
|
241
|
|
247
|
242 Wild SQL
|
|
|
243 ========
|
|
|
244
|
|
|
245 Wild SQL is a nonstandard SQL feature that must be enabled with `set wildsql on`. When it is
|
|
282
|
246 enabled, column names in a SELECT statement do not need to be explicitly typed; they can be
|
|
|
247 specified with special Wild SQL symbols: wildcards (`*`, `%`, `_`); column numbers (`#{N}`);
|
|
|
248 and NOT-style exclusion (`!`). The symbols can even be combined.
|
|
247
|
249
|
|
282
|
250 ::
|
|
247
|
251
|
|
282
|
252 jrrt@orcl> cat party
|
|
|
253
|
|
|
254 NAME STR INT WIS DEX CON CHA
|
|
|
255 ------- --- --- --- --- --- ---
|
|
|
256 Frodo 8 14 16 15 14 16
|
|
|
257 Gimli 17 12 10 11 17 11
|
|
|
258 Legolas 13 15 14 18 15 17
|
|
|
259 Sam 11 9 14 11 16 13
|
|
|
260
|
|
|
261 4 rows selected.
|
|
|
262
|
|
|
263 jrrt@orcl> set wild on
|
|
|
264 wildsql - was: False
|
|
|
265 now: True
|
|
|
266 jrrt@orcl> select *i* from party;
|
|
|
267
|
|
|
268 INT WIS
|
|
|
269 --- ---
|
|
|
270 14 16
|
|
|
271 12 10
|
|
|
272 15 14
|
|
|
273 9 14
|
|
|
274
|
|
|
275 4 rows selected.
|
|
|
276
|
|
|
277 jrrt@orcl> select #1, #5 from party;
|
|
|
278
|
|
|
279 NAME DEX
|
|
|
280 ------- ---
|
|
|
281 Frodo 15
|
|
|
282 Gimli 11
|
|
|
283 Legolas 18
|
|
|
284 Sam 11
|
|
|
285
|
|
|
286 4 rows selected.
|
|
|
287
|
|
|
288 jrrt@orcl> select !str from party;
|
|
|
289
|
|
|
290 NAME INT WIS DEX CON CHA
|
|
|
291 ------- --- --- --- --- ---
|
|
|
292 Frodo 14 16 15 14 16
|
|
|
293 Gimli 12 10 11 17 11
|
|
|
294 Legolas 15 14 18 15 17
|
|
|
295 Sam 9 14 11 16 13
|
|
|
296
|
|
|
297 4 rows selected.
|
|
|
298
|
|
|
299 jrrt@orcl> select n*, !#3, !c* from party;
|
|
|
300
|
|
|
301 NAME STR WIS DEX
|
|
|
302 ------- --- --- ---
|
|
|
303 Frodo 8 16 15
|
|
|
304 Gimli 17 10 11
|
|
|
305 Legolas 13 14 18
|
|
|
306 Sam 11 14 11
|
|
|
307
|
|
|
308 4 rows selected.
|
|
247
|
309
|
|
282
|
310 Wild SQL symbols only work in the first SELECT statement in a query; they do not work in
|
|
|
311 subqueries, subsequent UNIONed queries, etc.
|
|
|
312
|
|
|
313 Python
|
|
|
314 ======
|
|
|
315
|
|
|
316 The `py` command allows the user to execute Python commands, either one-at-a-time (with
|
|
|
317 `py {command}`) or in an interactive environment (beginning with a bare `py` statement,
|
|
|
318 and continuing until `end py` is entered).
|
|
|
319
|
|
|
320 A history of result sets from each query is exposed to the python session as the list `r`;
|
|
|
321 the most recent result set is `r[-1]`. Bind variables are exposed as the dictionary `binds`.
|
|
|
322 All variables are retained each time the python environment is entered (whether interactively,
|
|
|
323 or with one-line `py` statements).
|
|
|
324
|
|
|
325 Resultsets in `r` are read-only, but `binds` can be written as well as read, and will
|
|
|
326 be working bind variables in the SQL environment.
|
|
|
327
|
|
|
328 ::
|
|
247
|
329
|
|
282
|
330 0:testschema@eqtest> select title, author from play;
|
|
|
331
|
|
|
332 TITLE AUTHOR
|
|
|
333 --------------- -----------
|
|
|
334 Timon of Athens Shakespeare
|
|
|
335 Twelfth Night Shakespeare
|
|
|
336 The Tempest Shakespeare
|
|
|
337 Agamemnon Aeschylus
|
|
|
338
|
|
|
339 4 rows selected.
|
|
|
340
|
|
|
341 0:testschema@eqtest> py import urllib
|
|
|
342 0:testschema@eqtest> py current_season = urllib.urlopen('http://cincyshakes.com/').read()
|
|
|
343 0:testschema@eqtest> py
|
|
|
344 Now accepting python commands; end with `end py`
|
|
|
345 >>> r[-1]
|
|
|
346 [('Timon of Athens', 'Shakespeare'), ('Twelfth Night', 'Shakespeare'), ('The Tempest', 'Shakespeare'), ('Agamemnon', 'Aeschylus')]
|
|
|
347 >>> for row in r[-1]:
|
|
|
348 ... print '%s by %s' % (row.title, row.author)
|
|
|
349 Timon of Athens by Shakespeare
|
|
|
350 Twelfth Night by Shakespeare
|
|
|
351 The Tempest by Shakespeare
|
|
|
352 Agamemnon by Aeschylus
|
|
|
353 >>> [row.title for row in r[-1] if row.title in current_season]
|
|
|
354 ['Timon of Athens', 'Twelfth Night']
|
|
|
355 >>> binds['nowplaying'] = [row.title for row in r[-1] if row.title in current_season][0]
|
|
|
356 >>> end py
|
|
|
357 0:testschema@eqtest> print
|
|
|
358 :nowplaying = Timon of Athens
|
|
|
359 0:testschema@eqtest> select title, author from play where title = :nowplaying;
|
|
|
360
|
|
|
361 TITLE AUTHOR
|
|
|
362 --------------- -----------
|
|
|
363 Timon of Athens Shakespeare
|
|
|
364
|
|
|
365 1 row selected.
|
|
281
|
366
|
|
|
367 Parameters
|
|
|
368 ==========
|
|
|
369
|
|
|
370 Several parameters control the behavior of sqlpython itself.
|
|
|
371
|
|
|
372 ===================== ================================================== ===============
|
|
|
373 default
|
|
|
374 ===================== ================================================== ===============
|
|
|
375 autobind When True, single-row queries automatically `bind` False
|
|
|
376 commit_on_exit Automatically commits work at end of session True
|
|
|
377 continuation_prompt Prompt for second line and onward of long statement >
|
|
|
378 default_file_name The file opened by `edit`, if not specified afiedt.buf
|
|
|
379 echo Echo command entered before executing False
|
|
|
380 editor Text editor invoked by `edit`. varies
|
|
|
381 heading Print column names True
|
|
|
382 maxfetch Maximum number of rows to return from any query 1000
|
|
|
383 maxtselctrows Maximum # of rows from a tselect or \\n query 10
|
|
|
384 prompt Probably unwise to change user@instance>
|
|
|
385 scan Interpret & as indicating substitution variables True
|
|
|
386 serveroutput Print DBMS_OUTPUT.PUT_LINE results True
|
|
|
387 sql_echo Print text of "behind-the-scenes" queries False
|
|
|
388 timeout In seconds 30
|
|
|
389 timing Print time for each command to execute False
|
|
|
390 wildsql Accept *, %, #, and ! in column names False
|
|
|
391 ===================== ================================================== ===============
|
|
|
392
|
|
|
393 The user can change these with the `set {paramname} {new-value}` statement.
|
|
|
394 The True/False parameters accept new
|
|
|
395 values permissively, recognizing "True", "False", "T", "F", "yes", "no", "on", "off"...
|
|
|
396
|
|
|
397 `set` and `show` both list the current values of the sqlpython parameters.
|
|
|
398 `show parameter {param}` shows current Oracle parameters (from v$parameter), as it does
|
|
|
399 in SQL\*Plus.
|
|
|
400
|
|
|
401 Tuning
|
|
|
402 ======
|
|
|
403
|
|
|
404 In sqlpython, `explain {SQL ID}` shows the execution plan for the SQL statement with the
|
|
|
405 given ID. If SQL ID is omitted, it defaults to the most recent SQL executed.
|
|
|
406 (This is not necessarily the last statement `EXPLAIN PLAN` was issued against.)
|
|
|
407
|
|
|
408 Other specialized sqlpython tuning commands include:
|
|
|
409
|
|
|
410 load
|
|
|
411 Displays OS load on cluster nodes (10gRAC)
|
|
|
412
|
|
|
413 longops
|
|
|
414 Displays long-running operations
|
|
|
415
|
|
|
416 sessinfo
|
|
|
417 Reports session info for the given sid, extended to RAC with gv$
|
|
|
418
|
|
|
419 top, top9i
|
|
|
420 Displays active sessions
|
|
|
421
|
|
|
422
|
|
|
423
|
|
|
424
|
|
|
425
|
