Enhanced Guide to Oracle8i

1. Enhanced Guide to Oracle8i Chapter 6: Creating Oracle Data Block Forms

2. Forms • Application with a graphical user interface that looks like a paper form • Used to insert, update, delete and view database data

3. Primary Form Uses • Viewing/retrieving records • Inserting/updating/deleting records • Counting the number of records a query will retrieve

4. Data Block Forms • Form associated with a specific Oracle database table • System automatically creates: • Text fields associated with table fields • Programs for inserting, modifying, deleting, and viewing data records

5. Data Block Form

6. Form Modes • Normal • You can view records and sequentially step through records • Enter Query • You can enter search parameters in form fields and then retrieve the associated records • To place the form in Enter Query mode, click the Enter Query button

7. Inserting New Records • In Normal mode, click the Insert Record button to insert a new blank record • Type the data values in the form fields • Click the Save button to save the values in the database

8. Retrieving Records • Click the Enter Query button to place the form in Enter Query mode • Type a search condition • Click the Execute Query button to retrieve selected records • If you click , do not enter a search condition, and then click , all table records will be retrieved

9. Form Search Types • Exact search: only retrieves records that exactly match the entered search condition • Restricted search: retrieves records that fall within a range of values

10. Restricted Search Operators • _: wildcard replacing a single character • %: wildcard replacing multiple characters • >, <: greater than, less than • >=, <=: greater than or equal to, less than or equal to • <> or !=: not equal to

11. Scrolling Through Retrieved Records • To view the next record, click the Next Record button • To view the previous record, click the Previous Record button

12. Updating and Deleting Records • Retrieve the record to be updated or deleted • To update, change the data value and click the Save button • To delete, click the Remove Record button

13. Data Block Form File Types • .fmb • Form design file, used by form programmer in Form Builder • .fmx • Form executable file, run by form users

14. Form Components • Form module: form application • Form window • Title bar on top • Horizontal and vertical scrollbars • Can be resized, maximized, minimized

15. Form Components • Canvas • Surface that displays form items • Block • Object that contains form items • Form items • Command buttons, radio (option) buttons, text items • Items in the same block can appear on different canvases

16. Form Components Window Canvas Name Block of items Cash Check Credit Card

17. Creating a Data Block Form • Steps: 1. Create the data block using the Data Block Wizard 2. Create the form layout using the Layout Wizard

18. Data Block Wizard • Welcome page • Type page • Select table or view on which to base the block source • Table page • Select table associated with form • Finish page • Option to use the Layout Wizard to automate creating the layout

19. Layout Wizard • Canvas page • Select the canvas where the block is displayed • Data Block page • Select the fields that are displayed on the layout • Items page • Specify the column labels, heights and widths • Style page • Specify to create a form- or tabular-style layout

20. Layout Styles • Form style • One record appears on the form at a time • Tabular style • Multiple records display on the form in a table

21. Form Builder Wizards Are Re-entrant • You can select a block or layout, then re-enter it and modify its properties using the Wizard • A Wizard is in re-entrant mode when its pages appear as tabs

22. Form Builder Object Navigator Window • Shows form objects • Allows developer to access form objects • Object Navigator has 2 views • Ownership view: shows form objects as a flat list • Visual view: hierarchical relationships between objects

23. Guidelines For Configuring Form Windows • Change the window title • Allow user to minimize the window • Do not allow the user to maximize the window • Do not allow the user to resize the window • Include horizontal and vertical scrollbars if items appear beyond window boundaries • Make the form window fill the Forms Runtime window

24. Configuring Form Windows Window Title Minimize/ Maximize buttons Window size

25. Creating a Form Based on a Database View • View can be derived from multiple database tables • Form based on a view allows you to display data from multiple tables • Form cannot be used to insert, update, or delete data

26. Modifying Form Properties • Every form object has a Property Palette that allows you to configure form properties Property Nodes Property List

27. Intersection Property Palette • Used to change property of several items to the same value • Intersection/Union button appears as • Name appears as *****

28. Frame Properties • Update Layout • Automatically • Frame items are automatically repositioned based on their order in the Data Blocks list when you move or resize a frame, or modify the form layout using the Layout Wizard in reentrant mode • Manually • Items are automatically when layout is modified in Layout Wizard in reentrant mode • Items are not repositioned if you move or resize the frame • Locked • Items are never automatically repositioned

29. Multiple-Table Forms • Data block forms can display data from multiple tables that have a master-detail relationship • Master record has multiple related detail records • Examples: • One ITEM record has multiple related INVENTORY records • One CUSTOMER might have multiple CUST_ORDER records

30. Master-Detail Forms Master block Detail block

31. Creating a Master-Detail Form • Create the master block first • Specify the relationship on the Data Block Wizard Master-Detail page of the detail block

32. Complex Master-Detail Relationships • A master block can have multiple detail blocks • An item can be in multiple customer orders, and have multiple shipment records • A detail block can be a master block in a second master-detail relationship • A customer can have multiple CUST_ORDER records • One CUST_ORDER record can have multiple ORDER_LINE records

33. Complex Master-DetailRelationship Example Master Detail Detail Master

34. Formatting Text Items • Specify desired format mask in text item Format Mask property • If format mask makes value wider than text item Data Width property, data appears as ***** • If format mask makes value wider than Visible Width property, data appears clipped

35. Formatting Character Text Items • Precede format mask with “FM” • Place embedded characters in double quotes

36. Form Triggers • PL/SQL procedure that runs in response to a user or system action • Clicking a button • Loading a form • Exiting a form • Triggers can be associated with: • An entire form (form level) • A block (block level) • A specific form item (item level)

37. Using Form Triggers • Creating a trigger: • select the form or form item event • type the trigger code • Referencing a form item in PL/SQL code: • :block_name.item_name • Triggers are compiled before a form is run

38. Using Sequences in Forms • Create a block-level WHEN-CREATE-RECORD trigger • Trigger PL/SQL code: SELECT sequence_name.NEXTVAL INTO :block_name.item_name FROM DUAL;

39. Form Lists of Values (LOVs) • List of legal values that can be selected for use in a form field LOV command button LOV display

40. Creating an LOV Using the LOV Wizard • Use the LOV Wizard to: • Specify the LOV display records using a SQL query • Format the LOV display • Attach the LOV to a form text item • Change the LOV and record group name in the Object Navigator

41. Opening the LOV Display • Place the insertion point in the text item to which the LOV is attached • Press F9

42. Creating a Command Button to Open the LOV Display • Create a command button on the canvas • Iconic button • Displays an icon image from an .ico file • Complete path to .ico file must be specified in button’s ICON property

43. LOV Command Button Trigger • Create a trigger for the button that does the following: • Places the form insertion point in the text item to which the LOV is attached: GO_ITEM(‘IN_VID’); • Executes the LIST_VALUES command: LIST_VALUES;

44. Creating a Text Item Editor • Editor: dialog box for editing character, number, or date values in text items • Larger editing area than text item • Provides Search and Replace functions

45. Editor Example

46. Default Editor • Every text item has a default editor • To open the default editor, place the insertion point in the text item and press Ctrl + e • e must be lower case

47. Custom Editors • Provides same editing functions as the default editor • Has a customized window size, title, and appearance

48. Creating a Custom Editor • Creating a new Editor object in the Object Navigator • Configure the Editor object properties • Attach the custom editor to the text item

49. Alternate Form Input Items • Radio buttons • Check boxes • Lists

50. Radio Buttons • Limits user to one of two or more related, mutually exclusive choices