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.
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:
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.
Have access to or have installed the Oracle sample
schemas.
This OBE uses the HR schema included with Oracle Database
10g. Instructions for installing the HR schema and creating
a connection to it in JDeveloper are available online at:
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.
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.
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.
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.
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.
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.
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.
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
Languagefor 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 orReconcile 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.
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.
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.
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
(
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
Bookmark
to shuttle it across to the Selected Technologies list. 
to accept all the changes, then those changes will be generated into the
script that is created. 
