In this tutorial, you will create an application and a project in which to organize your database files. You connect to a database and create a new user, then work in Oracle JDeveloper to create an offline database that you will reverse-engineer.
show more or lessRead more...

JDeveloper should be already installed and you should have access to an Oracle 11g database with DBA grants.

Purpose Duration Application
This tutorial shows you how to create a database diagram with database components. You'll see how to create a database user, reconcile the offline definitions with the current database definitions, and generate a DDL reconciliation script. Finally you reverse-engineer database objects.
To see the complete application you will create, click the Download button to download a zip of the final application. Unzip it into your JDeveloper folder.
40 minutes Download applicationname.zip
Step 1: Create a New Application and Project

The JDeveloper application is the highest level in the organizational structure. It stores information about the objects you are working with, while you are creating your application. It keeps track of your projects and the environment settings while you are developing.

  1. Start JDeveloper by selecting Start > All Programs > Oracle Fusion Middleware 11.1.2 > JDeveloper Studio 11.1.2

  2. If prompted for a Role, choose Database Developer and click OK.

    Select Role dialog at startup
  3. Close the Tip of the Day window.

    Once loaded, the JDeveloper IDE appears. Show more or lessRead more...
    The very first time you open JDeveloper, the Start Page displays. Notice the various options available to help you learn about JDeveloper. After exploring these options, click the X on the Start Page tab to close it. (the X appears when you mouse over the tab).
    You can re-invoke the Start Page later by choosing Help | Start Page.

    JDeveloper Start Page

  4. In the Applications Navigator, click New Application.

    New Application option
  5. In the New Gallery, select General | Applications and choose Database Application.

    The New Gallery

    Click OK.

  6. In the Create Database Application dialog, change the Application Name from Application1 to CreateDBObjects and type oracle as the Application Package Prefix. Notice that the Directory Name automatically changes to match the new Application Name.

    The Create Database Application dialog

    Click Next.

  7. In Step 2 of the Wizard enter NewSchema as the Project Name.

    The Create Database Application dialog

    Click Finish.

    When you work in JDeveloper, you organize your work in projects within applications. Show more or lessRead more...

    JDeveloper provides a number of predefined templates which enable you to create applications and projects that are configured for developing different types of applications.

    The templates provide the environment for the basic range of technologies supported by JDeveloper. You create your working environment by selecting the template that best fits your needs and then configuring it to add any additional technologies you intend to use.

    The options available to you in the New Gallery, and for some context menu operations, depend on your template selection for that application.
  8. The Application Navigator should look like the following:

    The Application Navigator
    In the Application Navigator, Projects panel, projects are displayed as the top level in the hierarchy. Show more or lessRead more...

    The Application Navigator

    The Application Overview window (Application | Show Overview) opens in the editor window area when you create a new application. All objects that you create within JDeveloper appear in the Application Overview file summary pages, arranged by object type. You can create new files and artifacts, and view them filtered by status and project.

    The Overview pane

    You can optionally close the window, since you will not be using it to create objects for this application.
  9. Save your work by clicking Save All Save All icon. You should save your work at regular intervals as you progress through the rest of the tutorial.

Step 2: Create a Database Diagram

One way to develop offline database object definitions is by using a database diagram. You can create offline tables, foreign key relationships, views, synonyms, sequences, usages, and joins directly on the diagram. You drag components from the Component Palette onto the diagram, then draw relationships and annotations, such as attachments and dependencies, between objects. A form of UML notation is used to represent each of the objects on the diagram. Each component you create on the diagram is also displayed in the Application Navigator. To begin creating a database diagram, you will open the Create Database Diagram dialog.

  1. To open the dialog, choose File | New.

    File | New menu option
  2. In the New Gallery, expand Database Tier and select Offline Database Objects, then select Database Diagram.

    The New Gallery

    Click OK.

    From the diagram, you can edit your objects with in-place editing.Show more or lessRead more...

    Alternatively, you can open the object editors to edit them declaratively, adding the elements that define the objects, such as columns, indexes, partitions, and tablespaces.

    A project can have many database diagrams and mix objects from different schemas and databases on one diagram.
  3. In the Create Database Diagram dialog, enter Database Diagram as the name.

    Create Database dialog

    Click OK.

  4. A new empty diagram opens.

    Diagram pane
  5. Make sure the Component Palette displays the Database components.

    Component Palette
  6. From the Component Palette, drag and drop the table icon Table Icon component onto the diagram.

    Diagram pane
  7. In the Specify Location dialog, select Application Project. Click New to open the Create Offline Database dialog.

    Specify Location dialog
  8. Enter DBOffline1 as the database name, and DBDEMO as the default schema name.

    Create Offline Database dialog

    Click OK. Click OK again to close the Specify Location dialog.

  9. The Application Navigator displays the new offline database diagram and table.

    The Application Navigator
  10. The database diagram displays the default table, named TABLE1.

    Table in  diagram
  11. Click Save All Save All icon to save your work.

