Database Development with JDeveloper

Purpose

This tutorial shows you how to use JDeveloper to perform offline database development. You learn to model existing database tables, to create a new view offline, and to generate changes back to the database. You also learn how to create and test PL/SQL objects in the database.

Time to Complete

Approximately 20 minutes.

Topics

The tutorial covers the following topics:

Overview
Scenario
Prerequisites
Creating a Database Connection
Creating the Model Project
Modeling Offline Database Objects
Creating 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 view offline
Generate the changes to the database
Create and test 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 Human Resources schema, and creating a new database 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 this tutorial, you should:

1.

Have access to or have installed the Oracle sample schemas.

This OBE uses the HR schema included with Oracle Database 10g.
2.

Have access to or have installed Oracle JDeveloper (10.1.3.1.0).

 
3.

Have started JDeveloper by double-clicking <jdev_home>\jdeveloper.exe.

If you receive a message asking if you want to migrate from a previous version, click No.

Close the Tip of the Day window.

 
4.

The JDeveloper IDE should now be displayed.

Back to Topic List

Creating a Database Connection

If you have not yet created a JDBC connection, this topic describes how to create a connection to the HR schema. You need to create a connection with the following details:

Connection Name: hrconn

UserName: hr

Password: hr

Deploy Password: checked

Driver: thin

Host Name: localhost

JDBC Port: 1521

SID: ORCL

To define a connection in JDeveloper, perform the following steps:

1.

Click the Connections tab. If the Connection Navigator is not displayed, choose View > Connection Navigator.

 
2.

Right-click the Database node and choose New Database Connection from the context menu.

 
3.

If the Welcome page of the Create Database Connection wizard displays, click Next.

In Step 1of the wizard, enter hrconn as the Connection Name, then click Next.

 
4.

In Step 2, the Authentication page of the wizard:

In the Username and Password fields, type the user name and password prescribed above.
Check the Deploy Password checkbox.

Click Next.


5.

In Step 3, the Connection page of the wizard:

In the Host Name, JDBC Port, and SID fields, leave the entries as prescribed above. Click Next.
6.

In Step 4 you can check whether the information you provided has established a connection with the database. Click the Test Connection button.

If the database is available and the connection details are correct, you see Success! in the Status window.

If an error occurs, verify the settings, click Back to 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.

 
7.

Click Finish. The connection now appears below the Database node in the Connection Navigator. Expand the connection and its nodes to look at the database objects in the schema.

Back to Topic List

Creating the Model Project

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 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 project that use a web application template, which you then customize to enable you to utilize JDeveloper's database functionality.

1.

In the Applications Navigator, right-click Applications and choose New Application from the context menu.

 
2.

In the Create Application dialog, change the Application Name from Application1 to DBModeling. Notice that the Directory Name automatically changes to match the new Application Name.
3.

Before selecting the application template, you customize it to make JDeveloper's database functionality available. Click Manage Templates to invoke the Manage Application Templates dialog.

Notice the types of application template available, and their associated technologies.
4.

Under the Web Application [JSP, Struts, ADF BC] node, select Data Model.

Select Database in the Available Technologies list and click to shuttle it across to the Selected Technologies list.

Click OK.
5.

Notice that the Application Template is now set to Web Application [JSP, Struts, ADF BC] in the Create Application dialog. Click OK.


6.

The DBModeling application is created and displayed as a node in the Applications Navigator.

Save your work by clicking Save All. You should save your work at regular intervals as you work through the rest of the tutorial.

Back to Topic List

Modeling Database Objects Offline

In addition to working with online database objects, which is not covered in this tutorial, JDeveloper enables you to model database objects offline and then generate the changes back to the database.

The topics below guide you through creating an empty database diagram, to which you add two tables from the HR schema.
You then make some small changes to the data in the tables. You also create a view based on data in the tables. Finally you generate a SQL script containing the DDL for the changes you have made. This script could then be run to update the database.

Create a database diagram
Import tables from a database connection
Edit objects on the diagram
Create a new view
Generate DDL for the changes

Back to Topic List

Creating a Database Diagram

1.

Right-click the Model project in the Applications Navigator and choose New from the context menu.

The New Gallery displays.

 

2.

In the Categories list, expand Database Tier if it is not already expanded, and select Offline Database Objects. Then select Database Diagram from the Items list and click OK.
3.

In the Create Database Diagram dialog, change the Name to HRDiagram and click OK.

A new database diagram opens.

 
4.

Notice the Component Palette to the right of the diagram area. The Component Palette provides the elements available for you to use on this diagram. If the Component Palette is not visible, open it by choosing Component Palette from the View menu.

