Mercurial > sqlpython

comparison docs/source/capabilities.rst @ 281:701f0aae837a

Find changesets by keywords (author, files, the commit message), revision number or hash, or revset expression.
got maxtselctrows working again
author catherine@dellzilla
date Thu, 19 Mar 2009 16:45:45 -0400
parents 8ea39093ddf2
children 4eef08cfaf25
comparison
equal deleted inserted replaced
280:8ea39093ddf2 281:701f0aae837a
1 ==============================
1 SQLPython's extra capabilities 2 SQLPython's extra capabilities
2 ============================== 3 ==============================
3 4
4 For the most part, SQLPython simply duplicates SQL\*Plus's capabilites. 5 For the most part, SQLPython simply duplicates SQL\*Plus's capabilites.
5 6
64 65
65 `run <str>`, `run /<regex>/` 66 `run <str>`, `run /<regex>/`
66 Run command matching <str> or <regex> (as for `history`) - 67 Run command matching <str> or <regex> (as for `history`) -
67 if multiple items would match, run most recent 68 if multiple items would match, run most recent
68 69
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
69 UNIX-like commands 119 UNIX-like commands
70 ================== 120 ==================
71 121
72 Many sqlpython commands allow you to act as though the database objects 122 Many sqlpython commands allow you to act as though the database objects
73 were files in a UNIX filesystem. Many of the commands also accept flags 123 were files in a UNIX filesystem. Many of the commands also accept flags
74 to modify their behavior. 124 to modify their behavior.
75 125
76 ls 126 ls {object type/object name, with wildcards}
77 Lists objects from the data dictionaries, as though they were in a 127 Lists objects from the data dictionaries, as though they were in a
78 *object_type*/*object_name* directory structure. Thus, `ls view/\*` 128 *object_type*/*object_name* directory structure. Thus, `ls view/\*`
79 lists all the user's views. 129 lists all the user's views. Calling with no argument is equivalent
80 130 to `ls *`.
81 cat 131
82 Shorthand for "SELECT * FROM" 132 Options::
83 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
138
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
154
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
84 PostgreSQL-like shortcuts 161 PostgreSQL-like shortcuts
85 ========================= 162 =========================
86 163
87 ----- ------------------ 164 ----- ------------------
88 z y 165 z y
104 \\dv _dir_views 181 \\dv _dir_views
105 \\di _dir_indexes 182 \\di _dir_indexes
106 \\? help psql 183 \\? help psql
107 ----- ------------------ 184 ----- ------------------
108 185
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
109 Wild SQL 242 Wild SQL
110 ======== 243 ========
111 244
112 Wild SQL is a nonstandard SQL feature that must be enabled with `set wildsql on`. When it is 245 Wild SQL is a nonstandard SQL feature that must be enabled with `set wildsql on`. When it is
113 enabled, column names in a SELECT statement do not need to be explicitly typed. 246 enabled, column names in a SELECT statement do not need to be explicitly typed.
118 251
119 SELECT 252 SELECT
120 253
121 Wild SQL can only be used in the primary column list of straightforward SELECT statements, 254 Wild SQL can only be used in the primary column list of straightforward SELECT statements,
122 not in subqueries, `UNION`ed queries, etc. 255 not in subqueries, `UNION`ed queries, etc.
256
257 Parameters
258 ==========
259
260 Several parameters control the behavior of sqlpython itself.
261
262 ===================== ================================================== ===============
263 default
264 ===================== ================================================== ===============
265 autobind When True, single-row queries automatically `bind` False
266 commit_on_exit Automatically commits work at end of session True
267 continuation_prompt Prompt for second line and onward of long statement >
268 default_file_name The file opened by `edit`, if not specified afiedt.buf
269 echo Echo command entered before executing False
270 editor Text editor invoked by `edit`. varies
271 heading Print column names True
272 maxfetch Maximum number of rows to return from any query 1000
273 maxtselctrows Maximum # of rows from a tselect or \\n query 10
274 prompt Probably unwise to change user@instance>
275 scan Interpret & as indicating substitution variables True
276 serveroutput Print DBMS_OUTPUT.PUT_LINE results True
277 sql_echo Print text of "behind-the-scenes" queries False
278 timeout In seconds 30
279 timing Print time for each command to execute False
280 wildsql Accept *, %, #, and ! in column names False
281 ===================== ================================================== ===============
282
283 The user can change these with the `set {paramname} {new-value}` statement.
284 The True/False parameters accept new
285 values permissively, recognizing "True", "False", "T", "F", "yes", "no", "on", "off"...
286
287 `set` and `show` both list the current values of the sqlpython parameters.
288 `show parameter {param}` shows current Oracle parameters (from v$parameter), as it does
289 in SQL\*Plus.
290
291 Tuning
292 ======
293
294 In sqlpython, `explain {SQL ID}` shows the execution plan for the SQL statement with the
295 given ID. If SQL ID is omitted, it defaults to the most recent SQL executed.
296 (This is not necessarily the last statement `EXPLAIN PLAN` was issued against.)
297
298 Other specialized sqlpython tuning commands include:
299
300 load
301 Displays OS load on cluster nodes (10gRAC)
302
303 longops
304 Displays long-running operations
305
306 sessinfo
307 Reports session info for the given sid, extended to RAC with gv$
308
309 top, top9i
310 Displays active sessions
311
312
313
314
315