Step 3: Create Offline Database Tables on a Diagram

You can use JDeveloper to work directly with database objects through a database connection. Alternatively, you can work with offline database definitions which you can subsequently generate to a database schema.

You can create new tables and views and generate the information to a database. Show more or lessRead more...

Or you can import database objects from a database schema, make the changes you want, and then generate the changes back to the same database schema, to a new database schema, or to a file that you can run against a database at a later date. You can also use JDeveloper's modeling tools to visualize your offline database objects on a diagram.

You will use in-place editing in the database diagram to visually define and edit tables, columns and key relationships.
  1. In the database diagram, select the new table name TABLE1, and change it by typing PERSONS. Then press Enter or Tab.

    Table in diagram
  2. To add a column definition, click in the white box under the table name, then click again to expose the default format. Type PERSON_ID:NUMBER(15,0) and press Tab.

    Table in diagram
  3. With the default format exposed in the next box, add the column definitions FIRST_NAME:VARCHAR2(30 BYTE), LAST_NAME:VARCHAR2(30 BYTE) and GENDER:VARCHAR2(1 BYTE), one at a time pressing Tab to advance to the next.

    Table in diagram
    When you see the default format exposed in the box, you can type over the default text.Show more or lessRead more...

    Table in diagram

    Press Tab when you finish entering the columns, then click the top of the box next to the table name.
    You can resize the table box by dragging any of the points along the edge.

    Table in diagram

    To create a Primary Key or change the Primary Key to another column, you can double-click the table definition in the diagram and use the Edit Table dialog.

    The Primary Key is indicated by <<PK>> on a database diagram. You might have to stretch the inside box to show the primary key.

    Table in diagram

    Indexes can also be displayed in the third block.

    To control the displayed properties, right-click in the table and select Visual Properties from context.
  4. Double-click the PERSONS table component on the diagram to open the Edit Table dialog, where you can create a primary key.

    Edit Table dialog
  5. Select Primary Key in the navigation panel, then shuttle PERSON_ID from the Available Columns list to the Selected Columns list.

    Edit Table dialog

    Click OK. A unique index is automatically added for the primary key.

  6. In the Component Palette, drag and drop a Table component Table icon onto the diagram. Then change the table name to ADDRESSES.

    Diagram pane
  7. In the ADDRESSES table, add the following column definitions: ADDRESS_ID:NUMBER(10,0), ADDRESS_LINE1:VARCHAR2(40) and ADDRESS_LINE2:VARCHAR2(240).

    Table in diagram
  8. Double-click the ADDRESSES table component on the diagram to open the Edit Table dialog, where you can create a primary key.

    Edit Table dialog
  9. Select Primary Key in the navigation panel, then shuttle ADDRESS_ID from the Available Columns list to the Selected Columns list.

    Edit Table dialog

    Click OK.

  10. In the Component Palette, select the Foreign Key component Foreign Key icon. In the diagram, click the top of the PERSONS table, then click the top of the ADDRESSES table to create the foreign key.

    Database diagram
    The Create Foreign Key dialog lets you edit the default foreign key properties.Show more or lessRead more...

    You can change the default name of the foreign key column or select another existing column as the foreign key column if you have already created it.

    By drawing the foreign key from PERSONS to ADDRESSES, a foreign key with cardinality 1:M is created in ADDRESSES.


    Database diagram

    You could delete the key from the diagram by right-clicking the relationship and choosing Delete.
  11. In the Create Foreign Key dialog, accept the default foreign key properties and click OK.

    Create Foreign Key dialog
  12. The diagram should now look like the following:

    Database diagram

    Notice the Primary, Foreign keys and indexes displayed in the tables.

  13. The Application Navigator should look like the following:

    Application Navigator
    In the Application Navigator, the PERSONS and ADDRESSES tables are displayed in the Offline Database Sources node.Show more or lessRead more...

    The Foreign Key is shown on the diagram. If you cannot see the Foreign Key, Primary Keys or some of the columns, stretch the table objects.

  14. Click Save All Save All Icon to save your work.

