About SQL and PL/SQL File Editor

SQL and PL/SQL File Editor 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 SQL and PL/SQL File Editor consists of an editor window and a separate results window.

This section covers the following topics:

Opening SQL and PL/SQL File Editor

To open the SQL and PL/SQL File Editor, right click on a connection node in Server Explorer and from the menu select Open New SQL File or Open Existing SQL File. The opened file will be associated with the Oracle Database Connection as indicated in the SQL and PL/SQL File Editor toolbar.

You can also open files containing SQL using the Visual Studio File->Open->File menu, or by clicking on files located in a Visual Studio solution.

Note:

Files opened using the Visual Studio File menu or the Visual Studio solutions will not be associated with a Server Explorer Oracle connection and therefore you will need to use the connection dropdown in the SQL and PL/SQL File Editor toolbar to associate the file with an Oracle connection.

Only files with certain file extensions can be opened using the Visual Studio File->Open->File menu, or by clicking on files located in a (non Oracle Database Project) Visual Studio solution. The file extensions that can be opened using those methods are those associated with the SQL and PL/SQL File Editor:

  • .plsql

  • .pls

  • .bdy

  • .fnc

  • .pck

  • .plb

  • .pkb

  • .pkh

  • .pks

  • .prc

  • .spc

  • .trg

  • .osql

Note:

Files with the extension .sql or other files with extensions not in the list above must be opened using the Open New SQL File, Open Existing SQL File menus or by clicking on a file in Oracle Database Project or Oracle Database Project v2. They cannot be opened using Visual Studio File->Open menus, or by clicking on files located in a (non-Oracle Database Project) Visual Studio Solution.

Executing SQL Statements Using SQL and PL/SQL File Editor

This section tells you how to execute SQL statements using SQL and PL/SQL File Editor.

This section includes these topics:

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:

  • As you type, IntelliSense suggestions will automatically appear. You may also press Control+spacebar to obtain suggestions. 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 SQL and PL/SQL File Editor:

  1. If you have multiple SQL statements and want to run one of them, place the cursor on the line containing the SQL you wish to execute.

  2. Click Execute, either in the menu or the toolbar.

Afterwards, a results window appears, if not already visible, showing the results. The results window displays the following:

  • The SQL statement that was executed.

  • Query results in grid format.

  • Any errors

You can customize the behavior of the results window in the Query Execution Settings.

Executing Multiple SQL Statements

SQL and PL/SQL File Editor can process an entire script file or multiple selected SQL statements and PL/SQL calls.

To execute an entire SQL script file, right click in the editor and select Execute All from the menu or click the Execute All toolbar icon. To execute multiple selected SQL statements, highlight the SQL or PL/SQL statements that you wish to execute and then right click in the editor and select Execute from the menu or click the Execute toolbar icon.

Enabling and Disabling Autocommit

To enable or disable autocommit, go to the Query Execution Settings and check or uncheck Commit each SQL statement executed

When autocommit is disabled, you will need to explicitly execute COMMIT or ROLLBACK statements.

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.