Database Development with JDeveloper

Purpose

This tutorial shows you how to use JDeveloper to perform online and offline database development. You learn to model existing database tables, to create database diagrams for online and offline objects, and to generate changes back to the database from an offline diagram or to synchronize an offline diagram from a database definition. You also learn how to create and test PL/SQL objects in the database.

Time to Complete

Approximately 50 minutes.

Topics

The tutorial covers the following topics:

	Overview
	Scenario
	Prerequisites
	Creating the Model Project and a Database Connection
	Working with Offline Database Definitions
	Working with Online Database Definitions
	Generating DDL for Offline Changes
	Creating and Debugging PL/SQL Objects in the Database
	Summary

Viewing Screenshots

Place the cursor over this icon to load and view all the screenshots for this tutorial. (Caution: This action loads all screenshots simultaneously, so response time may be slow depending on your Internet connection.)

Note: Alternatively, you can place the cursor over an individual icon in the following steps to load and view only the screenshot associated with that step. You can hide an individual screenshot by clicking it.

Overview

The tutorial uses the database features of JDeveloper to:

	Model existing tables
	Make changes in the offline database model
	Create a new offline table using templates
	Create a new view offline
	Make changes in the online database model
	Generate the changes to the database
	Create, test and debug PL/SQL objects in the database

Back to Topic List

Scenario

You have been assigned the task of updating the database by making some small changes to the Fusion Order Demo schema, and creating a new database table and a new view. You have also been asked to create some PL/SQL objects in the database and then test them.

Back to Topic List

Prerequisites

Before starting the tutorial, you should:

Back to Topic List

Creating the Model Project and Database Connection

When you work in JDeveloper, you organize your work in projects within applications. 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.

This topic guides you through creating an application and a project without using any predefined template, then you customize the project to enable you to utilize JDeveloper's database functionality.

1.	In the Applications Navigator, click the New Application link.
2.	In the Create Generic Application dialog, change the Application Name from Application1 to DBModeling. Notice that the Directory Name automatically changes to match the new Application Name. Make sure that the Generic Application template is selected then click Next.
3.	In Step 2 of the Wizard enter Model as the Project Name and from the Project Technologies tab, select Database (Offline) and click to shuttle it into the Selected pane. Click Finish.
4.	In the Application Navigator, open the Application Resources accordion. Right click the Connections node and select New Connection --> Database.
5.	In the Create Database Connection, enter the following values: Connection Name FOD UserName fod Password fusion Deploy Password checked Driver thin Host Name localhost JDBC Port 1521 SID or Service Name ORCL/XE Click the Test Connection button. If the database is available and the connection details are correct, you see Success! in the Status window then click OK. If an error occurs, verify the settings, make any changes necessary, and then retest the connection. If you still cannot connect, there may be a problem with the database, or it may not be running.
6.	A new Database entry appears in the Application Resources navigator. Expand the Database node to see the FOD connection you just created. Save your work by clicking Save All . You should save your work at regular intervals as you progress through the rest of the tutorial.

Back to Topic List

Working with Offline Database Definitions

JDeveloper allows you to model database objects offline and then generate the changes back to the database.

The topics below guide you through creating a database diagram to which you add two tables from the FOD schema.
You then make some small changes to the data in the tables. You use a default database table template to create a new offline table and you create a new view.

	Create a database diagram
	Import tables from a database connection
	Edit objects on the diagram
	Add a new offline object
	Create a new view

Back to Topic List

Creating a Database Diagram

Back to Topic

Back to Topic List

Importing Tables from a Database Connection

Now that you have a blank database diagram, you can import tables by dragging them from the database connection onto the diagram. Importing the tables creates the offline table definitions that you can work with before using DDL to create new tables or modify tables in the database.

1.	In the Application Resources tab, expand the Database | FOD | Tables nodes. In the Tables node, select ORDER_ITEMS, then hold down the [Ctrl] key for multi-select and select ORDERS. Drag the ORDER_ITEMS and ORDERS tables onto the diagram.
2.	In the Specify Location dialog, select Copy Objects to Project and check the Initialize Default Templates checkbox. Click OK. JDeveloper connects to the database and creates offline table definitions based on the tables you have selected. There may be a short delay while this occurs.
3.	The tables and foreign keys are created and displayed on the diagram.
4.	If necessary, click the Zoom in icon to be able to see the table information more clearly. Examine the offline tables and the foreign key definitions on the diagram.

Back to Topic

Back to Topic List

Editing Objects on the Diagram

JDeveloper's modeling tools allow you to edit offline tables directly on the diagram (in-place editing), or by using a Properties dialog. You can change the name of a table, create new columns, edit existing columns, and edit constraints on the table.

The steps below guide you through using both approaches: you add a new column using in-place editing, and add a check constraint using the Edit Table dialog.

