Design and Build Database Tables

You can easily create an application and project in which to organize your database files, and connect to a database and create a new user. Then work in JDeveloper's IDE to create an offline database, and reverse engineer it.

Create a New Application and Project: Use the Create Application wizard to create a new application and project for your database objects.

Create a Database Diagram: Use the Create Database dialog to create a new offline database diagram, and the Component Palette to add a table.

Create Database Tables on a Diagram: Use the Component Palette to add additional tables and a foreign key.

Create an Offline Database View: Use the Component Palette to add an offline database view to the diagram, and the Edit View dialog to specify properties.

Add a Sequence to a Diagram: Use the Edit Table dialog to add a new sequence, and then drag and drop onto the diagram.

Create a Table with Partitions: Begin from the Application Navigator and use the Create Table dialog to create a new table and add partitions.

Create a Database User and Database Connection: Use the Create Database Connection dialog to create a connection to the schema using a new user.

Generate a DDL Script: Use the Generate SQL from Database Objects wizard to generate a DDL script.

Reconcile the Database: Use the Edit Table dialog to add a new column, and then use the Generate SQL from Offline Database Objects wizard to generate another DDL script.

Reverse Engineer Database Objects: Use the Copy Database Objects to a Project wizard to copy the objects to a new project, and then drag and drop them onto a new diagram.

 

Create a New Application and Project

tell me more icon Creating 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.   [ tell me more...]





  1. Open the Create Application wizard [ File > New > General > Applications > Application] .
  2. Enter the application name CreateDBObjects to follow along with the example.
  3. In the A pplication Package Prefix field, enter oracle.
  4. Select Generic Application in the Applica tion Template list, and click Next .

    tell me more icon Application Templates

    Application templates provide you with a quick way to create the project structure for standard applications with the appropriate combination of technologies already specified. The new application created from the template appears in the Application Navigator already partitioned into tiered projects, with the associated technology scopes set in each project.   [ tell me more...]



  5. Enter NewSchema as the project name and click Finish .

    tell me more icon In the IDE

    In the Application Navigator, Projects panel, projects are displayed as the top level in the hierarchy.  [ tell me more...]



 

Create a Database Diagram

tell me more icon Creating a Database Diagram

To follow along with the example, in this step and throughout the cards, enter values as shown in the instructions. Then you will be able to follow the steps in the cue cards exactly as written.   [ tell me more...]





  1. In the Application Navigator, select the NewSchema project you just created and open the Create Database Diagram dialog [ File > New > Database Tier > Offline Database Objects > Database Diagram] .
  2. Enter DBOffline1 as the diagram name, and confirm that oracle is entered as the package name. Then click OK.

    tell me more icon Offline Database Objects

    You have created a new database diagram.  [ tell me more...]



  3. In the Component Palette, Database page, Database Objects panel, drag and drop the table icon Table component onto the diagram.
  4. In the Specify Location dialog, select Application Project.
  5. Click Ne w to open the Create Offline Database dialog.
  6. Enter DBOffline1 as the database name, and DBDEMO as the default schema name. Then click OK.
  7. Click OK again to close the Specify Location dialog.
  8. Click save all Save All to save your work.

    tell me more icon In the IDE

    The Application Navigator displays the new offline database diagram and table.   [ tell me more...]



 

Create Offline Database Tables on a Diagram

