1 / 56

Enhanced Guide to Oracle10g

Enhanced Guide to Oracle10g. Chapter 6: Creating Custom Forms. Data Block and Custom Forms. Data block form Based on data blocks that are associated with specific database tables Reflect the structure of the database Custom form

bran
Download Presentation

Enhanced Guide to Oracle10g

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Enhanced Guide to Oracle10g Chapter 6: Creating Custom Forms

  2. Data Block and Custom Forms • Data block form • Based on data blocks that are associated with specific database tables • Reflect the structure of the database • Custom form • Based on control blocks that process data from multiple tables • Reflect business processes rather than the database structure

  3. Creating a Custom Form • Create the form • Create the form window and canvas manually • Create a control block • Data block that is not associated with a specific table • Contains form items that you manually draw on the canvas • Create form triggers to process data

  4. Form Triggers • Code that is associated with a form object and an event • Can contain SQL INSERT, UPDATE, DELETE, and SELECT commands • Referencing form text item values in triggers: :block_name.item_name

  5. Program Units • Self-contained programs • Can be called from PL/SQL triggers • Used to make triggers more modular, and reuse code within triggers

  6. Practice1 • Create a custom form that has: • Employee Name • Employee Salary • Department Name • Create a LOV to retrieve block records • Use triggers to retrieve block records • Create a button that calls a program unit to clear the block.

  7. Referencing System Date and Time Values in Forms

  8. Form Triggers • Categories • Block processing • Interface event • Master-detail processing • Message handling • Navigational • Query time • Transactional • Validation

  9. Trigger Timing • PRE- • Fires just before associated event occurs • POST- • Fires just after associated event occurs • ON-, WHEN-, KEY- • Fires immediately, in response to specific user actions, such as key presses

  10. Trigger Scope • Defines where an event must occur to make the trigger fire • Trigger scope includes the object to which the trigger is attached, as well as all objects within that object • Form-level: fires when event occurs within any block or item in the form • Block-level: fires when event occurs within any item in the form • Item-level: fires only when event occurs within that item

  11. Trigger Execution Hierarchy • If 2 related objects have the same trigger, the lower-level object’s trigger fires instead of the higher-level one. • Form & block, block’s trigger fires • Block & item, item’s trigger fires • You can specify a custom execution order using the Execution Hierarchy property of the trigger.

  12. Navigational Triggers • External navigation: occurs when user causes form focus to change • Internal navigation: occurs as a result of internal form triggers that fire in response to external navigation events

  13. Triggers That Fire at Form Startup Result on User Screen Display Triggers Fired User Action 3 2 1 Form appears, but with no data visible PRE-FORM PRE-BLOCK User starts form 4 5 WHEN-NEW-FORM-INSTANCE WHEN-NEW-BLOCK-INSTANCE WHEN-NEW-RECORD-INSTANCE WHEN-NEW-ITEM-INSTANCE Form is available for use

  14. Triggers That Fire as a Result Of External Navigation Result on User Screen Display Triggers Fired User Action User places the insertion point in a text item Insertion point appears in item WHEN-NEW-ITEM-INSTANCE User clicks the Next Record button Next record appears WHEN-NEW-RECORD-INSTANCE WHEN-NEW-ITEM-INSTANCE

  15. Triggers That Fire When a Form Closes Result on User Screen Display User Action Triggers Fired User closes the Forms Runtime window POST-BLOCK POST-FORM Forms Runtime window closes

  16. Practice2 • Create a department data block. • Create an Update button. • Create a Cancel button that uses a procedure.

  17. Directing External Navigation • Form tab order is based on item order in Object Navigator block list Form tab order

  18. Moving to a Specific Form Item • GO_ITEM(‘block_name.item_name’); • Do not write colon(:) before the block name in this commnd. • E.g: • GO_ITEM(‘emp.sal’); • Other navigational instructions: • GO_BLOCK(‘block_name’) • GO_FORM(‘form_name’)

  19. Oracle Error Message Severity Levels • 5: informative message • 10: informative message advising user of procedural error • 20: condition that keeps trigger from working correctly • 25 condition that keeps form from working correctly • >25: extreme severity

  20. Suppressing Lower Level System Messages • Set :SYSTEM.MESSAGE_LEVEL variable to a higher level in PRE-FORM trigger :SYSTEM.MESSAGE_LEVEL := 25;

  21. Providing User Feedback in Forms • Message • Text in message line at bottom of form • Informational only; user doesn't have to respond • Alert • Dialog box • Allows user to choose different ways to proceed

  22. Messages • Syntax: MESSAGE(‘message text’); Message

  23. Alerts • Form-level object • Object properties define alert appearance Message Title Style icon Buttons

  24. Code for Displaying an Alert DECLARE alert_button NUMBER; BEGIN alert_button := SHOW_ALERT('alert_name'); IF alert_button = ALERT_BUTTON1 THEN program statements for first button; ELSE program statements for second button; END IF; END;

  25. Avoiding User Errors • Make primary and foreign key text items non-navigable (How?) • When user moves form focus to primary or foreign key text item, move focus to alternate form item (How?)

  26. How to make nonnavigable item? • Set the text item property Keyboard Navigable to NO • Use When New Item Instance or When Mouse Up triggers to make internal navigation from the nonnavigable item.

  27. Trapping Runtime Errors • Create an ON-ERROR event trigger • Form-level trigger • Executes whenever an FRM- or ORA- error occurs • -FRM errors: generated by Forms Runtime • -ORA errors: generated by database

  28. Form Procedures That Return System Error Information • DBMS_ERROR_CODE: The most recent -ORA error number. (Negative Number) • DBMS_ERROR_TEXT: -ORA error message and number. • ERROR_CODE: The most recent FRM error number. (Positive Number) • ERROR_TEXT: FRM error message and number.

  29. Structure of ON-ERROR Trigger BEGIN --trap FRM errors IF ERROR_CODE = FRM_error_code1 THEN error handler; ELSIF ERROR_CODE = FRM_error_code2 THEN error handler; … ELSE --trap ORA errors IF DBMS_ERROR_CODE = -ORA_error_code1 THEN error handler ELSIF DBMS_ERROR_CODE = -ORA_error_code2 THEN error handler … END IF END IF; END; Code to trap –FRM errors Code to trap –ORA errors

  30. Form Validation • Ensures that form data meets preset requirements so erroneous data is not sent to database • Using validation properties or using validation triggers.

  31. Form Validation Categories • Data • Specifies data types, lengths, and maximum and minimum values • Database • Specifies which operations a user can perform on a text item • List of Values • Specifies whether a data value must be validated against the text item’s LOV (Validate From List text item property)

  32. Validation Properties • Validation unit form property: specifies the largest data chunk that the user can enter before validation occurs • Can be performed at the form, block, record, or item level • Specified in the Validation Unit property on the form Property Palette • By default, it is set to item level.

  33. Cont. • You should use the item level with the custom forms, and the record or block level with the data block forms. • The validation properties that are checked always and before checking other properties and triggers are: format mask, required, datatype, range, and validate from list

  34. Text Item Validation Properties • Data Type • Minimum Value • Maximum Value • Validate From List • Insert Allowed • Query Allowed • Required • …

  35. Question Why do not we use validation properties instead of validation triggers?

  36. Answer We use validation triggers for more complex validations. (like what?)

  37. Validation Triggers • When Validate Item • It should be Item level trigger. • Executes when the item validation occurs depending on Validation Unit property. • E.g. : IF NOT :student.s_class IN (‘FR’,’SO’) THEN Message(‘Legal Values are FR and SO); RAISE FORM_TRIGGER_FAILURE; END IF;

  38. How To Disable Buttons • SET_ITEM_PROPERTY • E.g. : SET_ITEM_PROPERTY(‘Control.Delete’ , ENABLED, PROPERTY_FALSE);

  39. Data Blocks vs. Control Blocks • Data block • Easy to create and use • Is associated with a single table, and reflects the table’s structure • Control block • Requires a lot of custom programming • Can contain items from many different tables • You can link data and control blocks to take advantages of the strengths of each

  40. Linking Data Blocks and Control Blocks • Create the control block as the master block • Create the data block as the detail block, but do not create a master-detail relationship • Create a master-detail relationship manually in the WHERE Clause property of the detail block: data_block_field := control_block.text_item

  41. Displaying and Refreshing the Data Block Values • Create a trigger to: • Place the insertion point in the data block GO_BLOCK(‘block_name’); • Flush the data block to make its data consistent with the master block and the database: EXECUTE_QUERY;

  42. Converting a Data Block to a Control Block • Create a data block and layout that contains most of the required text items • Convert the data block to a control block by changing the following block properties: • Database Data Block = No • Required = No

  43. Creating a Form with Multiple Canvases • Users should be able to see all canvas text items without scrolling • For complex applications with many text items, divide application into multiple canvases

  44. Block Navigation Order • First block in Object Navigator Data Blocks list determines block items that first appear when form opens • Users can use the Tab key to navigate among different block items • Canvas that contains block items automatically appears

  45. Block Navigation Order Block order Canvas order doesn’t matter

  46. Controlling Block Navigation • Block Navigation Style property • Same Record: navigation cycles through items on same block • Change Data Block: navigation moves to next data block in list

  47. Canvas • It is the surface that has the form items on it. • It has many types: • Content (The default) • Tab • Stacked • … • To display the canvas at runtime, use the canvas property ‘Window’.

  48. Tab Canvases • Multiple-page canvases that allow users to move among different canvas surfaces by clicking tabs

  49. Tab Canvas Components • Tab canvas • Collection of related tab pages • Tab pages • Surfaces that display form items • Tab labels • Identifier at top of tab page • A tab canvas lies on top of a content canvas

  50. Creating a Tab Canvas • Use the Tab Canvas tool on the Layout Editor tool palette to draw a tab canvas on an existing content canvas • By default, a new tab canvas has 2 tab pages • Create new tab pages as needed • Adjust tab page properties

More Related