Building Interactive Forms Applications using Oracle

1
Building Interactive Forms Applicationsusing
Oracle
2
Developer 9i

• All databases have some kind of interface
• Oracle implementation is Developer 9i
• Allows non-programmers to access the DB by
  entering values on a form
• 4GL Tool - much of the work is done for you
• Usually a form would be developed for each
  transaction within a system
• 2 modes -a Designer Mode and an Operator mode

3
Developer 9i

• Forms Designer
• Define transactions that combine data from
  multiple tables into a single form
• Quickly create default forms using a full set of
  defaults
• Customize all aspects of an application design
• Supports master/detail relationships

4
Developer 9i

• A Forms application consist of objects
• Objects contain information needed to manipulate
  and produce an application
• Oracle forms contains many different types of
  object
• e.g. Blocks, items, triggers, buttons, graphics

5
Developer 9i

• A number of tools exist in order to manipulate
  these objects
• Object Navigator
• Layout Editor
• Properties Window
• PL/SQL Editor

6
Developer 9i

• Object Navigator
• Provides a hierarchical view of all objects in a
  form
• used to create new objects
• used to select existing objects
• Layout Editor
• Provides a means of graphically displaying the
  form
• used to change a forms appearance - move fields
  around etc

7
Object Navigator
8
Layout Editor
9
Developer 9i

• Properties Editor
• All objects on a form have properties
• Used to change the characteristics of objects,
  such as height and width, font, colour, capitals
  etc
• PL/SQL Editor
• Used to write programs (called triggers) to add
  to the functionality of a form

10
Running your Forms

• Developer 9i is web based
• Forms are compiled and run as applications within
  a web browser
• Important to remember to close down the form
  before you close down your web browser
• Otherwise leaves a connection to the database
  open
• Further forms may not run

11
Example Running Form
12
PL/SQL Triggers

• WHAT IS A TRIGGER ?
• Piece of custom code that is executed by a FORMS
  event
• Contains PL/SQL code.
• Every trigger has a name
• Name corresponds to an event

13
Triggers

• WHERE CAN A TRIGGER BE USED ?
• Within a form at
• FORM LEVEL
• BLOCK LEVEL
• ITEM LEVEL

14
Triggers Uses

• WHAT CAN A TRIGGER BE USED FOR?
• Perform data validation, data entry and data
  deletion.
• Control the logical flow of the application.

15
Events Examples

• Pressing a button (WHEN-BUTTON-PRESSED)
• Pressing tab key (KEY-NEXT-ITEM)
• Leaving the item (POST-TEXT-ITEM)
• Enter the block (PRE-BLOCK)
• Ready record for Input (WHEN-NEW-RECORD-INSTANCE)

16
Example Event

• EXAMPLE
• KEY-NEXT-ITEM is a trigger used at item level.
  It is fired when the next item key (return or
  tab) is pressed. The normal function of the next
  item key is replaced by the code contained within
  the trigger

17
PL/SQL

• WHAT IS PL / SQL ?
• A programming language composed of procedural and
  non-procedural constructs.
• It is based around the ADA programming language
• Designed to augment SQLPLUS
• Can be used in SQLPLUS to write stored
  procedures and triggers

18
PL/SQL Block

• THE PL/SQL BLOCK
• A PL/SQL program is known as a block - short for
  block of code.
• The PL/SQL block is composed of 4 sections
• DECLARE, BEGIN ,
• EXCEPTION, END

19
PL/SQL Blocks

• PL/SQL BLOCK SECTIONS
• DECLARE
• Declarative statements
• BEGIN
• Executable statements
• EXCEPTION
• Exception handlers
• END

20
PL/SQL Blocks

• DECLARE SECTION
• Variables, constants and cursors to be used in
  the block are used here.
• Variables and constants must be defined before
  they can be used.
• DECLARE
• surname varchar2(10)
• n1 number 23.4567
• joindate date
• endate date 28-jan-95

21
PL/SQL Blocks

• BEGIN SECTION
• This tells oracle that executable statements
  follow.
• Must be terminated by an end section
• begin
• select surname
• into b1.surname
• from tenant
• where b1.property_id property_id
• end

