The Pre-Query trigger fires just before Form Builder issues the SELECT statement to the database, after the operator has defined the example record by entering query criteria in Enter Query mode.
A Pre-Query trigger can be used to disallow query conditions that might be invalid. When a form is in Enter Query mode, normal validation is suspended and no validation triggers fire as they do in Normal mode. The Pre-Query trigger thus allows you to verify that any values entered by the operator are valid query conditions.
The Post-Query trigger fires after the query has executed, when Form Builder is fetching records into the form. Post-Query fires once for each record retrieved into the form, which allows you to read and set the values of items in a fetched record before the operator sees them displayed in the block.
A Post-Query trigger is also useful for populating control items whose values are derived from a table other than the block's base table.
Trigger Typical Usage
Pre-Query Validate the current query criteria or provide additional query criteria programmatically, just before sending the SELECT statement to the database.
Post-Query Perform an action after fetching a record, such as looking up values in other tables based on a value in the current record. Fires once for each record fetched into the block.
2.SET_ITEM_INSTANCE_PROPERTY VS SET_ITEM_PROPERTY
SET_ITEM_INSTANCE_PROPERTY built-in
Modifies the current item instance in a block by changing the specified item property. SET_ITEM_INSTANCE_PROPERTY does not change the appearance of items that mirror the current instance.
You can reference any item in the current form. Note that SET_ITEM_INSTANCE_PROPERTY only affects the display of the current instance of the item; other instances of the specified item are not affected. This means that if you specify a display change for an item that exists in a multi-record block, SET_ITEM_INSTANCE_PROPERTY only changes the instance of that item that belongs to the block's current record. If you want to change all instances of an item in a multi-record block, use SET_ITEM_PROPERTY .
Any change made by a SET_ITEM_INSTANCE_PROPERTY remains in effect until:
• the same item instance is referenced by another SET_ITEM_INSTANCE_PROPERTY, or
• the same item instance is referenced by the DISPLAY_ITEM built-in, or
• the instance of the item is removed (e.g., through a CLEAR_RECORD or a query), or
• the current form is exited
SET_ITEM_INSTANCE_PROPERTY examples /*
** Built-in: SET_ITEM_INSTANCE_PROPERTY ** Example: Change the visual attribute of each item instance in the current record
*/ DECLARE cur_itm VARCHAR2(80); cur_block VARCHAR2(80) := :System.Cursor_Block; BEGIN cur_itm := Get_Block_Property( cur_block, FIRST_ITEM ); WHILE ( cur_itm IS NOT NULL ) LOOP cur_itm := cur_block||'.'||cur_itm; Set_Item_Instance_Property( cur_itm, CURRENT_RECORD, VISUAL_ATTRIBUTE,'My_Favorite_Named_Attribute'); cur_itm := Get_Item_Property( cur_itm, NEXTITEM ); END LOOP; END;
Usage Notes
When working with properties specified at multiple levels (item instance, item, and block), consider the following guidelines:
• Required properties specified at multiple levels are ORed together
• Other boolean properties specified at multiple levels are ANDed together
The value derived from combining properties specified at the item instance, item, and block levels is called the effective value. Some of the effects of these two rules are as follows:
• setting INSERT_ALLOWED to true has no effect at the item instance level unless it is set consistently at the block and item levels. For example, your user cannot type data into an item instance if INSERT_ALLOWED is true at the instance level, but not at the item or block levels.
• setting NAVIGABLE to true has no effect at the item instance level unless it is set consistently at the item and item instance levels
• Setting NAVIGABLE to true may affect whether the block is considered enterable. A block’s read-only Enterable property will be true if and only if its current record contains an item instance whose effective value for the NAVIGABLE property is true.
• setting REQUIRED to false has no effect at the item instance level unless it is set consistently at the item and item instance levels.
• setting UPDATE_ALLOWED to true has no effect at the item instance level unless it is set consistently at the block, item, and item instance levels.
• setting BORDER_BEVEL at the item instance level will override the item level BORDER_BEVEL setting, except when the item instance BORDER_BEVEL property is unspecified (that is, set to " ").
• setting VISUAL_ATTRIBUTE at the item instance level will override the properties at the item and block levels unless you specify a partial visual attribute, in which case a merge will occur between the partial visual attribute and the item's current visual attribute. If VISUAL_ATTRIBUTE is set to " " at the item instance level, the item-level settings of this property are used.
• When a new record is created, its item instance properties are set to values that do not override the values specified at higher levels. For example, the BORDER_BEVEL and VISUAL_ATTRIBUTE properties get set to " ", REQUIRED is set to false, and other boolean properties are set to true.
• Setting an item instance property does not affect the item instance properties of any items that mirror the specified item.
• An instance of a poplist will, when selected, display an extra null value if its current value is NULL or if its Required property is set to false. When selecting the current value of an instance of a text list (t-list), it will be unselected (leaving the t-list with no selected value) if its Required property is set to false. If its Required property is set to true, selecting a t-list instance's current value will have no effect, that is, the value will remain selected.
SET_ITEM_PROPERTY built-in
Modifies all instances of an item in a block by changing a specified item property. Note that in some cases you can get but not set certain object propertie
Usage Notes
The following issues can affect your decisions on how to apply certain property values to an item:
• Validation of property changes
• Propagation of property changes
Set_Item_Property(it_id,ICON_NAME,'entquery');
3.POST
Writes data in the form to the database, but does not perform a database commit. Form Builder first validates the form. If there are changes to post to the database, for each block in the form Form Builder writes deletes, inserts, and updates to the database.
Any data that you post to the database is committed to the database by the next COMMIT_FORM that executes during the current Runform session. Alternatively, this data can be rolled back by the next CLEAR_FORM.
Usage Notes
If this form was called via OPEN_FORM with the NO_SESSION parameter specified, then the POST will validate and write the data both in this form and in the calling form.
/*
** Built-in: POST and EXIT_FORM ** Example: Leave the called form, without rolling back the posted changes so they may be posted and ** committed by the calling form as part of the same transaction. */ BEGIN
Post; /* ** Form_Status should be 'QUERY' if all records were successfully posted. */
IF :System.Form_Status <> 'QUERY' THEN Message('An error prevented the system from posting changes'); RAISE Form_Trigger_Failure; END IF;
/* By default, Exit_Form asks to commit and performs a rollback to savepoint. We've already posted, so we
do not need to commit, and we don't want the posted changes to be rolled back. */
Exit_Form(NO_COMMIT, NO_ROLLBACK);
END;
4. OPEN FORM VS NEW FORM VS CALL FORM
To programmatically invoke another form module, a form can execute three built-in Form Builder procedures:
• OPEN_FORM (opens a separate, independent form)
• NEW_FORM (replaces the current form with a different form)
• CALL FORM (calls a modal form)
When a form executes OPEN_FORM to invoke another form, the first form remains displayed, and end users can navigate between the forms as desired. An opened form can share the same database session as the form from which it was invoked, or it can create a separate session of its own. For most GUI applications, using OPEN_FORM is the preferred way to implement multiple-form functionality.
When a form executes NEW_FORM to invoke another form, Form Builder exits the first form and releases its memory, and then loads the new form (i.e., the second form completely replaces the first. If changes are pending in the first form, Form Builder will prompt the end user to save them before the new form is loaded.
When a form executes CALL_FORM to invoke another form, the called form is modal with respect to the calling form. Any windows that belong to the calling form are not usable until the called form is exited and control returns to the calling form.
Both OPEN_FORM and CALL_FORM allow you to leave the calling form displayed. Using this technique, you can integrate forms so tightly that end users are not aware that they are invoking separate forms.
Form Builder maintains any locks (obtained by a form) across both OPEN_FORM (in the same session), CALL_FORM and NEW_FORM procedure calls. Thus, a called form automatically has the same locks as its calling form.
CALL_FORM is an unrestricted procedure, OPEN_FORM and NEW_FORM are restricted. Therefore, CALL_FORM is valid in Enter Query mode, while OPEN_FORM and NEW_FORM are not.
5.CLOSE FORM VS EXIT FORM
EXIT FORM
Provides a means to exit a form, confirming commits and specifying rollback action.
• In most contexts, EXIT_FORM navigates “outside” the form. If there are changes in the current form that have not been posted or committed, Form Builder prompts the operator to commit before continuing EXIT_FORM processing.
• If the operator is in Enter Query mode, EXIT_FORM navigates out of Enter Query mode, not out of the form.
• During a CALL_INPUT, EXIT_FORM terminates the CALL_INPUT function.
Usage Notes
Because the default parameters of EXIT_FORM are ASK_COMMIT for commit_mode and TO_SAVEPOINT for rollback_mode, invoking EXIT_FORM without specifying any parameters in some contexts may produce undesired results. For example, if the form is in POST only mode and EXIT_FORM is invoked without parameters, the user will be prompted to commit the changes. However, regardless of the user’s input at that prompt, the default rollback_mode of TO_SAVEPOINT rolls back the changes to the form despite a message confirming that changes have been made. To avoid conflicts explicitly specify parameters.
Exit_Form(NO_COMMIT, NO_ROLLBACK);
CLOSE FORM
A multiple-form application, closes the indicated form. When the indicated form is the current form, CLOSE_FORM is equivalent to EXIT_FORM.
CLOSE_FORM restrictions
• You cannot close a form that is currently disabled as a result of having issued CALL_FORM to invoke a modal called form.
• You cannot close a form that has called you. For example, if Form_A calls Form_B, then Form_B cannot close Form_A.
MASTER-DETAIL RELATION
A master-detail relation is an association between two data blocks—a master block and a detail block. The relation reflects one of the following:
• A primary-key to foreign-key relationship between the tables on which the blocks are based
• A REF pointer relationship between the tables on which the blocks are based.
Consider an inventory-control application for a worldwide sporting goods distributor. There is a REGION table for geographic regions, and WAREHOUSE tables for goods warehouses in the various regions.
The application includes data blocks based on each of these tables. Since each warehouse is in a specific region, there is a primary-key to foreign-key relationship between the REGION and WAREHOUSE tables. This relationship also defines a master-detail relation between the blocks: the REGION block is the master block, while the WAREHOUSE block is the detail block.
The master-detail relation automatically does the following:
• Ensures that the detail block displays only those records that are associated with the current (master) record in the master block.
• Coordinates querying between the two blocks
For example, when the end user changes the current record in the master block by navigating to a different region record, Form Builder updates the detail block to display only the records for warehouses that are within the selected region. Similarly, when the end user queries a particular region in the master block, you can have Form Builder automatically retrieve the records for all of the warehouses in that region.
BLOCK COORDINATION
To maintain the master-detail relation at runtime, Form Builder coordinates the master and detail blocks to ensure that the records displayed in the detail block are associated with the current record in the master block.
Any event that changes the current record in the master is a coordination-causing event. Deleting a record or pressing [Up] or [Down] to move to a different record are both examples of coordination-causing events. When such an event occurs, Form Builder automatically does the processing necessary to coordinate the master and detail blocks.
There are two phases of block coordination: the clear phase and the population phase. During the clear phase, Form Builder navigates internally to the detail block and flushes the obsolete detail records. During the population phase, Form Builder issues a SELECT statement to repopulate the detail block with the detail records associated with the new master record. These operations are accomplished through the execution of triggers.
To continue the example, consider what happens when the end user is on the first record in the REGION block and presses [Next Record]. Since Next Record is a coordination-causing event, Form Builder first navigates internally to the WAREHOUSE block and flushes the existing warehouse records (the clear phase). Form Builder then makes the next region the current master record in the REGION block, and fetches the records of those warehouses that are within the selected region (the population phase).
CREATE A MASTER-DETAIL RELATION
There are two ways to create a master-detail relation in Form Builder:
• Create the relation on the Master-Detail Relation page of the Data Block Wizard (when you create the detail block for that relation).
• In the Object Navigator, insert a relation object under the Relations node for the appropriate master block. Both the master block and the detail block must already exist.
Master and detail blocks are linked to each other in one of two ways: either through a relational join condition, or though a REF column in one block that points to data in the other block.
JOIN CONDITION
The master block is related to the detail block through the join condition. The join condition establishes the primary-key item(s) in the master block and the foreign-key item(s) in the detail block. The join condition for the example application would be stated as follows:
warehouse.region_id = region.id
That is,
detail_block.foreign_key_item = master_block.primary_key_item
Note: For Forms processing, it is recommended that you avoid basing a join condition on columns in the database that are of type CHAR. (VARCHAR2 columns, however, are acceptable.)
REF pointer
A REF column in a table/block contains pointers to rows of data in another table/block. This reference forms a natural master-detail relationship. The detail block has a REF item that points to the master block. Note that a REF link is not dependent on any primary or foreign key values in either table.
ABOUT THE RELATION OBJECT
You can define a master-detail relation at any time during the form development process. You define a master-detail relation between blocks by creating and setting the properties of a relation object.
A relation is a logical object that specifies the relationship between one master block and one corresponding detail block. In the Object Navigator, the relation object appears under the block that is the master block in the relation.
When you create a relation, Form Builder generates the triggers and PL/SQL procedures required to enforce coordination between the master and detail blocks. The actual code that Form Builder generates depends on how the properties of the relation are set.
MASTER-DETAIL TRIGGERS
1.On-Check-Delete-Master
Form Builder creates this trigger automatically when you define a master/detail relation and set the Delete Record Behavior property to Non-Isolated. It fires when there is an attempt to delete a record in the master block of a master/detail relation.
Example
The following example replaces the On-Check-Delete-Master that is generated by default for a master/detail relation with a trigger that will fail if the sum of the distributions does not equal the purchase order total.
DECLARE the_sum NUMBER; BEGIN SELECT SUM(dollar_amt) INTO the_sum FROM po_distribution WHERE po_number = :purchase_order.number; /* Check for errors */ IF the_sum <> :purchase_order.total THEN Message('PO Distributions do not reconcile.'); RAISE Form_Trigger_Failure; ELSIF form_fatal OR form_failure THEN raise form_trigger_failure; end if; END;
2.On-Clear-Details
Fires when a coordination-causing event occurs in a block that is a master block in a Master/Detail relation. A coordination-causing event is any event that makes a different record the current record in the master block.
Usage Notes
Form Builder creates the On-Clear-Details trigger automatically when a Master/Detail block relation is defined.
3.On-Populate-Details
Form Builder creates this trigger automatically when a Master/Detail relation is defined. It fires when Form Builder would normally need to populate the detail block in a Master/Detail relation.
Usage Notes
Use an On-Populate-Details trigger when you have established a Master/Detail relationship and you want to replace the default populate phase of a query.
The On-Populate-Details trigger does not fire unless an On-Clear-Details trigger is present. If you are using the default Master/Detail functionality, Form Builder creates the necessary triggers automatically. However, if you are writing your own Master/Detail logic, be aware that the On-Clear-Details trigger must be present, even if it contains only the NULL statement.
When Immediate coordination is set, this causes the details of the instantiated master to be populated immediately. Immediate coordination is the default.
When Deferred coordination is set and this trigger fires, Form Builder marks the blocks as needing to be coordinated.
Relation object
A relation is a logical object that specifies the relationship between one master block and one corresponding detail block.
When you create a relation, Form Builder generates the triggers and PL/SQL procedures required to enforce coordination between the master and detail blocks. The actual code that Form Builder generates depends on how the properties of the relation are set.
When you create a relation, Form Builder sets the Copy Value from Item property on the foreign-key items in the detail block automatically. The Copy Value from Item property specifies the name of the corresponding primary-key item in the format MASTER_BLOCK.ITEM_NAME. At runtime, the value stored in the primary-key item in the master block is copied to the foreign-key item in the detail block whenever a detail record is created or queried.
Delete Record Behavior, Coordination, and Prevent Masterless Operation.
The properties that affect the functionality of a relation include Delete Record Behavior, Coordination, and Prevent Masterless Operation.
Delete Record Behavior Property - The Delete Record Behavior property (formerly called the Master Deletes property) allows you to specify how the deletion of a record in the master block should affect records in the detail block. You can specify Cascading, Isolated, or Non-Isolated:
Cascading - The master record can be deleted, and any associated detail records are automatically deleted
from the database at commit time. If relations are nested to several levels, only records in the
immediate detail block are deleted. That is, deletions do not automatically cascade to multiple
levels of a relation chain.
Isolated - The master record can be deleted, but the associated detail records are not deleted from the
database.
Non-Isolated - The default setting. The master record cannot be deleted if associated detail records exist in the
database.
Note: If your database is using the ORACLE7 Server cascading deletes feature, do not use the Cascading deletes option in Form Builder.
Coordination Properties - The values of the coordination properties Deferred and Automatic Query determine when the population phase of block coordination will occur:
Immediate (Deferred = No) The default setting. When a coordination-causing event occurs, Form Builder
fetches the detail records immediately.
Deferred (Deferred = Yes, Automatic Query = No) When a coordination-causing event occurs, Form
Builder does not automatically fetch the detail records. To fetch the detail records, the end
user must navigate to the detail block and explicitly execute a query.
Deferred with Automatic Query (Deferred = Yes, Automatic Query = Yes) When a coordination-causing
event occurs, Form Builder defers fetching the associated detail records until the end user
navigates to the detail block.
Preventing masterless operations in the detail block
Preventing Navigation to the Detail Block Setting the Prevent Masterless Operation property to Yes prevents end users from querying and inserting in a detail block for which there is no corresponding master record, but it does not prevent them from navigating to the detail block and attempting these operations.
complex master-detail relations
Many applications require complex master-detail relations that involve more than two blocks. To create such relations, simply define as many individual relations as needed.
There is no practical limit to the number of relations that can be defined in a form. Further, any block can be the master or detail in more than one relation, and a block that is the master in one relation can be the detail in another.
When you create complex master-detail relations, Form Builder automatically adjusts the existing triggers to manage the relations you define.
TYPE OF WINDOWS
A window is a container for all visual objects that make up a Form Builder application, including canvases. A single form can include any number of windows. While every new form automatically includes a default window named WINDOW1, you can create additional windows as needed by inserting them under the Windows node in the Object Navigator.
A window can be either modeless or modal. A modal window (often a dialog) requires the end user to respond before continuing to work in the current application. A modeless window requires no such response. When you create a window, you specify its modality by setting the Modal property to Yes or No. The default is No (modeless).
Modeless Windows
Modeless Windows You can display multiple modeless windows at the same time, and end users can navigate freely among them (provided your application logic allows it). On most GUI platforms, you can layer modeless windows so that they appear either in front of or behind other windows.
Modeless windows remain displayed until they are dismissed by the end user or hidden programmatically. You can set the Hide on Exit property for a modeless window to specify whether it should remain displayed when the end user navigates to another window. You also can set the properties of a modeless window to specify its border and title, and whether end users should be allowed to scroll, resize, move, close, iconify, and zoom the window.
Modal Windows
Modal Windows Modal windows are usually used as dialogs, and have restricted functionality compared to modeless windows. On some platforms, for example, end users cannot resize, scroll, or iconify a modal window. Modal windows are often displayed with a platform-specific border unique to modal windows. On some platforms, modal windows are "always-on-top" windows that cannot be layered behind modeless windows.
Multiple Document Interface
MDI applications display a default parent window, called the application window. All other windows in the application are either document windows or dialog windows.
Single Document Interface
Although MDI is the default system of window management during Forms Runtime, Form Builder also provides support for an SDI root window on Microsoft Windows.
MDI applications display a default parent window, called the application window. All other windows in the application are either document windows or dialog windows.
Document windows
Document windows always remain within the application window frame. If the operator resizes the application window so that it is smaller than a document window, the document window is clipped. An operator can maximize a document window so that is occupies the entire workspace of the application window.
Dialog windows
Dialog windows are free-floating, and the operator can move them outside the application window if they were defined as Movable. If the operator resizes the application window so that it is smaller than a dialog window, the dialog window is not clipped.
TYPE OF CANVAS
A canvas is a surface--inside a window container--on which you place the interface items and boilerplate objects that end users interact with when they run the form. By default, any canvas you create at runtime is assigned to the window named WINDOW1. To explicitly associate a canvas to a specfic window, set the canvas' Window property accordingly.
Content The default. Specifies that the canvas should occupy the entire content area of the window to which it is assigned. Most canvases are content canvases.
Stacked Specifies that the canvas should be displayed in its window at the same time as the window's content canvas. Stacked views are usually displayed programmatically and overlay some portion of the content view displayed in the same window.
Vertical Toolbar Canvas
Specifies that the canvas should be displayed as a vertical toolbar under the menu bar of the window. You can define iconic buttons, pop-lists, and other items on the toolbar as desired.
Horizontal Toolbar Canvas
Specifies that the canvas should be displayed as a horizontal toolbar at the left side of the window to which it is assigned.
Record Group
This object represents an internal Form Builder data structure that has a column/row framework similar to a database table. However, unlike database tables, record groups are separate objects that belong to the form module in which they are defined. A record group object an have an unlimited number of columns of type CHAR, LONG, NUMBER, or DATE provided that the total number of columns does not exceed 64K. Record group object column names cannot exceed 30 characters.
Static Specifies that the record group is constructed of explicitly defined column names and column values. The values of a static record group are specified at design time and cannot be changed at runtime.
Query
Specifies that the record group is associated with a SELECT statement, and thus can be populated dynamically at runtime. When you select this option, enter the SELECT statement in the multi-line field provided, then choose Apply.
Non - Query
Once you create a query or non-query record group at runtime, you can perform the following operations on the group:
• modify its structure by adding columns and rows
• populate the group
• modify the query associated with a query group
• add and delete rows
• set and get column values
• mark and unmark rows as "selected"
Note: Static record groups can be created and modified only at design time.
Record group rows are numbered internally (1,2,3, and so on) and operations on individual rows and columns must reference those rows by row number.
Property Class Vs Visual Attributes
Visual Attributes
Visual attributes are the font, color, and pattern properties that you set for form and menu objects that appear
in your application's interface. Visual attributes can include the following properties:
• Font properties: Font Name, Font Size, Font Style, Font Width, Font Weight
• Color and pattern properties: Foreground Color, Background Color, Fill Pattern, Charmode Logical Attribute, White on Black
Every interface object has a Visual Attribute Group property that determines how the object's individual visual
attribute settings (Font Size, Foreground Color, etc.) are derived. The Visual Attribute Group property can be
set to Default, NULL, or the name of a named visual attribute defined in the same module.
Property Class
A property class is a named object that contains a list of properties and their settings. Once you create a
property class you can base other objects on it. An object based on a property class can inherit the setting of
any property in the class that makes sense for that object.
Property class inheritance is an instance of subclassing. Conceptually, you can consider a property class as a
universal subclassing parent.
There can be any number of properties in a property class, and the properties in a class can apply to different
types of objects. For example, a property class might contain some properties that are common to all types of
items, some that apply only to text items, and some that apply only to check boxes.
When you base an object on a property class, you have complete control over which properties the object
should inherit from the class, and which should be overridden locally.
Property classes are separate objects, and, as such, can be copied between modules as needed. Perhaps more
importantly, property classes can be subclassed in any number of modules.
COPY VS NAME_IN
About indirectly referencing parameters
You cannot use bind variable syntax to refer directly to a parameter in a trigger, user-named routine, menu command, or stored procedure that is not declared in the same module as the parameter itself.
Note: When you reference parameters indirectly, the parameter names are enclosed in single quotes, but are not
preceded with a colon.
COPY
Copies a value from one item or variable into another item or global variable. Use specifically to write a value into
an item that is referenced through the NAME_IN built-in. COPY exists for two reasons:
• You cannot use standard PL/SQL syntax to set a referenced item equal to a value.
• You might intend to programmatically place characters such as relational operators in NUMBER and DATE fields while a form is in Enter Query mode.
Syntax
PROCEDURE COPY (source VARCHAR2, destination VARCHAR2);
Usage Notes
When using COPY with date values, the format defined in the BUILTIN_DATE_FORMAT property will be used if the
DATE_FORMAT_COMPATIBILITY_MODE property is set to 5.0. If this property is set to 4.5 COPY will expect date
strings to be formatted using the default American format.
** Built-in: COPY ** Example: Set the value of a global variable whose name is ** dynamically constructed. */ DECLARE global_var_name VARCHAR2(80); BEGIN IF :Selection.Choice = 5 THEN global_var_name := 'Storage_1'; ELSE global_var_name := 'Storage_2'; END IF; /* ** Use the name in the 'global_var_name' variable as the ** name of the global variable in which to copy the ** current 'Yes' value. */ COPY( 'Yes', 'GLOBAL.'||global_var_name ); END;
NAME_IN
Returns the value of the indicated variable.
The returned value is in the form of a character string. However, you can use NAME_IN to return numbers and dates as character strings and then convert those strings to the appropriate data types. You can use the returned value as you would use any value within an executable statement.
If you nest the NAME_IN function, Form Builder evaluates the individual NAME_IN functions from the innermost one to the outermost one.
Syntax
FUNCTION NAME_IN (variable_name VARCHAR2);
Usage Notes
If the returned value is a date string, NAME_IN will use the format mask specified in the BUILTIN_DATE_FORMAT property. If the DATE_FORMAT_COMPATIBILITY_MODE property is set to 4.5 the default American format is used to format the returned string.
TRIGGER EXECUTION HIERARCHY PROPERTY
Specifies how the current trigger code should execute if there is a trigger with the same name defined at a higher
level in the object hierarchy.
The following settings are valid for this property:
Override Specifies that the current trigger fire instead of any trigger by the same name at any higher scope. This is known as "override parent" behavior.
Before Specifies that the current trigger fire before firing the same trigger at the next-higher scope. This is known as "fire before parent" behavior.
After Specifies that the current trigger fire after firing the same trigger at the next-higher scope. This is known as "fire after parent" behavior.
PARAMETER TYPE
Text Parameters
The value of a text parameter being passed to a called product is a CHAR string that can represent the following:
* a user-defined form parameter defined in a form invoked by the CALL_FORM, OPEN_FORM, or
NEW_FORM built-in subprograms
* a command line or user-defined parameter for a product invoked with the RUN_PRODUCT
built-in subprogram
Data Parameters
The value of a data parameter being passed to a called product is always the name of a
record group defined in the current form. (A record group is a data structure that stores records derived from a query or through programmatic assignment.) Data parameters are used to pass data to products invoked with the RUN_PRODUCT built-in subprogram. You cannot pass data parameters to forms.
PARAMETER VARIABLE VS GLOBAL VARIABLE
Passing parameters to forms
When you invoke a form with the procedures OPEN_FORM, CALL_FORM, or NEW_FORM, you can pass values for form parameters from the calling form to the called form.
To pass parameter values from one form to another, each parameter and its value must be in a parameter list. Parameter lists are internal, three-column data structures that contain the key (name), the type (Text_Parameter or Data_Parameter) and the value of each parameter on the list.
The parameters whose values are being passed must have been defined in the called form at design time. That is, the called form must be expecting a value for each of the parameters included in the parameter list it receives from the calling form.
You can define parameters in a form in the Object Navigator at design time and also programmatically at runtime. The properties of a parameter include Name, Parameter Data Type, Length, and Parameter Initial Value. Parameter lists must be created programmatically with the built-in routines CREATE_PARAMETER_LIST and ADD_PARAMETER.
Parameter Variable
Parameter values are not visible across multiple forms. Thus, even if there is a parameter named p1 defined in both Form A and Form B, each form has a separate context, and setting the value of p1 in Form B has no effect on the value of p1 in Form A. For this reason, parameters are useful in multiple-form applications primarily as inputs to a form when it is first invoked.
Global Variable
If your application requires variables whose values are visible across called forms, you should use global variables. Global variables are visible across called forms, and remain active until they are explicitly deleted with the ERASE built-in procedure, or until the session ends.
A global variable is a Form Builder variable whose value is accessible to triggers and subprograms in any module that is active during the current session. A global variable stores a character string of up to 255 characters in length.
Global variables are not formally declared the way PL/SQL local variables are. Rather, you initialize a global variable the first time you assign a value to it:
:GLOBAL.my_var := TO_CHAR(:order.total * .85);
To reference a global variable, prefix the variable name with the word GLOBAL and a colon. The following example shows a global variable passed as an actual parameter to a procedure call:
calculate_discount(TO_NUMBER(:GLOBAL.my_var));
Referencing a global variable that has not been initialized through assignment causes a runtime error.
To destroy a global variable and release its memory, use the ERASE built-in procedure:
Erase('GLOBAL.my_var');
OBJECT GROUP VS OBJECT LIBRARY
Object Group
An object group is a container for a group of objects. You define an object group when you want to package related objects so you can copy or subclass them in another module.
Object groups provide a way to bundle objects into higher-level building blocks that can be used in other parts of an application and in subsequent development projects.
For example, you might build an appointment scheduler in a form and then decide to make it available from other forms in your applications. The scheduler would probably be built from several types of objects, including a window and canvas, blocks, and items that display dates and appointments, and triggers that contain the logic for scheduling and other functionality. If you packaged these objects into an object group, you could then copy them to any number of other forms in one simple operation.
You can create object groups in form and menu modules. Once you create an object group, you can add and remove objects to it as desired.
Object Library
In any development environment, you will always have standards to which you want your developers to adhere as well as common objects which can be reused throughout the development effort.
The Object Library provides an easy method of reusing objects and enforcing standards across the entire development organization.
You can use the Object Library to:
• create, store, maintain, and distribute standard and reusable objects.
• rapidly create applications by dragging and dropping predefined objects to your form.
There are several advantages to using object libraries to develop applications:
• Object libraries are automatically re-opened when you startup Form Builder, making your reusable objects immediately accessible.
• You can associate multiple object libraries with an application. For example, you can create an object library specfically for corporate standards, and you can create an object library to satisfy project-specific requirements.
• Object libraries feature SmartClasses-- objects that you define as being the standard. You use SmartClasses to convert objects to standard objects.
WHEN-VALIDATE-ITEM VS POST-TEXT-ITEM VS PRE-TEXT-ITEM
When-Validate-Item Trigger
Fires during the Validate the Item process. Specifically, it fires as the last part of item validation for
items with the New or Changed validation status.
Usage Notes
• Use a When-Validate-Item trigger to supplement Form Builder default item validation processing.
• It is possible to write a When-Validate-Item trigger that changes the value of an item that Form Builder is validating. If validation succeeds, Form Builder marks the changed item as Valid and does not re-validate it.
• While this behavior is necessary to avoid validation loops, it does make it possible for your application to commit an invalid value to the database.
• The setting you choose for the Defer Required Enforcement property can affect the When-Validate-Item
• trigger. See Defer_Required_Enforcement for details.
Pre-Text-Item Trigger
Fires during the Enter the Item process, during navigation from an item to a text item.
Usage Notes
Use a Pre-Text-Item trigger to perform the following types of tasks:
• derive a complex default value, based on other items previously entered into the same record.
• record the current value of the text item for future reference, and store that value in a global variable or form parameter.
A Pre-Text-Item trigger fires only when the form is run with a validation unit of the item, as specified by the Validation Unit form property.
Post-Text-Item Trigger
Fires during the Leave the Item process for a text item. Specifically, this trigger fires when the input focus moves from a text item to any other item.
Usage Notes
Use a Post-Text-Item trigger to calculate or change item values.
Post-Text-Item Trigger restrictions
The Post-Text-Item trigger does not fire when the input focus is in a text item and the operator uses the mouse to click on a button, check box, list item, or radio group item that has the Mouse Navigate property Off. When Mouse Navigate is Off for these items, clicking them with the mouse is a non-navigational event, and the input focus remains in the current item (in this example, a text item).
SHOW-WINDOW VS OPEN-WINDOW
Displays the indicated window at either the optionally included X,Y coordinates, or at the window's current X,Y coordinates. If the indicated window is a modal window, SHOW_WINDOW is executed as a GO_ITEM call to the first navigable item in the modal window.
/* ** Built-in: SHOW_WINDOW ** Example: Override the default (x,y) coordinates for a windows location while showing it. */ BEGIN Show_Window('online_help',20,5); END;
I HAVE A MULTI RECORD BLOCK I WANT TO HIGHLIGHT THE RECORD WHERE MY CURSOR IS . WHICH PROPERTY WILL U USE TO SET THE ABOVE ONE?
SYSTEM.CURSOR_ITEM
SYSTEM.CURSOR_ITEM represents the name of the block and item, block.item, where the input focus (cursor) is located. The value is always a character string.
Usage Notes
Within a given trigger, the value of SYSTEM.CURSOR_ITEM changes when navigation takes place. This differs from SYSTEM.TRIGGER_ITEM, which remains the same from the beginning to the end of single trigger.
SYSTEM.CURSOR_ITEM restrictions
Avoid using SYSTEM.CURSOR_ITEM in triggers where the current navigation unit is not the item, such as Pre- and Post-Record, Block, and Form triggers. In these triggers, the value of SYSTEM.CURSOR_ITEM is NULL.
WHAT IS THE TRIGGER WE WILL WRITE WHEN THE TAB PAGE CHANGED?
When-Tab-Page-Changed
Fires whenever there is explicit item or mouse navigation from one tab page to another in a tab canvas.
Usage Notes
• Use a When-Tab-Page-Changed trigger to perform actions when any tab page is changed during item or mouse navigation.
• When-Tab-Page-Changed fires only when tab page navigation is explicit; it does not respond to implicit navigation. For example, the trigger will fire when the mouse or keyboard is used to navigate between tab pages, but the trigger will not fire if an end user presses [Next Item] (Tab) to navigate from one field to another field in the same block, but on different tab pages.
• When-Tab-Page-Changed does not fire when the tab page is changed programmatically.
/* Use a When-Tab-Page-Changed trigger to dynamically
** change a tab page's label from lower- to upper-case (to indicate to end users if they already have ** navigated to the tab page):*/
DECLARE tp_nm VARCHAR2(30); tp_id TAB_PAGE; tp_lb VARCHAR2(30); BEGIN tp_nm := GET_CANVAS_PROPERTY('emp_cvs', topmost_tab_page); tp_id := FIND_TAB_PAGE(tp_nm); tp_lb := GET_TAB_PAGE_PROPERTY(tp_id, label); IF tp_lb LIKE 'Sa%' THEN SET_TAB_PAGE_PROPERTY(tp_id, label, 'SALARY'); ELSIF tp_lb LIKE 'Va%' THEN SET_TAB_PAGE_PROPERTY(tp_id, label, 'VACATION'); ELSE null; END IF; END;
WHAT ARE THE PROCEDURES TO CALL THE REPORTS FROM FORM?
RUN_PRODUCT built-in
Invokes one of the supported Oracle tools products and specifies the name of the module or module to be run. If the called product is unavailable at the time of the call, Form Builder returns a message to the end user.
RUN_REPORT_OBJECT built-in
Use this built-in to run a report from within a form. You can run the report against either a local or remote database server. Executing this built-in is similar using the RUN_PRODUCT built-in on a report.
Usage Notes
• Returns a VARCHAR2 value that uniquely identifies the report that is running either locally or on a remote report server. You can use this report ID string as a parameter to REPORT_OBJECT_STATUS , COPY_REPORT_OBJECT , and CANCEL_REPORT_OBJECT.
• If you invoke Run_Report_Object with a blank Report Server property, the return value will be NULL. In that case, you cannot then use the built-ins Report_Object_Status and Copy_Report_Object_Output, because they require an actual ID value.
HOW WILL U DYNAMICALLY POPULATE THE VALUES INTO A LIST ITEM AT RUNTIME ?
A list item is a list of text elements that can be displayed as either a poplist, Tlist, or combo box.
A list item displays a fixed number of elements. Each element in a list is a text string up to 30 characters long. At
runtime, you can programmatically evaluate, add, or remove list elements.
Runtime Behavior
Poplist The poplist style list item appears initially as a single field (similar to a text item field). When the end user selects the list icon, a list of available choices appears.
Tlist The Tlist style list item appears as a rectangular box which displays a fixed number of values. When the Tlist contains values that cannot be displayed (due to the displayable area of the item), a vertical scroll bar appears, allowing the end user to view and select undisplayed values.
Combo Box The combo box style list item combines the features found in poplists and text items. It displays fixed values and can accept a user-entered value.
Manipulating list items at runtime
Form Builder provides triggers that you can use to respond to interface events that occur when the end user
manipulates a list item.
• When-List-Activated
• When-List-Changed
At runtime, you can programmatically add, remove, modify, or evaluate elements in a list item by using the
following built-in subprograms:
• ADD_LIST_ELEMENT
Adds a single element to a list item.
/* ** Built-in: ADD_LIST_ELEMENT ** Example: Deletes index value 1 and adds the value "1994" to the list item called years when a button is pressed. * Trigger: When-Button-Pressed */
BEGIN Delete_List_Element('years',1); Add_List_Element('years', 1, '1994', '1994'); END;
• CONVERT_OTHER_VALUE
Converts the current value of a check box, radio group, or list item to the value associated with the current check box state (Checked/Unchecked), or with the current radio group button or list item element.
/*
** Built-in: CONVERT_OTHER_VALUE ** Example: Ensure that a particular checkbox's value represents either the checked or unchecked ** value before updating the record. ** Trigger: Pre-Update */ BEGIN Convert_Other_Value('Emp.Marital_Status'); END;
• DELETE_LIST_ELEMENT
Deletes a specific list element from a list item.
Delete_List_Element('years',1);
• GET_LIST_ELEMENT_COUNT
Returns the total number of list item elements in a list, including elements with NULL values.
• GET_LIST_ELEMENT_LABEL
Returns information about the requested list element label.
Usage Notes
The value associated with a list item element is not necessarily the list item's current value. That
is, the value of :block.list_item.
• GET_LIST_ELEMENT_VALUE
Returns the value associated with the specified list item element.
• POPULATE_LIST
Removes the contents of the current list and populates the list with the values from a record group.
The record group must be created at runtime and it must have the following two column (VARCHAR2)
structure:
Usage Notes
• Do not use the POPULATE_LIST built-in if the Mapping of Other Values property is defined and there are queried records in the block. Doing so may cause Form Builder to be unable to display records that have already been fetched.
For example, assume that a list item contains the values A, B, and C and the Mapping of Other
Values property is defined. Assume also that these values have been fetched from the
database (a query is open). At this point, if you populate the list using POPULATE_LIST, an error
will occur because Form Builder will attempt to display the previously fetched values (A, B, and
C), but will be unable to because these values were removed from the list and replaced with
new values.
• Before populating a list, close any open queries. Use the ABORT_QUERY built-in to close an open query.
• RETRIEVE_LIST
Retrieves and stores the contents of the current list into the specified record group. The target record
roup must have the following two-column (VARCHAR2) structure:
TO DISPLAY THE STACKED CANVAS ON A SPECIFIC PLACE WHICH PROPERTY WILL U SET?
Example 1: These 2 procedure calls both display the stacked canvas STACK_IT: SHOW_VIEW('stack_it'); SET_VIEW_PROPERTY('stack_it', visible, property_true); /* Example 2: This procedure call scrolls the view of the stacked canvas STACK_IT to X,Y coordinates 12 and 0:
SCROLL_VIEW('stack_it', 12, 0); /* Example 3: This procedure call changes the position of the stacked canvas STACK_IT to X,Y coordinates 12 and 20 on the underlying content canvas:*/
SET_VIEW_PROPERTY('stack_it', display_position, 12, 20); /* Example 4: This procedure call increases the width of the view,thereby making more of the canvas visible: SET_VIEW_PROPERTY('stack_it', width, 22);
WHEN WILL U USE ON-INSERT TIGGER?
Fires during the Post and Commit Transactions process when a record is inserted. Specifically, it fires after the Pre-Insert trigger fires and before the Post-Insert trigger fires, when Form Builder would normally insert a record in the database. It fires once for each row that is marked for insertion into the database.
Usage Notes
• Use an On-Insert trigger to replace the default Form Builder processing for handling inserted records during transaction posting.
• To perform the default Form Builder processing from this trigger, include a call to the INSERT_RECORD built-in.
INSERT_RECORD built-in
When called from an On-Insert trigger, inserts the current record into the database during Post and Commit Transactions processing. This built-in is included primarily for applications that will run against a non-ORACLE datasource.
HOW TO FIND THE WHICH ITEM IS FIRING THROUGH TRIGGER ?
SYSTEM.TRIGGER_ITEM
SYSTEM.TRIGGER_ITEM represents the item (BLOCK.ITEM) in the scope for which the trigger is currently firing. When referenced in a key trigger, it represents the item where the cursor was located when the trigger began. The value is always a character string.
Usage Notes
SYSTEM.TRIGGER_ITEM remains the same from the beginning to the end of given trigger. This differs from SYSTEM.CURSOR_ITEM, which may change within a given trigger when navigation takes place.
ON-ERROR TRIGGER USE? WHEN WILL THIS TRIGGER IS FIRE?
An On-Error trigger fires whenever Form Builder would normally cause an error message to display.
Usage Notes
• Use an On-Error trigger for the following purposes:
• to trap and recover from an error
• to replace a standard error message with a custom message
Use the ERROR_CODE , ERROR_TEXT , ERROR_TYPE , DBMS_ERROR_TEXT , or DBMS_ERROR_CODE built-in function in an On-Error trigger to identify a specific error condition.
• In most cases, On-Error triggers should be attached to the form, rather than to a block or item. Trapping certain errors at the block or item level can be difficult if these errors occur while Form Builder is performing internal navigation, such as during a Commit process.
Example
The following example checks specific error message codes and responds appropriately.
DECLARE lv_errcod NUMBER := ERROR_CODE; lv_errtyp VARCHAR2(3) := ERROR_TYPE; lv_errtxt VARCHAR2(80) := ERROR_TEXT; BEGIN IF (lv_errcod = 40nnn) THEN /* ** Perform some tasks here */ ELSIF (lv_errcod = 40mmm) THEN /* ** More tasks here */ ... ... ELSIF (lv_errcod = 40zzz) THEN /* ** More tasks here */ ELSE Message(lv_errtyp||'-'||to_char(lv_errcod)||': '||lv_errtxt); RAISE Form_Trigger_Failure; END IF; END;
BREAK
Halts form execution and displays the Debugger, while the current form is running in debug mode. From the Debugger you can make selections to view the values of global and system variables. The BREAK built-in is primarily useful when you need to inspect the state of a form during trigger execution.
/* ** Built-in: BREAK ** Example: Brings up the debugging window for a particular value of the 'JOB' item anytime the user changes records. Trigger: When-New-Record-Instance */ BEGIN IF :Emp.Job = 'CLERK' THEN Break; Call_Form('clerk_timesheet'); Break; END IF; END;
HOW TO DEBUGGING THE FORMS?
Creates a debug-capable form.
The debug Form Compiler option creates entries in your .FMX file used by the runtime source-level debugger, so set debug=yes for Form Compiler whenever you plan to set debug=yes for runtime.
ifcmp60 module=myform userid=scott/tiger debug=yes
RECALCULATE BULID IN
Marks the value of the specified formula calculated item (in each record of the block) for recalculation. Typically you would invoke this when the formula (or function or procedure that it invokes) refers to a system variable or built-in function which now would return a different value.
Note that actual recalculation doesn't happen immediately; it occurs sometime after the item is marked but before the new value of the calculated item is referenced or displayed to the end user. Your application's logic should not depend on recalculation of a calculated item occurring at a specific time.
About recalculation of calculated items
Form Builder automatically marks a calculated item for recalculation whenever the value of a bind variable involved in the calculation is modified in any way.
Note that actual recalculation doesn't happen immediately. It occurs sometime after the item is marked but before the new value of the calculated item is referenced or displayed to the end user. Your application's logic should not depend on recalculation of a calculated item occurring at a specific time.
Record Status Recalculation does not change the status of a calculated item's record, even if the status is NEW.
HOW TO FIND THE RECORD STATUS? STATUS TYPE?
SYSTEM.RECORD_STATUS
SYSTEM.RECORD_STATUS represents the status of the record where the cursor is located. The value can be one of four character strings:
CHANGED Indicates that a queried record's validation status is Changed.
INSERT Indicates that the record's validation status is Changed and that the record does not exist in the database.
NEW Indicates that the record's validation status is New.
QUERY Indicates that the record's validation status is Valid and that it was retrieved from the database.
Usage Notes
Both SYSTEM.RECORD_STATUS and the GET_RECORD_PROPERTY built-in return the status of a record in a given block, and in most cases, they return the same status. However, there are specific cases in which the results may differ.
SYSTEM.RECORD_STATUS can in certain cases return a value of NULL, because SYSTEM.RECORD_STATUS is undefined when there is no current record in the system. For example, in a When-Clear-Block trigger, Form Builder is at the block level in its processing sequence, so there is no current record to report on, and the value of SYSTEM.RECORD_STATUS is NULL.
GET_RECORD_PROPERTY, on the other hand, always has a value of NEW, CHANGED, QUERY, or INSERT, because it returns the status of a specific record without regard to the processing sequence or whether the record is the current record.
Assume that you want to create a trigger that performs a commit before clearing a Changed record. The following Key-CLRREC trigger performs this function.
IF :System.Record_Status IN ('CHANGED', 'INSERT') THEN Commit_Form; END IF; Clear_Record;
SYSTEM.RECORD_STATUS VS GET_RECORD_PROPERTY
Returns the value for the given property for the given record number in the given block. The three parameters are required. If you do not pass the proper constants, Form Builder issues an error. For example, you must pass a valid record number as the argument to the record_number parameter.
Usage Notes
The following table illustrates the situations which return a NEW status.
Record Status Block Status Form Status
Created record with no modified fields NEW
...and all records in current block are NEW NEW NEW
...and all blocks in current form are NEW NEW NEW NEW
The following table illustrates the effect on record, block, and form status of changes to base table items and control item in base table and control blocks.
Note:
In general, any assignment to a database item will change a record's status from QUERY to CHANGED (or from NEW to INSERT), even if the value being assigned is the same as the previous value. Passing an item to a procedure as OUT or IN OUT parameter counts as an assignment to it.
Both GET_RECORD_PROPERTY and the system variable SYSTEM.RECORD_STATUS return the status of a record in a given block, and in most cases, they return the same status. However, there are specific cases in which the results may differ.
GET_RECORD_PROPERTY always has a value of NEW, CHANGED, QUERY, or INSERT, because GET_RECORD_PROPERTY returns the status of a specific record without regard to the processing sequence or whether the record is the current record.
SYSTEM.RECORD_STATUS, on the other hand, can in certain cases return a value of NULL, because SYSTEM.RECORD_STATUS is undefined when there is no current record in the system. For example, in a When-Clear-Block trigger, Form Builder is at the block level in its processing sequence, so there is no current record to report on, and the value of SYSTEM.RECORD_STATUS is NULL.
HOW TO RUN THE USER DEFINED TRIGGER?
A user-named trigger is one that has a unique, user-supplied name. Because its name does not correspond to any Form Builder event, a user-named trigger can only be executed by calling it from within a built-in trigger, menu item command, or user-named subprogram. To call a user-named trigger, use the EXECUTE_TRIGGER built-in procedure. This procedure takes a parameter that names the trigger to be fired:
Execute_Trigger('my_user_named_trigger');
User-named triggers are required only in special situations. For most applications, writing a user-named subprogram and then calling that from a trigger or menu item command is preferred.
EXECUTE_TRIGGER VS EXECUTE_QUERY
EXECUTE_TRIGGER
EXECUTE_TRIGGER executes an indicated trigger.
Usage Notes
Because you cannot specify scope for this built-in, Form Builder always looks for the trigger starting at the lowest level, then working up.
To execute a built-in associated with a key, use the DO_KEY built-in instead of EXECUTE_TRIGGER. For example, rather than:
Execute_Trigger ('KEY-NEXT-ITEM');
Use instead:
Do_Key('NEXT_ITEM');
EXECUTE_QUERY built-in
Flushes the current block, opens a query, and fetches a number of selected records. If there are changes to commit, Form Builder prompts the operator to commit them before continuing EXECUTE_QUERY processing.
/* ** Built-in: EXECUTE_QUERY ** Example: Visit several blocks and query their contents, then go back to the block where original block. */
DECLARE block_before VARCHAR2(80) := :System.Cursor_Block; BEGIN Go_Block('Exceptions_List'); Execute_Query; Go_Block('User_Profile'); Execute_Query; Go_Block('Tasks_Competed'); Execute_Query; Go_Block( block_before ); END;
WHAT IS USE OF THE PARAMETER DETAILS IN RUN_PRODUCT?
Parameters
product Specifies a numeric constant for the Oracle product you want to invoke: FORMS specifies a Runform session. GRAPHICS specifies Graphics Builder. REPORTS specifies Report Builder. BOOK specifies Oracle Book.
module Specifies the VARCHAR2 name of the module or module to be executed by the called product. Valid values are the name of a form module, report, Graphics Builder display, or Oracle Book module. The application looks for the module or module in the default paths defined for the called product.
commmode Specifies the communication mode to be used when running the called product. Valid numeric
constants for this parameter are SYNCHRONOUS and ASYNCHRONOUS.
SYNCHRONOUS specifies that control returns to Form Builder only after the called product has been exited. The end user cannot work in the form while the called product is running.
ASYNCHRONOUS specifies that control returns to the calling application immediately, even if the called application has not completed its display.
execmode Specifies the execution mode to be used when running the called product. Valid numeric constants for this parameter are BATCH and RUNTIME. When you run Report Builder and Graphics Builder, execmode can be either BATCH or RUNTIME. When you run Form Builder, always set execmode to RUNTIME.
location Specifies the location of the module or module you want the called product to execute, either the file system or the database. Valid constants for this property are FILESYSTEM and DB.
Paramlist_name or paramlist_ID Specifies the parameter list to be passed to the called product. Valid values for this parameter are the VARCHAR2 name of the parameter list, the ID of the parameter list, or a null string (''). To specify a parameter list ID, use a variable of type PARAMLIST.
You can pass text parameters to called products in both SYNCHRONOUS and ASYNCHRONOUS mode. However, parameter lists that contain parameters of type DATA_PARAMETER (pointers to record groups) can only be passed to Report Builder and Graphics Builder in SYNCHRONOUS mode. (SYNCHRONOUS mode is required when invoking Graphics Builder to return an Graphics Builder display that will be displayed in a form chart item.)
Note: You can prevent Graphics Builder from logging on by passing a parameter list that includes a parameter with key set to LOGON and value set to NO.
Note: You cannot pass a DATA_PARAMETER to a child query in Report Builder. Data passing is supported only for master queries.
display Specifies the VARCHAR2 name of the Form Builder chart item that will contain the display (such as a pie chart, bar chart, or graph) generated by Graphics Builder. The name of the chart item must be specified in the format block_name.item_name. (This parameter is only required when you are using an Graphics Builder chart item in a form.)
Run_Product(REPORTS, 'empreport', SYNCHRONOUS, RUNTIME, FILESYSTEM, pl_id, NULL);
FORMS_DDL
Issues dynamic SQL statements at runtime, including server-side PL/SQL and DDL.
Note: All DDL operations issue an implicit COMMIT and will end the current transaction without allowing Form Builder to process any pending changes.
Syntax
FUNCTION FORMS_DDL (statement VARCHAR2);
Usage Notes
Commit (or roll back) all pending changes before you issue the FORMS_DDL command. All DDL operations issue an implicit COMMIT and will end the current transaction without allowing Form Builder to process any pending changes, as well as losing any locks Form Builder may have acquired.
Some supplied stored procedures issue COMMIT or ROLLBACK commands as part of their logic. Make sure all pending changes in the form are committed or rolled back before you call those built-ins. Use the SYSTEM.FORM_STATUS variable to check whether there are pending changes in the current form before you issue the FORMS_DDL command. (See Example 4.)
If you use FORMS_DDL to execute a valid PL/SQL block:
• Use semicolons where appropriate.
• Enclose the PL/SQL block in a valid BEGIN/END block structure.
• Do not end the PL/SQL block with a slash.
• Line breaks, while permitted, are not required.
If you use FORMS_DDL to execute a single DML or DDL statement:
• Omit the trailing semicolon to avoid an invalid character error.
To check whether the statement issued using FORMS_DDL executed correctly, use the FORM_SUCCESS or FORM_FAILURE Boolean functions. If the statement did not execute correctly, check the error code and error text using DBMS_ERROR_CODE and DBMS_ERROR_TEXT. Note that the values of DBMS_ERROR_CODE and DBMS_ERROR_TEXT are not automatically reset following successful execution, so their values should only be examined after an error has been detected by a call to FORM_SUCCESS or FORM_FAILURE.
** Built-in: FORMS_DDL ** Example: The expression can be a string literal. */ BEGIN Forms_DDL('create table temp(n NUMBER)'); IF NOT Form_Success THEN Message ('Table Creation Failed'); ELSE Message ('Table Created'); END IF; END;
POST VS FORM_COMMIT
FORM_COMMIT
Causes Form Builder to update data in the database to match data in the form. Form Builder first validates the form, then, for each block in the form, deletes, inserts, and updates to the database, and performs a database commit. As a result of the database commit, the database releases all row and table locks.
If the end user has posted data to the database during the current Runform session, a call to the COMMIT_FORM built-in commits this data to the database.
Following a commit operation, Form Builder treats all records in all base-table blocks as if they are queried records from the database. Form Builder does not recognize changes that occur in triggers that fire during commit processing.
/* ** Built-in: COMMIT_FORM ** Example: If there are records in the form to be committed, then do so. Raise an error if the ** commit was not successful. */ BEGIN /* ** Force validation to happen first */
Enter;
IF NOT Form_Success THEN RAISE Form_Trigger_Failure; END IF;
/* ** Commit if anything is changed */ IF :System.Form_Status = 'CHANGED' THEN
Commit_Form;
/* ** A successful commit operation sets Form_Status back to 'QUERY'. */ IF :System.Form_Status <> 'QUERY' THEN Message('An error prevented your changes from being committed.'); Bell; RAISE Form_Trigger_Failure; END IF; END IF; END;
POST
Writes data in the form to the database, but does not perform a database commit. Form Builder first validates the form. If there are changes to post to the database, for each block in the form Form Builder writes deletes, inserts, and updates to the database.
Any data that you post to the database is committed to the database by the next COMMIT_FORM that executes during the current Runform session. Alternatively, this data can be rolled back by the next CLEAR_FORM.
USAGE NOTES
IF THIS FORM WAS CALLED VIA OPEN_FORM WITH THE NO_SESSION PARAMETER SPECIFIED, THEN THE POST WILL VALIDATE AND WRITE THE DATA BOTH IN THIS FORM AND IN THE CALLING FORM.
POST-FORM-COMMIT VS POST – DATABASE- COMMIT
POST-FORMS-COMMIT TRIGGER
Fires once during the Post and Commit Transactions process. If there are records in the form that have been marked as inserts, updates, or deletes, the Post-Forms-Commit trigger fires after these changes have been written to the database but before Form Builder issues the database Commit to finalize the transaction.
If the operator or the application initiates a Commit when there are no records in the form have been marked as inserts, updates, or deletes, Form Builder fires the Post-Forms-Commit trigger immediately, without posting changes to the database.
Usage Notes
Use a Post-Forms-Commit trigger to perform an action, such as updating an audit trail, anytime a database commit is about to occur.
Example
This example can be used in concert with the Post-Database-Commit trigger to detect if records have been posted but not yet committed.
/* ** FUNCTION recs_posted_and_not_committed ** RETURN BOOLEAN IS ** BEGIN ** Default_Value('TRUE','Global.Did_DB_Commit'); ** RETURN (:System.Form_Status = 'QUERY' ** AND :Global.Did_DB_Commit = 'FALSE'); ** END; */ BEGIN :Global.Did_DB_Commit := 'FALSE'; END;
Post-Database-Commit Trigger
Fires once during the Post and Commit Transactions process, after the database commit occurs. Note that the Post-Forms-Commit trigger fires after inserts, updates, and deletes have been posted to the database, but before the transaction has been finalized by issuing the Commit. The Post-Database-Commit Trigger fires after Form Builder issues the Commit to finalize the transaction.
Usage Notes
Use a Post-Database-Commit trigger to perform an action anytime a database commit has occurred.
Example
/*
** FUNCTION recs_posted_and_not_committed RETURN BOOLEAN IS ** BEGIN ** Default_Value('TRUE','Global.Did_DB_Commit'); ** RETURN (:System.Form_Status = 'QUERY' ** AND :Global.Did_DB_Commit = 'FALSE'); ** END; */
BEGIN :Global.Did_DB_Commit := 'FALSE'; END;
WHAT IS THE USE OF RECORD GROUP AND LOV?
About LOV values and record group
LOV values are derived from an internal data structure called a record group. When you define an LOV, you associate it with a named record group.
A record group has a column/row framework that is similar to a database table. The LOV is the interface object that allows end users to view, scroll, and select records currently stored in the underlying record group.
A record group can have an unlimited number of columns of type CHAR, LONG, NUMBER, or DATE, provided that the total number of columns does not exceed 64K.
When you create an LOV, you specify which of the columns in the underlying record group should be displayed in the LOV window. Record group columns are always displayed in the LOV window in the order that they occur in the record group.
Because LOVs and record groups are separate objects, you can create multiple LOVs based on the same record group. For example, you might create two LOVs based on the same 4-column record group, each of which might display different columns from that record group.
Record Group Values The actual values in a record group come from one of two sources:
• the execution of a SELECT statement that you associated with the record group at design time (query record group)
• an array of static values that you associated with the record group at design time (static record group)
Note: By default, when an LOV is based on a query record group (a record group that has an associated SELECT statement), Form Builder executes the query each time the LOV is displayed, and fetches all of the records that meet the record group query criteria. This functionality ensures that the LOV always displays current database values.
HOW TO PASS THE PARAMETERS TO REPORTS AND WHAT IS THE METHODS AVAILABLE IN FORMS?
PROCEDURE RUN_PRODUCT (product NUMBER, module VARCHAR2, commmode NUMBER, execmode NUMBER, location NUMBER, paramlist_name VARCHAR2, display VARCHAR2);
Paramlist_name or paramlist_ID Specifies the parameter list to be passed to the called product. Valid values for this parameter are the VARCHAR2 name of the parameter list, the ID of the parameter list, or a null string (''). To specify a parameter list ID, use a variable of type PARAMLIST.
You can pass text parameters to called products in both SYNCHRONOUS and ASYNCHRONOUS mode. However, parameter lists that contain parameters of type DATA_PARAMETER (pointers to record groups) can only be passed to Report Builder and Graphics Builder in SYNCHRONOUS mode. (SYNCHRONOUS mode is required when invoking Graphics Builder to return an Graphics Builder display that will be displayed in a form chart item.)
Note: You can prevent Graphics Builder from logging on by passing a parameter list that includes a parameter with key set to LOGON and value set to NO.
Note: You cannot pass a DATA_PARAMETER to a child query in Report Builder. Data passing is supported only for master queries.
HOW TO CALL REPORTS FROM FORM?
Use this built-in to run a report from within a form. You can run the report against either a local or remote database server. Executing this built-in is similar using the RUN_PRODUCT built-in on a report.
Usage Notes
n Returns a VARCHAR2 value that uniquely identifies the report that is running either locally or on a remote report server. You can use this report ID string as a parameter to REPORT_OBJECT_STATUS , COPY_REPORT_OBJECT , and CANCEL_REPORT_OBJECT.
If you invoke Run_Report_Object with a blank Report Server property, the return value will be NULL. In that case, you cannot then use the built-ins Report_Object_Status and Copy_Report_Object_Output, because they require an actual ID value.
SYNCHRONIZE VS ASYNCHRONIZE?
commmode Specifies the communication mode to be used when running the called product. Valid numeric constants for this parameter are SYNCHRONOUS and ASYNCHRONOUS.
SYNCHRONOUS specifies that control returns to Form Builder only after the called product has been exited. The end user cannot work in the form while the called product is running.
ASYNCHRONOUS specifies that control returns to the calling application immediately, even if the called application has not completed its display.
WHAT ARE THE SYSTEM VARIABLES?
A system variable is an Form Builder variable that keeps track of an internal Form Builder state. You can reference the value of a system variable to control the way an application behaves.
Form Builder maintains the values of system variables on a per form basis. That is, the values of all system variables correspond only to the current form.
RESTRICTED AND UNRESTRICED BUILD –IN
Any built-in subprogram that initiates navigation is restricted. This includes subprograms that move the input focus from one item to another, and those that involve database transactions. Restricted built-ins are not allowed in triggers that fire in response to navigation.
For example, the GO_ITEM and NEXT_SET built-ins are both restricted procedures. GO_ITEM moves the input focus from a source item to a target item, which requires navigation. Similarly, the NEXT_SET procedure causes Form Builder to navigate internally to the block level, fetch a set of records, and then navigate to the first item in the first record. (Note that this navigation happens internally as a result of default processing, and may not be apparent to the form operator.)
Because GO_ITEM and NEXT_SET both initiate navigation, they cannot be called from triggers that fire in response to internal navigational events; that is, triggers that fire while navigation is already occurring. Thus, a restricted procedure cannot be called from a Pre-Block trigger, because the Pre-Block trigger fires during internal navigation. In fact, restricted subprograms are not allowed from any PRE- or POST- navigational triggers.
You can, however, call a restricted built-in subprogram from a When-New-Instance trigger. For example, the When-New-Item-Instance trigger fires after navigation to an item has succeeded, when the form is waiting for input. This is an important point to remember as you select trigger types during application development.
The reference topic of each trigger has a "Legal Commands" section that describes which types of built-ins (restricted or unrestricted) can be called from the trigger. Also, each built-in description includes a "Built-in Type" section that indicates whether the built-in is a restricted or unrestricted procedure or function.
SYSTEM.MESSAGE_LEVEL := 5 AND 20
SYSTEM.MESSAGE_LEVEL system variable
Syntax
SYSTEM.MESSAGE_LEVEL
Description
SYSTEM.MESSAGE_LEVEL stores one of the following message severity levels: 0, 5, 10, 15, 20, or 25. The default value is 0.
SYSTEM.MESSAGE_LEVEL can be set to either a character string or a number. The values assigned can be any value between 0 and 25, but values lower than 0 or higher than 25 will generate an error.
During a Runform session, Form Builder suppresses all messages with a severity level that is the same or lower (less severe) than the indicated severity level.
Assign a value to the SYSTEM.MESSAGE_LEVEL system variable with standard PL/SQL syntax:
:System.Message_Level := value;
The legal values for SYSTEM.MESSAGE_LEVEL are 0, 5, 10, 15, 20,and 25. Form Builder does not suppress prompts or vital error messages, no matter what severity level you select.
Assume that you want Form Builder to display only the most severe messages (level 25). The following Pre-Form trigger suppresses all messages at levels 20 and below.
:System.Message_Level := '20';
SYSTEM.CURRENT_ITEM VS SYSTEM.TRIGGER_ITEM
SYSTEM.CURRENT_ITEM
Description
The value that the SYSTEM.CURRENT_ITEM system variable represents depends on the current navigation unit:
1. If the current navigation unit is the item (as in the Pre- and Post-Item triggers), the value of SYSTEM.CURRENT_ITEM is the name of the item that Form Builder is processing or that the cursor is in. The returned item name does not include a block name prefix.
2. If the current navigation unit is the record, block, or form (as in the Pre- and Post- Record, Block, and Form triggers), the value of SYSTEM.CURRENT_ITEM is NULL.
The value is always a character string.
SYSTEM.TRIGGER_ITEM system variable
Syntax
SYSTEM.TRIGGER_ITEM
Description
SYSTEM.TRIGGER_ITEM represents the item (BLOCK.ITEM) in the scope for which the trigger is currently firing. When referenced in a key trigger, it represents the item where the cursor was located when the trigger began. The value is always a character string.
Usage Notes
SYSTEM.TRIGGER_ITEM remains the same from the beginning to the end of given trigger. This differs from SYSTEM.CURSOR_ITEM, which may change within a given trigger when navigation takes place.
Assume that you want to write a user-defined procedure that navigates to the item where the cursor was when the current trigger initially fired. The following statement performs this function.
Go_Item(:System.Trigger_Item);
HOW TO PASS THE PARAMETER ONE FORM TO ANOTHER FORM?
/* Example 1: ** Call a form in query-only mode. BEGIN CALL_FORM('empbrowser', no_hide, no_replace, query_only); END; /* Example 2: ** Call a form, pass a parameter list (if it exists)
DECLARE pl_id PARAMLIST; theformname VARCHAR2(20); BEGIN theformname := 'addcust'; /* Try to lookup the 'TEMPDATA' parameter list */ pl_id := GET_PARAMETER_LIST('tempdata'); IF ID_NULL(pl_id) THEN CALL_FORM(theformname); ELSE CALL_FORM(theformname, hide, no_replace, no_query_only, pl_id); END IF; CALL_FORM('lookcust', no_hide, do_replace, query_only); END;
TWO TEXT ITEM AND NOT REQUIRED FIELD. IF I MOVE CURSOR THROUGH MOUSE FROM ONE ITEM
TO ANOTHER ITEM (WITHOUT ENTERING). WHAT ARE THE TRIGGER HAS FIRED? WHEN_VALIDATE_ITEM ONLY HOW TO POPULATED DATA IN THE CONTROL BLOCK?
HOW TO CONTROL THE DUPLIATE RECORD IN MULTIPLE RECORD BLOCK?
INSERT SEQUENCE OF THE TRIGGER
ParamType
Pll Procedure Vs Programm Unit Procedure Vs Back End Procedure
TYPE OF ALERTS
Trigger Sequences Item Level, Block Level & Form Level
Error-Code & Error-Text (Form) / SqlErrm & SqlCode (Back End)
Multi Record Block Unique Check ?
IF U CALLING ONE FORM TO ANOTHER FORM? HOW TO COMMIT FIRST FORM?
