Enhanced Guide to Oracle8i

1. Enhanced Guide to Oracle8i Chapter 8: Advanced Form Builder Topics

2. Non-Input Form Items • Form items that display data but don’t 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 don’t 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, AIFF< 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-INSTANCE 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-COMMIT 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 block’s DML Array Size property value to a larger value • To enable query array processing, change the data block’s Query Array Size property to a larger value

37. Enabling Array Processing • To enable DML array processing: • Change the data block’s DML Array Size property value to a larger value • To enable query array processing: • Change the data block’s 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