Back to Topic

Back to Topic List

Importing Tables from a Database Connection

Now that you have a blank database diagram, you 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.

Click the Connections tab to view the Connection Navigator, or choose View | Connection Navigator.

Expand the Database, hrconn, and HR nodes to see all the database objects available through this connection.

 

2.

Expand the Tables node, select DEPARTMENTS, then hold down the [Ctrl] key and select EMPLOYEES.

Drag the DEPARTMENTS and EMPLOYEES tables onto the diagram.
3.

In the Create From Database Object dialog, ensure that Offline Database Objects is selected and 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.

The offline tables and foreign keys are created and displayed on the diagram.

 
4.

Examine the offline tables and their foreign keys on the diagram. You may need to scroll to see the complete 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 modifying a check constraint using in-place editing, and changing a column definition using the Edit Offline 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 third compartment of the diagrammed Employees table, select the check constraint that says <<Check>> EMP_SALARY_MIN:salary > 0, then click again so you can edit the line (in the diagram).

Change it to EMP_SALARY_MIN:salary > 10.

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 Offline Table dialog.

Open the Edit Offline Table dialog by double-clicking the Employees 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 Offline Table dialog, select Column Information, then select EMAIL in the Columns list. In the Column Properties section, change Size to 30, then click OK.
4.

Notice that the table in the diagram is updated with the change.


Click Save All to save your changes.

Back to Topic

Back to Topic List

Creating a New View

In this topic you create a new database view combining the employee's last name and the name of the department he/she works in.

1.

In the Applications Navigator, right-click the Model project and select New from the context menu.

 

2.

In the New Gallery, expand Database Tier in the Categories list and select Offline Database Objects. In the Items list, select View and click OK.


3.

If the Welcome page of the Create Offline View wizard displays, click Next.

On the View Information page of the wizard, enter the name EmpDept, then click Next.


4.

Notice that there are two ways of creating a view. Here you create the view declaratively, by selecting the objects from a list rather than typing in the SQL query.

On the Choose View creation method page of the wizard, select Declarative and click Next.
5.

On the Quick-pick objects page of the wizard, expand the EMPLOYEES node in the Available list, then expand EMP_DEPT_FK and under that, expand DEPARTMENTS.

Select LAST_NAME under the EMPLOYEES node. Hold down the [Ctrl] key and also select DEPARTMENT_NAME under the DEPARTMENTS node that is a subnode of EMP_DEPT_FK.

Click to shuttle LAST_NAME and DEPARTMENT_NAME to the Selected list.

Click Next, review the query, then click Finish to create the offline view.
6.

In the Applications Navigator, expand the Model project, then expand Offline Database Sources and HR. Select the EMPDEPT view and drag it to a blank spot on the diagram.
7.

Examine the view on the diagram.

Click Save All to save your changes.

Back to Topic

Back to Topic List

Generating DDL for the Changes

In the previous steps you saw how to import tables from a database connection and how to modify the data in them and how to create a new view based on them.
In this topic you create a SQL file containing the DDL for the changes, which could then be used to update the database.

JDeveloper also allows you to generate directly to a database, or to reconcile your changes against a database.

1.

Right-click in the diagram and choose Generate > Data Definition Language for Diagram from the context menu. This invokes the Generate SQL from Offline Database Objects wizard.

Alternatively, you could select the tables and the view in the Applications Navigator and choose Generate or Reconcile Objects from the context menu.

 
2.

If the Welcome page of the Generate SQL from Offline Database Objects wizard is displayed, review the information and click Next.

On the Select Objects page, check that the Departments and Employees tables and the EmpDept view are already in the Selected list, then click Next.


3.

On the Generate Options page, ensure that the ALTER Objects option is selected.

Select Manual Reconcile, then click Next.

 
4.

On the SQL Script Options page of the wizard select Generate SQL Scripts and Generate SQL*Plus extensions.

Change the SQL File Name to dbmodeling.sql, then click Next.

 
5.

On the Database Options page of the wizard, click Next, then click Finish on the Finish page.

The Reconcile dialog is displayed. The Offline Database list contains the proposed changes that you generated from the offline database, while the Online Database list shows the online database definitions. The dialog enables you to accept or reject the proposed changes. Initially all of the changes you have made are selected in the Offline Database list, so if at this point you click either to accept the selected changes or to accept all the changes, then those changes will be generated into the script that is created.

You have made only two changes, both of which are to the EMPLOYEES table: one to the EMAIL column and another to the EMP_SALARY_MIN check constraint.

