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:

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:

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

Back to Topic

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

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

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.

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.

Back to Topic

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

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.

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.

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 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.