Step 4: Create an Offline Database View

Views are virtual tables based on the result-set of a SELECT statement that lets you combine tables and present the data as if the data were coming from a single table. You can add SQL Functions, Joins, WHERE, GROUP BY, ORDER BY, or HAVING clauses to a view, to present exactly the data you want to the user.

  1. From the Component Palette, drag and drop the View component View Icononto the diagram. Then change the view name to PERSON_INFO.

    Database diagram
  2. In the diagram, double-click PERSON_INFO to open the Edit View dialog and then select FROM clause in the navigation panel.

    Edit View dialog
  3. Expand ADDRESES | ADDRESSES_PERSONS_FK in the Available list, and select PERSONS, then shuttle it to the Selected list to create a join between ADDRESSES and PERSONS.

    Edit View dialog
  4. Select the JOIN node in the Selected list, and then click Edit Edit Icon.

    Edit View dialog
    You can create a JOIN in the Edit View dialog; once the JOIN node is selected, the Edit button is available.Show more or lessRead more...

    When you click Edit , the default JOIN is USING in the Edit Join dialog because the key column in both tables is PERSON_ID.

    Edit Join dialog

    Another way to populate a view and create a JOIN is to use the diagram. You can drag tables onto the view to add the table usages to the FROM clause of the view and all the columns to the SELECT clause. You can delete columns from the view using in-place editing. Alternatively you can select and drag individual columns from tables onto the view. You can have multiple table usages on the same view. You then need to create JOIN objects between the table usages in the view.
    To do this, you would select Join Object Join Object icon in the Component Palette and click on the two table usages to be joined. This opens the Edit Join dialog, which would look similar to this:

    Edit Join dialog

    You then select the Join properties. Use JDeveloper's code completion functionality to select the columns.
    In the editor:
    type ' p' and select PERSONS
    type ' .p' and select PERSON_ID
    type ' ='
    Then do the same to get ADDRESSES.PERSON_ID
  5. In the Edit Join dialog, accept the default JOIN, which is created using the Foreign Key, and click OK.

    Edit Join dialog

    Click OK again to close the Edit View dialog.

  6. In the database diagram, select All the columns except PERSON_ID in the PERSONS table. Then drag and drop them onto PERSONS view.

    Database diagram
  7. In the diagram, select All the columns except PERSON_ID in the ADDRESSES table. Then drag and drop them onto ADDRESSES view.

    Database diagram
  8. The database diagram now includes the offline database view: 

    Database diagram

    The database diagram now includes the offline database view: Show more or lessRead more...

    Database diagram

    You can use the Edit View dialog to edit other clauses of the view, such as GROUP_BY and ORDER_BY. To specify that the SQL statement for the view uses CREATE or CREATE OR REPLACE, use the DDL page of the Edit View dialog. This page can also be used to review the complete SQL for the view.

    Edit View dialog

  9. Click Save All Save All icon to save your work.

Step 5: Add a Sequence to a Diagram

A sequence is a schema object that can generate unique sequential values. These values are often used for primary and unique keys. One way to populate a key with a value from a sequence is to use a trigger to insert the sequence's NEXTVAL into the field.


You use the Edit Table dialog to specify that you want to have the primary key for the table populated from a new sequence and updated via a new trigger. Show more or lessRead more...

On generation of the DDL for the table, both the sequence and the trigger will be generated with no additional coding required.

