Transcript Chapter 6

Enhanced Guide to Oracle10g
Chapter 6:
Creating Custom Forms
Data Block and Custom Forms

Data block form



Based on data blocks that are associated
with specific database tables
Reflect the structure of the database
Custom form


Based on control blocks that process data
from multiple tables
Reflect business processes rather than the
database structure
Creating a Custom Form
1.
2.
3.
Create the form
Create the form window and canvas
manually
Create a control block


4.
Data block that is not associated with a specific
table
Contains form items that you manually draw on
the canvas
Create form triggers to process data
Form Triggers



Code that is associated with a form
object and an event
Can contain SQL INSERT, UPDATE,
DELETE, and SELECT commands
Referencing form text item values in
triggers:
:block_name.item_name
Program Units



Self-contained programs
Can be called from PL/SQL triggers
Used to make triggers more modular,
and reuse code within triggers
Practice1

Create a custom form that has:






Employee Name
Employee Salary
Department Name
Create a LOV to retrieve block records
Use triggers to retrieve block records
Create a button that calls a program
unit to clear the block.
Referencing System Date and
Time Values in Forms
System Variable
Return Value
$$DATE$$
Current operating system date
$$TIME$$
Current operating system time
$$DATETIME$$
$$DBDATE$$
Current operating system date
& time
Current DB server date
$$DBTIME$$
Current DB server time
$$DBDATETIME$$
Current DB server date & time
Form Triggers

Categories








Block processing
Interface event
Master-detail processing
Message handling
Navigational
Query time
Transactional
Validation
Trigger Timing

PRE

POST

Fires just before associated event occurs
Fires just after associated event occurs
ON-, WHEN-, KEY
Fires immediately, in response to specific
user actions, such as key presses
Trigger Scope


Defines where an event must occur to make
the trigger fire
Trigger scope includes the object to which the
trigger is attached, as well as all objects
within that object



Form-level: fires when event occurs within any
block or item in the form
Block-level: fires when event occurs within any
item in the form
Item-level: fires only when event occurs within
that item
Trigger Execution Hierarchy

If 2 related objects have the same
trigger, the lower-level object’s trigger
fires instead of the higher-level one.



Form & block, block’s trigger fires
Block & item, item’s trigger fires
You can specify a custom execution
order using the Execution Hierarchy
property of the trigger.
Navigational Triggers


External navigation: occurs when user
causes form focus to change
Internal navigation: occurs as a result
of internal form triggers that fire in
response to external navigation events
Triggers That Fire at
Form Startup
Triggers Fired
User Action
1
2
PRE-FORM
PRE-BLOCK
User starts form
4
Result on User
Screen Display
3
Form appears,
but with no
data visible
5
WHEN-NEW-FORM-INSTANCE
WHEN-NEW-BLOCK-INSTANCE
WHEN-NEW-RECORD-INSTANCE
WHEN-NEW-ITEM-INSTANCE
Form is
available for use
Triggers That Fire as a Result
Of External Navigation
User Action
User places the
insertion point
in a text item
User clicks
the Next Record
button
Triggers Fired
WHEN-NEW-ITEM-INSTANCE
WHEN-NEW-RECORD-INSTANCE
WHEN-NEW-ITEM-INSTANCE
Result on User
Screen Display
Insertion point
appears in item
Next record
appears
Triggers That Fire When a
Form Closes
User Action
User closes
the Forms
Runtime window
Triggers Fired
POST-BLOCK
POST-FORM
Result on User
Screen Display
Forms Runtime
window closes
Practice2



Create a department data block.
Create an Update button.
Create a Cancel button that uses a
procedure.
Directing External
Navigation

Form tab order
is based on
item order in
Object
Navigator block
list
Form
tab
order
Moving to a Specific Form
Item



GO_ITEM(‘block_name.item_name’);
Do not write colon(:) before the
block name in this commnd.
E.g:


GO_ITEM(‘emp.sal’);
Other navigational instructions:


GO_BLOCK(‘block_name’)
GO_FORM(‘form_name’)
Oracle Error Message Severity
Levels





5: informative message
10: informative message advising user
of procedural error
20: condition that keeps trigger from
working correctly
25 condition that keeps form from
working correctly
>25: extreme severity
Suppressing Lower Level
System Messages

Set :SYSTEM.MESSAGE_LEVEL variable
to a higher level in PRE-FORM trigger
:SYSTEM.MESSAGE_LEVEL := 25;
Providing User Feedback
in Forms
 Message
 Text in message line at bottom of form
 Informational only; user doesn't have to
respond
 Alert
 Dialog box
 Allows user to choose different ways to proceed
Messages

Syntax:
MESSAGE(‘message text’);
Message
Alerts


Form-level object
Object properties define alert
appearance
Message
Title
Style
icon
Buttons
Code for Displaying an Alert
DECLARE
alert_button NUMBER;
BEGIN
alert_button := SHOW_ALERT('alert_name');
IF alert_button = ALERT_BUTTON1 THEN
program statements for first button;
ELSE
program statements for second button;
END IF;
END;
Avoiding User Errors


Make primary and foreign key text
items non-navigable (How?)
When user moves form focus to
primary or foreign key text item,
move focus to alternate form item
(How?)
How to make nonnavigable item?


Set the text item property
Keyboard Navigable to NO
Use When New Item Instance or
When Mouse Up triggers to make
internal navigation from the
nonnavigable item.
Trapping Runtime Errors
 Create an ON-ERROR event trigger
 Form-level trigger
 Executes whenever an FRM- or ORA- error
occurs
 -FRM errors: generated by Forms
Runtime
 -ORA errors: generated by database
Form Procedures That Return
System Error Information




