This tutorial shows you how to use JDeveloper to perform online
and offline database development. You learn to create a logical model using
a UML class diagram and transform it to a physical model. You also reverse engineer
database definitions and use a table template for creating new table definitions.
Using Subversion as versioning tool, you compare various defintions of a component.
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:
Model Classes and transform them in Database
definitions
Reverse engineer database definitions into a database
model
Use Subversion as the versioning tool
Compare logical and physical database model
Compare versioned copies of database objects
Create and use a table template definition
Create a customized report
Create, test and debug PL/SQL objects in the database
As a database designer you have been asked to review portions
of the current production schema, make some updates, extend it and carry out
an audit and start to set standards for future database development. Using the
UML class diagram, you create the logical model and then transform it to a physical
model.
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. You have also been asked to create
some PL/SQL objects in the database and then test them.
Have access to or have installed Oracle JDeveloper
11g Version 11.1.1.2.0. You can download it from Oracle
Technology Network.
2.
Have access to or have installed
the Oracle Fusion schema.
This OBE uses the FOD Fusion Order Demo schema. For
downloading and installing the Sample Schema follow the instructions
available at the following location:
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 LogicalModel as the
Project Name and from the Project Technologies tab,
select Database (Offline) and UML,
then click
to shuttle it into the Selected pane.
Click Finish.
4.
Save your work by clicking Save All
.
You should save your work at regular intervals as you progress through
the rest of the tutorial.
In this section you are going to recreate part of the Fusion Order
Demo Class Model. You will then transform the model to a database model and
compare the table definitions with those reverse engineered from the live database
The topics below guide you through creating a UML Class diagram.
To prepare the diagram environment, perform the following
steps:
1.
Right-click the Model project in the Applications
Navigator and choose New from the context menu to display
the New Gallery.
2.
In the Categories list, expand General if it is not
already expanded, and select Diagrams. Then select Class
Diagram from the Items list and click OK.
3.
In the Create Class Diagram dialog, change the Name to Logical
Database and the Package name to logicalmodel,
then click OK.
A new database diagram opens.
4.
Set some class diagram preferences, select Tools -> Preferences
5.
In the Preferences dialog, select Diagrams -> Class,
and in the Edit Preferences for, choose Class,
and uncheck Show Operations as we are doing logical DB
modeling and don’t need operations.
6.
Click the Attributes tab, and uncheck Show
Visibility as that is not needed and turn off Sort Alphabetically.
Click OK. These preferences will be set for all Class
diagrams.
7.
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.
Look at the following image showing the classes to create:
Product Base, Warehouse, Warehouse Stock Level. You can see that each product
is stocked at many warehouses
To create the diagram, perform the following steps:
1.
In the Component Palette, select the Class component
and drag and drop it it onto
the diagram.
Change the name to ProductBase.
2.
Repeat the above operation to create 2 additional classes named Warehouse
and Warehouse Stock Level.
3.
Using in place edit, add attributes to classes. In the Product
Base class, click once to select the place (goes blue) click
again to enter edit mode (don’t double click) and over-write in
format <att name>:<DB type> for Name
: String
4.
Repeat the above operation to create the attributes as per following
image:
5.
Double click within the ProductBase
class to edit the properties. Expanding the Owned Attribute
node shows the created attributes.
You can try to expand the Name node and explore the
various definable properties. Click OK to exit.
6.
In the Component Palette, click the 1 to * Association
icon ,
then click within the Product Base class and drawing
a line, click within the WarehouseStockLevels class.
Double click the association to open the Properties
and expanding the Owned End node, enter stocked
at and of as names.
Click OK.
7.
Repeat the above step, for a 1 to * Association between
Warehouse and WarehouseStockLevels,
and name it holder of and at.
You have been asked to update this class model and transform
the new classes into DB tables. As you can see from the existing class model,
the WarehouseStock Level class records the Quantity on Hand for a Product at
each Warehouse.
To create the diagram, perform the following steps:
1.
Select Warehouse StockLevel and click in the line
underneath Quantity on Hand to add another attribute:
Reorder Level: Number. Add another attribute Reorder
Quantity: Number
2.
From the Component Palette, add a new class
to the diagram and name it Stock Reorder.
3.
Using in place edit, add 2 attributes to the class. Add attributes
Date Reordered: Date and Date Delivered: Date.
4.
In the Component Palette, click the 1 to * Association
icon ,
then click within the Warehouse class and drawing a line,
click within the Stock Reorder class.
Click the endWarehouse association and change the Name
field to replenish by.
Click anywhere in the diagram to deselect the association, then click
within the endWarehouse end association and in the Property
Inspector enter the following Name: replenish by.
Click the endStock Reorder association end and in the
Property Inspector enter the following text: reordered
for.
5.
Repeat the above step to create an association between Warehouse
Stock Level and Stock Reorder and name the association
on behalf of and the association ends respectively recipient
of and on behalf of.
6.
Your diagram should now look like the following:
7.
From the Component Palette, add a new class
to the diagram and name it Manual.
Add an attribute: Reorder Quantity: Number
8.
Using the Generalization icon
from the Component Palette, draw a line from the Manual
class to the Stock Reorder one.
9.
From the Component Palette, add a new class
to the diagram and name it Automatic.
10.
Using the Generalization icon
from the Component Palette, draw a line from the Automatic class
to the Stock Reorder one.
Stock Reorder is the generalization class for Manual and Automatic. We
can also implement this concept in a 'Database oriented representation'
using subtypes.
11.
Select the Manual class and drop it within the Stock
Reorder one.
12.
Even if the model can accept having a mixed representation of generalization
using in and out combination, it is recommended to choose a convention
(in or out) and stick with it consistently to make the models more readable.
Select the Automatic class and drop it within the Stock
Reorder one. The two subclasses within the superclass is just
a visual effect. There is no semantic change to the model.
The two generalizations represent the possible ways that stock is reordered.
In database modeling terminology these are two subtypes of Stock Reorder.
13.
Change the layout out of your diagram to make it look like this:
To create the database diagram, perform the following steps:
1.
From the Main Menu select Edit --> Select All
to select all classes in the diagram.
2.
Right click within any class and from context select Transform
--> New Diagram.
3.
In the Transform dialog, select UML to Offline
Database Objects. If we had opened JDeveloper with another role,
than the Database Edition role, we would then have other options to choose
from.
Click OK.
4.
In the Offline Database Objects from UML Class Model dialog, click
the New button next to the Offline Database field.
In the Create Offline Database dialog, enter StockLevel
as name and FOD as default schema.
Click OK.
5.
Back to the previous dialog, select Default Schema
and click Next.
6.
Check the following options:
Capitalize the UML name
Insert underscores between lower and uppercase letters
Attempt to pluralize table names
Invert UML association
Click Next.
7.
Select the Transform only root classes, inheriting from specialized
classes option.
Click Next.
8.
Expand STOCK_REORDERS, see that column STOCK_REORDERS_TYPE
is added as a discriminator for handling MANUAL or AUTOMATIC subtypes.
Click Next then Finish.
9.
In the Create Database Diagram dialog, enter LogicalDBModel
as name and leave the default logicalmodel package name.
Click OK.
10.
From the main menu, select Edit --> Select All
then right click in any table and select Optimize Shape Size -->
Height and Width.
The new diagram should now look something like this:
11.
Right click in diagram surface and choose the Lay Out Shapes
--> Hierarchical (Left to Right) option for a new presentation.
12.
Right click on an association and from the context select Select
All This Type, right click again and select Straighten
Lines.
Now we are going to take definitions from a database schema
and create an offline representation of it in a new project. Prior to reverse
engineer a database schema, you need to create a database connection.
To create a database connection to the FOD schema, perform
the following steps:
1.
In the Application Navigator, open the
Application Resources accordion. Right click the Connections
node and select New Connection --> Database.
2.
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.
3.
A new Database entry appears in the Application
Resources navigator. Expand the Database node to see
the FOD connection you just created.
Reverse Engineer Database
Components into a Database Model
To reverse engineer database tables from the FOD schema into
a database model, perform the following steps:
1.
We want to create a separate project to partition our logical and
physical models. From the Main Menu select File--> New.
2.
In the New Gallery, expanding the General node, select
the Projects category and Generic Project
as item.
Click OK.
3.
In the Project Name enter PhysicalModel and shuttle
Database (offline) in the Selected technology
Click Finish.
4.
In the Application Navigatorn right click the PhysicalModel
project and select New from context.
5.
In the New Gallery, select Diagrams and Database
Diagram.
Click OK.
6.
In the Create Database Diagram dialog, type FOD_Physical as
the name and physicalmodel as the Package name.
Click OK.
7.
Open the Application Resources accordion and expand Connections
--> Database --> FOD --> Tables nodes and multi select
PRODUCTS_BASE, WAREHOUSES, WAREHOUSE_STOCK_LEVELS. Drop
the selected tables onto the diagram surface to import them.
8.
In the Specify Location dialog, select the Copy Objects to
Project option and click the New button next
to the Offline Database field.
In the Create Offline Database, enter FOD_Physical as
name, FOD as default schema, and uncheck the initialize
default templates option.
Click OK, then OK again.
9.
Right click within the diagram and select Lay Out Shapes -->
Row from context.
Click OK.
10.
In the WAREHOUSES table, add a column STOCK_VALUE:
NUMBER(15,2).
Move the column under WAREHOUSE_NAME.
Note that (stock_value = (quantity on hand of each product) x (cost price))
and it could be implemented in a number of different ways.
In many enterprises a source code management repository
is used to store all application code and documentation - including database
design. The offline database model can then be accessed and used as the single
point of truth during both development and maintenance phases. In this tutorial
we will create a 'test' local subversion repository to demonstrate how JDEV
can be used to compare different versions of database models. Note that in real
life a full Subversion (SVN) repository should be created - for full details
on creating and securing your SVN repository see http://svnbook.red-bean.com/
1.
In the main menu, select Versioning --> Create Local
Repository
2.
In the Create Subversion Repository dialog, enter repository
a new repository path, and specify a connection name: MyRepository.
3.
Note that the Versioning Navigator opens. Click the Subversion
node to see the new repository listed.
4.
Right click the Local Test Repository and select
New Remote Directory from context.
5.
In the Create Remote Directory dialog, enter trunk
as directory name and main root folder for applications
as the comment.
Click OK.
6.
The SVN Console Log shows the statement issued
for this command.
A new entry is created in the Versioning Navigator.
7.
From the Main Menu, select Versioning --> Version Application.
8.
In the Import Subversion wizard, click Next to
skip the Welcome page, and in step 2 select MyRepository
as the repository connection and trunk as the path.
Click Next.
9.
In step 3, the location for the current workspace comes as default
and add Versioned DB Modeling application as comments.
Click Next. Notice the list on the right which is
the default filters that do apply when you import an application (for
instance compiled classes are not imported see the '**/classes/**'
entry).
Click Next again in Step4.
10.
In the Options page, select Perform Checkout.
Click Next, then Finish. The process
of importing files to Subversion control starts.
11.
The Application Navigator should now look like the following:
JDeveloper's allow you to compare
object definitions. Since the objects to be compared are in separate projects,
you need to create a dependency between them to be able to perform this comparison.
To compare definitions, perform the following steps:
1.
First let's create a dependency between the LogicalModel project
and the PhysicalModel project. Right click the LogicalModel
project node and select Project Properties from context.
Click the Dependencies node and click the
Edit Dependencies button.
In the Edit Dependencies dialog, check the Build Output
checkbox of the PhysicalModel project.
Click OK, then click OK again.
2.
Next create a dependency between the two offline databases (StockLevel
and FOD_Physical) . Right click the offline database StockLevel
from theLogicalModel | Offline Data Sources, and select
Properties from context.
Select the Dependencies node, then click the Add
button and
in the Offline Database dialog, select PhysicalModel
project and FOD_Physical as the offline database.
Click OK then OK again.
3.
Right click StockLevel as the offline database
sources and select menu Copy to Project.
'Copy to Project' allows you to copy, compare or merge DB objects
from a 'source' database (either offline or online) with another offline
database. In this example comparing the StockLevel offline DB with the
FOD_Physical offline DB. To do the same from a source database with
another online database use the menu option 'Generate'.
4.
In the Copy Database Objects to a Project wizard, select Source
from project and ensure StockLevel offline
database is selected.
Click Next.
5.
In the following step, select PhysicalModel as the
Project and FOD_Physical as the Offline Database.
Click Next.
6.
In the Object Picker page, select STOCK_REORDERS
and WAREHOUSE_STOCK_LEVELS and using the right arrow
button, shuttle them in the selected pane.
Click Next.
7.
In the Choose Operation page, select ALTER and manual
reconcile. Note that this allows us to preview any changes
we are making to the Physical model.
Then Next.
8.
Review all the possible changes. Note the differences between the
logical and the physical model.
9.
Note that the STOCK_REORDERS table doesn't exist
in the physical model, and that differences are detected on the WAREHOUSE_STOCK_LEVELS
tables.
10.
Investigate more in details the WAREHOUSE_STOCK_LEVELS
differences. (for example, the quantity_on_hand column
doesn't have the correct number precision in the logical model, and
there are no audit columns in the WAREHOUSE_STOCK_LEVELS logical table)
11.
For each column in the logical model for the WAREHOUSE_STOCK_LEVELS
that doesn't exist in the physical model shuttle it using the right
arrow button.
Note the (add) comment in each of the newly added
column in the physical side.
12.
Also shuttle the STOCK_REORDERS table in the physical
model.
Click Next.
13.
Review the Summary report and click Finish.
14.
The STOCK_REORDERS table is now appearing in the
Application Navigator.
15.
Reopen the FOD_Physical.db diagram. Note that the
new columns now appear in the WAREHOUSE_STOCK_LEVELS
table as the diagram just represents the offline object.
16.
From the Navigator, select STOCK_REORDERS and drop
it onto the diagram surface.
17.
Right click in the diagram and select Lay Out Shapes -->
Row.
Versioning components allows you to browse through the
historical changes of a component and make comparison between these versions.
To compare versioned definitions, perform the following steps:
1.
First we need to update the Subversion repository
with all the changes made to the application. From the main menu select
Versioning | Pending Changes.
2.
The Pending Changes window displays the status of the various components.
The Outgoing tab contains a list of those new files
and all the other files that have been changed since last commit. Note
if the Pending Changes menu is greyed out, select any app node in navigator
and try again.
3.
Click the Candidates tab to see all the new files
added to the application that are not yet under version control. The
STOCK_REORDERS table is not versioned yet.
4.
From the main menu, select Versioning | Commit Working Copy.
5.
In the Commit Working Copy dialog, click OK.
Best practice when versioning is to always add a comment when doing
a commit.
6.
Click the Candidates tab, select the STOCK_REORDERS
table and click the Add button
to move the STOCK_REORDERS in the outgoing tab.
7.
In the Outgoing tab, select the STOCK_REORDERS
table and click the Commit button to version the table.
(This is another way to version an element under Subversion.)
In the Commit Resources dialog, click OK to commit.
8.
Now we're ready to compare the changes we have made to warehouse_stock_levels
between the different versions. In the application navigator right click
on warehouse_stock_levels and select Compare
with--> Other revision from context.
9.
In the top pane select the first revision (lowest number) on one
side and the Working copy on the other side. In the bottom pane expand
the nodes and see the changes between the current save copy in the IDE
and that earlier revision.
In this topic you create a table template to ensure all
future tables have audit columns by default. To implement this functionality,
perform the following steps:
1.
In the Applications Navigator, right-click the PhysicalModel
node and select New from the context menu.
2.
In the New Gallery,select the Database Tier | Offline Database
Objects as Category, and Offline Database as
Item.
Click OK.
3.
In the Create Offline Database dialog, name the offline database DB_TEMPLATES
and the default schema TEMPLATES.
Click OK.
4.
The new database appears in the Navigator.
5.
Right click the DB_TEMPLATE and select Properties
from context.
6.
In the Edit Offline Database dialog, select the Default Templates
node. Select TEMPLATES for both default schema
and schema and select Tables as object.
Click the Add button
next to the Name field.
7.
In the Create Table dialog, enter Template_Table as
Name and change the existing Column1 column characteristic
for Number and Primary Key.
Click the Advanced check box.
8.
Click the Primary Key node and see that COLUMN1
is primary key.
9.
Select Column Sequences and check Populate
Column from a Sequence on insert. Leave the default names generated
as Sequence Name and Trigger. Note that it names the sequence and trigger
TEMPLATE_TABLE_xxx to match
the table name.
Click OK. Click OK again.
10.
Save your work.
11.
Double click the TEMPLATE_TABLE_TRG entry to edit
the file content.
See the PL/SQL code generated:
12.
Create a dependency between offline databases, right click the FOD_Physical
node and select Properties from context.
13.
In the Edit Offline Database, select Dependencies
and click the Add button
.
14.
The DB_TEMPLATES database is already selected. Click
OK.
This sets up a dependency to allow the FOD_Physical to use the templates
in the DB_TEMPLATES offline DB.
In the Edit Offline Database, select Default Templates
and Tables. Then, from the drop down list, select TEMPLATES
as the schema , and choose TEMPLATE_TABLE as the name.
Click OK.
15.
Open the FOD_Physical diagram. Note that all the original
tables include audit columns (CREATED_BY, CREATION_DATE,
LAST_UPDATED_BY, LAST_UPDATE_DATE) The
template table needs to include these columns too.
16.
Drag and drop the TEMPLATE_TABLE from navigator onto
the diagram.
17.
Go to the WAREHOUSES 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.
18.
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
19.
Right click on TEMPLATE_TABLE in the diagram and select
Cut to remove from the diagram. The template table is now ready
for any new tables that need creating.
20.
Test by creating a new table in the diagram. Drag and drop the Table
button from the Component Palette onto the diagram, and see if defaults
columns are created.
21.
In the Specify Location dialog, check Application Project for the
FOD_Physical offline database.
Click OK.
22.
The table is created with the template table definition. Including
audit columns and primary key. Notice the sequence and
trigger files created as well in the navigator.
JDeveloper offers a wide range of pre-built reports that you can
use, but it offers also the possibility to create your own customized reports.
To create a report that lists the tables not having audit columns, perform the
following steps:
1.
In the Applications Navigator, right-click the PhysicalModel
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 Report and click OK.
3.
In the Create Report dialog, enter missing audit column
as the Name.
4.
In the Offline Database tab, select FOD_Physical.
5.
Click on the Query Definition tab. In the SQL pane,
copy and paste the following statement:
SELECT
T.NAME
FROM
DB_TABLES T
WHERE
NOT EXISTS (SELECT
1
FROM
DB_COLUMNS C
WHERE
C.PARENT_ID = T.ID AND C.NAME = 'CREATION_DATE')
this query returns table name for each table that does not contain a
CREATION_DATE column. Click the Check Syntax button to validate the query.
6.
Expand the Query Definition node and open all subsequent
nodes to see how the query could be build.
7.
Select Publish Report, (by default
the report is just output to the log window) select HTML,
Output file name = missingauditcolreport, all others
leave as default
click OK.
8.
The
new report definition appears in the Application Navigator. Save your
work. Right click missing audit column and select Run
from context.
The report starts executing.
9.
The Log window displays the report results.
10.
Because you selected 'html'. when the report is run an html version
of the report is generated and appears in the Application Navigator. Double
click missingauditcolreport to open it.
Then you can add CSS, edit the report and circulate to appropriate team
members or publish in central location or whatever.
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.
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 | Database | 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.
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 | 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, meaning that
it is now in the database.
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.
In this tutorial, you used some of the database features
of JDeveloper. You modeled classes and converted them in database definitions
using a database diagram, you created a new table templat.Next you modeled online
objects from the database, manipulating them in the diagram. You used Subversion
as the versioning tool and compared versioned definitions of a component. You
created a customized report querying the database. You then created a SQL script
for generating the offline 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:
Use a
UML Class model for logical modeling
Transform a Class
model into a database model
Reverse engineer database
definitions into a database model
Version the application
Make comparisons between logical and physical
models and between different versions of a versioned component
to shuttle it into the Selected pane. 
.
You should save your work at regular intervals as you progress through
the rest of the tutorial.
and drag and drop it it onto
the diagram.
,
then click within the Product Base class and drawing
a line, click within the WarehouseStockLevels class.
from the Component Palette, draw a line from the Manual
class to the Stock Reorder one. 
and
in the Offline Database dialog, select PhysicalModel
project and FOD_Physical as the offline database. 
to execute both statements.
