Getting Started With the Oracle Database Plugin for Eclipse DTP

  1. Introduction
  2. Using the Tool
    1. Database Explorer
      1. Connecting to an Oracle Database
      2. Exploring an Oracle Database
      3. Editing Data from a Table
      4. Loading Data into a Table
      5. Extracting Data from a Table
      6. Generating DDL
    2. SQL Tools
      1. SQL Editor
      2. Executing a Stored Procedure or Function
      3. Executing Execution Plan
  3. Known Issues
  4. Further Resources

1. Introduction

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.

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. Use the database connection to connect and explore to the database. Screen shots and examples in this document use the HR schema that is installed by default with Oracle Database.
Note:You may download Free DownloadOracle Database 10g Express Edition - Free Download.



Figure 1. Open Database Perspective

To open the Database Development perspective, click Windows->Open Perspective from the Main menu. This perspective opens the Data Source Explorer (DSE) view.



Figure 2. New Database Connection

Right click on the Databases node in DSE and select New... to create a database connection. This displays the New Connection Profile wizard dialog.



Figure 3. Select Connection Profile

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



Figure 4. Database Connection Details

Complete the rest of the dialog as follows:

  • Select Oracle Database 10g Driver Default from the drop-down list of drivers.
  • Click on the Edit Driver Definition button, go to the Jar List page, select the existing entry and click Edit JAR/Zip. Now browse to your eclipse directory, open the plugins folder and select the jar file named oracle.dbtools.dtp.jdbc.driver*.jar. You need to do this only once.
  • 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.
  • The following optional properties can be used as required.
    Note: These properties are not case sensitive.
    1. autocommit=false
      By default autocommit is set to true; Setting this property to true will result immediate commit of the operations from SQL Editor. When set to false explicit COMMIT need to be executed to commit the changes. See Oracle Database SQL Reference for more details.
    2. sysdba=true
      Use this property to login with SYSDBA role.
    3. sysoper=true
      Use this property to login with SYSOPER role.
  • Click Test Connection to test the connectivity.
  • Select Finish to complete the wizard.

This creates a connection with the specified name on the Data Source Explorer (DSE).

2.1.2. Exploring the Oracle Database

Use the database connection to connect to the database. You can then navigate through the database objects.



Figure 5. Connect to Database

Right click on the connection node on the DSE and select Connect. This opens a connection to the database. By default, only the schema associated to the logged in user is listed. To list the remaining, right click on the connection node, and select Properties from the popup menu. This will open the Properties dialog box. Select Default Schema Filter on the left pane. On the right pane, either check "Disable filter" or modify the Expression or Selection to select the schemas to be listed. Use the navigation tree to drill down through the database objects.



Figure 6. Explore Database

2.1.3. Editing Data from a Table

You can edit the table data using the database connection in the Data Source Explorer (DSE).



Figure 7. Edit Database Table

Navigate to the table you want to edit in the DSE. Right click the table and select Data->Edit. The table data is opened in the editor as shown in the Figure 11.



Figure 8. Database Table Editor

You can make changes to the table data using the popup menu. When you have finished editing, click Save to save the table data into the database.

2.1.4. Loading Data into a Table

In the DSE, navigate to the table into which you want to load data. Right click the table and select Data->Load to load data from a text file. This may fail if there is a foreign key violation.

2.1.5. Extracting Data from a Table

In the DSE, navigate to the table from which you want to extract data. Right click the table and select Data->Extract to extract table data to a text file.

2.1.6. Generating DDL

You can use 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 to create a DDL script. (See Figure 12 and Figure 13)



Figure 9. Generate DDL

Figure 10. Generating DDL

Note: You need to create a project, to save the generate DDL script. See Creating a project for more details.

The steps above 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

The SQL Tools enable you to edit and run Stored Procedures and Functions, and to execute Explain Plans in either Graphic or text mode.

2.2.1. SQL Editor

In the DSE, navigate to the procedure or function you want to edit. Right click the procedure or function and select Edit. The procedure or function opens in the SQL Editor.



Figure 11. Edit Procedure

The SQL Editor enables standard text-based editing of SQL statements with the added functionality of Content Assist, provides syntax color, and multiple statement support. Content Assist presents a list of available syntax options when you edit SQL statements.



Figure 12. SQL Editor - Content Assist

2.2.2. Executing 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. 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.



Figure 13. Configure Parameters

2.2.3. Executing Execution Plans

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 and right click and select either Execute Text Explain Plan or Execute Graphic Explain Plan.



Figure 14. Execution Plan - Graphic mode

For example, open the views.sql file created in the Generating DDL section. Highlight the SELECT statement block as in Figure 17. Right-click and select Execute Graphic Explain Plan. This opens the execution plan in graphic mode in the Execution Plan view.



Figure 15. Execution Plan - Text mode

Alternatively, if you select Execute Text Explain Plan, it results in a text version of the Execution Plan as shown in Figure 18.

3. Known Issues

  1. 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.

  2. 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.

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

    When the user right clicks on a DSE node at higher level and selects 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