DBMS_ERROR_CODE: The most recent
-ORA error number. (Negative Number)
DBMS_ERROR_TEXT: -ORA error
message and number.
ERROR_CODE: The most recent FRM
error number. (Positive Number)
ERROR_TEXT: FRM error message and
number.
Structure of ON-ERROR
Trigger
BEGIN
--trap FRM errors
IF ERROR_CODE = FRM_error_code1 THEN
error handler;
Code to
ELSIF ERROR_CODE = FRM_error_code2 THEN
trap –FRM
error handler;
errors
…
ELSE
--trap ORA errors
IF DBMS_ERROR_CODE = -ORA_error_code1 THEN
error handler
ELSIF DBMS_ERROR_CODE = -ORA_error_code2 THEN
Code to
error handler
trap
…
–ORA
END IF
END IF;
errors
END;
Form Validation


Ensures that form data meets preset
requirements so erroneous data is not
sent to database
Using validation properties or using
validation triggers.
Form Validation Categories

Data


Database


Specifies data types, lengths, and maximum and
minimum values
Specifies which operations a user can perform on
a text item
List of Values

Specifies whether a data value must be validated
against the text item’s LOV (Validate From List text
item property)
Validation Properties

Validation unit form property: specifies
the largest data chunk that the user can
enter before validation occurs



Can be performed at the form, block,
record, or item level
Specified in the Validation Unit property on
the form Property Palette
By default, it is set to item level.
Cont.
You should use the item level with the
custom forms, and the record or block
level with the data block forms.
 The validation properties that are
checked always and before checking
other properties and triggers are:
format mask, required, datatype, range,
and validate from list

Text Item Validation Properties








Data Type
Minimum Value
Maximum Value
Validate From List
Insert Allowed
Query Allowed
Required
…
Question
Why do not we use validation properties
instead of validation triggers?
Answer
We use validation triggers for more complex
validations. (like what?)
Validation Triggers

When Validate Item
It should be Item level trigger.
 Executes when the item validation occurs
depending on Validation Unit property.
 E.g. :
IF NOT :student.s_class IN (‘FR’,’SO’) THEN
Message(‘Legal Values are FR and SO);
RAISE FORM_TRIGGER_FAILURE;
END IF;

How To Disable Buttons
SET_ITEM_PROPERTY
 E.g. :
SET_ITEM_PROPERTY(‘Control.Delete’ ,
ENABLED, PROPERTY_FALSE);

Data Blocks vs. Control Blocks

Data block



Control block



Easy to create and use
Is associated with a single table, and reflects the
table’s structure
Requires a lot of custom programming
Can contain items from many different tables
You can link data and control blocks to take
advantages of the strengths of each
Linking Data Blocks and
Control Blocks
1.
2.
3.
Create the control block as the master block
Create the data block as the detail block, but
do not create a master-detail relationship
Create a master-detail relationship manually in
the WHERE Clause property of the detail
block:
data_block_field := control_block.text_item
Displaying and Refreshing the
Data Block Values

Create a trigger to:
Place the insertion point in the data block
GO_BLOCK(‘block_name’);

Flush the data block to make its data
consistent with the master block and the
database:
EXECUTE_QUERY;

Converting a Data Block to a
Control Block


Create a data block and layout that
contains most of the required text items
Convert the data block to a control
block by changing the following block
properties:


Database Data Block = No
Required = No
Creating a Form with Multiple
Canvases


Users should be able to see all canvas
text items without scrolling
For complex applications with many text
items, divide application into multiple
canvases
Block Navigation Order



First block in Object Navigator Data
Blocks list determines block items that
first appear when form opens
Users can use the Tab key to navigate
among different block items
Canvas that contains block items
automatically appears
Block Navigation Order
Block order
Canvas order doesn’t matter
Controlling Block Navigation

Block Navigation Style property


Same Record: navigation cycles through
items on same block
Change Data Block: navigation moves to
next data block in list
Canvas


It is the surface that has the form items on it.
It has many types:





Content (The default)
Tab
Stacked
…
To display the canvas at runtime, use the
canvas property ‘Window’.
Tab Canvases

Multiple-page canvases that allow users
to move among different canvas
surfaces by clicking tabs
Tab Canvas Components

Tab canvas


Tab pages


Surfaces that display form items
Tab labels


Collection of related tab pages
Identifier at top of tab page
A tab canvas lies on top of a content
canvas
Creating a Tab Canvas

Use the Tab Canvas tool
on the
Layout Editor tool palette to draw a tab
canvas on an existing content canvas



By default, a new tab canvas has 2 tab
pages
Create new tab pages as needed
Adjust tab page properties
Important Tab Page Properties
Name: how the page
Is referenced in the form
Label: Caption that
appears
on the associated tab
Adjusting the Tab Page Order

Tab page that appears first is tab page
whose block items appear first in the
Object Navigator
Block order
Tab page order
doesn’t matter
Stacked Canvases


Canvas that appears on an existing
content canvas, and can be
displayed or hidden as needed
Allows user to configure canvas
items
Stacked Canvas Example
Content
canvas
Stacked
canvas
Creating a Stacked Canvas



Use the Stacked Canvas tool
on the
Layout Editor tool palette to draw a
stacked canvas on an existing content
canvas
Create block items on the stacked
canvas
Create buttons and triggers to display
and hide the stacked canvas
Displaying and Hiding a
Stacked Canvas

Displaying a stacked canvas:
GO_BLOCK(‘stacked_canvas_block’);
SHOW_VIEW(‘stacked_canvas’);

Hiding a stacked canvas:
GO_BLOCK(‘content_canvas_block’);
HIDE_VIEW(‘stacked_canvas’);