0870-0872

Previous Table of Contents Next

Page 870

commit his changes, these changes are 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 returns to the calling form when the called form is exited. If changes are pending in the calling form, the called form is executed in POST-ONLY mode. If the user tries to save changes made in the called form, the changes are posted to the database (a rollback loses any changes) and they are 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 use of the CALL_FORM is 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 is 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 connects the user in a second (or third, and so on) 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. You can develop an interesting application where the first form executes a query based on a timer (for example, every two minutes). You could then use the second form to maintain records on the database and the changes would show up in the original form. (This is 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 contains query criteria elements in a control block and the default where property can be defined as

 where database_table_field = :CONTROL.control_field 

This works 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 might also need to be based on the customer name . Using the preceding technique, the default where property becomes

 where database_field1 = :CONTROL.input_field1 or      (:CONTROL.input_field1 is null and           database_field2 = :CONTROL.input_field2) 

Page 871

The performance of this query is poor because the Oracle optimizer resolves both halves of the query and then merges the result. As shown in this basic example, as the number of query fields increases in the control block, the where clause becomes more complex and the performance of the query degrades 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 can modify the where clause dynamically at runtime. In the previous example, you can write the following PRE-QUERY trigger to dynamically update the query:

 declare    qry_where     VARCHAR2(100); begin    if :control.cust_no is not null then       qry_where := `cust_no ='to_char (:control.cust_no);    elsif :control.cust_name is not null then       qry_where := `cust_name = `'':control.cust_name'''';    else       message (`Either customer number or name must be entered.');       raise FORM_TRIGGER_FAILURE;    end if;    set_block_property (`CUSTOMERS', DEFAULT_WHERE, qry_where); end; 

Although in many cases, directly building a where clause at runtime seems to be the best way to handle dynamic queries, you can use the techniques that were available in prior versions of SQL*Forms. To accomplish this, a database field is set equal to a value based on how the where clause should be created. Table 35.7 defines possible entries using the field ITEM_VALUE.

Table 35.7. Implementing dynamic queries in Oracle Forms.

Field Contents	Runtime Modification	Example	Resulting Where Clause
Any text value	Checks for equality to entered value	SMITH	ITEM_VALUE = `SMITH'
Begins with <, <=, >=, >, or !=	Checks for respective inequalities	> 47	ITEM_VALUE >> 47
Contains % or _	Uses pattern matching algorithm	%SM_TH	ITEM_VALUE like `%SM_TH'

 continues 

Page 872

Table 35.7. continued

Field Contents	Runtime Modification	Example	Resulting Where Clause
Any text value	Checks for equality to entered value	#between '01-JAN-95' and '31-OCT-95'	ITEM_VALUE in between '01-JAN-95'and '31- OCT-95'
		# in (`01','02',`03') # is not null	ITEM _ VALUE in (`01', `02', `03') ITEM_VALUE is not null
		# = 1.10 * OTHER_VALUE ITEM_VALUE = 1.10 * # = ITEM_VALUE and exists (select `x' from orders o where o.order_cust_no = CUSTOMERS.cust_no)	ITEM_VALUE =1.10* OTHER_VALUE ITEM_VALUE=ITEM _VALUE and exists (select `x' from orders o where o.order_cust_no = CUSTOMERS.cust_no)

Generally speaking, treating each column independently in the PRE-QUERY trigger results in a more maintainable module. Given that most environments are in a constant state of change, ease of maintenance should be a determining factor.

Working with Menus

So far, all the application modules developed in this chapter are completely independent of each other. Although you can develop an application using iconic buttons and procedures that can be used to pass control from one form to the next, most applications are held together using menu modules. Typically, a main form is executed first and all other forms are called from the original module. As you might have noticed in the Form property sheet, each form module can define a menu to be used within the form.

To create a menu module, select File New Menu in the Oracle Forms Designer. A new menu module is created in the Object Navigator. Object groups in the menu are attached libraries,

Previous Table of Contents Next