OEPE's database support lets you easily connect to, create, explore, and query Oracle databases. Support includes a graphic editor for SQL schemas, and DDL generation.
This document contains the following sections:
Welcome to the first step towards supporting the Eclipse Data Tool Platform (DTP) by Oracle. This document provides a high-level tour through each of the Oracle Database Plugin features.
If you are new to using databases with OEPE, one of the easiest ways to get started is to try out Oracle Database 11g Express Edition (Oracle Database XE). Oracle Database XE is an entry-level, small-footprint database based on the Oracle Database 11g Release 2 code base. It is free to develop, deploy, and distribute; fast to download; and simple to administer. You can download it from Getting Started: Oracle Database Express Edition (XE), which is available at
The Eclipse view that you use to work with databases is the Data Source Explorer provided by the DTP, shown in Figure 9-1. You use it to create database connections and to navigate the database.
You can create a connection to a database.
To create a connection to a database using the Data Source Explorer view:
To open the Database Development perspective, click Windows > Open Perspective > Other from the Main menu, and then select Database Development from the Open Perspective dialog. This perspective opens the Data Source Explorer (DSE) view.
You can also open the Open Perspective dialog by clicking .
Right click on the Databases node in DSE and select New to create a database connection.
Select Oracle Database Connection from the list and provide a name for the connection. Click Next to proceed to the next step.
Complete the rest of the dialog as follows:
Oracle Database 10g Driver Default or
Oracle Database 11g Driver Default from the drop-down list of drivers.
If the default settings are not appropriate for your configuration, change the following information on the Properties > General tab of the New Connection Profile dialog:
In the SID field, replace
xe with the Service Name (or SID) of the Database Service.
In the Host field, replace server with the hostname or IP address of the Oracle Database server (possibly
In the Port number field, replace 1521 with the port number of the Oracle Database Listener service.
Provide the database user name and password for the connection.
Note that the Connection URL field reflects your changes.
Select a use group in the Catalog field.
On the Properties > Optional tab, set the following optional properties, if required:
By default, autocommit is set to true, which results in immediate commit of the operations from SQL Editor. When set to false, you need to execute the explicit COMMIT to commit the changes. See Oracle Database SQL Reference for more information.
Use this property to login with SYSDBA account.
Use this property to login with SYSOPER account.
Click Test Connection to test the connectivity. If this fails, try restarting Eclipse with -clean command option.
Select Connect when the wizard completes checkbox to enable the database connection.
Select Finish to complete the wizard.
The open database connection allows you to navigate through the database objects.
With the enabled database connection, you can edit the table data in the Data Source Explorer (DSE). Note that, for comprehensive editing capabilities, it is recommended that you install Data Tools Platform SQL Development Tools Data Functions feature.
To install the Data Tools Platform SQL Development Tools Data Functions feature:
If your machine is located inside of a network, which requires a proxy to access outside resource such as the Internet, the configuration (download) may fail due to the fact that Eclipse IDE includes a Web browser to let you access the Internet from within the IDE. In this case, reconfigure your Eclipse IDE proxy settings using Window > Preferences > General > Network Connections.
Select Help > Software Updates from the main menu.
Open the Available Software tab on the Software Updates and Add-ons dialog.
Click Add Site to install the feature provided by OEPE from OEPE's update site. To do so, enter
http://www.oracle.com/technology/software/products/oepe/oepe_12g.html in the Location field of the Add Site dialog, and then click OK.
Expand the Ganymede Update Site node, and then expand the Database Development node. Select Data Tools Platform SQL Development Tools Data Functions, and then click Install.
Navigate to the table you want to edit in the DSE, then right-click the table, and select Data > Edit. This opens the table data in the editor.
Make changes to the table data by right-clicking on a table cell and using the popup menu. When you have finished editing, click Save to save the changes to the database.
You can load data from a text file into a table using the DSE.
In the DSE, navigate to the table into which you want to load data. Right-click the table, and select Data > Load from the drop-down menu This opens the Load Data dialog.
Complete fields on the dialog, and then click Finish. Validation is performed before the data is loaded.
To extract data from a table to a text file using the DSE.
In the DSE, navigate to the table from which you want to extract data. Right-click the table and select Data > Extract from the drop-down menu. This opens the Extract Data dialog, as Figure 11 shows. Complete fields on the dialog, and then click Finish.
Complete fields on the dialog, and then click Finish.
You can use the Generate DDL option on most database objects to create or drop the object.
If necessary, create a project in order to save the generated DDL Script.
In the DSE, navigate to the object you want to create or drop, right-click the object, and select Generate DDL from the drop-down menu to create a DDL script.
The DDL is generated.
SQL Tools enable you to edit and run stored procedures and functions, as well as execute the so-called explain plans in either graphic or text mode.
The SQL Editor enables standard text-based editing of SQL statements, provides syntax color, and multiple statement support.
In the DSE, navigate to the procedure or function you want to edit.
Right-click the procedure or function and select Edit from the drop-down menu. The procedure or function opens in the SQL Editor.
You can execute stored procedures and functions.
To execute a stored procedure or function:
In the DSE, navigate to the procedure or function you want to run.
Right-click the procedure or function and select Run from the drop-down menu.
If the procedure or function has any input parameters, the Configure Parameters dialog appears.
Enter input values and click OK to run the procedure or function.
You can use explain plans to optimize your code.
In the Navigator or DSE, navigate to the script containing the SQL statement for which you want to execute an explain plan.
Right-click on the script and select either Execute Text Explain Plan or Execute Graphic Explain Plan from the drop-down menu.
For example, open the
views.sql file that you created in the Generating DDL section. Highlight the SELECT statement block. Right-click and select Execute Graphic Explain Plan from the drop-down menu. This opens the execution plan in graphic mode in the Execution Plan view.
Alternatively, if you select Execute Text Explain Plan from the drop-down menu, it will result in a text version of the execution plan.
To grant specific database privileges to a specific user:
In the DSE, navigate to the element (such as a table, for example) for which you want the user to have certain privileges.
Right-click the element and select Grant Privileges from the drop-down menu. This opens the Grant Privileges dialog.
Select one of the privileges from the list and click OK.
To revoke specific database privileges from a specific user:
In the DSE, navigate to the element (such as a table, for example) for which you want to revoke the user privileges.
Right-click the element and select Revoke Privileges from the drop-down menu. This opens the Revoke Privileges dialog.
Select one of the privileges from the list and click OK.
Using the tool, you can create new database tables by declaring new columns, defining primary, unique, and foreign keys, as well as adding checked constraints and indices.
To create a new database table, follow this procedure:
In the DSE, navigate to the Tables element.
Right-click the element and select New Table from the drop-down menu. This opens the Create Table dialog.
Even though by default the table is created using the schema of the user who established the connection, you can change the database schema. If the user who established the connection does not have privileges to create a new table in another schema, the SQL Result view will display an error message after the wizard completes.
Using the Create Table dialog, specify the following:
The name for your table.
One or more table columns: to define a column, click Add, and then provide the column's name, select the type using the Type dialog, and specify whether or not the value can be
You can reorder the table columns using the Move Up and Move Down buttons.
Click Next on the Create Table dialog to open the Create Table > Primary Key dialog. On this dialog, select Add primary key, and then define the name and select one or more columns that make up the primary key. If a column type is not suitable as a primary key column, a validation error will be displayed. Proceed by clicking Next.
On the next Create Table > Unique Constraints dialog, define unique constraints for the table by clicking Add. When defined, click Next to proceed.
There are three types of validation for the unique constraints:
Each column must be suitable for unique constraints. For example, a column of type CLOB is not suitable as a column in unique constraints.
A unique constraint must not contain the same columns as the primary key.
A unique constraint must not contain the same columns as another unique constraint.
On the next Create Table > Foreign Keys dialog, define foreign keys for the table by clicking Add.
Note:Foreign keys can reference the primary key of a table which resides in another schema. You can switch schemas by clicking Browse for the Referenced schema field. By clicking Browse for the Referenced table field you can switch to different tables in the referenced schema. When the referenced table is selected, the wizard will look up its primary key and fill the Referenced constraint field if the primary key exist. It will also try to match the columns in the referenced table with columns in the new table based on their types. Matched columns will show in the Associations table. If there are multiple columns that match a column in the referenced table, the wizard will choose the first one it finds, which you can change.
When finished, click Next.
Using the next Create Table > Check Constraints dialog, add checked constraints for the new table by clicking Add.
Note:The Condition field is represented by a free-formatted text area. When using it, you have to ensure that the text for the checked constraints conforms to PL/SQL syntax. Otherwise, you will not be able to set the condition successfully.
When finished, click Next.
Use the next Create Table > Indices dialog to add create indices for the new table by clicking Add, and then click Next.
The next Create Table > DDL dialog shows summarizes the DDL used to create the new table. You can make changes to the DDL before it is executed. Note that if you do make changes to the DDL, and then go back to previous dialogs and decide to add a new column, then your changes to the DDL will be lost and a new DDL will be generated.
Click Finish to complete the wizard.
Upon completion, the table creation DDL is sent to the JDBC driver and executed there. If the execution is successful, a new table with all the specified constraints will be created. You can examine the result of new table creation from the SQL Result view that opens.
Note:The table creation DDL is not executed as a single SQL statement. For any constraint, an
ALTER TABLEstatement is executed. If any of the
ALTER TABLEstatements fails, you have the option on whether or not to continue to execute subsequent statements.
If the newly created table is not displayed in the Tables node in Data Source Explorer, you will need to refresh the view.
OEPE allows you to examine and edit your database schema using the RDB Schema Editor that displays tables and the relationships between them, as shown in Figure 9-2.
The editor displays tables as table nodes. Each node lists all the columns in a table and shows column data types. The node also provides primary and foreign key indicators in a form of icons. Foreign key relationships between tables are represented by links in a form of arrows.
Before you can use the RDB Schema Editor you must set up and configure a database connection. For more information, see Section 18.104.22.168, "Creating a Connection to a Database."
You can open the editor from:
A table in the schema. In this case, the table is selected in the editor when it opens.
To open the RDB Schema Editor:
Choose Window > Show View > Data Source Explorer from the top-level menu. This opens the Data Source Explorer view.
Expand the database connection node to the database, and then to the schema or a particular table in the schema.
Right-click on the schema or table name, and choose edit. OEPE builds a graphical model for the database schema and opens the editor to display the tables in the schema and their relationships.
The schema editor gives you a visual view of your database schema and the relationships between the tables. In the editor, you can:
Manipulate the layout of the schema in the editor. Select one table using the Select tool in the Palette, or a group of tables by selecting the Marquee tool in the Palette and drawing around the tables you want.
Move tables by selecting one or more of them and dragging to a new position. Move foreign keys by clicking on the line to create a bend point, then drag the bend point to the new location.
When the editor is saved, the layout is also saved so that the next time the editor is launched from the same schema the saved layout is used.
When you select a single table some editing tools become available, as shown in Figure 9-3.
To add a column, click + and edit the new column entry in the table.
To delete a column, select it and click X.
To show the table properties, click Show in Properties View. The Properties window, which opens by default under the schema editor, displays detailed information about the selected table, column or foreign key. You can edit any fields in the Properties window.
To save changes to the table, click Save.
Edit table names, column names, column types and other column constraints such as size and scale. When the editor is saved, all the tables on the diagram are compared against the database. Where there have been changes to tables, the database version of the table is dropped and recreated using the version in the editor.
Edit table names, column names, column types and other column constraints such as size and scale. When the editor is saved, all the tables on the diagram are compared against the database. If a table is found to be different than what is in the database, either:
An ALTER TABLE statement is used to update the table in the database.
If that is not appropriate, for example, when columns are reordered, the table will be dropped and recreated. OEPE will warn you if the table being dropped contains data.
You can create foreign keys using (Foreign Key) in the Palette. Click on the source table then on the destination table. The Define Foreign Key wizard opens where you can specify the column mappings. When you click Finish in the wizard, the foreign key is created.
You can drop foreign keys by choosing the Select tool from the Palette, selecting the foreign key and pressing delete.
The context menu of the editor allows you to perform the following:
Refresh the schema in the editor.
Add a new table.
Select all tables.
Select all nodes.
Show a grid.
Automatically adjust the layout horizontally or vertically.
Zoom in, zoom out, or zoom to the actual size.
Save the layout as a PNG image file.
Send the layout to a printer.