Getting Started With the Oracle Database Plugin
for Eclipse DTP
Created: June 2007
- Introduction
- Preparing the Eclipse
Environment
- Downloading Eclipse
- Installing
the Eclipse Data Tools platform using Update Manager
- Installing the Oracle Database
Plugin for Eclipse DTP Using the Zipped Bundle
- Disabling
or uninstalling the Oracle Database Plugin for Eclipse
- Using the Tool
- Database
Explorer
- Connecting
to an Oracle Database
- Exploring
an Oracle Database
- Editing
Data from a Table
- Loading
Data into a Table
- Extracting
Data from a Table
- Generating
DDL
- SQL Tools
- SQL Editor
- Executing
a Stored Procedure or Function
- Executing
Execution Plan
- Known Issues
- Further Resources
1. Introduction
Welcome to the first step towards supporting the
Eclipse Data Tool Platform (DTP) by Oracle. The purpose of this document
is to guide you through installing the Oracle Database Plugin for Eclipse
DTP. It also provides a high level tour through each of the Oracle Database
Plugin features. 2. Preparing
the Eclipse Environment
2.1. Downloading Eclipse
Download Eclipse version 3.2 from Eclipse
downloads.
Note: Refer to the Install/Update
section (for Eclipse 3.2) or Network
Connections section (for Eclipse 3.3) of Eclipse help topics on how-to
configure proxy settings.
2.2. Installing
the Eclipse Data Tools platform using Update Manager
The Data Tools Platform and its dependencies are
required. Follow the steps below to install these plugins.
- Start Eclipse
- Select Help->Software Updates->Find
and Install from the Main menu.
- Select "Search for new features to install"
and then click Next.
- Add the following URLs as remote sites using "New
Remote Site..." option.
- http://download.eclipse.org/datatools/updates
Figure 1. Add new update sites
- Click the checkbox to select the Discovery
Site and the newly added site.
Select "Automatically select mirrors" to let the wizard
automatically select the mirror sites for download.
- Click "Finish" to search for features
available from these sites.
- In the Search Results dialog, expand the Discovery
Site and newly added site (in step 4 above) and use the check box to
select Eclipse Data Tools Platform.
- Click the Select Required button
to let the wizard select the dependencies automatically.
Oracle Database Plugin for Eclipse
DTP 1.0 is tested against the following versions of dependant DTP
plugins:
- Data Tools Platform Connectivity 1.0.0
- Data Tools Platform Open Data Access Designer
1.0.0
- Data Tools Platform Open Data Access Runtime
1.0.0
- Data Tools Platform Documentation 1.0.0
- Data Tools Platform Enablement 1.0.0
- Data Tools Platform ODA XML UI Enablement 1.0.0
- Data Tools Platform ODA XML Enablement 1.0.0
- Data Tools Platform Intro 1.0.0
- Data Tools Platform Model Base 1.0.0
- Data Tools Platform SDK 1.0.0
- Data Tools Platform SQL Development Tools 1.0.0
- Continue the installation and restart Eclipse when
prompted.
2.3 Installing the Oracle Database Plugin
for Eclipse DTP Using the Zipped Bundle
The plugin can be installed by downloading the zip
file and extracting it into the Eclipse installation directory (For
e.g. C:\eclipse (windows) or /home/scott/eclipse (unix)). Restart Eclipse
for changes to take effect.
2.4. Disabling or Uninstalling
the Oracle Database Plugin for Eclipse DTP
The Oracle Database Plugin can be disabled or uninstalled
using the Manage Configuration interface. See Enabling,
disabling, and uninstalling features in Workbench User Guide for details.
To completely remove the plugins, using either
Windows Explorer (on Windows) or a command shell (for e.g. bash on unix)
goto eclipse directory and delete recursively features/oracle.dbtools*
and plugins/oracle.dbtools*. This will remove all directories and jar
files installed by this plugin.
3. Using the Tool
3.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.
3.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  Oracle
Database 10g Express Edition - Free Download.
Figure 4. 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 5.
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 6.
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 7. Database
Connection Details
Complete the rest of the dialog as follows:
- Select Oracle Database 10g Driver Default from the
drop-down list of drivers.
- 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.
- 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.
- sysdba=true
Use this property to login with SYSDBA role.
- 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).
Use the database connection
to connect to the database. You can then navigate through the database objects.
Figure 8. 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 9.
Explore Database
You can edit the table data using the database connection
in the Data Source Explorer (DSE). 
Figure 10. 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 11.
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.
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.
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.
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 12.
Generate DDL
Figure 13. 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;
3.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. 3.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 14. 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 15. SQL Editor - Content Assist
3.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 16. Configure Parameters
3.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 17. 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 18. 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.
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 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
|
Bookmark