tell me more icon Creating 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.   [ tell me more...]





  1. In the database diagram, select the new table name, TABLE1, and change it by typing PERSONS. Then press Enter or Tab.
  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.
  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.
  4. Double-click the PERSONS table component on the diagram to open the Edit Table dialog, where you can create a primary key.

    tell me more icon Adding Objects to the Diagram

    When you see the default format exposed in the box, you can type over the default text.   [ tell me more...]



  5. Select Primary Key in the navigation panel, then shuttle shuttle PERSON_ID from the Availa ble Columns list to the Sele cted Columns list. Click OK.
  6. In the Component Palette, Database page, Database Objects panel, drag and drop a table Table component onto the diagram. Then change the table name to ADDRESSES.
  7. Add the column definitions ADDRESS_ID, ADDRESS_LINE1 and ADDRESS_LINE2 to the ADDRESSES table.
  8. Double-click the ADDRESSES table component on the diagram to open the Edit Table dialog, where you can create a primary key.
  9. Select Primary Key in the navigation panel, then shuttle shuttle ADDRESS_ID from the Availa ble Columns list to the Sele cted Columns list. Click OK.
  10. In the Component Palette, Database Objects panel, select the foreign key Foreign Key component. In the diagram, click the top of the PERSONS table, then click the top of the ADDRESSES table to create the foreign key.

    tell me more icon Creating a Foreign Key on a Diagram

    The Create Foreign Key dialog lets you edit the default foreign key properties. 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.  [ tell me more...]



  11. In the Create Foreign Key dialog, accept the default foreign key properties and click OK.
  12. In the diagram, select the default foreign key name you just added and change the name to ADDRESSES_PERSONS_FK.
  13. Click save all Save All to save your work.

    tell me more icon In the IDE

    In the Application Navigator, the PERSONS and ADDRESSES tables are displayed in the Offline Database Sources node.   [ tell me more...]



 

Create an Offline Database View

tell me more icon Creating 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.   [ tell me more...]





  1. In the Component Palette, Database page, Database Objects panel, drag and drop the view View component onto the diagram. Then change the view name to PERSON_INFO.
  2. In the diagram, double-click PERSON_INFO to open the Edit View dialog and then select FROM clause in the navigation panel.
  3. Expand ADDRESES | ADDRESSES_PERSONS_FK in the A vailable list, and select PERSONS, then shuttle shuttle it to the Selec ted list to create a join between ADDRESSES and PERSONS.
  4. Select the JOIN node in the Selec ted list, and then click Ed it .

    tell me more icon Creating a JOIN

    You can create a JOIN in the Edit View dialog; once the JOIN node is selected, the Edit button is available.   [ tell me more...]



  5. In the Edit Join dialog, accept the default JOIN, which is created using the Foreign Key, and click OK.
  6. Click OK again to close the Edit View dialog.
  7. In the database diagram, select all the columns except PERSON_ID in the PERSONS table. Then drag and drop them onto relation usage icon PERSONS view.

    tell me more icon Populating the SELECT Clause for the View

    When you drag the selected table columns on the view, the cursor should change to indicate a valid target drop.   [ tell me more...]



  8. In the diagram, select all the columns except PERSON_ID in the ADDRESSES table. Then drag and drop them onto relation usage icon ADDRESSES view.
  9. Click save all Save All to save your work.

    tell me more icon In the IDE

    The database diagram now includes the offline database view:  [ tell me more...]



 

Add a Sequence to a Diagram

tell me more icon Adding 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.  [ tell me more...]





  1. In the Application Navigator, under Offline Database Sources | DBOffline1 | DBDEMO, double-click PERSONS to open the Edit Table dialog.
  2. Click Column Sequences in the navigation panel. Confirm that PERSON_ID is selected in the Column dropdown list.
  3. Select Populate Column from a Sequence on insert , then click OK.

    tell me more icon Creating a Default Sequence and Trigger

    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.  [ tell me more...]



  4. In the Application Navigator, select the PERSONS_SEQ node then drag and drop it on the diagram.
  5. In the Component Palette, Database page, Diagram Annotations panel, select the dependency Dependency component. In the diagram, click the PERSONS table, then click the PERSONS_SEQ sequence to create the relationship.
  6. Click save all Save All to save your work.

    tell me more icon In the IDE

    The offline database diagram and Application Navigator display the new sequence and trigger you created.   [ tell me more...]



 

Create a Table with Partitions