To open the Edit Table dialog, you will double-click the table in the Application Navigator. You can also open the dialog by choosing Properties from the context menu.

  1. In the Application Navigator, under Offline Database Sources | DBOffline1 | DBDEMO, double-click PERSONS to open the Edit Table dialog.

    Application Navigator
  2. Click Column Sequences in the navigation panel. Confirm that PERSON_ID is selected in the Column dropdown list.

    Edit Table dialog
  3. Select Populate Column from a Sequence on insert check box.

    Edit Table dialog

    Click OK.

    In the Edit Table dialog, selecting Populate Column from a Sequence on insert on a column creates a default sequence for that column, and an associated trigger. Show more or lessRead more...

    In this example, PERSONS_SEQ is created. PERSONS_TRG is created to insert the next value of the sequence into the PERSON_ID column.

  4. PERSONS_TRG trigger and PERSONS_SEQ sequence appear in the Application Navigator.

    Application Navigator
  5. In the Application Navigator, select the PERSONS_SEQ node then drag and drop it onto the diagram.

    Application Navigator and diagram
  6. In the Component Palette, select the Dependency component. In the diagram, click the PERSONS table, then click the PERSONS_SEQ sequence to create the relationship.

    database diagram
  7. Click Save All Save All icon to save your work.

  8. The offline database diagram and Application Navigator display the new sequence and trigger you created.  

    Edit Sequence dialog

    Table in diagram

    You can edit the sequence (for example, set the increment, minimum and maximum values) by double-clicking PERSONS_SEQ in the Application Navigator to open the Edit Database Sequence dialog. Show more or lessRead more...

    Trigger SQL syntax

    To review or edit the PL/SQL for the trigger, double-click PERSONS_TRG in the Application Navigator to open the source editor.

Step 6: Create a Table with Partitions

If you have been following along with the example, the database objects created emulate an Oracle 11g database. If you have created objects that emulate another database type, for example an Oracle XE database, you may not be able to create partitions in a table.


Partitioned tables allow the data to be broken down into smaller, more manageable pieces called partitions, that can aid performance. Show more or lessRead more...

Partitions can be further broken down into subpartitions for finer levels of manageability and improved performance. Indexes can be partitioned in a similar fashion. You will add another table (ORDERS) to your example and create a foreign key between it and the PERSONS table. You then add a partition to the ORDERS table to partition orders with an order date before 1 January 2007 and for the year 2007.

To begin creating another new table , you will open the Create Table dialog. To open the dialog, right-click the offline database node in the Application Navigator and choose New Data base Object | New Table .

You will specify the table name and other details in the Create Table dialog.

  1. In the Application Navigator, under Offline Database Sources, right-click the DBOffline1 node, and choose New Data base Object New Table.

    Application Navigator
  2. In the Create Table dialog, enter ORDERS as the table name, and select Advanced to display additional options. You should see a default column ( COLUMN1) populated in the Columns list.

    Create Table dialog
  3. Under Column Properties, change the name to ORDER_ID, then select NUMBER from the Type dropdown list.

    Create Table dialog
  4. Click Add Add Icon three times to add three more default columns. Then change the names and types to the following values: ORDER_DATE (type DATE), ORDER_TOTAL (type NUMBER), PERSON_ID (type NUMBER).

    Create Table dialog
  5. In the navigation panel, select Primary Key, and shuttle ORDER_ID from the Available list to the Selected list.

    Create Table dialog
  6. In the navigation panel, select Foreign Keys and click Add Add Icon to add a new foreign key.

    Create Table dialog
  7. Choose PERSONS from the Referenced Table dropdown list.

    Create Table dialog
  8. Confirm that PERSON_ID is selected in the Local Column dropdown list.

    Create Table dialog
    You can choose from any of the schemas that you have referenced in your project to create foreign keys between tables in different schemas.  Show more or lessRead more...

    The Associations column in the referenced table defaults to that table's primary key (in this example, PERSON_ID). 

    Select any column in the local table as the foreign key column (in this example PERSON_ID).

    A name based foreign key allows you to create a foreign key in a table that you have not added to your project. To do this, select Name based.

    You can then enter the Referenced Schema, Table, and Constraint names, select the column in your table that is to be used as the foreign key, and enter the Referenced Column from the referenced table.

    The Offline Only option lets you specify whether or not the information is used when DDL is generated for the table.
  9. In the navigation panel, select Partitioning, and select RANGE from the Partition By dropdown list.

    Create Table dialog
  10. Shuttle ORDER_DATE from the Available list to the Selected list.

    Create Table dialog
  11. In the navigation panel, select Partition Definitions and click Add Add Icon.

    Create Table dialog
  12. In the Partition Details section, enter ORDERS_PRE_2007 for the name and TO_DATE('01/01/2007', 'MM/DD/YYYY') for the values.

    Create Table dialog
  13. Click Add Add Icon to add a second partition definition. Enter ORDERS_2007 for the name and TO_DATE('01/01/2008', 'MM/DD/YYYY') for the values. .

    Create Table dialog

    Click OK.

  14. Click Save All Save All icon to save your work.

  15. The Application Navigator displays the new ORDERS table that was created: 

    Application Navigator
  16. From the Application Navigator, drag ORDERS onto the diagram.

    Application Navigator and diagram

    Notice that since you defined a Foreign Key between Orders and Persons in the Edit Table Properties dialog, it is automatically drawn on the diagram when you drop the Orders table.

  17. Range, Hash, List, Composite Range-Hash and Range-List partitioning is supported for both tables and indexes. Show more or lessRead more...

    In this example, two range partitions of the ORDERS table were created to partition orders with an order date before 1 January 2007 and for the year 2007. You can review these by double-clicking ORDERS in the Application Navigator to open the Edit Table dialog.