22
PL/SQL Blocks

• EXCEPTION SECTION
• Contains routines for handling exception
  conditions.
• There are two kinds of exception-
• Pre-defined
• User-defined
• THIS SECTION IS OPTIONAL

23
PL/SQL User Defined Exception Example

• DECLARE
• too_old exception
• BEGIN
• if b1.age gt 35 then
• raise too_old
• end if
• EXCEPTION
• when too_old then
• message (You are past it!)
• END

24
PL/SQL Pre-Defined Trigger Example

• DECLARE
• BEGIN
• select tenant.surname
• into b1.surname
• from tenant
• where b1.paymentid tenant.payment_id
• EXCEPTION
• when no_data_found then
• message(This payment id does not exist)
• END

25
PL/SQL Blocks

• END SECTION
• All PL/SQL blocks must have an end statement
  followed by a

26
User Interface Design

• WHAT IS IT?
• Concerned with how people use computer
• systems to perform particular tasks.

27
User Interface Design

• WHY IS IT IMPORTANT?
• Frustration
• Sabotage
• Non-use
• Productivity

28
Requirements to Identify

• Users
• experts, occasional users, novices, older people,
  users with special needs
• Tasks
• understand the tasks that have to be performed to
  accomplish the goal of activity
• Environment
• Where will the interface be located physically?
• Will the user be sitting, standing etc
• Does the interface accommodate light, space,
  noise etc

29
Interaction Styles

• Command Languages
• Menus
• Question Answer
• Form filling
• Direct Manipulation
• Natural Language

30
Training Materials

• Computerised tutorials
• Help reference information
• Customer Support helplines

31
Screen Design

• Structured
• One idea per screen
• Use of colour
• Use of Text
• HIGHLIGHTING

32
Some Simple Rules

• 1. Use simple natural dialogue
• 2. Speak the users language
• 3. Minimise the users memory load
• 4. Be consistent
• 5. Provide Feedback
• 6. Provide clearly marked exits
• 7. Provide shortcuts
• 8. Provide good error messages
• 9. Error prevention

33
User Interface Design

• REQ094 HILL.BOOKREQ RELEASE 3.3
  USERCA3JNA
• LIBRARY BOOK REQUEST SYSTEM
• AUTHER TITLE
• HILL gt
• SP gt
• STATUS
• ENTER REQUIRED BOOK DETAILS AND RETURN
• F10HELP F7AUTHORS F3OTHER SERVICES
• F8TITLES F5SITES

34
User Interface Design

• On entering the details, there is a delay of 30
  seconds while the system searches for the
  required book. If the book is not in the library
  the message ILLEGAL ENTRY appears in the middle
  of the screen.
• How many faults can you detect with this screen.
• Which RULE(s) does it violate?

35
Answers

• The use of release information adds unnecessary
  clutter (RULE No 1)
• The name of the user is unnecessary, as the user
  probably knows who they are!! (RULE No 1)
• TEXT SHOULD NOT BE IN UPPER CASE (Good screen
  design principle)
• The mispelling of author will reduce confidence
  in the system (GENERAL)
• Requirement to enter author details on separate
  lines is not helpful (RULE 1)

36
User Interface Design

• The use of the gt symbol is meaningless (RULE 1)
• The message Enter details and return will cause
  many users to type in the word return (RULE 2)
• The use and grouping of the function keys is
  illogical. (RULE 1)
• The 30 second delay will cause anxiety in the
  user. (RULE 5)
• Status is system jargon (RULE 2)

37
User Interface Design

• The error message ILLEGAL ENTRY is not user
  friendly.(RULE 8)
• There is no indication of how the user exits from
  the system. (RULE 6)
• There is no facility to cancel a request. (RULE
  6)
• The screen provides no information as to what
  users should do when they have obtained the
  required information.
• (RULE 3)

38
Further Reading

• Developing Oracle Forms
• CIFM05/06 Book 2 Introduction to Forms Part I
• PL/SQL and Triggers
• CIFM05/06 Book 3 Introduction to Forms Part II
• User Interface Design Guidelines
• Connolly Begg, pp301-303
• Next session
• Object Database Design