1.	If the table diagram is the wrong size to work with easily, click Zoom In or Zoom Out at the top of the window. In the first compartment of the diagrammed ORDER_ITEMS table, click after the last column to create a new row (a blue row displays). Click within the blue row to create a default column definition. Type over the default column name and enter ITEM_TOTAL: NUMBER(12,0). The changes are made when you press [Enter] or move the focus off the table.
2.	Now compare in-place editing with editing using the Edit Table dialog. Open the Edit Table dialog by double-clicking the ORDER_ITEMS table in the diagram, or by right-clicking it in either the diagram or the Application Navigator and choosing Properties from the context menu.
3.	In the Edit Table dialog, select Columns item, then select ITEM_TOTAL in the Columns list. In the Column Properties section, change the Scale value to 2. Click the Primary Key, Unique Constraints, Foreign Keys items to see the existing properties defined for that table.
4.	Click the Check Constraints item. Click the Add button to add a new constraint. Update the generated name to ORDER_ITEMS_CHK_QUANTITY and in the Condition section, type QUANTITY != 0
5.	Select the Table Properties item, then click the Storage Options button.
6.	In the Storage dialog in the Extents section, set the Next value to 32 and Min to 2. Click OK.
7.	Select the Partitioning item to see what the possible options are.
8.	Back in the diagram, the check constraint appears in the second section of the ORDER_ITEMS table and the new scale for ITEM_TOTAL is displayed. (Note: If you don't see the check constraint, click on the right-hand side of the bottom box. A set of scroll bars should appear. Scroll down until you find the check constraint.) Click Save All to save your changes.

Back to Topic

Back to Topic List

Adding a New Offline Object

In this topic you use the default templates to create a new table.

1.	In the Application Navigator, notice the Templates node below the DATABASE1 node. (Expand the DATABASE1 node if necessary). Expand the Templates node.
2.	Double-click TEMPLATE_TABLE to open the Edit Table dialog. (Make sure that you select the second TEMPLATE_TABLE option, not the materialized log, which has the little log icon over the table icon). Notice that there is one default column, TEMPLATE_TABLE_ID, which is also the primary key for the table.
3.	Click Column Sequences in the left-hand list, check the Populate Column from a Sequence on insert checkbox, and click OK, accepting all other defaults. Click Save to save the amended template.
4.	Drag TEMPLATE_TABLE onto the diagram..
5.	Go to the Orders table in the diagram, and use [Ctrl]+click to select the four audit columns. If you cannot see the audit columns, position your mouse on the right-hand side of the table so that a vertical scroll bar appears. Scroll down until you see the audit columns.
6.	Drag the four selected columns onto the diagram and use [Ctrl]+drop onto the template table to copy the columns. Save your work. Notice that when you drag columns onto a table you have to use [Ctrl] or [Shift] on the drop as follows: to copy columns: drag and [Ctrl] + drop to move cols: drag and [Shift] +drop
7.	Select TEMPLATE_TABLE in the diagram, and from the context menu, choose Cut to remove it from the diagram.
8.	In the Component Palette, select Table and drag it onto the diagram. In the Specify Location dialog ensure that Application Project and DATABASE1 are selected, and click OK.
9.	Use inplace editing to change the table name to GIFTWRAP_OPTIONS. Notice that TEMPLATE_TABLE has been replaced by GIFTWRAP_OPTIONS and that its audit columns and primary key have been transferred to GIFTWRAP_OPTIONS.
10.	Notice that the new table and its sequence and trigger are displayed in the Application Navigator.
11.	Double-click GIFTWRAP_OPTIONS_TRG to see the code generated for the trigger.
12.	Close the Code Editor and return to the diagram. Select GIFTWRAP_OPTIONS_ID, the primary key of the GIFTWRAP_OPTIONS table, and drag it into the constraint area of the ORDERS table. Notice that a foreign key, ORDERS_GIFTWRAP_OPTIONS_FK is created in the ORDERS table. This approach can be further developed by the user, as required, so that the GIFTWRAP_FLAG and GIFTWRAP_MESSAGE columns in ORDERS could be similarly relocated. However there is no time to pursue this further in this OBE.

Back to Topic

Back to Topic List

Creating a New View

In this topic you create a new database view that joins the ORDERS and ORDER_ITEMS tables.

Back to Topic

Back to Topic List

Working with Online Database Definitions

JDeveloper now allows you to browse an 'online' database connection in a diagram. You can visualize database objects directly instead of having to copy them into an offline database diagram as you did in the previous topics.

	Create an online database diagram
	Using the database Navigator

Back to Topic List

Creating an Online Database Diagram

In this topic you create a new database diagram. You add two tables to the diagram to work with them in the live environment.

Back to Topic

Back to Topic List

Using the Database Navigator

In this topic you use the Database Navigator to manipulate database objects outside the context of an application.

Back to Topic

Back to Topic List

Generating DDl for Offline Changes

In an earlier topic you copied tables from the FOD schema into your project, you edited them and added a new table based on a template and a new view. In this topic you create a SQL file containing the DDL for the changes, which could then be used to update the database.

Creating and Debugging PL/SQL Objects in the Database

JDeveloper enables you to create, edit, test, and debug stored PL/SQL in the database, including object types, packages, procedures, functions, and triggers. One way to do this is to create a SQL script as a file in JDeveloper and then run that script against the database. Another way is to create a PL/SQL object directly in the database.

In the topics below you create an object type, order_items_rec, that defines an order item record layout. You then create a package containing a function, get_order_items, that returns an order item record defined by order_items_rec. Finally you create the package body to execute the query and populate order_items_rec. You test the package using JDeveloper's debug functionality.

	Grant FOD with debug privilege
	Run a SQL script to create an object type
	Create a PL/SQL Package Directly
	Test and Debug the PL/SQL Function

Back to Topic List

Granting FOD with debug privilege

Before being able to use the debugging function, you need to grant FOD with the corresponding authorization.

1.	To set the privilege to FOD select Tools | SQL Worksheet from the main menu.
2.	In the Select Connection dialog, click OK to accept the FOD(DBModeling) connection.
3.	In the SQL Worksheet window, enter connect system/oracle; (provide the system name and password allowing you to issue DBA statements in your environment). Then enter grant debug connect session to fod;
4.	Click the Execute Script icon to execute both statements. The Script Output window should report no errors.

Back to Topic

Back to Topic List

Running a SQL Script to Create an Object Type

1.	To create the SQL script, right-click the Model project in the Applications Navigator and choose New to open the New Gallery.
2.	In the Categories list, expand Database Tier and select Database Files, then select SQL File in the Items list. Click OK.
3.	In the Create SQL File dialog, change the File Name to order_items_rec.sql and click OK. The new empty file opens in the code editor.
4.	Copy the code below and paste it into the code editor: REM REM Create object ORDER_ITEMS_REC REM CREATE or REPLACE type ORDER_ITEMS_REC as object ( ORDER_ID NUMBER(15), LINE_ITEM_ID NUMBER(3), PRODUCT_ID NUMBER(15), QUANTITY NUMBER(6), UNIT_PRICE NUMBER(8,2), CREATED_BY VARCHAR2(60), CREATION_DATE DATE, LAST_UPDATED_BY VARCHAR2(60), LAST_UPDATE_DATE DATE, OBJECT_VERSION_ID NUMBER(15) ); / show errors;   Click the Run Script icon. On the Select Connection dialog, click OK to accept the FOD (DBModeling) connection.
5.	The Script Output window should report no errors. Click Save All to save your changes.
6.	In the Application Resources pane, in the Connections Navigator, expand the Database| FOD | Types nodes to find the new object type, ORDER_ITEMS_REC.

Back to Topic

Back to Topic List

Creating a PL/SQL Package

You have just seen how you can create a PL/SQL object by first creating a script and then running it against the database. In this topic you create the PL/SQL package ORDER_ITEMS_FETCH directly in the database.

1.	Click the Databases tab to open the Database Navigator, (select View | Database Navigator from the main menu if it is not already open). In the Database Navigator, expand the DBModeling node, and the FOD node. Right-click the Packages node and choose New Package from the context menu.
2.	In the Create PL/SQL Package dialog, set the package name to ORDER_ITEMS_FETCH, then click OK. The ORDER_ITEMS_FETCH package opens in the Code Editor.
3.	In the Code Editor for the ORDER_ITEMS_FETCH package, type the following code between the two existing lines, to create the get_order_items function: FUNCTION get_order_items (order_id_no IN NUMBER, line_item_id_no IN NUMBER) RETURN ORDER_ITEMS_rec;
4.	Click the Save icon in the toolbar to save your amended package. The package displays in the navigator.
5.	Right click the ORDER_ITEMS_FETCH entry and select Create Body from the context menu.
6.	The package body template opens in the Code Editor.
7.	In the Code Editor for the ORDER_ITEMS_FETCH package body, enter the following code that executes the query and populates the order_items_found record. (Enter only the bold, non-italicised code. Tthe code in italics is already there; it is reproduced here to provide the context). PACKAGE BODY ORDER_ITEMS_FETCH AS FUNCTION get_order_items(order_id_no IN NUMBER, line_item_id_no IN NUMBER) RETURN ORDER_ITEMS_rec AS order_items_found order_items%rowtype; order_items_rtn order_items_rec; BEGIN /* TODO implementation required */ SELECT * INTO order_items_found FROM order_items WHERE order_items.order_id=order_id_no -- and order_items.line_item_id = line_item_id_no; order_items_rtn := order_items_rec (order_items_found.order_id, order_items_found.line_item_id, order_items_found.product_id, order_items_found.quantity, order_items_found.unit_price, order_items_found.created_by, order_items_found.creation_date, order_items_found.last_updated_by, order_items_found.last_update_date, order_items_found.object_version_id ); RETURN order_items_rtn; END get_order_items; END ORDER_ITEMS_FETCH; (Note: Delete the line RETURN NULL;)
8.	In the Code Editor window, under the commented line, start entering the following statement and order_items.line_item_id = line_item_id_no; to see how to use JDeveloper's code insight feature. Type and order_items. and see, as you type, the popup windows helping you to select the appropriate syntax to complete the statement.
9.	Click the Compile icon to compile the package body.

Back to Topic

Back to Topic List

Testing and Debugging the PL/SQL Function