Step 7: Create a Database User and a Database Connection

You can connect to any database for which you have connection details. If you can connect as a user with DBA privileges, follow the steps in this card to create an IDE database connection and then create a new database user with the name DBDEMO.


If you don't have DBA privileges, ask your DBA to create a DBDEMO database user with the Roles and System privileges as specified in the following steps, and give you the password and connection details.Show more or lessRead more...

Then you can proceed with the steps near the end of this card to create a DBDEMO connection.

To begin creating a database connection, you will open the Create Database Connection dialog. To open the dialog, choose File | New. In the New Gallery, select Connections in the Categories tree and Database Connection in the Items list. Then click OK.

New Gallery

When you create a database connection, you specify a username and password to authenticate the connection. You may use any valid username and password and connect to any schema. If you are connecting to the database to create other users, you must enter a username that has been granted DBA privileges.

  1. From the main menu, select File | New to open the New Gallery. Select General | Connections | Database Connection.

    New Gallery

    Click OK.

  2. If you can connect as a user with DBA privileges, follow steps 3 through 14 to create an IDE database connection and then create a new database user with the name DBDEMO. If you don't have DBA privileges, ask your DBA to create a DBDEMO database user with the Roles and System privileges as specified in this tutorial. Then proceed to step 16 to create a DBDEMO connection.

  3. In the Create Database Connection dialog, select IDE Connections if it is not selected as the Create Connection In option.

    Create Database Connection dialog
  4. Enter a name for the connection TutorialConn and select the connection type. Then enter your username and password for connecting to the database. You must have DBA privileges if you (and not your DBA) will be creating a new database user. Select the JDBC Driver, and enter the Host Name, SID or Service Name, and JDBC Port . If you're not sure what driver to use, accept the default ( thin).

    Create Database Connection dialog
  5. Click Test Connection to confirm that you can connect.

    Create Database Connection dialog

    Click OK if the connection was successful.

  6. From the View menu option, choose Database --> Database Navigator.

    Main menu View option
    When you create the IDE database connection, the Database Navigator should look similar to this, when you expand the IDE Connections node:Show more or lessRead more...

    Database Navigator

    The Database Navigator provides you with a complete editing environment for online databases. You can create, update and delete database objects using the navigator.

    The database connection is added under the IDE Connections node (for example, TutorialConn). To expand the database connection and see the schema and database objects, click the + icons.

    Database Navigator

    If you have DBA privileges, you can proceed to create a new database user and grant that new user specific Roles and Privileges.

  7. Expand the IDE Connections node and the TutorialConn connection node you created.

    Database Navigator
  8. Right-click the Other Users node and choose Create User.

    Database Navigator
  9. In the Create/Edit User dialog, enter DBDEMO as the name for the new user, then enter a password and confirm it.

    Create/Edit User dialog
  10. Click the Roles tab and select Granted for the following roles: CONNECT, RESOURCE.

    Create/Edit User dialog
  11. Click the System Privileges tab and select Granted for the following roles: ALTER ANY SEQUENCE, ALTER ANY TABLE, COMMENT ANY TABLE, CREATE ANY INDEX, CREATE ANY SEQUENCE, CREATE ANY TABLE, CREATE ANY TRIGGER, CREATE ANY VIEW.

    Create/Edit User dialog
  12. Click the SQL tab and review the SQL script to create the new user, then click Apply .

    Create/Edit User dialog
  13. Review the results of the SQL execution.

    Create/Edit User dialog

    Click Close.

  14. From the Main menu, select File | New. In the New Gallery, select General > Connections > Database Connection again to create a DBDEMO connection for the DBDEMO user you added earlier.

    New Gallery

    Click OK.

  15. Create a DBDEMO connection for the DBDEMO user you added earlier. Enter DBDEMOConn as the connection name, and DBDEMO for the user name. Then enter the password and connection details. (If your DBA created the DBDEMO user, get the password and connection details from your DBA.). Click Test Connection to confirm that you can connect.

    Create Database Connection dialog

    Click OK if the connection was successful.

  16. When you create the DBDEMO database connection, the connection is now included as a resource for your application in the Application Resources panel of the Application Navigator. Show more or less Read more...

    To see the DBDEMO connection you created, expand Connections | Database.

    Database Navigator

    If you created the new database user, in the Database Navigator, the Other Users node should include the user DBDEMO and look like this:

    Database Navigator

    If you created the IDE database connection, the Resource Palette should look like this, when you open it:

    Resource Palette

    The IDE connection you created is added under the Database node. To see the schema and database objects, click the + icons. The Resource Palette is used to manage catalogs and repository connections. Users working in a collaborative environment can share resources using catalogs. Users working alone can access the resources or groups of resources in a catalog where they are available in one place, for use with all applications.

  17. Click Save All Save All icon to save your work.

