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

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 tenant.surname
• from tenant
• where tenant.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 tenant.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 tenant.surname
• from tenant
• where tenant.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
Further Reading

• Developing Oracle Forms
• COM220 Book 2 Introduction to Forms Part I
• PL/SQL and Triggers
• COM220 Book 3 Introduction to Forms Part II
• Next session
• Database Security