In the Offline Database list, expand the EMPLOYEES node, then expand both the Columns and Constraints nodes.

To create a script that has only the column change, and not the constraint change, select only the EMAIL column change.
You also want to generate DDL for the new view, so select EMPDEPT as well, and then click to accept the selected changes and shuttle them to the Online Database list.

 
6.

Click OK to generate the DDL script. The script is generated and opens in the editor.

Although you don't do so in this tutorial, you could run this script in SQL*Plus to apply the changes to the database.

Back to Topic

Back to Topic List

Creating 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, emp_rec, that defines the employee record layout. You then create a package containing a function, get_emp, that returns an employee record defined by emp_rec. Finally you create the package body to execute the query and populate emp_rec.

Run a SQL script to create an object type
Create a PL/SQL Package Directly
Test the PL/SQL Function

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 emp_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 EMP_REC
REM

CREATE or REPLACE type EMP_REC as object
(
employee_id number(6),
last_name varchar2(25),
job_id varchar2(10),
manager_id number(6),
hire_date date,
salary number(8,2),
commission_pct number(2,2),
department_id number(4)
);
/
show errors;

Click Save All to save your changes.
5.

Right-click emp_rec.sql in the editor and choose Run in SQL*Plus > hrconn. If prompted, enter the password hr. Running this SQL file against the Human Resources schema in the database creates an object type called EMP_REC.

Note: If this is the first time that SQL*Plus has been run from JDeveloper on this computer, you will be asked to identify the location of the SQL*Plus executable. Click Browse and browse to the location of sqlplusw.exe.

An Oracle SQL*Plus window opens, and the script runs. You should see the following messages:

Type created.

No errors.

Type exit to close the SQL*Plus window.
6.

In the Connection Navigator, expand the Database node. Expand the connection hrconn, then HR, then Types to find the new object type, EMP_REC.

Back to Topic

Back to Topic List

Creating a PL/SQL Package Directly

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, EMP_FETCHER directly.

1.

In the Connection Navigator, expand the Database node, the hrconn node, and the HR node. Right-click the Packages node and choose New PL/SQL Package from the context menu.

 
2.

In the Create PL/SQL Package dialog, set the Object Name to EMP_FETCHER, then click OK.

The EMP_FETCHER package opens in the Code Editor.

 
3.

In the Code Editor for the EMP_FETCHER package, type the following code between the two existing lines, to create the get_emp function:

FUNCTION get_emp(emp_no IN NUMBER) RETURN emp_rec;

Save the package.

 
4.

In the Connection Navigator, right-click the EMP_FETCHER package and choose New Package Body from the context menu.

The package body opens in the Code Editor.

 
5.

In the Code Editor for the EMP_FETCHER package body, enter the following code to execute the query and populate the employee record:

PACKAGE BODY "EMP_FETCHER" AS

FUNCTION get_emp(emp_no IN NUMBER) RETURN emp_rec IS

emp_found employees%rowtype;
emp_rtn emp_rec;

BEGIN

SELECT *
INTO emp_found
FROM employees
WHERE employees.employee_id=emp_no;
emp_rtn := emp_rec
(

emp_found.employee_id,
emp_found.last_name,
emp_found.job_id,
emp_found.manager_id,
emp_found.hire_date,
emp_found.salary,
emp_found.commission_pct,
emp_found.department_id

);

RETURN emp_rtn;

END;

END;

Click Save to compile the package body.

Back to Topic

Back to Topic List

Testing the PL/SQL Function

1.

In the Connection Navigator, right-click the EMP_FETCHER package body and choose Run from the context menu.

 

2.

In the Run PL/SQL dialog, make the following changes, so that the last name only of employee number 200 is displayed:

Change the line
EMP_NO := NULL;
to
EMP_NO := 200;

Uncomment and change the line
--DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return);
to
DBMS_OUTPUT.PUT_LINE('v_Return = ' || v_Return.last_name);

 
3.

Click OK. The log window displays the output of the get_emp() function in the EMP_FETCHER package.

 

Back to Topic

Back to Topic List

Summary

In this tutorial, you used some of the database features of JDeveloper. You modeled database tables offline on a database diagram and created a new offline view. You then created a SQL script for generating those changes to the database. You also ran a SQL script to create an object type in the database and created a PL/SQL package directly in the database and tested them from within JDeveloper.

You've learned how to:

Model offline database tables in a database diagram
Update data in a database through a database diagram
Create PL/SQL objects in the database

Back to Topic List

Place the cursor over this icon to hide all screenshots.