Editing & Executing SQL

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.

1. Select Window>Preferences, expand MyEclipse>Database Explorer, and select Performance Optimization.
2. Select the Load all database metadata when connection is established checkbox, and click OK.
   
   
   Setting metadata load option to enable code completion
   
   

To optimize metadata retrieval, consider using a schema filter. See Schema Filtering to learn more.

2. Using SQL Code Completion

1. Select File>New>SQL File.
   
   
   Opening new SQL file in the editor
   
   
2. 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
   
   
3. 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.