Transcript Oracle Application Express (APEX)

Project Implementation for
COSC 5050 Distributed Database Applications
Lab6
Advanced Topics
 Work with table lookups on composite primary key
 View and trigger
 Report and form
 User friendly pages
 Validations
 Navigate between pages
 Publish the application and create test users
 Export and import the application
Composite Primary Key Tables
 Tables such as
 MEMBER_TALENT
 MEMBER_PROJECT
 Work with composite primary key tables
 Create a view
 Create an INSTEAD OF trigger to be
used by application
Creating View
 Create a view that contains ROWID
 A rowid identifies a row in a table
 A rowid is a pseudo column that uniquely identifies a
row within a table, but not within a database
CREATE VIEW VW_MEMBER_TALENT_COM (ID, MEMBER_ID, TALENT_ID)
AS
SELECT ROWID ID, MEMBER_ID, TALENT_ID
FROM MEMBER_TALENT;
Creating INSTEAD OF Trigger
 An updatable view is one you can use to insert, update,
or delete base table rows
 You can create a view to be inherently updatable, or you
can create an INSTEAD OF trigger on any view to make
it updatable
 Create an INSTEAD OF trigger on the view
 Test the view and the trigger
 Make sure the view and the trigger works
 Run sample testing script in SQL Command area
Creating Report and Form
 Create a report and a form for the view
 Create page  Form  Form on a table with report
 Table/view name:

VW_MEMBER_TALENT_COM
Defining the Report
 Define report page
 Page name: MEMBER TALENT
 Region title: MEMBER TALENT
 Breadcrumb: Breadcrumb
 Breadcrumb entry name: MEMBER TALENT
 Breadcrumb parent entry: Home
Defining the Report
Defining the Report
 Define report page
 Tab options: Use an existing tab set and create a new tab
within the existing tab set
 Tab set: TS1
 New tab label: MEMBER TALENT
 Select columns:

MEMBER_ID and TALENT_ID
 Edit link image: default
Defining the Report
Defining the Form
 Define form page
 Page name: MEMBER TALENT ENTRY
 Region title: MEMBER TALENT ENTRY
 Breadcrumb entry name: MEMBER TALENT ENTRY
 Primary key type: Select primary key column(s)
 Primary key column: ID
 Source for the primary key: Existing trigger
 Select columns: both columns
 Process options: Insert, Update, Delete
Defining the Form
Defining the Form
Confirm and Finish
Running Report and Form
 Report and form pages are working
 List, edit, create, update, delete
Making Pages User Friendly
 Instead of showing ids, member name and talent
description should be displayed
 Report page
 Modify report region source
Making Pages User Friendly
 To show desired columns in an active report
 Use Action  Select columns
Making Pages User Friendly
 Form page
 Change labels to Member and Talent
 Change both to select list (LOV)

MEMBER_ID and TALENT_ID
Making Pages User Friendly
Adding Validations
 Maintain entity integrity
 Provide user friendly error message on the form page
 Validation script
SELECT *
FROM MEMBER_TALENT
WHERE MEMBER_ID = &P??_MEMBER_ID.
AND TALENT_ID = &P??_TALENT_ID.
 Use your page number
 Notice the ending period for substitution variables
Adding Validations
 Edit the form page, page processing  create
validation
 Validation level: Page
 Validation name: Insert validation
 Error display location: Inline in Notification
 Validation type: SQL  NOT Exists
 Validation code: {the validation script}
 Error message: The member already has the selected
talent.
 When button pressed: Create
 Create a similar validation for update
Adding Validations
Adding Validations
Page Navigation
 Navigation controls are shared components
 Create navigation control at the application level on
the shared components page
 Tabs
 Navigation bar entries
 Breadcrumbs
 Lists
Creating Tabs
 Tabs are grouped in collections called a tab set
 Each tab must be part of a tab set
 Two different types of tabs
 Standard tabs

For only one level of tabs with each tab associated with a
particular page
 Parent tabs


Parent tab set acts as a container for a group of standard tabs
Define more contextual list of tabs
Creating Tabs
 To manage tabs
 Shared components  Navigation  Tabs  Manage
tabs
 Add new parent tab
 The first parent tab replaces the pseudo parent tab
Creating Tabs
 Move a standard tab between parent tabs
 Edit the tab and switch Standard Tab Set
Creating Tabs
 Add more standard tabs under parent tab
 In order to show tabs, make sure set to the appropriate
Page Template and Standard Tab Set for each page
 Right click title of the page  Edit  Display attributes
Creating Tabs
Navigation Bar Entries
 Navigation bar entries offer an easy way to move users
between pages
 A navigation entry enables application to display a link
for quick access on pages
 To access navigation bar entries
 Shared components  Navigation  Navigation bar




entries
Create  From scratch  Navigation to URL
Entry label: Help
Page: {a help page} and check [Printer friendly] (optional)
Create
Publish Application
 Publish the application with URL
 http://icarusweb.webster.edu/pls/apex/f?p={app_id}
 Manage APEX users
 APEX home  Administration  Manage users and
groups
 Create a test user




Username:
Email address:
User is a developer: No
Password:
Application Export and Import
 Application home  Export/Import
Readings
 Application Express User’s Guide
 Adding Navigation
 Deploying an Application