Editing & Executing SQL
The Database Explorer tool set includes a smart SQL Editor. The SQL Editor can be associated with an open database connection that enables it to perform code completion of SQL statements and for executing SQL snippets. In this tutorial, you will learn to:
- Enable and use SQL code completion
- Execute a SQL statement
- Generate SQL queries and table creation scripts
- Change the SQL command separator
This feature is available in MyEclipse.
1. Enabling SQL Code Completion
The SQL code completion features requires the metadata of an editor’s associated database to be preloaded. By default, SQL code completion is disabled to avoid severe performance problems that might result from reading the metadata of very large database or retrieving this information over very low bandwidth database connections. If you prefer the help of code completion, you can enable this feature in the preferences.
- Select Window>Preferences, expand MyEclipse>Database Explorer, and select Performance Optimization.
- Select the Load all database metadata when connection is established checkbox, and click OK.
Setting metadata load option to enable code completion
2. Using SQL Code Completion
- Select File>New>SQL File.
Opening new SQL file in the editor
- Select the project folder in which you want to save the new SQL file, name the file, and click Finish.
Selecting project folder for the SQL file
- In the SQL editor, select the database connection to which you want to associate the file.
Note: This step is required for code completion and snippet execution to function correctly.
Associating database connection to SQL file
Now, you can type in SQL statements, taking advantage of code assist (CTRL+Space) in each section of your SQL as demonstrated in the figures below.
Standard SQL syntax code assistance
Code assist for schema names
Code assist for table names
Completed SQL statement
Tip: A useful SQL editor feature is synchronizing the DB Browser with the context of the SQL editor. In the editor, press CTRL while selecting a table name. This positions the DB Browser on the selected table.
3. Executing SQL Statements
Execute a SQL statement by clicking or pressing CTRL + F9. The action executes the selected snippet. In the case no statements are selected, as in the following example, the entire SQL Editor text is executed. The query results appear in the SQL Results view.
Results of executing a SQL query
4. Using SQL Generation Utilities
Database Explorer tools provide two SQL generation utilities, a table creation script generator and an example SQL query generator.
To generate a Data Definition Language (DDL) script for an existing table, right-click the table in the DB Browser, and select Generate>DDL.
Note: Subscribers at the Professional level also have an advanced DDL generation capability when working with Oracle.
Creating a table script
This generates a DDL script for recreating the selected table and displays it in the SQL editor.
Newly created table script DDL
To generate an example
SELECT statement for a table, right-click the table in the DB Browser, and select Generate>Select Statement.
Generating SQL select statement from table
This generates a
SELECT * statement and displays it in a SQL editor.
New SELECT statement in SQL editor
5. Changing the Command Separator
The default command separator is the semicolon (;). This implies that all SQL commands and queries are terminated by the ; character.
However, in more complex scripts, the ; is often used as an internal separator while other strings are used as the command separator. Such scripts do not execute correctly when the semicolon is the command separator.
You can change the command separator by using the SQL editor’s Cmd Sep drop-down. Select from an existing set of separators, or you type in your own.
Selecting a command separator
Note: The Cmd Sep drop-down is not available for Oracle as queries to Oracle are internally managed by the editor’s parser.