annotate docs/source/capabilities.rst @ 348:c652478be4fd

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