Enhanced Guide to Oracle 10g

1. Enhanced Guide to Oracle 10g Chapter 5: Introduction To Form Builder

2. Forms • Why Do We Use Form Builder? • Why Don’t We Use SQL Only?!

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

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

5. Data Block Form • Is a 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

6. Data Block Forms • ORACLE 10g forms are displayed in a web browser as a web page. • They appear in the Forms Services Window within the browser window. • The form specification is translated into Java Applet (those are displayed in web browsers) using a Web server process called OC4J Instant (details: page 264, next slide too). • They are Dynamic Web Pages.

7. Steps of running the form • Design the form and write its code (.fmb file) • Compile the form (.fmx file) When you run the form: • OC4J Instance process converts the compiled file into java applet • Java applet form starts on the web browser

8. Using a Data Block Form • A block is a group of related form items (text fields, option buttons,…). • A data block is a specific DB table, and contains objects (text fields, option buttons) that display values from the table’s data fields. • A single-block form: is the form that displays data from only one table. • A multiple-block form: is the form that displays data from multiple DB tables.

9. Using a Data Block Form • To start Form Builder: • Start, All programs, Oracle Developer Suit-OraHome, Forms Developer, Forms Builder • Open an existing for (.fmb) or design yours and save it (the path name or the form name should not contain spaces). • To run the form, click Program on the menu bar, Run Form. • If you are asked to install the Jinitiator, click yes and accept all default values. • Jinitiator application iniitializes the Java applet in the web browser.

10. Expected Errors • If the error “FRM-90928” appears, it means that the path name or the form name contains space. • If the web page does not appear and the browser diplays the characters http://%%20 or similar. Then the form builder should be configured correctly to use your web browser. (see page 266 to know how to configure it).

11. Form Modes • When the form is running in the Forms Services window in the web browser it can be one of two modes: • Normal Mode • You can view records and sequentially step through records and change data. • Enter Query Mode • 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 on the toolbar. • Execute the query or cancel it to return to Normal mode.

12. 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

13. 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

14. 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

15. 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

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

17. 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

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

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

20. 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

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

22. Object Navigator • Forms Builder Toolbar. • Form Module. See Page 276, next slde. • Hierarchical Display.

23. To create a new Form module or Data block..

24. 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

25. 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 • Enforce Integrity Constraint Check Box • Finish page • Option to use the Layout Wizard to automate creating the layout

26. 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

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

28. 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

29. 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 (the form is presented as a top-level object, and then lists all form object nodes on the next level) . (fig. 5-8, slide 23) • Visual view: hierarchical relationships between objects ( form objects contain other objects: a form contains windows, a window contains canvases and a canvas contains data blocks).(fig. 5-27, next slide) • Details about ownership and visual views is in page 300.

31. 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

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

33. Example: create a Data Block using WIZARD • Click on the Data Block node under the MODULE form node. • Click the + sign or choose create from Edit on the menu bar. • The following window appears: • Choose the first option, now • Then the Ok  • A Welcome page might appear, if so click next • The wizard window appears as shown next slide:

35. Example: create a Data Block using WIZARD • Make sure the table or view is selected, now. Then NEXT • The tables window appears so as to choose the table from your schema, which the data block is based on. • You might need to connect to the DB, if you are not doing yet. Choose the connect button or connect from file on the menu bar. • Select the table, pay attention to the user. • After that choose the field you want to show. • Type the data block name. • Then choose “create the data block, and then call the layout wizard, then FINISH.

36. Example: create a Data Block using WIZARD • The form layout specifies how a given form looks to the user. So a welcome window might appear, click next. • Then a Canvas (the area where the form objects are located) window appear, choose the canvas and its type. For details about the types refer to page 285. • The Data block page appear so you can choose the fields that will be displayed in the form. Fig. 5-18. • Then the items page appears, where the specifications and labels (prompts) can be determined.

37. Example: create a Data Block using WIZARD • You can change the prompts (labels that appears next to each field). • Then choose whether the layout style is FORM or Tabular. • If form is chosen, then determine the title that appears on the frame of the data block, the number of rows that appear in the form style is always one row at a time. If the tabular is chosen, then number can be more. The distance between record is determined in a tabular case. Choose whether to display the scroll bar. • Finish. • The window should appear as in next slide:

39. Example: create a Data Block using WIZARD • You can import a static image to make your form looks better: • Click on the layout editor window shown in the previous slide. • From EDIT choose import  image. • Select the image you like. • Adjust its place on the window. • To run the form so as to see the data of the table: • Run the form using the button or from Program on the menu bar choose run form. Important: If the “FRM-10142” error appears, then run the OC4J instant of the form If the HTML code appears instead of the form on your browser then do the following” from IE chose tools  Internet Options  Advanced  Look for Security value then Check the “allow active controls to run in files on my computer”.

40. Lesson B

41. Creating a Form Based on a Database View • View can be derived from one or more database tables • Form based on a view only allows you to display data from table(s) • Form could be used to insert, update, or delete data if the view is a simple view only. • How to do that ? Select Views from Figure 5-12, see slide 35 when you create the form using the wizard.

42. Modifying Form Properties • Every form object has a Property Palette that allows you to configure form and its objects properties (page 322) • How to open the property palette? From the Object navigator chose windows node, select your window and right click the mouse, choose property palette. OR: • On the layout editor, right click and then select property palette. OR: • See page 323 for other two ways. Context bar Property Nodes Property List

43. Intersection Property Palette • Used to change property of several items to the same value (page 328) • Select the objects together and open the property palette. • Intersection/Union button on the tool bar appears as • When the objects have different values, it appears as *****, and the property node appears with a ? Before it.

44. Frame Properties • The frame is that encloses a data block’s items. • Its update Layout can be: • 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 frame

45. Multiple-Table Forms • Data block forms can display data from multiple tables that have a master-detail relationship in which • Master record can have multiple related (detail) records through foreign key relationship. • Examples: • One FACULTY record can have multiple related STUDENT records. The FACUTLY record is the master record, because a faculty member can have many student records, but a student can have only one related faculty record. • One CUSTOMER might have multiple CUST_ORDER records

46. Multiple-Table Forms • When creating a form that contains a master-detail relationship, you always use the Data Block Wizard to create the master block, which is the block that displays the master records first. • Then start the wizard a second time, and create the detail block, which displays the detail records. • When creating the detail block, the master-detail relationship should be specified in the wizard on a page called Master_Detail page. • That page appears only when you create a data block that already contains one or more data blocks.

47. Master-Detail Forms Master block Detail block

48. 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

49. 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 student has an advisor and also enrolled in courses. page 342. • 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 • A faculty member can have many students and a student can be enrolled in many classes. Page 342.

50. Complex Master-DetailRelationship Example Master Detail Detail Master