tell me more icon Creating 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.   [ tell me more...]





  1. In the Application Navigator, under Offline Database Sources, right-click the DBOffline1 node, and choose New Data base Object then choose New Table .
  2. In the Create Table dialog, enter ORDERS as the table name, and select Ad vanced to display additional options. You should see a default column ( COLUMN1) populated in the Columns list.
  3. Under Column Properties, change the name to ORDER_ID, then select NUMBER from the T ype dropdown list.
  4. Click add Add 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).
  5. In the navigation panel, select Primary Key, and shuttleshuttle ORDER_ID from the Availa ble list to the Sele cted list.
  6. In the navigation panel, select Foreign Keys and click Add to add a new foreign key.
  7. Choose PERSONS from the Referenced Table dropdown list.
  8. Confirm that PERSON_ID is selected in the Local Column dropdown list.

    tell me more icon Creating Foreign Keys Between Tables

    You can choose from any of the schemas that you have referenced in your project to create foreign keys between tables in different schemas. The Associations column in the referenced table defaults to that table's primary key (in this example, PERSON_ID).  [ tell me more...]



  9. In the navigation panel, select Partitioning, and select RANGE from the Partition By dropdown list.
  10. Shuttle ORDER_DATE from the A vailable list to the Selected list.
  11. In the navigation panel, select Partition Definitions and click add Add.
  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.
  13. Click add Add to add a second partition definition. Enter ORDERS_2007 for the name and TO_DATE('01/01/2008', 'MM/DD/YYYY') for the values. Then click OK.
  14. Click save all Save All to save your work.

    tell me more icon In the IDE

    The Application Navigator displays the new ORDERS table that was created:  [ tell me more...]



 

Create a Database User and a Database Connection

tell me more icon Creating a Database Connection and Database User

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.   [ tell me more...]





  1. Open the Create Database Connection dialog [ File > New > General > Connections > Database Connection] .
  2. If you can connect as a user with DBA privileges, follow steps 3 through 14 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 this card. Then proceed to step 16 in this card to create a DBDEMO connection.
  3. Select I DE Connections if it is not selected as the Create Connection In option.
  4. Enter a name for the connection 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.
  5. Select the JDBC Driv er , and enter the H ost Name , S ID or Service Na me , and JD BC Port . If you're not sure what driver to use, accept the default ( thin).
  6. Click Test Connection to confirm that you can connect.
  7. Click OK if the connection was successful.
  8. From the View menu, choose Database then choose Database Navigator . Expand the IDE Connections node and the connection node you created.

    tell me more icon Database Navigator

    When you create the IDE database connection, the Database Navigator should look similar to this, when you expand the IDE Connections node:   [ tell me more...]



  9. Right-click the Other Users node and choose Create User .
  10. In the Create/Edit User dialog, enter DBDEMO as the name for the new user, then enter a password.
  11. Click the Roles tab and select Granted for the following roles: CONNECT, RESOURCE.
  12. 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.
  13. Click the SQL tab and review the SQL script to create the new user, then click Apply .
  14. Review the results of the SQL execution, then click Close .
  15. Open the Create Database Connection dialog [ File > New > General > Connections > Database Connection]  again to create a DBDEMO connection for the DBDEMO user you added earlier.
  16. Select Application Resources .
  17. Enter DBDEMO 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.)
  18. Click Test Connection to confirm that you can connect.
  19. Click OK if the connection was successful.

    tell me more icon In the IDE

    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. To see the DBDEMO connection you created, expand Connections | Database.   [ tell me more...]



 

Generate a DDL Script

tell me more icon Generating 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.  [ tell me more...]





  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.
  2. On step 1, make sure Source from project is selected. Click Next .
  3. On step 2, confirm that SQL Script is selected. Click Next .
  4. On step 3, shuttle all shuttle the offline database objects from the A vailable list to the Selec ted list. Then click Next .

    tell me more icon DDL Script Options

    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.   [ tell me more...]



  5. On step 4, confirm that CREATE is selected. Then click Finish to generate the DDL script.
  6. In the editor window, click SQL Worksheet.
  7. In the SQL Worksheet, select the connection DBDEMO ( application_name) from the dropdown list on the right of the toolbar.

    tell me more icon Using the SQL Worksheet

    You can use the SQL Worksheet to enter and execute SQL, PL/SQL and SQL*Plus statements. 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.  [ tell me more...]



  8. Click run script Run Script and review the output displayed.
  9. In the SQL Worksheet toolbar, Click commit Commit.
  10. Choose View then choose Database then choose Database Navigator to open the Database Navigator. Fully expand the DBDEMO node and its subnodes to see the new database objects.

    tell me more icon In the IDE

    When you run the SQL script, the output displays in the Script Output page of the SQL Worksheet:  [ tell me more...]



 

