CP621 Database Design

1
CP621 Database Design

• Oracle Forms Developer
• Trigger Basics
• LOV Object (List of Values)
• Alerts
• Multiple Forms Applications

2
Lecture Objectives

• Learn to use SQL statements and PL/SQL code in
  Triggers
• Categorise Triggers
• Understand Trigger Scope
• Learn about LOV (List of Value) object
• Learn more about windows and canvases
• Learn to call one Form from another

3
Trigger

• Trigger contains PL/SQL code that responds to
  Forms events.
• It is necessary to understand when triggers fire
  and how they are organised.
• You have already been exposed to triggers and the
  events that fire them in the previous Lab
  Exercises, so you have a general idea of how they
  work.
• One of the advantages of using Oracle Forms and
  an Oracle database together is that the PL/SQL
  programming language is used in both of them.

4
Trigger

• Certain trigger types can be attached at the item
  level, the block level or the form level
• Triggers at the item level fire in response to
  events within the scope of their respective items
• Block triggers only fire in response to events
  within the scope of the block.
• Form-level triggers fire in response to events
  within the scope of the form

5
Trigger

• Triggers are always attached to other objects.
• The level of the object in the Forms hierarchy
  helps determine the scope of the attached
  trigger/s
• Figure 1.1 illustrates form, block and item
  triggers
• WHEN-BUTTON-PRESSED triggers is linked to
  individual item (button)

Figure 1.1 Triggers
6
Trigger

• It is possible to attach a WHEN-BUTTON-PRESSED
  trigger at the block or form level.
• Assume you have a CONTROL block with three
  buttons (e.g. Insert, Delete and Update)
• You assign a WHEN-BUTTON-PRESSED trigger at the
  block level. When will the trigger fire?
• It will fire in response to a Button Pressed
  event for any of the buttons in the block.
• It means the scope of the trigger is now at the
  block level rather than only at the item level.

7
Trigger Categories

• Triggers can be categorised in two ways
• By name
• By functional category
• Understanding the two methods of categorisation
  will help you understand when and why certain
  triggers fire
• There are five named trigger categories.
• The first word in a triggers name will tell how
  it will affect Forms default processing and when
  it will fire relative to Forms default
  processing.

8
Categorising Triggers by Name

• The five named categories are as follows
• When event triggers - Forms default processing.
• On event triggers - which replace Forms default
  processing.
• Pre event triggers, which fire just before a When
  event or an On event.
• Post event triggers, which fire just after a When
  event or On event.
• Key triggers, which fire when a user presses a
  certain key.

9
Categorising Triggers by Name

• You would choose the appropriate trigger from
  one of these categories depending on what you
  want your own trigger code to do and how you want
  Forms to handle its own default processing.

10
Categorising Triggers

• Your form is used to allow user to create a new
  employee (INSERT SQL statement). There are
  several insert-related triggers to choose from,
  including ON-INSERT, PRE-INSERT, and POST-INSERT
• Do you want to replace Forms default insert
  processing and write all of the insert logic
  yourself? In that case, use an ON-INSERT trigger
• Do you want to fire some of your own logic just
  before Forms executes its default insert
  processing? In that case, you would use a
  PRE-INSERT trigger
• Or, you might want to use a POST-INSERT trigger
  to fire just after Forms has completed its
  default processing

11
Categorising Triggers by Function

• Triggers can also be categorized by the functions
  to which they are related
• A WHEN-BUTTON-PRESSED trigger is an Interface
  Event trigger because it responds to the Button
  Pressed event, which, as its category name
  implies, is an interface event

12
Categorising Triggers by Function

• ON-INSERT and PRE-INSERT triggers belong to the
  Transactional functional category because they
  are related to transactions and respond when
  there are transaction-related events
• The Forms help system lists a number of
  functional trigger categories

13
Categorising Triggers by Name

• 1) Query triggers, which respond to events
  regarding queries.
• 2) Validation triggers, which respond to events
  regarding the validation of items and records.
• 3) Transactional triggers, which respond to
  events regarding inserting, updating, and
  committing of records.
• 4) Key triggers, which respond to Key Press
  events.
