C H A P T E R

1
Enhanced Guide to Oracle8i
Chapter 8 Advanced Form Builder Topics
2
Non-Input Form Items

• Form items that display data but dont allow the
  user to change values
• Boilerplate text and objects
• Display items
• Image items
• Sound items

3
Boilerplate Text and Objects

• Enhance form appearance
• Text
• Captions or other text
• Drawn with Text tool
• Objects
• Circles, rectangles, lines, etc.
• Drawn with tools on tool palette

4
Display Items

• Used to display text data in a text box that the
  user cannot change
• Retrieved from database
• Calculated values
• Other text
• Drawn using Display Item tool
• Note tool text background is gray, not white

5
Creating Display Items thatDisplay Calculated
Values

• Draw the display item
• Change its Calculate Mode property
• Formula value specified by a PL/SQL formula
• Summary value specified by a summary operation

6
Displaying Images in Forms

• Static imported images
• Incorporates image into form .fmb file
• Makes .fmb file larger
• Used to display images that are the same on each
  form, regardless of data that appears
• Dynamic images
• Loads image data at runtime
• Used to display
• Images that are retrieved from the database
• Large images that you dont want to store in the
  .fmb file

7
Adding a Static Image to a Form

• Click File, point to Import, click Image
• Select image file and specify image properties

8
Static Image Properties

• Image format
• Specifies the image file format and file
  extension
• Image quality
• Determines how image data is stored in Form
  Builder in terms of resolution and number of
  colors
• Values Excellent, Very Good, Good, Fair, Poor
• Excellent stores more data than Good

9
Creating a Dynamic Image

• Create an image item on the form using the Image
  Item tool
• Create a trigger to load image items into the
  database using the READ_IMAGE_FILE procedure

Image item
Button with trigger to load image
10
READ_IMAGE_FILE Procedure

• Syntax
• READ_IMAGE_FILE(filename, file_type,
  item_name)
• Parameters
• filename complete folder path and filename
  specification of the image file
• file_type type of image file (TIFF, BMP, etc.)
• item_name name of image item, in
  block_name.item_name format

11
Using Sound Items in Forms

• Sound data can be stored in Oracle database BLOB
  data fields
• You can create a form sound item to allow users
  to play sound clips

Slider bar
Volume control
Time indicator
Play button
Fast forward/ Rewind buttons
12
Creating a Sound Item in a Form

• Create a Sound Item on the form using the Sound
  Item tool
• You can also convert the item type to a Sound
  Item in the Data Block Wizard in re-entrant mode
• Load the sound item into the database from the
  file system using the READ_SOUND_FILE procedure

13
The READ_SOUND_FILE Procedure

• Syntax
• READ_SOUND_FILE(filename, file_type,
  item_name)
• Parameters
• filename complete specification of sound clip
  file
• file_type supported sound types (AU, AIFFlt
  AIFF-C, WAVE)
• item_name name of form sound item, in
  block_name.item_name format

14
Data Block Sources

• Sources for data block data
• Table
• View
• FROM clause query
• Stored procedure
• Transactional trigger

15
Using Tables as Block Data Sources

• Enables you to create a data block based on a
  single table, or tables with master-detail
  relationships
• Allows DML operations
• Allows queries
• Easy to create
• Can be slow when retrieving large datasets

16
Using Views as Block Data Sources

• Allows you to seamlessly display data from
  multiple tables
• Only allows DML operations on simple views
• Allows queries
• Easy to create
• View must be created as a database object
• Can be slow when retrieving large datasets

17
Using FROM Clauses as Data Block Sources

• Enables you to create a data block based on a
  query that joins multiple tables
• Does not allow DML operations
• Allows queries
• Avoids having to create a view

18
Using Stored Procedures as Data Block Sources

• Stored procedure creates a table or REF cursor
  on which the data block is based
• Does not support DML or query operations
• Can provide more complex data displays
• Can incorporate program logic into a data display
• Can improve performance when retrieving large
  datasets

19
Using Transactional Triggers as Data Block Sources

• Trigger that fires in place of a DML command on a
  table
• Can support DML and query operations
• Can be used to create a form based on a
  non-Oracle data source
• Requires custom programs to be written that
  intercept DML commands

20
Programming Form Key Operations

• When the user presses a function key or key
  combination while a form is running, a key
  trigger executes
• Examples of form key operations
• F1 Commits current form data
• Ctrl e Opens an editor for the current text
  item

21
Key Triggers

• Examples of Form Builder predefined key triggers
  (more in Table 8-4)

22
Redefining Existing Key Operations

• Create a key trigger associated with the existing
  key sequence trigger event
• Example to redefine F10, create a Key-COMMIT
  trigger
• Specify alternate code in key trigger

23
Oracle Transaction Processing

• Transaction series of DML commands that
  constitute a logical unit of work
• Phases
• Posting
• DB server receives and acknowledges a DML command
• Change is visible to current user, but not to
  other users
• Committing
• Change is made permanent in the database
• Change is visible to other users