Step 8: Generate a DDL Script

You can use JDeveloper to generate DDL for any offline objects you have created. You can choose the objects to include, whether you need CREATE, REPLACE or ALTER keywords, and other options for the DDL. You can choose to generate the DDL straight into a specific database or to generate a .sql script, using the Generate SQL from Database Objects wizard. 

  1. In the Application Navigator, under Offline Database Sources | DBOffline1, right-click the DBDEMO node, and choose Generate to launch the Generate SQL from Database Objects wizard.

    Generate option from context menu
  2. On step 1, make sure Source from project is selected. Click Next .

    Generate SQL from Database Objects dialog
  3. On step 2, confirm that SQL Script is selected. Click Next.

    Generate SQL from Database Objects dialog
  4. On step 3, shuttle the offline database objects from the Available list to the Selected list. Then click Next .

    Generate SQL from Database Objects dialog

    If you have been following along with the example, the database objects created emulate an Oracle 11g database, and you can shuttle all the offline database objects to the Selected list. Show more or lessRead more...

    If you have created objects that emulate another database type, for example an Oracle XE database, shuttle all objects except ORDERS to the Selected list. This is because table partitions have been defined in ORDERS, which are not supported in XE databases. After selecting database objects in step 3 of the wizard, no additional action is needed in subsequent steps.

    In Step 4 of the wizard you can select whether to CREATE, REPLACE or ALTER the chosen objects. In this example you are creating new objects so the default of CREATE can be accepted. Step 5 allows you to specify the name and directory of the SQL script to be generated and other options such as prefixing the object name with the schema name. Step 6 summarizes the options chosen in the wizard and lets you save this set of options for reuse.

  5. On step 4, confirm that CREATE is selected. Then click Finish to generate the DDL script.

    Generate SQL from Database Objects dialog
  6. The script1.sql file opens in the editor.

    SQL Script content
  7. In the editor window, click the SQL Worksheet bottom tab.

    SQL Script in SQL Worksheet
  8. In the SQL Worksheet, select the connection DBDEMOConn from the dropdown list on the right of the toolbar.

    SQL Script in SQL Worksheet
    You can use the SQL Worksheet to enter and execute SQL, PL/SQL and SQL*Plus statements. Show more or lessRead more...

    You can specify any actions that can be processed by the database connection associated with the worksheet, such as creating a table, inserting data, creating and editing a trigger, selecting data from a table, and saving that data to a file.

    Make sure that the database in which you are creating the objects supports all your offline database types. For instance if you are using an Oracle XE Database, note that it does not support table partitions. If you choose a database that does not support table partitions and you have been following along with this example, the range partitions for the ORDERS table should be deleted from the script as they cannot be added to an XE database.

    In the SQL Worksheet toolbar, Run Script Run Script iconexecutes all the statements in the Enter SQL Statement box. To run a specific statement, select the statement and click Execute Statement Execute icon. Other toolbar icons allow for operations such as History that displays a dialog with information about SQL statements that you have executed, and Explain Plan that generates the execution plan for the statement.

  9. Click Run Script and review the output displayed.

    SQL Script in SQL Worksheet
  10. In the SQL Worksheet toolbar, click Commit.

    SQL Script in SQL Worksheet and Script Output
  11. From the main menu, choose View | Database -->Database Navigator to open the Database Navigator. Click the Refresh button.

    Database Navigator
  12. Fully expand the DBDEMO node and its subnodes to see the new database objects.

    Database Navigator
    When you run the SQL script, the output displays in the Script Output page of the SQL Worksheet: Show more or lessRead more...

    Script Output

    The Database Navigator lets you view and edit database objects directly in your database. Database connections that are owned by the IDE (available to all applications) are displayed under an IDE Connections node. If a connection is owned by an application, it is displayed under a node for the owning application, for example CreateDBObjects. The DBDEMO connection under CreateDBObjects contains the database objects you have just generated.


    Database Navigator