Reconcile the Database

tell me more icon Reconciling 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.   [ tell me more...]





  1. In the Application Navigator, under Offline Database Sources | DBOffline1 | DBDEMO , double-click the ADDRESSES node to open the Edit Table dialog.
  2. Click add Add to add a new default column. Then change the name to TOWN and the size of type VARCHAR2 to 25. Then click OK.
  3. In the Application Navigator, double-click the PERSONS node to open the Edit Table dialog.
  4. In the Columns list, select GENDER and then select Cannot be NULL .
  5. In the C omment box, enter 3 way flag (M)ale (F)emale (D)eclined to answer. Then 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.
  7. On step 1, make sure Source from project is selected. Click Next .
  8. On step 2, confirm that SQL Script is selected. Click Next .
  9. On step 3, shuttle shuttle PERSONS and ADDRESSES from the A vailable list to the Selec ted list. Then click Next .
  10. On step 4, select A LTER and Manual Reconcile . Confirm that DBDEMO is the selected connection. Then click Next .

    tell me more icon Using ALTER and Manual Reconcile

    Using A LTER and Manual Reconcile allows you to review the differences between the offline object definition and the object definition in the database. You are then able to specify which of the differences should be included in the generated DDL.   [ tell me more...]



  11. On step 5, expand the PERSONS and ADDRESSES nodes to review the differences, which are highlighted in the left panel. Then shuttle all shuttle the differences to the online DBDEMO user on the right.
  12. Click Finish to generate the DDL script
  13. Click save all Save All to save your work.

    tell me more icon In the IDE

    After completing the wizard, the new script displays in the DBOffline1 node in the Application Navigator and is open in the SQL Worksheet source editor.   [ tell me more...]



 

Reverse Engineer Database Objects

tell me more icon Reverse Engineering 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.  [ tell me more...]





  1. Open the Create Generic Project wizard [ File > New > General > Empty Project] .
  2. Enter DBRevEng as the project name and click Finish .
  3. In the Application Navigator, select the DBRevEng project and launch the Copy Database Objects to a Project wizard [ File > New > Database Tier > Offline Database Objects > Copy Online Database Objects to a Project] .
  4. On step 1, confirm that the application connection you created earlier, DBDEMO, is selected in the dropdown list. Click Next .
  5. On step 2, click Ne w to the right of the Offline Database field to create a new offline database. Enter DBDemoReverse as the name and DBDEMO as the default schema. Then click OK.
  6. Click Next . On step 3, click Query to display all available database objects. Then shuttle shuttle the database objects from the A vailable list to the Selec ted list.
  7. Click Next and then click Finish to generate the database objects.

    tell me more icon Generated Database Objects

    The generated objects are displayed in the Application Navigator.   [ tell me more...]



  8. In the Application Navigator, select the DBRevEng project and open the Create Database Diagram dialog [ File > New > Database Tier > Offline Database Objects > Database Diagram] .
  9. Enter DBOffline2 as the diagram name, and confirm that oracle is entered as the package name. Then click OK.
  10. In the Application Navigator, select all the generated objects (that have been reverse engineered into the project) then drag and drop them onto the diagram.
  11. Click save all Save All to save your work.

    tell me more icon In the IDE

    The new database diagram shows the reverse-engineered objects, which were generated into the project and then dragged onto the diagram.   [ tell me more...]



 

Copyright © 1997, 2009, Oracle. All rights reserved.