About Oracle Query Window
Oracle Query Window lets you enter, edit, execute, and view the results of SQL and PL/SQL statements. Additionally, you can perform an Explain Plan on a SQL statement to aid in performance tuning.
The Oracle Query Window consists of two panes: The top is an editor, and the bottom is an output window.
This section covers the following topics:
Opening Oracle Query Window
Oracle Query Window opens from any collection or object node, as follows:
From Server Explorer, expand a collection or object node, and from the menu, select Query Window. If you are opening from the Data Connection node, you need to right-click your named connection before selecting Query Window. Note that this menu is available only if the data connection has been established or opened previously.
When you open a query window from a Data Connection node, the query window internally uses its own connection, which is based on, but separate, from that of the Data Connection node. Once the query window is created, its connection properties are not changeable. The data connection window allows you to edit the data connection properties, but these changes do not affect the query window connection.
Executing SQL Statements Using Oracle Query Window
This section tells you how to execute SQL statements using Oracle Query Window.
This section includes these topics:
Entering a SQL Statement
You can paste or type a SQL or PL/SQL into Query Window.
You can also use drag-and-drop to automatically generate a query. Drag a Server Explorer Table or View node onto the Query Editor.
Using IntelliSense
IntelliSense offers smart suggestions and code completion for SQL and PL/SQL. As you type SQL or PL/SQL you can take utilize IntelliSense like so:
-
Press Control-Space to receive context sensitive suggestions for keywords and object names. You can select from the suggestions using arrow keys and the return key or by using the mouse.
-
Type a schema name or object name followed by a period (".") to receive suggestions. In the case of procedure or function names, the full signature of the procedure or function name will be outputted along with placeholder values.
-
After typing a procedure or function name, the left parenthesis ("(") will trigger parameter info to be displayed. The current parameter you are entering will be boldfaced.
-
Hover the mouse over variables and object names to display Quick Info about the variable or object name.
IntelliSense metadata is cached. If you have recently modified database objects, click the Rebuild Intellisense toolbar icon or menu item to receive up to date suggestions.
To change the casing of suggestions, for example to receive suggestions in lower case, see the IntelliSense Settings Options Page.
Executing a SQL Statement
To execute a SQL or PL/SQL statement in the Query Window:
-
If you have multiple SQL statements and want to run one of them, highlight the SQL statement you want to execute. (Otherwise, Oracle Query Window will run the last SQL statement.)
-
Click Execute, either in the Oracle Query Window or the toolbar.
The Visual Studio status bar at the bottom of the screen displays the status of the SQL execution: Ready, Executing, and Cancelling. The status bar also displays a progress bar to indicate the estimated progress of a task, and shows the line and column position of the current cursor in the Query Window.
Afterwards, Query Output appears, if not already visible, showing the results. Query Output displays the following:
-
The SQL statement that was executed.
-
Query results in either a grid or text format. The default is grid format.
-
Any errors
-
All output that comes from the PL/SQL
DBMS_OUTPUT.PUT_LINEor similar method. -
The number of rows affected by your SQL statement.
-
For UDT column data, the actual data representation is in XML format and includes only the level of nestedness.
You can restrict the number of rows fetched and display row numbers in the General Options Page.
Executing Multiple SQL Statements
Oracle Query Window can process multiple SQL statements and PL/SQL anonymous blocks.
You must select (highlight) a block of commands. If there are multiple commands in the Query Window, but no command is selected, then only the last command is executed.
Enabling and Disabling Autocommit
By default, each statement in the Query Window is automatically committed.
You can disable autocommit mode by clicking AutoCommit Mode in the Oracle Query Toolbar. AutoCommit Mode is a toggle button with a check or an x indicating enabled or disabled.
When autocommit is disabled, you can explicitly execute COMMIT or ROLLBACK statements.
To commit or rollback, use either the Oracle Query Toolbar or the Query Editor as follows:
Oracle Query Toolbar: Click on Commit to commit all DML statements executed since the last commit. Click on Rollback to rollback all changes made using DML statements since the last commit was issued.
Oracle Query Editor: You can type COMMIT or ROLLBACK as statements and then select Execute to execute the statement.
Using Bind Parameters with SQL Statements or PL/SQL blocks
You can include one or more bind variable placeholders in your SQL statements or PL/SQL blocks. A parameter placeholder is identified by a colon followed by any arbitrary identifier (for example, :ID or :1 ). When parameterized SQL or PL/SQL is executed, the Query Parameters Dialog opens, allowing you to enter the corresponding values. For more information, see Query Parameters Dialog.
Displaying the Output from PL/SQL Blocks and Subprograms
The Oracle Query Window can display the output from PL/SQL blocks and subprograms using the PUT_LINE, NEW_LINE, and PUT methods from the DBMS_OUTPUT package. By default, not all lines are displayed; to display the lines on screen, you must use the DBMS_OUTPUT.ENABLE method. Performing this step is only necessary once after opening the query window. For example:
CREATE OR REPLACE PROCEDURE list_tables AS
BEGIN
dbms_output.enable;
dbms_output.put_line('These are the tables you own:');
...
For more information on the DBMS_OUTPUT package command, refer to Oracle Database Advanced Application Developer's Guide and Oracle Database SQL Language Reference.