Step 9: Reconcile the Database
A powerful function of JDeveloper is the ability to compare offline database object definitions with live database objects. You can visually compare each change and choose to either generate that change to the database in the form of an ALTER script, or live to the database.  
You will use the Edit Table dialog to make changes to your previously created offline objects and compare those changes to the live version of the objects in the database. Show more or lessRead more...

To open the dialog, you will double-click the table in the Application Navigator. You will then use the Generate SQL from Database Objects wizard to generate a SQL script to update the live database definitions.

  1. In the Application Navigator, under Offline Database Sources | DBOffline1 | DBDEMO , double-click the ADDRESSES node to open the Edit Table dialog.

    Application Navigator
  2. Click Add to add a new default column. Then change the name to TOWN and the size of type VARCHAR2 to 25. Then click OK.

    Edit Table dialog
  3. In the Application Navigator, double-click the PERSONS node to open the Edit Table dialog.

    Application Navigator
  4. In the Columns list, select GENDER and then select Cannot be NULL.

    Edit Table dialog
  5. In the Comment box, enter a 3 way flag (M)ale (F)emale (D)eclined to answer.

    Edit Table dialog

    Click OK.

  6. In the Application Navigator, under Offline Database Sources, right-click the DBOffline1 node, and choose Generate to launch the Generate SQL from Database Objects wizard.

    Generate context menu option
  7. On step 1, make sure Source from project is selected. Click Next .

    Generate SQL from Database Objects dialog
  8. On step 2, confirm that SQL Script is selected. Click Next .

    Generate SQL from Database Objects dialog
  9. On step 3, shuttle PERSONS and ADDRESSES from the Available list to the Selected list. Then click Next .

    Generate SQL from Database Objects dialog
  10. On step 4, select ALTER and Manual Reconcile. Confirm that DBDEMO is the selected connection. Then click Next .

    Generate SQL from Database Objects dialog
    Using ALTER and Manual Reconcile allows you to review the differences between the offline object definition and the object definition in the database.Show more or lessRead more...

    You are then able to specify which of the differences should be included in the generated DDL.

    The left panel of the Manual Reconcile page shows the offline definition of the objects in your project. Objects that differ from the live database version of the same object are highlighted. You can select Only Show DifferencesOnly Show Differences icon to remove from view those items that have not changed.

    Generate SQL from Database Objects dialog


    By shuttling differences to the right panel you are specifying that these changes should be included in the DDL to be generated. If there is any text that has been changed (such as the addition of the comment on the GENDER column in this example), highlighting the text node will enable the Compare Text button so that you can compare the full text.
  11. On step 5, expand the PERSONS and ADDRESSES nodes to review the differences, which are highlighted in the left panel.

    Generate SQL from Database Objects dialog
  12. Shuttle the differences to the online DBDEMO user on the right.

    Generate SQL from Database Objects dialog
  13. Click Finish to generate the DDL script.

    Generate SQL from Database Objects dialog
  14. After completing the wizard, the new script script2.sql displays in the DBOffline1 node in the Application Navigator.

    Application Navigator
  15. The file opens in the SQL Worksheet source editor.

    SQL Worksheet
  16. Click Save All Save All icon to save your work..

