1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
|
<HTML><HEAD><TITLE>SQL Worksheet</TITLE></HEAD><BODY>
<H1>SQL Worksheet</H1>
This tool provides a way to execute arbitrary SQL or PL/SQL code and also investigate how it is execute and it's resource consumption.<P>
<IMG SRC=images/worksheet.png><P>
The worksheet is split into two panes. The upper one is the editor where you enter the queries you want to run. In the lower you can
investigate the result.
<H2>Toolbar & menu</H2>
There is a toolbar and a tool menu available for the worksheet. They both contain the following commands.<P>
<TABLE>
<TR><TD VALIGN=top><B>
Execute Current</B></TD>
<TD VALIGN=top>This executes the statement you current are within or after. When executed
the statement executed will be highlighted in the editor. The keyboard shortcut is <B>CTRL-Return</B>.
</TD></TR>
<TR><TD VALIGN=top><B>
Execute All</B></TD>
<TD VALIGN=top>Execute all the statements in the editor. You get no result from the execution, the
statement is simply executed. You can investigate the success of the commands in the <B>Logging</B>
pane of the result. The keyboard shortcut is <B>F8</B>.
</TD></TR>
<TR><TD VALIGN=top><B>
Execute Next</B></TD>
<TD VALIGN=top>This does exactly the same as the <B>execute current</B> command with one difference.
If the cursor is after the last ; of the statement and before the first non whitespace character
of the next statement this command will execute the next statement when
the other command will execute the previous statement. The keyboard shortcut is <B>F9</B>.
</TD></TR>
<TR><TD VALIGN=top><B>
Execute Newline Separated</B></TD>
<TD VALIGN=top>Execute a statement that is separated by two newlines instead of the normal
';' character. Could be useful for people who are familiar with some other Oracle tools.
The keyboard shortcut is <B>SHIFT+F9</B>. Only available in the menu, not in the toolbar.
</TD></TR>
<TR><TD VALIGN=top><B>
Refresh</B></TD>
<TD VALIGN=top>This will re-execute the last executed statement. The
keyboard shortcut is <B>F5</B>.
</TD></TR>
<TR><TD VALIGN=top><B>
Describe under cursor</B></TD>
<TD VALIGN=top>Describe the table currently under the cursor. The keyboard shortcut is <B>F4</B>.
</TD></TR>
<TR><TD VALIGN=top><B>
Enable Statistics</B></TD>
<TD VALIGN=top>Enable or disable collecting statistics when running the statements. This will cause
TOra to execute at least two extra queries for every statement executed when enabled. Next to this
button in the toolbar is a combo box in which you can choose the refreshtime for the charts in the
statistics pane.
</TD></TR>
<TR><TD VALIGN=top><B>
Stop execution</B></TD>
<TD VALIGN=top>Abort execution of the currently running query. This will not work in Windows where
the query will keep running in the background until the first row of the query is returned and consume
server resources before it is aborted. When you execute a new statement the current execution will be
aborted.
</TD></TR>
<TR><TD VALIGN=top><B>
Execute Saved SQL</B></TD>
<TD VALIGN=top>Execute the latest selected saved SQL. For more information see
<A HREF=worksheet.html#saved>saved SQL</A>. Keyboard shortcut is <B>F7</B>. This is not available in
the toolbar.
</TD></TR>
<TR><TD VALIGN=top><B>
Select Saved SQL</B></TD>
<TD VALIGN=top>Popup menu containing saved SQL. For more information see
<A HREF=worksheet.html#saved>saved SQL</A>. Keyboard shortcut is <B>CTRL+SHIFT+S</B>.
</TD></TR>
<TR><TD VALIGN=top><B>
Save last SQL</B></TD>
<TD VALIGN=top>Save the most recently executed SQL to the saved SQL list. When pressed will
ask for the name to save the SQL under. If ':' is entered in this name it will separate into
submenues on the colon.
</TD></TR>
<TR><TD VALIGN=top><B>
Edit Saved SQL</B></TD>
<TD VALIGN=top>Open the SQL editor to edit saved SQL. For more information see
<A HREF=worksheet.html#saved>saved SQL</A>. Only available in menu, not in toolbar.
</TD></TR>
<TR><TD VALIGN=top><B>
Previous Log Entry</B></TD>
<TD VALIGN=top>Display the result of the previous entry in the log. If result caching is on
in the <A HREF=worksheet.html#preferences>settings</A> the previous result is displayed, if it
isn't the statement is reexecuted to generate the result. Keyboard shortcut is <B>ALT+Up</B>.
Only available in menu, not in toolbar.
</TD></TR>
<TR><TD VALIGN=top><B>
Next Log Entry</B></TD>
<TD VALIGN=top>Display the result of the next entry in the log. If result caching is on
in the <A HREF=worksheet.html#preferences>settings</A> the next result is displayed, if it
isn't the statement is reexecuted to generate the result. Keyboard shortcut is <B>ALT+Down</B>.
Only available in menu, not in toolbar.
</TD></TR>
<TR><TD VALIGN=top><B>
Erase Log</B></TD>
<TD VALIGN=top>
Erase the contents of the rows under the <B>Logging</B> pane of the result.</TD></TR>
<TR><TD VALIGN=top><B>
Current duration</B></TD>
<TD VALIGN=top>
While a query is currently executing you can see how long the query has been running in
the right of the toolbar as a slowly ticking up the seconds. This clock will not stop when
the first row is returned but run until all rows are read or the query is aborted. Moving the
cursor over this label will also display a tooltip containing the SQL currently executing.
</TD></TR>
<TR><TD VALIGN=top><B>
Change connection</B></TD>
<TD VALIGN=top>
Change the connection this tool window should operate on.</TD></TR>
</TABLE><P>
<H2>Using the worksheet editor</H2>
TOra uses a ; to separate statements in the same way as SQL*Plus. One difference is that TOra doesn't
count whitespaces in any way to separate statements. You could write several statements on the same
row and it wouldn't be a problem.<P>
Another difference to SQL*Plus is that TOra parses the SQL you write to determine if you are within
a PL/SQL block. If that is the case the entire block will be executed. This feature can be enabled
or disabled in the <A HREF=worksheet.html#preferences>options</A>.<P>
<table width=100% border=0><tr><td bgcolor="#BEEAE0">
<pre>
SELECT * FROM dual; SELECT * FROM all_tables;
BEGIN
DBMS_OUTPUT.PUT_LINE('Hello');
END;
</pre></td></tr></table><P>
In the example above there are three statements that TOra will recognize and execute.<P>
There are a few words that are treated as comments for compatibility with SQL*Plus scripts. They are
<B>--</B>, <B>REM</B> and <B>PROMPT</B>. Comments will disregard whatever is to the right on the line.
Also multi line <B>/* ... */</B> style comments are recognised and ignored.<P>
You can also use bind variables in the worksheet by using the normal <B>:</B> character. See
<A HREF=common.html#param>getting bind parameters</A> for more information how this works. One nice
feature is that the first line retrieved from the result set is stored in the parameter cache with
a bind name the same as the column name in lowercase. The example in the <A HREF=common.html#param>getting
bind parameters</A> could have been used directly after executing the line <code>select * from essprc</code>
and get the second row in the table by just using the defaults of the bind dialog if <code>essprc</code>
contains a column named <code>prcid</code>.<P>
You can also describe objects returned as in PL/SQL by using either <B>DESC</B> or <B>DESCRIBE</B> and an
object name.<P>
<H2>Exploring the result</H2>
There are five different panes available with information about the executed statements.<P>
<TABLE>
<TR><TD VALIGN=top><B>
Result</B></TD><TD>
This contains the actual result of the query or describe. For some notes about descriptions
see <A HREF=common.html#describe>this note</A>.
</TD></TR>
<TR><TD VALIGN=top><B>
Visualize</B></TD><TD>
Can be used to visualize the current content of the result. The first column is assumes to
contain labels and the rest contain the data.<P>
<IMG SRC=images/visualize.png><P>
The toolbar available contains the following controls.<P>
<TABLE>
<TR><TD VALIGN=top><B>
Chart type</B></TD><TD>
What kind of chart to generate. Available types are <B>Bar Chart</B>, <B>Line Chart</B> and
<B>Pie Chart</B>.
</TD></TR>
<TR><TD VALIGN=top><B>
Display Legend</B></TD><TD>
Display chart legend on the right of the generated chart.
</TD></TR>
<TR><TD VALIGN=top><B>
Display Grid</B></TD><TD>
Display a grid in the generated chart.
</TD></TR>
<TR><TD VALIGN=top><B>
Display Axis Legend</B></TD><TD>
Display legends of the axises of the generated chart.
</TD></TR>
<TR><TD VALIGN=top><B>
Update chart</B></TD><TD>
Generate a chart of the current data in the result tab and the current settings.
</TD></TR>
</TABLE>
</TD></TR>
<TR><TD VALIGN=top><B>
Execution plan</B></TD><TD>
The <A HREF=common.html#explain>execution plan</A> of the current statement.</TD></TR>
<TR><TD VALIGN=top><B>Information
</B></TD><TD>The information available about the current statement in the SGA.
</TD></TR>
<TR><TD VALIGN=top><B>Statistics
</B></TD><TD>The statistics collected when this statement was run. You need to <B>enable
statistics</B> for this to be enabled. To update the statistic view simply change to another tab and change back
to statistics. Chart are updated at the interval specified in the toolbar regardless of
if you refresh statistics or not.
</TD></TR>
<TR><TD VALIGN=top><B>Logging
</B></TD><TD>This pane will display the history of the statements you have executed and
their result. It can be sorted up or down depending on <A HREF=worksheet.html#preferences>options</A>.
You can see the statement, it's result and the time (As recorded by the database) when it
was executed. You can also see how many seconds until the first row was received. A new statement isn't
added to the log until the first row of the query is returned or the statement has executed, whichever
comes first.
</TD></TR>
</TABLE>
<A NAME=preferences><H2>Options</H2>
There are quite a few options available for the worksheet.<P>
<IMG SRC=images/worksheetsetup.png><P>
<TABLE>
<TR><TD VALIGN=top><B>
Auto save</B></TD><TD>
If checked the worksheet will always save changes to the editor <B>without asking</B> when the window is closed.</TD></TR>
<TR><TD VALIGN=top><B>
Ask about saving changes</B></TD><TD>
Ask about saving changes to the worksheet when it is modified. Selecting the <B>Auto save</B> have precedence over this
option.</TD></TR>
<TR><TD VALIGN=top><B>
Add log entries at end</B></TD><TD>
If checked new entries in the <B>Logging</B> pane will be added to the end of list, otherwise they will be added at the
top.</TD></TR>
<TR><TD VALIGN=top><B>
Multiple lines in log</B></TD><TD>
If checked the lines in the log will display all the lines of the SQL executed. If not checked only the first line is
displayed, although you can still see all of it in the tooltip or editor if you want to. For more information see
<A HREF=lists.html>using lists</A>.</TD></TR>
<TR><TD VALIGN=top><B>
Parse PL/SQL blocks</B></TD><TD>
If this is checked TOra will parse the text to determine PL/SQL blocks in the editor and execute them as one statement.
If it isn't checked statements are strictly separated by <B>;</B>.</TD></TR>
<TR><TD VALIGN=top><B>
Enable statistics</B></TD><TD>
Indicates if statistics should be enabled or disabled as default when a new worksheet is opened.</TD></TR>
<TR><TD VALIGN=top><B>
Enable timed statistics</B></TD><TD>
Set this to make TOra always enable timed statistics for the sessions that it is collecting statistics for.
Observe that timed statistics will not be disabled after the worksheet is closed. The change is strictly
for the current session though.</TD></TR>
<TR><TD VALIGN=top><B>
Display number column</B></TD><TD>
Indicate whether or not to display the number column as the first column of the result or suppress it.</TD></TR>
<TR><TD VALIGN=top><B>
Move cursor to error</B></TD><TD>
Move the cursor to the location of an error in an execute statement.
<TR><TD VALIGN=top><B>
Save previous result</B></TD><TD>
Save the results of all previous statements. This can consume a lot of memory, but will increase time to navigate
history results. You can still clear this cache by erasing the log.
<TR><TD VALIGN=top><B>
Default file</B></TD><TD>
A filename to open automatically when you start a new worksheet. Choose the <B>Browse</B> button to select
the file in a file dialog.</TD></TR>
</TABLE>
<A NAME=saved><H2>Saved SQL</H2>
This is a feature you can use to quickly access SQL you often use. This is a popupmenu accessible in the toolbar
of a worksheet. This popup will display all the SQL under "toWorksheet:" in the <A HREF=sqledit.html>SQL dictionary</A>.
You can create submenues by adding a ":" character in the SQL name. The name of the SQL dictionary is what is used to
generate the menu and not the description. You can still use placebinders in saved SQL. By default this list is empty.
</BODY>
</HTML>
|