Editing & Executing SQL in MyEclipse
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
Duration Time: 10 Minutes
Don't have MyEclipse? Download Now
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.
To optimize metadata retrieval, consider using a schema filter. See Schema Filtering to learn more.
2. Using SQL Code Completion
- Select File>New>SQL File.
- Select the project folder in which you want to save the new SQL file, name the file, and click Finish.
- 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.
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.
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.
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.
This generates a DDL script for recreating the selected table and displays it in the SQL editor.
To generate an example SELECT statement for a table, right-click the table in the DB Browser, and select Generate>Select Statement.
This generates a SELECT * statement and displays it in a 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.
Note: The Cmd Sep drop-down is not available for Oracle as queries to Oracle are internally managed by the editor’s parser.