Batch Execute Multiple Statements |
|
The SQL dialog in SQL*XL has the capability to take multiple SQL statements
and other commands at the same time. When submitting more than 1 SQL statement
each statement should be ended with a semi colon character (;). The source
of the SQL does not matter. Multiple select statements can be submitted from a SQL file, a worksheet or from
the SQL dialog itself. Let's give a few examples:
To query both the emp and the dept table in one go enter the following into the SQL
dialog (note that it also times these queries):
set timing on;
select * from emp;
select * from dept;
Clicking the OK button on the SQL dialog both statements will be processed and the Resultset
Options dialog will be shown for each statement. In the Resultset
Options dialog you can specify where to put the results of the query. Click OK
on the dialog to continue with the first statement. The same Resultset
Options dialog is shown but now for the second query. Note that the field
"Paste results in cell" is automatically set to 2 rows below the resultset of the first query. Click
OK to accept the default and there will be 2 resultsets in the active worksheet.
In order to provide a fully flexible system, at each stage in this process of executing multiple queries you can
decide to skip the Resultset dialog. There are 4 buttons
with which to influence the behavior of the Resultset dialog:
1- OK button:
The OK button will execute the current query and show the Resultset dialog for the next query. Use this button
to change the paste options for each query.
2- OK to all button:
This button will execute the current query. All subsequent queries will use the same options except for the start
cell where the pasting of the resultset will commence. The start cell is calculated automatically. SQL*XL will
skip 1 line by default before pasting a new resultset. This default behavior can be customised. In the SQL*XL settings
file, in the General section, you find options how many rows and columns to skip between 2 select statements by
default.
3- Skip button:
This button will discard the present SQL statement and continue with the next statement. The Resultset
Options dialog will be shown for the next select statement. This button can be used e.g. when too many rows
are fetched. This figure is optionally displayed on the Resultset
options dialog.
4- Skip all button:
This button effectively cancels the whole operation. The current SQL statement will be canceled as well as all
other pending statements.
When the user is sure the default settings on the Resultset dialog
are ok, on the SQL dialog it can be specified to skip the Resultset dialog
altogether. Use the "Prompt to specify options" list
and select the "Never" item to not be displayed the Resultset
options dialog.
Remember that in all SQL*XL dialogs the default settings can be altered in the preferences file.
See also: