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 http://www.oracle.com/technetwork/database/express-edition/overview/index.html
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.
Figure 9-1 Data Source Explorer
You can create a connection to a database.
To create a connection to a database using the Data Source Explorer view:
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.
To edit a table data:
You can load data from a text file into a table using the DSE.
To load data into a table:
To extract data from a table to a text file using the DSE.
To extract the data:
You can use the Generate DDL option on most database objects to create or drop the object.
To generate DDL:
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.
To use a SQL Editor:
You can execute stored procedures and functions.
To execute a stored procedure or function:
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:
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:
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 TABLE
statement is executed. If any of the ALTER TABLE
statements 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.
Figure 9-2 Database Objects in RDB Schema Editor
Before you can use the RDB Schema Editor you must set up and configure a database connection. For more information, see Creating a Connection to a Database.
You can open the editor from:
A schema
A table in the schema. In this case, the table is selected in the editor when it opens.
To open the RDB Schema Editor:
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.
Figure 9-3 Table Editing Tools
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.
Show guides.
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.