24
Form Builder Transaction Processing Phases

• Reads records from database and displays them in
  the form
• Allows users to make tentative changes
• Posts changes to the database
• Commits changes to the database when
• User clicks Save button
• User presses F10
• CLEAR_BLOCK or CLEAR_FORM built-in executes
• Users clicks Yes when asked to save changes to
  database

25
Form Transaction Triggers

• Triggers used to control transaction processing
  and record auditing information
• Prefixes
• PRE- fires just before an event occurs
• POST- fires just after an event occurs

26
Form Transaction Triggers
Result on User Screen Display
User Action
Triggers Fired
PRE-QUERY PRE-SELECT POST-SELECT POST-CHANGE POST-
QUERY WHEN-NEW-RECORD-INSTANCE WHEN-NEW-ITEM-INSTA
NCE
Data appears on form
Execute Query
WHEN-NEW-RECORD-INSTANCE WHEN-NEW-ITEM-INSTANCE
Insert Record
New blank record appears
POST-CHANGE WHEN-VALIDATE-RECORD POST-BLOCK PRE-CO
MMIT PRE-INSERT POST-INSERT POST-FORMS-COMMIT POST
-DATABASE-COMMIT
Message FRM-40400 Transaction complete 1
records applied and saved appears
Save (new or updated record)
27
Form Transaction Triggers(continued)
Result on User Screen Display
User Action
Triggers Fired
WHEN-NEW-RECORD-INSTANCE WHEN-NEW-ITEM-INSTANCE
Remove Record
Record data is removed from form
POST-BLOCK PRE-COMMIT PRE-DELETE POST-DELETE POST-
FORMS-COMMIT POST-DATABASE-COMMIT
Message FRM-40400 Transaction complete 1
records applied and saved appears
Save (after removing record)
28
Using Forms with Large Data Sets

• Approaches
• Create indexes on search and join fields
• Encourage users to count query hits before
  executing queries
• Limit number of retrieved records by forcing
  users to enter search conditions
• Configure LOVs to always allow users to filter
  data
• Use array processing
• Base retrievals on asynchronous queries

29
Forcing Users to Enter a Search Condition

• Create a PRE-QUERY trigger that fires when user
  does not enter a search condition in a data block
  form
• Trigger code
• Tests to see if user entered a search condition
• If not, advises user to enter search condition
• Abandons current query

30
Code to Test For Search Condition
31
Configuring LOVs to Handle Large Retrieval Sets

• An LOV retrieves all records before the LOV
  display appears
• Can be slow if many records are retrieved
• An LOV can retrieve a maximum of 32,767 records
• Use the LOV Wizard Advanced page to configure an
  LOV to handle large data sets

32
LOV Wizard Advanced Page
Record group fetch size
Automatic refresh
Filter before display
33
Advanced Page Properties

• Record Group Fetch Size
• Specifies how many records are fetched in each
  query processing cycle
• For queries that retrieve large data sets, make
  the record group fetch size larger to speed up
  processing

34
Advanced Page Properties

• Automatic Refresh
• Specifies whether the LOV queries the database
  each time LOV display opens
• When check box is cleared, LOV records are only
  retrieved the first time the user opens the LOV
  display
• Clear check box for large data sets

35
Advanced Page Properties

• Filter Before Display
• Specifies whether LOV records appear before user
  enters a search condition
• When check box is checked, no records appear in
  the LOV display until the user has a chance to
  enter a search condition
• Check the check box for large data sets

36
Array Processing

• By default, Form Builder processes each record
  one at a time
• Array processing allows a group of records to be
  processed as a single unit, which speeds up
  processing
• To enable DML array processing, change the data
  blocks DML Array Size property value to a larger
  value
• To enable query array processing, change the data
  blocks Query Array Size property to a larger
  value

37
Enabling Array Processing

• To enable DML array processing
• Change the data blocks DML Array Size property
  value to a larger value
• To enable query array processing
• Change the data blocks Query Array Size property
  to a larger value

38
Asynchronous Queries

• Retrieve and display part of the retrieved data
  while the rest of the data is being retrieved
• To implement in Form Builder
• Create a data block based on a stored procedure
• Configure the stored procedure to retrieve and
  display records in sets

39
Mouse Operations

• Mouse click triggers fire when user clicks a
  mouse button
• WHEN-MOUSE-DOWN
• WHEN-MOUSE-UP
• WHEN-MOUSE-CLICK
• WHEN-MOUSE-DOUBLECLICK

40
Mouse Operations

• Mouse move triggers fire when user moves the
  mouse pointer across the screen display
• WHEN-MOUSE-ENTER
• WHEN-MOUSE-LEAVE
• WHEN-MOUSE-MOVE

41
Creating Mouse Triggers

• Can be associated with
• Form
• Block
• Item
• Trigger fires when user performs the mouse action
  when the item associated with the trigger has the
  form focus

42
Changing the Mouse Pointer Appearance

