| Oracle® Enterprise Pack for Eclipse User's Guide Release 12.1.3 E53428-01 |
|
 Previous |
 Next |
OEPE's database support lets you easily connect to, create, explore, and query Oracle databases. Support includes database visualization through the Data Source Explorer view 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.
The Database Explorer is the Datasource View provided by the DTP. 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.
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:
Select either 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 localhost).
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:
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. 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.
To edit a table data:
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.
To load 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 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.
To extract the data:
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.
To generate DDL:
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.
To use a SQL Editor:
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.
To execute the explain plan:
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 null.
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 |
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 your database schema using SQL Schema Viewer that displays tables and relationships between them.
The viewer 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.
To use the SQL Schema Viewer:
Click Window > Show View > Data Source Explorer from the top-level menu. This will open the Data Source Explorer view.
Set up and configure a database connection.
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 select Show in Schema Viewer. This will open the schema viewer displaying the schema.
Notice that if you open the viewer from a table, this table will be selected in the viewer (similar to the EMPLOYEE table.
Using the schema viewer, you can do the following:
Select tables, columns, and relationships by clicking on their representations.
Highlight tables, columns, and relationships by mousing over them.
Display the tool-tip text containing properties by mousing over the table header or column.
Zoom in and out of the display by using either a combo box located on the viewer menu, or a menu that pops up upon the right click.
Export the schema diagram to the image by clicking on the corresponding icon located on the viewer menu.
Print the schema diagram by clicking on the corresponding icon located on the viewer menu.
Edit SQL statements by clicking on the corresponding icon located on the viewer menu. Note that the SQL Scrapbook opens showing the corresponding connection profile.
|
 Copyright © 2008, 2014, Oracle and/or its affiliates. All rights reserved. Legal Notices |
|