Getting Started With the Oracle Database Plugin for Eclipse

Introduction
Using the Tool
Known Issues
Further Resources

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.

Note that screen shots and examples in this document use the HR schema that is installed by default with Oracle Database. You may download Oracle Database 10g Express Edition - Free Download.

2. Using the Tool

2.1. Database Explorer

The Database Explorer is the Datasource View provided by the DTP. It is used to create Database connections and to navigate the database.

2.1.1. Connecting to an Oracle Database

You can 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.

Figure 1. Opening Database Perspective

Right click on the Databases node in DSE and select New... to create a database connection, as Figure 2 shows.

Figure 2. New Database Connection

This displays the New Connection Profile wizard, as Figure 3 shows.

Figure 3. New Connection Profile Dialog

Select Oracle Database Connection from the list, and then click Next.
If "Oracle Database Connection" is not listed, restart Eclipse with -clean command option. Provide a name for the connection and proceed to the next step.

Complete the rest of the dialog as follows:

Select Oracle Database 10g Driver Default from the drop-down list of drivers.
Click Edit Driver Definition triangular button on the right, select the Jar List tab, then select the existing entry, and click Edit JAR/Zip. Browse to your eclipse directory, open the plugins folder, and select the oracle.eclipse.tools.database.jdbc.driver*.jar file.
Note that you need to do this only once.
If the defaults are not appropriate for your configuration, change the following information in the URL field:
- replace localhost with the hostname or IP address of the Oracle Database server.
- replace 1521 with the port number of the Oracle Database Listener service.
- replace xe with the Service Name (or SID) of the Database Service.
Provide the database user name and password for the connection.
Use the following optional properties if required:

autocommit=false
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.
sysdba=true
Use this property to login with SYSDBA account.
sysoper=true
Use this property to login with SYSOPER account.

Click Test Connection to test the connectivity.
Select Connect when the wizard completes checkbox to enable the database connection.
Select Finish to complete the wizard.

Figure 4. Database Connection Details

2.1.2. Exploring the Oracle Database

The open database connection allows you to navigate through the database objects.

Figure 5. Exploring the Database

2.1.3. Editing Data from a Table

With the enabled database connection, you can edit the table data in the Data Source Explorer (DSE).

To do so, navigate to the table you want to edit in the DSE, then right-click the table, and select Data > Edit, as Figure 7 shows.

Figure 7. Editing Database Table

This opens the table data in the editor, as Figure 8 shows. You can 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.

Figure 8. Database Table Editor

2.1.4. Loading Data into a Table

To load data into a table from a text file, 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, as Figure 9 show.

Figure 9. Loading Data

This opens the Load Data dialog, as Figure 10 shows. Complete fields on the dialog, and then click Finish.

Figure 10. Load Data Dialog

Note that this may fail if there is a foreign key violation.

2.1.5. Extracting Data from a Table

To extract data from a table to a text file, 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, as Figure 11 shows.

Figure 11. Extracting Data

This opens the Extract Data dialog, as Figure 12 shows. Complete fields on the dialog, and then click Finish.

Figure 12. Extract Data Dialog

2.1.6. Generating DDL

You can use the Generate DDL option on most database objects to create or drop the object.

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, as Figure 13 and Figure 14 show.

Note that you need to create a project in order to save the generated DDL script. See Creating a project for more details.

Figure 13. Generate DDL

Figure 14. Generating DDL

The preceding steps generate the following statements:

CREATE VIEW EMP_DETAILS_VIEW 
  (EMPLOYEE_ID, JOB_ID, MANAGER_ID, 
  DEPARTMENT_ID, LOCATION_ID, COUNTRY_ID, FIRST_NAME, LAST_NAME, 
  SALARY, COMMISSION_PCT, DEPARTMENT_NAME, JOB_TITLE, CITY, 
  STATE_PROVINCE, COUNTRY_NAME, REGION_NAME) 
AS
SELECT
  e.employee_id,
  e.job_id,
  e.manager_id,
  e.department_id,
  d.location_id,
  l.country_id,
  e.first_name,
  e.last_name,
  e.salary,
  e.commission_pct,
  d.department_name,
  j.job_title,
  l.city,
  l.state_province,
  c.country_name,
  r.region_name
FROM
  employees e,
  departments d,
  jobs j,
  locations l,
  countries c,
  regions r
WHERE e.department_id = d.department_id
  AND d.location_id = l.location_id
  AND l.country_id = c.country_id
  AND c.region_id = r.region_id
  AND j.job_id = e.job_id
WITH READ ONLY;

2.2. SQL Tools

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.

2.2.1. Using SQL Editor

To use an SQL Editor, do the following:

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, as Figure 15 shows.

Figure 15. Editing Stored Procedure

The procedure or function opens in the SQL Editor, as Figure 16 shows.

Figure 16. SQL Editor

The SQL Editor enables standard text-based editing of SQL statements, provides syntax color, and multiple statement support.

2.2.2. Executing a Stored Procedure or Function

To execute a stored procedure or function, do the following:

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.

The Save and Launch dialog appears, as Figure 17 shows. Click OK to save the procedure or function.

Figure 17. Save and Launch Procedure or Function Dialog

If the procedure or function has any input parameters, the Configure Parameters dialog appears, as Figure 18 shows. Enter input values and click OK to run the procedure or function.

Figure 18. Configure Parameters Dialog

2.2.3. Executing Explain Plans

To execute the explain plan, do the following:

In the Navigator or DSE, navigate to the script containing the SQL statement for which you want to execute an explain plan.
Highlight the script.
Right-click on the selection and select either Execute Text Explain Plan or Execute Graphic Explain Plan from the drop-down menu, as Figure 19 shows.

Figure 19. Execution Plan - Graphic Mode

For example, open the views.sql file that you created in the Generating DDL section. Highlight the SELECT statement block, as shown in Figure 19. 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, as Figure 20 demonstrates.

Figure 20. Execution Plan - Text Mode

2.3 Additional Features

There is a number of other features that you can explore using the tool.

2.3.1. Granting Privileges

To grant specific database privileges to a specific user, do the following:

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, as Figure 21 shows.

Figure 21. Granting Privileges

This will open the Grant Privileges dialog, as Figure 22 shows.

Figure 22. Grant Privileges Dialog

Select one of the privileges from the list and click OK.

To revoke specific database privileges from a specific user, do the following:

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, as Figure 23 shows.

Figure 23. Revoking Privileges

This will open the Revoke Privileges dialog, as Figure 24 shows.

Figure 24. Revoke Privileges Dialog

Select one of the privileges from the list and click OK.

3. Known Issues

Unable to sort folders.

This issue results in an inability to sort the nodes on Data Source Explorer (DSE) in an order that is consistent with how it is displayed in Oracle SQL Developer.

Schema content appear incomplete at different levels on DSE.

This issue results in certain nodes being not displayed when the schema is drilled down from the "Other Users" node.

Generate DDL action from higher levels on DSE causes Eclipse to freeze, occasionally.

When you right-clicks on a DSE node at higher level and select the Generate DDL option, the plugin tries to generate the DDL for the underlying database objects recursively. This can sometimes freeze Eclipse due to possible memory outage or contention. The recommendation is to generate the DDL from a Schema level or lower.

4. Further Resources