• Syntax
• SET_APPLICATION_PROPERTY
• (CURSOR_STYLE, cursor_style)
• Cursor Style Values
• DEFAULT
• BUSY
• CROSSHAIR
• HELP
• INSERTION

43
Form Record Groups

• Static
• Contents are specified at design time, and cannot
  be changed at runtime
• Query
• Contents are based on a SQL query that executes
  while the form is running
• Nonquery
• Contents are inserted programmatically at runtime

44
Creating a Static Record Group

• Create a new record group object
• Specify the values in the Column Specification
  dialog box

45
Creating a Query Record Group Manually

• Create a new record group object
• Specify the SQL query that is the record group
  source

SQL query
46
Creating a Query Record Group Programmatically

• Use the CREATE_GROUP_FROM_QUERY procedure
• Syntax
• group_id CREATE_GROUP_FROM_QUERY
• (record_group_name, SQL_query_text,
  record_group_scope, number_of_fetch_records)

47
Record Group Scope

• FORM_SCOPE
• Record group is only visible inside the current
  form
• GLOBAL_SCOPE
• Record group is visible to all forms that are
  currently running in the current user session

48
Populating and Deleting Programmatic Query Record
Groups

• Populating the record group
• return_value POPULATE_GROUP(record_group_name
  )
• Deleting the record group
• DELETE_GROUP(record_group_name)

49
Example Code
50
Nonquery Record Groups

• Contains values that cannot be retrieved using a
  SQL query
• Examples
• Complex calculated values
• Future dates

51
Creating a Nonquery Record Group

• Create the record group
• Define the record group columns
• Add new blank rows
• Specify each value in each row

52
Creating a Nonquery Record Group

• Syntax
• group_id CREATE_GROUP(record_group_name,
  record_group_scope, number_of_fetch_records)

53
Defining Columns in a Nonquery Record Group

• Syntax
• column_id ADD_GROUP_COLUMN(record_group_name,
  column_name, column_data_type_specification,
  column_width)

54
Adding Data Values to a Nonquery Record Group

• Syntax
• SET_GROUP_datatype_CELL (record_group_name.colum
  n_name, row_number, value)
• Procedures
• SET_GROUP_CHAR_CELL
• SET_GROUP_NUMBER_CELL
• SET_GROUP_DATE_CELL

55
Controlling Data Block Relationships

• In a form that contains a master-detail
  relationship, the master and detail block are
  coordinated
• Users can perform coordination-causing events
• Operation that causes the current record in the
  detail block to change

56
Relations in Master-Detail Relationships

• When you create a master-detail relationship, a
  relation object is created in the master block

Master block
Relation
Detail block
57
Important Relation Properties

• Relation Type
• Join caused by joining 2 key fields
• Ref created using a REF pointer
• Detail Data Block
• Name of detail block
• Join Condition
• Name of master and detail items on which blocks
  are joined

58
More Relation Properties

• Delete Record Behavior
• Specifies how deleting a master record affects
  the corresponding detail records
• Values
• Non Isolated master block cannot be deleted
  when detail records exist
• Isolated master block can be deleted when
  detail records exist
• Cascading performs a cascading delete

59
More Relation Properties

• Prevent Masterless Operations
• Specifies whether the user can query or insert
  records in the detail block when no master record
  is present
• Values
• Yes detail records cannot be inserted or
  queried, and error message appears
• No detail block can be used independently of
  master block to insert and query records

60
More Relation Properties

• Deferred and Automatic Query
• Properties that work together to determine
  whether detail block records automatically change
  when the user selects a new master record
• Values
• Deferred No, Automatic Query Yes or No
  detail records are fetched automatically
• Deferred Yes, Automatic Query Yes detail
  records are not fetched until user navigates to
  detail block
• Deferred Yes, Automatic Query No detail
  records are not fetched until user navigates to
  detail block and executes the EXECUTE_QUERY
  built-in

61
Master-Detail Relationship Triggers

• When you create a master-detail relationship,
  Form Builder creates the following triggers
• ON-CLEAR-DETAILS
• clears detail block records
• ON-POPULATE-DETAILS
• coordinates values in master and detail blocks
• ON-CHECK-DELETE-MASTER
• prohibits deleting master record when detail
  records exist
• PRE-DELETE
• executes cascading delete

62
Master-Detail Relationship Triggers

• ON-CLEAR-DETAILS and ON-POPULATE-DETAILS are
  always present
• ON-CHECK-DELETE-MASTER is only present when
  Delete Record value is Non Isolated
• PRE-DELETE is only present when Delete Record
  value is Cascading

63
Master-Detail Program Units

• When you create a master-detail relationship,
  Form Builder automatically creates the following
  program units
• CLEAR_ALL_MASTER_DETAILS
• Clears detail records
• QUERY_MASTER_DETAILS
• Fetches detail records
• CHECK_PACKAGE_FAILURE
• Displays error messages as necessary

64
Changing Relation Properties Programmatically

• Syntax
• SET_RELATION_PROPERTY(relation_name, property,
  value)