• Each trigger falls into both a named and a
  functional trigger category.

14
Oracle Form Developer - The Forms help system
15
Oracle Form Developer - The Forms help system
16
Oracle Form Developer - The Forms help system
17
WHEN-VALIDATE-ITEM

• The WHEN-VALIDATE-ITEM trigger will fire when
  both of the following two conditions have been
  met
• 1) The user has changed the value in the item.
• 2) The user has navigated out of the item.

18
WHEN-VALIDATE-ITEM

• Assume there is a business rule in the
  application that states that no employee can earn
  more than 100,000.
• Whenever a user enters a value into a SALARY
  item, you want the form to confirm that the value
  theyve entered is valid according to the rule.
• You could do so by writing a WHEN-VALIDATE-ITEM
  trigger that contains the following code and
  attach it to the SALARY item

19
WHEN-VALIDATE-ITEM

• IF STAFF.SALARY gt 100000 THEN
• MESSAGE(Employee salary must be less than
  100,000.')
• RAISE FORM_TRIGGER_FAILURE
• END IF
• If the user enters a value greater than 100,000
  and navigates out of the item, the Validate Item
  event will occur and the WHEN-VALIDATE-ITEM
  trigger will fire. Since validation has failed,
  the user will receive a message and processing
  will stop.

20
WHEN-VALIDATE-ITEM

• Imagine that your application allows user to
  enter personal details for a new employee. Assume
  there is a business rule in the application
  requiring validation of an Australian postcode.
• You will write a Validation trigger for
  STAFF.PCODE item (text box).
• The trigger will validate that the PCODE value a
  user wishes to insert or update exists in the
  Postcodes table.

21
WHEN-VALIDATE-ITEM PL/SQL CODE

• DECLARE
• v_invalid BOOLEAN
• CURSOR c_val_pcode IS SELECT city, state
• FROM Postcodes
• WHERE postcode STAFF.PCODE
• BEGIN
• OPEN c_val_pcode
• FETCH c_val_pcode INTO STAFF.CITY, STAFF.STATE
• v_invalid c_val_pcodeNOTFOUND
• IF v_invalid THEN
• MESSAGE('This postcode is invalid. Re-enter
  another.')
• RAISE FORM_TRIGGER_FAILURE
• END IF
• END

22
WHEN-VALIDATE-ITEM PL/SQL CODE

• What typical PL/SQL sections and constructs can
  you see in a trigger code?
• Answer There are Declare, Begin, and End
  statements, a cursor, and conditional logic.

23
List Of Values (LOV)

• Lists of values (LOV) is a visual objects (pop-up
  window) with which users can interact by
  selecting an item from the list
• Selected option (from LOV) is than used to
  populate item/s on a form
• LOVs are usually assigned/attached to text items.
• LOV can be created manually or by using a wizard
• LOV is not positioned on a canvas

24
List Of Values (LOV)

• LOVs serve a number of purposes. They ensure the
  validity of data and make data entry easier by
  providing users with list of possible values.
• To select an option, the user can select the item
  in the list and click OK, or simply double-click
  the item.
• Once selected, some of the column values from the
  LOV will be returned to the form to populate text
  and display items.

25
List Of Values (LOV)

• All LOVs are based on another Forms object called
  a record group
• Record groups are logical objects, so they have
  no physical properties and are never displayed to
  the user
• A record group is similar to a database table in
  that it stores an array of values in a column and
  row format
• A record group can be based on a query or on a
  set of static values

26
Alerts

• Alerts are used to present an important message
  to the user. Alerts are windows that contain
  messages and buttons.
• Alerts buttons allow user to respond to the
  message that is being displayed
• They provide a convenient way to send a message
  to alert the user that something has caused an
  error, something they have done will have certain
  consequences, or that something has happened that
  they should simply know about.

27
Alerts

• Why alert when you can use MESSAGE(Some message
  string) to send messages to the user
• Built in MESSAGE function displays message in the
  status bar and there is no guarantee that the
  user would see the message and there is no way
  for the user to respond to the message
• Alerts appear in the middle of the screen and
  they require that the user react to them

28
Alerts

• Alerts are created in the Object Navigator and
  Property Palette is used to define the title,
  style, buttons, and the message for the alert
• To display an alert, you would use the following
  built-in function
• SHOW_ALERT('ALERT_NAME')
• SHOW_ALERT returns a number that corresponds to
  the value of the alert button that was pressed
• The three-button alert would require that code be
  written so that the form could respond
  differently for each alert button

29
Alerts

• DECLARE
• v_alert_button NUMBER
• BEGIN
• v_alert_button SHOW_ALERT('EXIT_ALERT')
• IF v_alert_button ALERT_YES THEN
• EXIT_FORM
• ELSIF v_alert_button ALERT_NO THEN
• MESSAGE('You have chosen to continue')
• END IF
• END
• The form will exit if the user selects Button
  Yes and will send a message if the user presses
  Button No.

30
Multiple Forms Applications

• In a typical application users will use one form
  to accomplish one task, and another form to
  accomplish another task
• If these tasks are related, you may want to have
  one form (calling form) call the other (called
  form)
• Furthermore, you may even want to pass
  information from form to from

31
Multiple Forms Applications

• Called form refers to the form that has been
  invoked
• For example, if FORM1 calls FORM2, then FORM1 is
  the calling form (also called calling
  environment) and FORM2 is the called form (also
  called called environment)

32
Multiple Forms Applications

• There are three built-ins that call one form from
  another OPEN_FORM, CALL_FORM, and NEW_FORM
• To open another form, you simply issue one of
  these statements along with the forms name
• The statement to open a form called STAFF would
  look like this
• OPEN_FORM(STAFF')

33
Multiple Forms Applications

• Assume that FORM1 is the calling form and FORM2
  is the called form
• FORM1 issues OPEN_FORM('FORM2')
• FORM2 will open, and FORM1 will remain active and
  accessible.
• The user will be able to navigate between both
  forms and access items in each

34
Multiple Forms Applications

• Assume that FORM1 is the calling form and FORM2
  is the called form
• FORM1 issues CALL_FORM('FORM2')
• FORM2 will open as a modal form
• The user will not be able to leave FORM2 until it
  has been exited or closed
• FORM1 may be visible, but none of its items will
  be accessible

35
Multiple Forms Applications

• What is Modal Form (Window)?
• Dialog-style windows are often implemented as
  modal
• They are windows that require some sort of
  response from the user before regular processing
  can continue.
• Navigation cannot leave a modal window until the
  user has completed whatever task the window calls
  for (e.g. LOV pop-up)

36
Multiple Forms Applications

• Assume that FORM1 is the calling form and FORM2
  is the called form.
• FORM1 issues NEW_FORM('FORM2')
• FORM1 will close and then FORM2 will open.

37
Multiple Forms Applications

• The EXIT_FORM built-in will exit and close the
  current form
• EXIT_FORM
• The CLOSE_FORM built-in will also exit and close
  a form, but it requires a form name as a
  parameter
• CLOSE_FORM('FORM2')

38
Multiple Forms Applications

• When multiple forms are open, you can navigate
  between them using navigation built-ins
• If forms FORM1 and FORM2 are open, you can
  navigate from one to the other with the following
  statement
• GO_FORM(FORM2')
• The GO_FORM built-in is passed a name or object
  ID to open a specific form

39
Multiple Forms Applications

• Additionally, there are two other built-ins for
  navigating from form to form and their syntax is
  as follows
• NEXT_FORM
• and
• PREVIOUS_FORM
• Note that these two built-ins do not refer to a
  specific form by name or object ID

40
Next Week

• No Laboratory Exercises
• Lecture Content
• Exam Discussion
• SET and SEU forms
• Thank you and good luck with your studies
• Sasha Ivkovic 2006

41
Book Demos

• E \forms\chapter3\dv06.mov
• (Triggers 0223 minutes)
• E\forms\chapter2\dv07.mov
• (LOV and Alerts 0608 minutes)

42
References

• Oracle Forms Developer A Complete Video Course
  2001 Prentice-Hall, Inc.,2001. ISBN
  0-13-015808-9
• A Tutorial on Oracle9i Forms and Reports
• http//cisnet.baruch.cuny.edu/holowczak/