Step 10: Reverse Engineer Database Objects

In many application development projects, the starting point is an existing database. JDeveloper gives you the ability to reverse engineer any existing objects in the database into your application. From there you can edit, delete and create new offline definitions prior to generating new SQL scripts.

Another advantage of reverse engineering from a database is the ability to visualize existing schemas in a diagram. Show more or lessRead more...

You will create a new project in your application for the reversed-engineered database objects.

To begin creating a new project, you will open the Create Generic Project dialog. To open the dialog, in the Application Navigator, open the Application Navigator dropdown menu and choose New Project.

Application Navigator

You will then reverse engineer database objects into the project using the Copy Database Objects to a Project wizard. To launch this wizard, choose File | New . In the New Gallery, expand Database Tier and select Offline Database Objects in the Categories tree. Then select Copy Database Objects to a Project in the Items list and click OK.

  1. Click the Application Navigator dropdown menu and select New Project from context.

    Application Navigator
  2. In the New Gallery, select General | Projects | Database Project.

    New Gallery
  3. Enter DBRevEng as the project name.

    New Gallery

    Click Finish .

  4. In the Application Navigator, right-click the DBRevEng project and select New from context.

    Application Navigator
  5. In the New Gallery, select Database Tier | Offline Database Objects| Copy Database Objects to a Project.

    New Gallery

    Click OK.

  6. On step 1 of the Copy Database Objects to a Project dialog, confirm that the application connection you created earlier, DBDemoConn, is selected in the dropdown list.

    Copy Database Objects to a Project dialog

    Click Next.

  7. On step 2, click the New button to the right of the Offline Database field to create a new offline database.

    Copy Database Objects to a Project dialog
  8. Enter DBDemoReverse as the name and DBDEMO as the default schema.

    Create Offline Database dialog

    Click OK. Then click Next.

  9. On step 3, click Query to display all available database objects. Then shuttle the database objects from the Available list to the Selected list.

    Copy Database Objects to a Project dialog

    Click Next and then click Finish to generate the database objects.

  10. The generated objects are displayed in the Application Navigator.

    Application Navigator

    You will create a new database diagram and add the generated objects to the new diagram.

  11. In the Application Navigator, right-click the DBRevEng project and select New from context.

    Application Navigator
  12. In the New Gallery, select Database Tier | Offline Database Objects | Database Diagram.

    New Gallery

    Click OK.

  13. Enter DBOffline2 as the diagram name, and confirm that oracle is entered as the package name.

    Create Database Diagram dialog

    Click OK.

  14. In the Application Navigator, select all the generated objects that have been reverse engineered into the project (except the trigger one that doesn't display on a database diagram) then drag and drop them onto the diagram.

    Application Navigator and Diagram
  15. The new database diagram shows the reverse-engineered objects, which were generated into the project and then dragged onto the diagram.

    Database diagram

    An alternative way to achieve the same end result. Show more or lessRead more...

    First create a new diagram, expand the Application Resources panel in the Application Navigator and navigate to the live database objects, then select the required objects and drag them directly onto the diagram.

    This generates the offline objects and adds them to the diagram in one movement.
  16. Click Save All Save All icon to save your work.

You've successfully performed this tutorial.

Summary
In this tutorial you used JDeveloper to: To learn more about database development, refer to:

Bookmark Print Expand all | Hide all
Back to top

Did you find this page helpful?



Copyright © 2011, Oracle and/or its affiliates. All rights reserved.