Oracle Unleashed- P14

Chia sẻ: Thanh Cong | Ngày: | Loại File: PDF | Số trang:50

lượt xem

Oracle Unleashed- P14

Mô tả tài liệu
  Download Vui lòng tải xuống để xem tài liệu đầy đủ

Oracle Unleashed- P14: When I first started using Oracle many years ago, it was possible to know the database and the tools available. With the rash of recent releases of different options for the database and the spate of new tools, only people who wear their underpants over their trousers will be able to know everything there is to know about the Oracle products.

Chủ đề:

Nội dung Text: Oracle Unleashed- P14

  1. The module access options define whether modules should be opened from the database or the file system (or both) and what types of files should be included in the selection. The last option defines the printer that should be used for any printing requirements in the Forms Designer. The second tab in this window (shown in Figure 32.4) is used to define the options to be used when a module is run from within the Oracle Forms Designer. The first option, Buffer Records, will cause Forms to buffer only the minimum number of records (the number of records displayed plus three) in memory. All additional rows retrieved will be stored in a temporary file. The next option causes the form to be executed in debug mode. This option allows the developer to insert break statements in the PL/SQL segments to observe the values in form items and to trace the execution of the program code. Figure 32.4. Setting Oracle Forms runtime options. The next four options are related to performance tuning of Oracle Forms. Array processing allows the form to return multiple rows from the database in a single fetch cycle rather than one at a time. This usually causes better performance; however, fetching more than one row at a time may impact the memory usage in the form. In order to maintain backward compatibility, Oracle Forms allows the developer to use Version 2-style triggers (separate processing steps) in the form. The Optimize SQL Processing option causes these triggers to be processed using an optimization technique to take advantage of the more "modern" capabilities that have been incorporated into PL/SQL. Transaction Mode Optimization causes all implicit SQL statements (for example, posting and committing triggers) to optimize cursor usage so that the cursors may be shared within the form. The Statistics option will return statistics regarding cursors and other resource utilization when the form is run. The other effect of this option is that a SQL Trace session will be generated, which can be analyzed by TKPROF or another performance-analysis tool to assist with tuning. The next option will display the block menu for a form—rather than the form itself—as soon as the form starts up. This menu will allow the developer to navigate directly to a particular block rather than to the initial default. Query-only mode disables any inserts, deletes, or updates in a form. Finally, Quiet mode "turns off" the audible beep that is played whenever a message is generated in the form. Creating New Forms Modules A form may be created using the File | New | Form menu choice or by using the create-form hot key (Ctrl+Y). The new form will be added to the Object Navigator. Additionally, whenever the Oracle Forms Designer is started, a new empty form is automatically created. Similarly, program-unit libraries (Ctrl+I) and menu modules (Ctrl+-E) may also be created from the menu. These modules may in turn be saved, either by running them (with the appropriate options selected) or by explicitly executing a save from the File menu or using the iconic button in the Designer windows. Building a Simple Default Form Oracle Forms' feature of intelligent defaulting allows the developer to create a basic form in mere minutes. As an example, the Warehouse Maintenance form (shown in Figure 32.5) can be created quickly without writing a single line of program code. Figure 32.5. Warehouse Maintenance form. To build this form, first create a new form in the Object Navigator (log into the database if you have not already done so) and select the Blocks group within the new form. The block-definition dialog box will appear as shown in Figure 32.6. This form will be based on the WAREHOUSES table; therefore, enter it into the base table field and navigate to the next field using the Tab key. (Alternatively, the table name can be obtained using the Select button to the right of the table name field. This button will present a dialog box that may be used to list all the available tables in the database.) Notice that by default the name of the block changes to match the table name, although the name can be any value the developer Please purchase PDF Split-Merge on to remove this watermark.
  2. desires. Hit the Tab key again to navigate to the canvas field, which has defaulted to a value such as CANVAS1. Change the name of this field to WH_CANVAS. Figure 32.6. Block-definition dialog box. Once this box is completed, click on the Items tab to present the Item-definition dialog box shown in Figure 32.7. To obtain the columns in this table, click on the Select Columns button and all columns in the table will be listed. A plus sign preceding the column indicates that the column will be used in the form. To exclude a column, double-click on the column name in the list box. For each column listed, modify the column label and width as indicated in Table 32.2. Table 32.2. Warehouse Maintenance Form Column Definitions. DB Column Label Width WH_CODE Code 35 WH_NAME Warehouse 150 WH_OPEN_DATE Opened 60 WH_CLOSE_DATE Closed 60 Figure 32.7. Default-column definition dialog box. After the column definition is completed, the form layout should be defined using the Layout tab as shown in Figure 32.8. This form presents multiple rows in a tabular arrangement with the individual rows arranged vertically. The records field representing the maximum number of records displayed should be 8, and there should be 0 spacing between the records. Other options are used to determine whether integrity constraints should be enforced for the form and if a button palette is needed for the table. (The button palette will create a default set of iconic buttons that can be used for table maintenance.) The last checkbox will include a scrollbar in the form that can be used to navigate through the block if all records cannot be displayed at one time. Figure 32.8. Defining form style and layout. After this dialog box has been completed, click the OK box to finish constructing the form. To test the form, run it by clicking the Run icon or choose the Run option in the File menu. (Note: This chapter will assume that the Designer options Save Before Generate and Generate Before Run are selected. Otherwise these steps must be done manually prior to running the form.) The form will appear, as shown previously in Figure 32.5, with all of the data boxes empty. To list the existing data, select the Query | Execute menu choice. Data may be entered into the fields and then saved using the Action | Save menu choice, and the Action | Exit menu choice is used to close the form. Using Boilerplate Text and Graphics to Enhance Forms Applications The previous example does not provide the three-dimensional look and feel that is common in most Windows software. Please purchase PDF Split-Merge on to remove this watermark.
  3. By utilizing some of Oracle Form's graphical objects and visual effects, you can transform the previous example into a more aesthetically pleasing application, as shown in Figure 32.9. Figure 32.9. Using boilerplate objects to enhance applications. To modify this form, first select the block title in the Layout Editor and remove it using the Delete key. Do the same with the box around the data grid. Now, using the select tool, select all of the column headings on the screen. To modify the font, choose the Format | Font menu choice to display the font selection dialog box as shown in Figure 32.10. In this case, choose the Arial font, Bold Italic style, and size 9 (A sample of the selected font will be shown within the dialog box.) and click the OK button to change the fonts. Figure 32.10. Font-selection dialog box. Now, position the column headings at the center above the column (defaults to left-justified using the Align Objects tool. To do this, click on the column heading text for the Warehouse Code and then while holding the shift key, click on the code field. Using the Arrange | Align Objects menu choice, choose the alignment options as shown in Figure 32.11. Repeat for all four columns. Figure 32.11. Align Objects dialog box. Now, select the Rectangle tool, and draw a rectangle as shown in Figure 32.9 so that it borders both sides and the bottom of the data grid. (Use the sizing anchors to resize and position the rectangle if necessary.) To create the three- dimensional effect, select the Format | Bevel menu choice and select the lowered effect to create the appearance of a lowered block on the screen. The depth may be adjusted using the Format | Line menu choice. Create another smaller rectangle overlapping the previous rectangle for the screen label. Using the Text tool, create the screen label (WAREHOUSES) using an appropriate font. Using the mouse, arrange the objects to appear on the screen as shown in the example. Sometimes the intelligent defaulting feature of Oracle Forms causes objects to appear differently than desired, especially regarding the fill and border-line visual attributes for the object. These can be corrected using the attribute palette buttons below the current visual-attribute display in the Layout Editor. Also, the Arrange menu provides options to move objects in front of or behind other objects. Customizing Forms with Properties As previously mentioned, Oracle Forms' intelligent defaulting capabilities can be used to create useful applications. In many cases, however, it is necessary to define specific characteristics for application objects. This can be done by modifying the properties of the form objects using the object property sheets. To examine properties, again load the form from the previous section. Each object in the form (including the form itself) has an associated context-sensitive property sheet. The properties for the objects are organized in logical groups based on usage. To change a property, select the object in the Object Navigator. If the property sheet is visible in the split window arrangement, the property sheet will automatically display for the object. If the property sheet is not visible, double-click on the icon at the left of the object name in the Object Navigator to view the property sheet. Usually, only some of the properties are visible in the window and a scrollbar is available to view the other properties. To modify a property, click on the property in the property sheet. The value of the property will be copied to the top line of the property sheet window. Edit this line and press return to modify the property. Please purchase PDF Split-Merge on to remove this watermark.
  4. The first custom modifications to be made will affect the overall application appearance. First select the property sheet for the form window. Change the Window Title to "Warehouse Maintenance Form," then change the window width to 382 and the height to 200. To prevent a user from using the Windows function to resize the window frame or to minimize the form, update the Fixed Size property to True and the Iconifiable property to False for the form window. Additionally, repeat the sizing properties for the WH_CANVAS canvas. Now, select the WH_CODE item and change the Update Allowed property to False. This change will protect the primary key for the warehouses table from being changed. Finally, select the WH_OPEN_DATE and WH_CLOSE_DATE fields together. Notice that the common property sheet shows that multiple objects have been selected and that in cases where the properties are different, a set of asterisks is shown. In this case, modify the Format Mask property to be MM/DD/YY. As a final step, change the font and sizing for all of the fields in the warehouse record, by selecting all of them and displaying the common property sheet. Modify the height to equal 14 and change the font name to Arial with a size of 8 points and a weight of bold. Upon completion of these changes, save and run the new form. The result should look like the form shown in Figure 32.12. Compare this form with the form created in the previous section to see how the property changes have affected the resulting form module. Figure 32.12. Example form demonstrating custom properties. Implementing Triggers and Program Units Oracle Forms applications can be further customized by developing PL/SQL procedures and functions called triggers. These triggers are attached to specific activities in the form called events. Events typically are defined as before (PRE- FORM, PRE-QUERY, PREINSERT), after (POST-FORM, POST-QUERY, POST-INSERT), or during (WHEN-NEW- FORM-INSTANCE, WHEN-BUTTON-PRESSED) common database activities. Additionally, triggers can be associated with certain keyboard activities (for backward compatibility with character-based applications), although the trend is to minimize key triggers. The customer maintenance form shown in Figure 32.13 utilizes a pre-insert trigger to determine a unique customer number based on a sequence generator. Additionally, triggers are defined for the buttons at the bottom of the form. Figure 32.13. Customer Maintenance form. To construct this form, create a block for the CUSTOMERS table using a form style in the block layout definition. Arrange the items and boilerplate objects as shown in the figure and using the button tool, create three buttons as shown on the screen (These buttons will be labeled as PUSH_BUTTONx.). Using the Size Objects and Align Objects tools in the Arrange menu, position and size the form objects to appear as shown. Now, define the object properties as needed by defining the window and canvas sizes and titles. Next, because the customer number should be protected from update, change the Update Allowed and Navigable properties for the CUST_NO item to False. Modify the label properties for the buttons to reflect the text that is shown in the figure. At this point, the additional processing logic may be added to the form. First, select the triggers group directly below the CUSTOMERS block in the Object Navigator. Click on the Add Object button and a list will appear with the names of all allowable triggers that can be built for the form. The trigger to be built will determine the customer number for a new customer prior to insert based on the database sequence generator. While the list is displayed, either use the scroll bar to find the PRE-INSERT trigger or type the trigger name into the input box. Please purchase PDF Split-Merge on to remove this watermark.
  5. Generally, lists in Oracle Forms use an algorithm to narrow a list as keys are typed. In this case after typing P and R, the list will show all PRE-x triggers and the input line will type ahead because no other triggers exist that begin with those letters. Type an I and the PRE-INSERT trigger will be the only trigger in the list. The PL/SQL editor (shown in Figure 32.14) will now appear, indicating the trigger level and the name of the trigger with an area that may be used to enter the trigger procedure as shown. Once the text has been entered, click the Compile button to make sure that there are no code errors and then click the Close button to complete the trigger definition. Note the colon used to reference form fields. Other buttons can be used to revert to the version prior as of the last close or compilation, to create a new trigger, or to delete the existing trigger. Figure 32.14. PL/SQL editor for trigger creation. Finally, WHEN-BUTTON-PRESSED triggers should be created for the three buttons that were created on the form canvas as follows: Save: begin commit_form; end; Clear: begin clear_form; end; Exit: begin do_key ('EXIT_FORM'); end; The first two triggers use standard built-in procedures, while the third uses the DO_KEY built-in procedure to simulate pressing the Exit key. The Exit key is defined by the keyboard mapping for your display device. On the IBM PC keyboard, this normally defaults to the Esc key, but may be modified using Oracle Terminal. To view the actual key mapping during Oracle Forms runtime, type Cntrl-K or select show keys from the help menu in the form. This is generally a good practice to use when there is more than one way to perform the same function. Any special logic that needs to be performed prior to exiting the form can be coded in a KEY-EXIT trigger to ensure consistency. Additionally, a trigger can be written so that it calls a user defined program unit. For example, if the customer maintenance form changes the credit limit for a customer, an acceptance letter should be sent to the customer. The POST- COMMIT trigger should be written as follows: begin Please purchase PDF Split-Merge on to remove this watermark.
  6. if :customers.cust_credit_limit > 0 and :customers.old_credit is null then print_confirmation_letter (:customers.cust_no); end if; end; The print_confirmation_letter procedure would then be created as a program unit in the form. The actual logic for this procedure will be discussed in Chapter 36 in the section describing integration of Oracle Forms and Oracle Reports. Using Record Groups and Lists of Values for Data Validation A record group is an internal structure that is analogous to a table in memory. Record groups contain columns and rows with data based on a structure defined by a query or column definition. Usages of record groups include parameters, structured arrays, and validation entities. This last usage of record groups will be demonstrated in the Item Price Maintenance form shown in Figure 32.15. Figure 32.15. Item Price Maintenance form. To build this form, start with a default block for the items table using the form style layout. Next, change the Displayed Canvas to and the Displayed property to False for the ITEM_PL_ID, ITEM_PC_ID, ITEM_PROD_NO, ITEM_CP_NO, and ITEM_SIZE_CODE items. These items will be hidden from view and will be updated by the List of Values validations that will be constructed for this form. Also, make the ITEM_NO field non-updatable and non- navigable. To make this form more user-friendly, the translations for the code fields will be displayed as the input fields in the form. Using the Field tool, create five new fields as PL_NAME, PC_NAME, PROD_NAME, CP_NAME, and SIZE_DESC. The properties for these fields are shown in Table 32.3. Table 32.3. Item maintenance description fields. Property Value Canvas ITEM_CANVAS Displayed True Width 200 Height 200 Database Table Item False Please purchase PDF Split-Merge on to remove this watermark.
  7. These fields will need to be populated with data whenever an ITEMS record is queried. To do this, create the POST- QUERY trigger on the ITEMS block as follows: begin select pl.pl_name, pc.pc_name, prod.prod_name, cp.cp_name, s.size_desc into :items.pl_name, :items.pc_name, :items.prod_name, :items.cp_name, :items.size_desc from product_lines pl, product_classes pc, products prod, color_patterns cp, sizes s where pl.pl_id = pc.pc_pl_id and pc.pc_pl_id = prod.prod_pl_id and pc.pc_id = prod.prod_pc_id and prod.prod_pl_id = :items.item_pl_id and prod.prod_pc_id = :items.item_pc_id and prod.prod_no = :items.item_prod_no and cp.cp_no = :items.item_cp_no and s.size_code = :items.item_size_code; exception when NO_DATA_FOUND then message ('Database Integrity Error. Contact your DBA.'); bell; raise FORM_TRIGGER_FAILURE; end; This trigger has been written this way because in a client/server application, a single query is much more efficient than multiple independent queries. Because each of the five fields that must be loaded can be obtained through a unique row query, they can be merged in a single query step. Another (and probably better) approach would be to create a view that retrieves these values in the initial query. A view could always be used as the base table for a block. The exception step introduces a few new concepts regarding triggers. The message built-in is used to send a message to the user screen and the bell built-in will play an audible beep from the user's terminal. Also, the FORM_TRIGGER_FAILURE exception that is raised prior to exiting the trigger will cause all processing to be aborted on failure. Now the lists of values should be defined for each of the fields. To create a list of values, select LOVs in the Object Please purchase PDF Split-Merge on to remove this watermark.
  8. Navigator and click the Add Object button. The new LOV dialog box will appear as shown in Figure 32.16. In the Query Text box, type in the query against the PRODUCT_LINES table as shown and click the OK button when finished. This will create a new record group and associate it with the new LOV. (Note that a list of values may also be created based on an existing record group.) Figure 32.16. Creating a new list of values. Now, select the new LOV and its property sheet. Change the name of the LOV to PRODLINE_LOV and select the Column Mapping property in the property sheet. A button will appear in the Value Edit box with the label More. Click this button to display the column mapping dialog box as shown in Figure 32.17. The column names from the query will be displayed in a table with the characteristics for the column shown below the table. To hide the ID column from the display, set the display width equal to 0. Select the PL_NAME column and set its display width to 150 and change the column title to Product Lines. Click OK to complete the column mapping. Now attach this LOV to the PL_NAME field in the ITEMS block by selecting its property sheet and scrolling towards the bottom to the Miscellaneous Properties section. Change the LOV property to PRODLINE_LOV and set the LOV X Position and LOV Y Position to 100 and 50, respectively. Finally, set the LOV For Validation property to True. This will cause the form to make sure that the value entered is valid without having to write a validation trigger. (To ensure data integrity, a WHEN-VALIDATE-ITEM trigger may be written for this field to "null out" the Product Class and Product Name fields whenever the Product Line is changed). Create a List of Values for each of the remaining non database fields. (Use the POST-QUERY trigger to determine the columns and tables for the mapping.) Figure 32.17. Column mapping for a list of values. Finally, to complete the form, create a PRE-INSERT trigger on the items block to select the next ITEM_SEQ value from the sequence generator as the value for a new ITEM_NO. The form should then be saved and generated to test this concept. A couple features to note are that the list can be activated using the List Values key (F9 in most standard IBM PC configurations. To see a list of defined keys, select Help | Show Keys from the menu) and that the validation feature allows the user to type only part of the field name to narrow the list. For example, type T into the Product Line field and then hit the tab key. The complete name, Terminal Tackle, will be filled in. Using Relationships to Link Separate Blocks in a Form Thus far, all of the examples discussed here have used only a single block for data. The key feature of Oracle (or other relational databases) is that the tables are related to each other by key fields. An example of the use of related tables is shown in Figure 32.18 in the Order Entry Form. Figure 32.18. Order entry form. To construct this form, first create a default block for the Orders table using the form layout style. Create non-database fields for customer name, address, and city using the Display Field tool. (A display field is used to display data but does not need to provide input capability). Also, create a list of values for the customer number field and define the order number and order date fields as non-navigable. (These fields will be populated in a pre-insert trigger.) A post-query trigger should be written to populate the customer information. Now, create a second block for the order_items table using a vertical tabular style that will display five rows of data. Modify the properties for the OI_ITEM_NO field so that it is not displayed and is assigned to the canvas. The only field that will remain on the canvas will be OI_QTY. Create non-database fields in this block for CATALOG_NO, ITEM_DESC, LIST_PRICE, ITEM_TOTAL using the Display Field tool. Also, create a numeric, non-displayed field ITEM_PRICE on the null canvas. Create a POST-QUERY trigger for the ORDER_ITEMS block as follows: begin Please purchase PDF Split-Merge on to remove this watermark.
  9. select i.item_pl_id||i.item_pc_id||'-'|| ltrim (to_char (i.item_prod_no, '099999'), ' ')||'-'|| ltrim (to_char (i.item_cp_no, '09'), ' ')||'-'|| i.item_size_code, p.prod_name||decode (cp.cp_name, 'N/A', ' ',' '||cp.cp_name||' ')|| s.size_desc, i.item_price into :order_items.catalog_no, :order_items.item_desc, :order_items. item_price from items i, products p, color_patterns cp, sizes s where items.item_no = :order_items.oi_item_no and p.prod_pl_id = i.item_pl_id and p.prod_pc_id = i.item_pc_id and p.prod_no = i.item_prod_no and cp.cp_no = i.item_cp_no and s.size_code = i.item_size_code; :order_items.list_price := ltrim(to_char(:order_items.item_price, '990.00'), ' '); :order_items.item_total := ltrim ( to_char ((:order_items.oi_qty * :order_items.item_price), '990.00'), ' '); end; Oracle Forms does not allow for a format mask on display items. For that reason, the fields should be defined as character fields and the item should be formatted using PL/SQL. Using right justification, the fields can be displayed so that the individual digits are properly aligned. To complete this block, create a list of values for the catalog number field with a WHEN-VALIDATE-ITEM trigger to display the list price and item total fields. Finally, to complete this form, select the Relationships group under the Orders block. Click the Add Object button to display the Relation dialog box as shown in Figure 32.19. Modify the relation name to order_item_rel and define the detail block as ORDER_ITEMS. Next, define the logic that should be followed if a master record is deleted. In this case, Please purchase PDF Split-Merge on to remove this watermark.
  10. select a cascading delete. (All detail records will be deleted if the associated master record is deleted.) The block coordination should be defined so that the detail query is immediate (Deferred is off), and the user should not be able to navigate to the detail block unless a record exists in the master block. Figure 32.19. Creating block relationships in a form. Mouse Events, Timers, and Other Advanced Widgets The standard Windows interface uses various graphical controls and other objects to control the operation of application components. Oracle Forms provides access to many of these features through the use of mouse triggers, timers, and VBX controls. Additionally, messaging in most Windows software is through an object called an alert box that has been implemented in Oracle Forms. Working with the Mouse The mouse pointer is the primary user-input device for navigation and selection in most Windows applications. Triggers have been provided in Oracle Forms to detect and act on various mouse activities. Oracle Forms 4.5 utilizes the mouse for navigation and command input. Additionally, the mouse can be used to trigger specific events. An event can be triggered when the mouse passes over an item on the screen (WHEN-MOUSE-ENTER) or when it leaves the item (WHEN-MOUSE-LEAVE). A third mouse status event can occur if the mouse moves within an item (WHEN-MOUSE-MOVE). Additional triggers have been added for mouse button activities: Table 32.4. Mouse triggers. Trigger Name Event Description WHEN-MOUSE-DOWN Operator presses and holds the mouse button. WHEN-MOUSE-UP Operator releases the mouse button. WHEN-MOUSE-CLICK Operator quickly presses and releases button. WHEN-MOUSE-DOUBLECLICK Operator clicks mouse twice in succession. When these activities occur, several system variables exist to retrieve status information for the mouse. These variables are: Table 32.5. Mouse system variables. Please purchase PDF Split-Merge on to remove this watermark.
  11. Variable Value MOUSE_BUTTON_PRESSED Returns 1 for left button; 2 for middle/right. MOUSE_BUTTON_SHIFT_STATE Returns , Shift+, Ctrl+, or Shift+Ctrl+ depending on key pressed. MOUSE_ITEM Current item where mouse cursor is located. MOUSE_CANVAS Current canvas where mouse cursor is located. MOUSE_X_POS Current x position of mouse within item. MOUSE_Y_POS Current y position of mouse within item. MOUSE_RECORD Record within block where mouse cursor is located. MOUSE_RECORD_OFFSET Record where mouse cursor is located relative to first displayed record. MOUSE_FORM Current form where mouse cursor is located. The sample form shown in Figure 32.20 can be constructed to test and observe the operations of the mouse triggers and variables. To construct this form, create a block, b1, that is not associated with a table. In the Layout Editor, create four fields for TRIGGER_NAME, BUTTON_NUMBER, SHIFT_STATE, and MOUSE_ITEM1, and position these fields with the appropriate caption as shown in the figure. Set the Default value property for the MOUSE_ITEM1 field as WILL TURN RED ON MOUSE ENTRY. Also, create a button object, DRAG_BUTTON, on the canvas with a Label property of Drag This Button. Figure 32.20. Mouse observation form. Next, select the Visual Attributes group in the Object Navigator and click the Add Objects button. In the property sheet for this object, set the font to Arial, size 8, and weight bold. Define the foreground color as BLACK and set the background to WHITE. Name this object BLACK_ON_WHITE. Create a second visual attribute, WHITE_ON_RED, with a white foreground and red background. These visual attributes will be used to define the display colors of the MOUSE_ITEM1 field using a WHEN-MOUSE-ENTER trigger as follows: begin :b1.trigger_name := 'MOUSE ENTER'; set_item_property ('B1.MOUSE_ITEM1', VISUAL_ATTRIBUTE, 'WHITE_ON_RED'); end; Please purchase PDF Split-Merge on to remove this watermark.
  12. Similarly create a WHEN-MOUSE-LEAVE trigger to use the BLACK_ON_WHITE attribute. Now, create WHEN- MOUSE-DOWN, WHEN-MOUSE-UP, WHEN-MOUSE-CLICK, and WHEN-MOUSE-DOUBLECLICK triggers at the form level to display the status of the mouse whenever a trigger event occurs. begin -- WHEN-MOUSE-DOWN trigger :b1.trigger_name := 'MOUSE DOWN'; :b1.button_number := :system.mouse_button_pressed; :b1.shift_state := :system.mouse_button_shift_state; end; The default installation of Oracle Forms includes several libraries and sample programs that can be used in your Forms development. One of these libraries, DRAG.PLL, provides functions that can be used for drag-and-drop functionality in Oracle Forms. To use this library, select the Attached Libraries group in the form and click the Add Object button. Select the DRAG.PLL file to attach to the form. To implement drag-and-drop in this form, create two triggers on the DRAG BUTTON item as follows: begin -- WHEN-MOUSE-DOWN trigger; end; begin -- WHEN-MOUSE-MOVE trigger if :system.mouse_button_pressed = 1 then mouse.move; end if; end; These triggers that reference procedures in the mouse package in the DRAG.PLL library are all that is needed to implement drag operations in a form. A third trigger should be created for the object to define the logic associated with the drop operation (WHEN-MOUSE-UP trigger). This completes the design of the mouse control form. Run the form to observe how it operates. A few important points should be noted at this time. First, observe the operation of passing the cursor over the MOUSE_ITEM1 field. The color of the field will change and the name of the trigger will appear in the appropriate field. Now, click anywhere on the canvas. Three triggers will actually fire with what appeared to be a single action. The WHEN-MOUSE-DOWN and WHEN-MOUSE-UP triggers fired before the WHEN-MOUSE-CLICK trigger. A double-click event will fire all of these triggers before firing the WHEN-MOUSE-DOUBLECLICK trigger. Therefore when working with the mouse, care should be taken when defining multiple triggers to prevent unwanted logic to be executed. Working with Alerts Alerts are devices that can be included in a form to provide the user with information that requires a response. An alert can be one of three styles: Stop (usually fatal errors), Caution (warning messages) and Note (informational). Depending on the style chosen, a different icon will appear in the alert box. Additionally, the programmer may define up to three Please purchase PDF Split-Merge on to remove this watermark.
  13. labeled buttons to determine the user response. The default setting is a two-button alert box with the captions OK and Cancel. To display the alert, a built-in function has been provided using the following syntax: button_no := SHOW_ALERT (alert_name); where button_no is defined as a numeric PL/SQL variable. Using the SET_ALERT_PROPERTY built-in, the ALERT_MESSAGE_TEXT property can be dynamically changed at runtime. Thus, using the standard trigger, ON- MESSAGE, an alert box can be created that will present all messages to the user in an alert box rather than on the status line, which may sometimes be missed by a user. An ON-MESSAGE that uses the MSG_ALERT dialog box (STOP, 1 button labeled OK) can be written as follows: declare msgtext VARCHAR(80) := message_text; bno number; begin set_alert_property ('MSG_ALERT', ALERT_MESSAGE_TEXT, msgtext); bno := show_alert ('MSG_ALERT'); end; Thus, whenever the message built-in is used, the message will be displayed as shown in Figure 32.21. The form that contains this alert will be described in the next section. Figure 32.21. Alert message. Using Timers for Event Control Timers may be used in Oracle Forms to trigger events that are dependent on a specific time interval. These timers may be iterative (repeating) or one-time only. Examples of iterative timers are a report queue manager that looks for requests every 15 seconds or a database status form that "refreshes" the screen every two minutes. Uses for a one-time only trigger may be as a delay timer for button help or as a timeout trigger. To create a timer, the following command would be issued: TIMER_ID := CREATE_TIMER (timer_name, interval, REPEAT|NO REPEAT); where TIMER_ID is a PL/SQL variable of type TIMER, TIMER_NAME is the name given to the timer by the programmer, and INTERVAL is the duration of the timer in milliseconds. Oracle Forms supports multiple timers; however, only one WHEN_TIMER_EXPIRED trigger may be included at the form level. To determine which timer has expired, the trigger should use the GET_APPLICATION_PROPERTY (TIMER_NAME) built-in function. Then by checking against the various timer names, the appropriate program sequence may be executed. The SET_TIMER (same syntax as the CREATE_TIMER built-in) built-in may be used to restart an existing timer or to change its interval or repeat parameters. Finally, the DELETE_TIMER built-in may be used to remove a timer. The example shown in Figure 32.22 shows how to implement multiple timers in a form using an iconic button bar. The WHEN_NEW_FORM_INSTANCE trigger creates two triggers that will be used in the form and the WHEN_TIMER_EXPIRED trigger executes the logic necessary when a timer expires. The first trigger is used to create Please purchase PDF Split-Merge on to remove this watermark.
  14. an animated button in a form button bar by toggling the icon file used based on a time interval. The second timer is used to validate that the user enters a valid name within 30 seconds or the form will terminate. Finally, the WHEN_MOUSE_ENTER and WHEN_MOUSE_LEAVE triggers have been set up to create a timer that will display button help after the mouse has been "resting" on a button for at least one half second. Figure 32.22. Timer demo form. First, create a canvas called DESKTOP and a non-database block called control. The Desktop and associated window should be defined as 300 points wide by 200 high. Create the USER_NAME field as shown on the desktop with the appropriate valid condition. Now, create an alert, called TIMEOUT_ALERT, as an informational alert with one button. The message text for this alert should be R E M I N D E R This form will terminate unless a valid user name is entered within 30 seconds after startup. This alert will be displayed whenever the Show Note button is pressed. Now, to create the iconic button bar, create a second canvas called BUTTON_BAR. The Canvas Type property for this canvas should be Horizontal Button Bar and it should be 300 points wide by 30 points high. Now create a button for the exit function. Properties for this button are shown below in Table 32.5. Table 32.5. Button bar iconic button properties. Property Value Name EXIT_BUTTON Canvas BUTTON_BAR X Position 0 Y Position 0 Width 30 Height 30 Navigable False Mouse Navigable False Please purchase PDF Split-Merge on to remove this watermark.
  15. Label Exit Form Iconic True Icon Name Exit Additionally create a second button, SHOW_NOTE, adjacent to the EXIT_BUTTON that will use the lighton iconic file. (Note the icon file will change to 'blink' the light at runtime.) Now, the button bar must be defined as such to the form. To do this, change the Horiz. MDI toolbar to point to the BUTTON_BAR canvas. This will cause the button bar to appear outside the frame of the form window when the form is executed. At any one time, only one MDI (Multiple Document Interface) button bar will appear in a Windows application. This prevents confusion when multiple documents or forms are open at the same time. Only one document may be active at any time and the MDI button bar will show the buttons associated with the active document. This is especially useful when working with multiple forms or OLE applications. Create triggers that will execute the proper commands when the button is pressed. The trigger for the EXIT_BUTTON item should be DO_KEY ('EXIT_FORM') and the following WHEN-BUTTON-PRESSED trigger should be created for the SHOW_NOTE button: declare bno NUMBER; begin bno := show_alert ('TIMEOUT_ALERT'); -- -- Note additional logic may be placed here based on the button pressed -- by the user. -- end; Now, the timer triggers may be added to the form. First, the timeout and blink timers are set up for the form in the WHEN-NEW-FORM-INSTANCE trigger. (This trigger replaces the KEY-STARTUP trigger in Forms 3.0.) This trigger is coded as follows: declare timeout_id TIMER; blink_id TIMER; Please purchase PDF Split-Merge on to remove this watermark.
  16. begin timeout_id := CREATE_TIMER ('TIMEOUT', 30000, NO_REPEAT); blink_id := CREATE_TIMER ('BLINK', 500, REPEAT); end; Additionally, triggers need to be added to provide button help as needed. This help text, which is a standard in many Windows applications, displays the value that was entered for the button Label directly below the iconic button. To add this functionality, attach the HINT.PLL library to the form and create a WHEN-MOUSE-ENTER and WHEN-MOUSE- LEAVE trigger for the form as follows: begin -- WHEN-MOUSE-ENTER trigger HINT.ShowButtonHelp; end; begin -- WHEN-MOUSE-LEAVE trigger HINT.HideButtonHelp; end; If the user enters a valid name in the user name field, the timeout timer should be canceled. To do this, create a WHEN- VALIDATE-ITEM trigger for the USER_NAME field. begin -- WHEN-VALIDATE-ITEM trigger if :control.user_name is not null then -- other validation logic may be needed. delete_timer ('TIMEOUT'); end if; end; To complete this form, a WHEN-TIMER-EXPIRED trigger must be written for all timers in the form. This trigger, shown forthwith, determines the timer that caused the trigger that fired and processes the logic associated with the trigger. declare -- WHEN-TIMER-EXPIRED trigger which_timer VARCHAR2(50); begin which_timer := get_application_property (TIMER_NAME); if which_timer := 'BLINK' then :control.message_switch := mod (:control.message_switch +1, 2); if :control.message_switch = 0 then Please purchase PDF Split-Merge on to remove this watermark.
  17. set_item_property ('CONTROL.SHOW_NOTE', ICON_FILE, 'lightoff'); else set_item_property ('CONTROL.SHOW_NOTE', ICON_FILE, 'lighton'); end if; elsif which_timer = 'TIMEOUT' then message ('Timeout Occurred. Form Canceled.'); do_key ('EXIT_FORM'); else HINT.ShowButtonHelpHandler; end if; end; A couple of important points should be noted when working with timers: 1. Only one timer can be handled by the WHEN-TIMER-EXPIRED trigger at a time. If a second timer expires while this trigger is handling the first, it will be placed on the stack until the trigger is completed. 2. The WHEN-TIMER-EXPIRED trigger will fire during transaction processing, navigation, and so on. If a second form is called by the form containing the trigger, the timer will be deferred until the user returns to the calling form. 3. A repeating trigger will not repeat until it is taken off the queue. In other words, the interval does not start up again until the first iteration is handled. 4. Any existing timers are deleted when the form is exited. If any timed activity is pending or on the queue, it will not complete if the form is exited. 5. Finally, DO NOT use timers where precise timing is essential. Because the above conditions can delay the actual execution time of the trigger logic, the Oracle timer can not be used for industrial fail-safe operations. Implementing VBX Controls VBX controls were originally developed as user interface elements for Microsoft Visual Basic programs. As the Visual Basic environment became accepted as a powerful business-applications development environment, interfaces to VBX controls were added to many other popular Windows program-development products. While only a few VBX controls are included with the Visual Basic software, many third party VBX controls are available for purchase. Additionally, developers may create their own VBX controls using C++ or other programming languages. With the advent of Oracle Forms 4.5, these elements have been incorporated into the Oracle application tools. In Oracle Forms, a VBX control may be used to either provide information to an application or to display application information in some specialized way. To demonstrate the ease with which these elements may be incorporated into a form, the simple form module shown in Figure 32.23 may be constructed. Figure 32.23. VBX demonstration form. Please purchase PDF Split-Merge on to remove this watermark.
  18. This form utilizes two VBX controls that are connected to a text item with triggers. The first VBX control is the Spin Control, which will increase or decrease the value in the text box by 500 units depending on whether the up arrow or down arrow is clicked with the mouse. The other VBX control is a VBX gauge control, which is defined as a horizontal bar gauge. This gauge will be filled based on the value of the text item as a percentage of the maximum value of 25,000. To create this form, create a numeric text field, VBX_VALUE, on the form as shown. The default value for this item should be 10000. Next, create a VBX control in the Layout Designer next to the text field. This VBX control should then be attached to the VBX file for the Spin button. The properties for this object are shown in Table 32.6. Table 32.6. Spin control properties. Property Value VBX Control File C:\WINDOWS\SYSTEM\spin.vbx VBX Control Name SpinButton VBX Control Value Property Name Border Thickness 1 Spin Orientation 0 -Vertical A second VBX control should be added below the other items and attached to the gauge VBX file. The properties for this control are shown in Table 32.7. Table 32.7 Gauge control properties. Property Value VBX Control File C:\WINDOWS\SYSTEM\gauge.vbx VBX Control Name Gauge VBX Control Value Property Value Max 25000 Min 0 Please purchase PDF Split-Merge on to remove this watermark.
  19. Style 0—Horizontal Bar Value 10000 Finally, triggers need to be created to link these three items. To establish the initial values, the WHEN_NEW_FORM_INSTANCE trigger should contain the following lines: :B_VBX.VBX_VALUE := 10000; :B_VBX.VBX_GAUGE := :B_VBX.VBX_VALUE; A WHEN_CUSTOM_ITEM_EVENT trigger should then be created for the Spin Control: BEGIN if :SYSTEM.CUSTOM_ITEM_EVENT = 'SpinUp' then if :B_VBX.VBX_VALUE < 24501 then :B_VBX.VBX_VALUE := :B_VBX.VBX_VALUE + 500; end if; elsif :SYSTEM.CUSTOM_ITEM_EVENT = 'SpinDown' then if :B_VBX.VBX_VALUE > 499 then :B_VBX.VBX_VALUE := :B_VBX.VBX_VALUE - 500; end if; end if; :B_VBX.VBX_GAUGE := :B_VBX.VBX_VALUE; END; A WHEN_VALIDATE_ITEM trigger should be written for the VBX_VALUE text item containing the following line: :B_VBX.VBX_GAUGE := :b_VBX.VBX_VALUE; Finally, triggers may be written for the cursor up and down keys so that pressing either of them will trigger the corresponding Spin Up or Spin Down events: BEGIN -- KEY-UP trigger VBX.FIRE_EVENT ('B_VBX.VBX_SPIN', 'SpinUp', NULL); END; Considerations for Multiple Form Applications Please purchase PDF Split-Merge on to remove this watermark.
  20. Oracle Forms provides three built-in procedures that enable the user to access other forms from an original calling form. These procedures are: NEW_FORM, CALL_FORM, and OPEN_FORM. NEW_FORM terminates execution of the original form and starts up the next form. If any changes have been made to database data, the user will be asked if he wants to commit the data. If he chooses not to commit his changes, these changes will be lost. A NEW_FORM call is typically used when the user navigates to an unrelated application module. CALL_FORM, on the other hand, passes execution to the next form, while maintaining a call stack that will return to the calling form when the called form is exited. If changes are pending in the calling form, the called form will be executed in POST-ONLY mode. If the user tries to save changes made in the called form, the changes will be posted to the database (a rollback will lose any changes) and they will be saved when the original form is committed. Typically, CALL_FORM is used when the two forms are dependent on each other and values can be passed either in global variables or as parameters. One usage of the CALL_FORM would be to add a button to the Order Entry form (discussed in the section on relationships) that can be used to create a new customer record. The second form could then be used to create the customer record, and after the new record is inserted and posted or committed, the customer number could be returned in a global variable to be used for order entry. Finally, OPEN_FORM is used to load a second form while maintaining the functionality of the first form. The second form, by default, becomes the active form; however the user can activate the first form by clicking within its frame. If the second form should not be made the active form, the second parameter in the procedure call can be defined as NO_ACTIVATE. Additionally, the new form will be opened in the same session as the original calling form. It is possible, however to call the second form with a separate session by defining the third parameter in the call as SESSION. This would connect the user in a second (or third, etc.) session. The advantage of having the second session open is that changes can be made within the first form and committed without affecting pending changes in the first form. An interesting application can be developed where the first form executes a query based on a timer (for example, every two minutes). The second form could then be used to maintain records on the database and the changes would show up in the original form. (This would be a crude, but effective way to pass data between two application areas.) Techniques for Dynamic, Runtime Applications In its simplest form, an Oracle Forms query can be defined by the default where property for the queried block. Typically, a form will contain query criteria elements in a control block and the default where property may be defined as: where database_table_field = :CONTROL.control_field This would work in cases where the query is based on a single required field such as customer number. In reality, however, a query form is seldom so cut-and-dried. The customer inquiry may also need to be based on the customer name. Using the above technique, the default where property would become: where database_field1 = :CONTROL.input_field1 or (:CONTROL.input_field1 is null and database_field2 = :CONTOL.input_field2) The performance of this query is poor because the Oracle optimizer will resolve both halves of the query and then merge the result. As can be seen by this basic example, as the number of query fields increases in the control block, the where clause would become more complex and the performance of the query would degrade very quickly. Ideally, the where clause should be written to reflect only the fields that contain data. Starting with Oracle Forms 4.0, an application may modify the where clause dynamically at runtime. Thus, in the previous example, the following PRE-QUERY trigger may be written to dynamically update the query: Please purchase PDF Split-Merge on to remove this watermark.
Đồng bộ tài khoản