Oracle Applications: Oracle Forms Material

Forms

Definition

Forms used for presenting and manipulating data can be developed. It is GUI used for

developing client server database application.

.FMB Form Module Binary

.FMT Form Module Text

.FMX Form Module Executable

COMPONENTS OF FORMS

1.Form Builder

It is used to create a form. The design and layout of data entry screens the creations of

event driven PL/SQL code used for data validation and navigate can be done via form builder.

2.Form Compiler

It is required to compile the file created in form builder and create a binary file, which

can be executable form runtime.

3.Form Runtime

It is used to run the complied code created by forms compiler.

COMPONENTS OF FORM BUILDER

1. Object Navigator

It is hierarchical browsing and editing interface that enables you locate and manipulate

application objects quickly and easily.

2.Property Palette

It is used set and modify the properties for all objects in form modules.

3.Layout Editor

It is graphical design facility for creating and arranging interface items and graphical objects in

your application.

4.PL / SQL Editor

It is the integrated functionality of oracle procedure builder that exists with in form builder. It

provides:

Development of Trigger, Procedures, Functions and Packages

Development of libraries to hold PL/SQL program unit.

FORM MODULE TYPES

1.Form Module

It is a collection of objectives such as block, canvas, items and event based PL/SQL code

blocks called trigger .

2.Menu Module

It is a collection of menu items. It can be main menu or sub menu.

3.PL / SQL Libraries

The library module is a collection of PL/SQL function and package stored ion a single library

file. This library file is the attached to form / menu modules. All other objects in the form or

menu can now access share the collection of PL/SQL functions and procedures.

4.Object Libraries

It is a collection of form objects that you can use in other modules. You can create it to store,

maintain and distribute standard objects that can be reuse across the entire development

organization.

5. Object Group (Form Builder)

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.

OBJECTS OF FORMS

1.Blocks

Block is logical owner of items. It provides a mechanism for grouping related items into a

functional unit for storing, displaying and manipulating records.

2.Items

These are interface objects that present data values to the user or enable the user to interact

with the form.

3. Canvas

A canvas is the background object upon which interface items appear.

4. Frames

Frames are used to arrange items with in a block.

5. Windows

Windows contains for all visual objects that make up a form builder application.

6. PL/SQL Code Block

It is used for event driven code. That code automatically executes when a specific event

occurs.

Form Built - ins

1.CLEAR_FORM

Causes Form Builder to remove all records from, or flush, the current form, and puts the input

focus in

the first item of the first block.

2.COMMIT_FORM

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.

3.DEBUG_MODE

Toggles debug mode on and off in a menu. When debug mode is on in a menu, Form Builder

issues an appropriate message when a menu item command executes.

4. ENTER

Validates data in the current validation unit. (The default validation unit is Item.)

5.ERASE

Removes an indicated global variable, so that it no longer exists, and releases the memory

associated with the global variable. Global always allocate 255 bytes of storage. To ensure

that performance is not impacted more than necessary, always erase any global variable when

it is no longer needs

6. EXECUTE_TRIGGER

EXECUTE_TRIGGER executes an indicated trigger.

7. EXIT_FORM

Provides a means to exit a form, confirming commits and specifying rollback action.

8.FIND_FORM

Searches the list of forms and returns a form module ID when it finds a valid form with the

given name. You must define an appropriately typed variable to accept the return value.

Define the variable with a type of Form module.

9. FORM_FAILURE

Returns a value that indicates the outcome of the action most recently

performed during the current Runform session.

Use FORM_FAILURE to test the outcome of a built–in to determine

further processing within any trigger. To get the correct results, you

must perform the test immediately after the action executes. That is,

another action should not occur prior to the test.

Example:

** Built–in: FORM_FAILURE

** Example: Determine if the most recently executed built–in

** failed.

BEGIN

GO_BLOCK(’Success_Factor’);

** If some validation failed and prevented us from leaving

** the current block, then stop executing this trigger.

** Generally it is recommended to test

** IF NOT Form_Success THEN ...

** Rather than explicitly testing for FORM_FAILURE

IF Form_Failure THEN

RAISE Form_Trigger_Failure;

END IF;

END;

FORM_FATAL

Returns the outcome of the action most recently performed during the current Runform

session.

Use FORM_FATAL to test the outcome of a built–in to determine further processing within any

trigger. To get the correct results, you must perform the test immediately after the action

executes. That is, another action should not occur prior to the test.

Example:

/* ** Built–in: FORM_FATAL

** Example: Check whether the most–recently executed built–in had a fatal error.*/

BEGIN

User_Exit(’Calculate_Line_Integral control.start control.stop’);

** If the user exit code returned a fatal error, print a

** message and stop executing this trigger.

** Generally it is recommended to test **

** IF NOT FORM_SUCCESS THEN ... **

** Rather than explicitly testing for FORM_FATAL

IF Form_Fatal THEN

Message(’Cannot calculate the Line Integral due to internal error.’);

RAISE Form_Trigger_Failure;

END IF;

END;

FORM_SUCCESS

Returns the outcome of the action most recently performed during the current Runform

session.

Use FORM_SUCCESS to test the outcome of a built–in to determine further processing within

any trigger. To get the correct results, you must perform the test immediately after the action

executes. That is, another action should not occur prior to the test.

Note: FORM_SUCCESS should not be used to test whether a COMMIT_FORM or POST built–in

has succeeded. Because COMMIT_FORM may cause many other triggers to fire, when you

evaluate FORM_SUCCESS it may not reflect the status of COMMIT_FORM but of some other,

more recently executed built–in.

A more accurate technique is to check that the SYSTEM.FORM_STATUS variable is set to

’QUERY’ after the operation is done.

Example:

** Built–in: FORM_SUCCESS

** Example: Check whether the most–recently executed built–in ** succeeded.

BEGIN

/* ** Force validation to occur*/

Enter;

/* ** If the validation succeeded, then Commit the data. ** */

IF Form_Success THEN

Commit;

IF :System.Form_Status <> ’QUERY’ THEN

Message(’Error prevented Commit’);

RAISE Form_Trigger_Failure;

END IF;

END;

FORMS_DDL

FORMS_DDL( statement);

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 Oracle Forms to process any pending changes.

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:

Example 1:

/* ** 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;

Example 2:

/* ** Built–in: FORMS_DDL ** Example: The string can be an expression or variable.

** Create a table with n Number columns. ** TEMP(COL1, COL2, ..., COLn).

PROCEDURE Create_N_Column_Number_Table (n NUMBER) IS my_stmt

VARCHAR2(2000);

BEGIN

my_stmt := ’create table tmp(COL1 NUMBER’;

FOR I in 2..N LOOP

my_stmt := my_stmt||’,COL’||TO_CHAR(i)||’ NUMBER’;

END LOOP;

my_stmt := my_stmt||’)’;

/* ** Now, create the table... */

Forms_DDL(my_stmt);

IF NOT Form_Success THEN

Message (’Table Creation Failed’);

ELSE

Message (’Table Created’);

END IF;

END;

Example 3:

/* ** Built–in: FORMS_DDL ** Example: The statement parameter can be a block

** of dynamically created PL/SQL code. */

DECLARE

procname VARCHAR2(30);

BEGIN

IF :global.flag = ’TRUE’ THEN

procname := ’Assign_New_Employer’;

ELSE

procname := ’Update_New_Employer’;

END IF;

Forms_DDL(’Begin ’|| procname ||’; End;’);

IF NOT Form_Success THEN

Message (’Employee Maintenance Failed’);

ELSE

Message (’Employee Maintenance Successful’);

END IF;

END;

Example 4:

/* ** Built–in: FORMS_DDL ** Example: Issue the SQL statement passed in as an argument,

** and return a number representing the outcome of ** executing the SQL statement.

** A result of zero represents success. */

FUNCTION Do_Sql (stmt VARCHAR2, check_for_locks BOOLEAN := TRUE)

RETURN NUMBER IS

SQL_SUCCESS CONSTANT NUMBER := 0;

BEGIN

IF stmt IS NULL THEN

Message (’DO_SQL: Passed a null statement.’);

RETURN SQL_SUCCESS;

END IF

;

IF Check_For_Locks AND :System.Form_Status = ’CHANGED’ THEN

Message (’DO_SQL: Form has outstanding locks pending.’);

RETURN SQL_SUCCESS;

END IF;

Forms_DDL(stmt);

IF Form_Success THEN

RETURN SQL_SUCCESS;

ELSE

RETURN Dbms_Error_Code;

END IF;

END;

GET_FORM_PROPERTY

Returns information about the given form. If your application is a multi-form application, then

you can call this built-in to return information about the calling form, as well as about the

current, or called form.

ID_NULL

Returns a BOOLEAN value that indicates whether the object ID is available.

NEW_FORM

Exits the current form and enters the indicated form. The calling form is terminated as the

parent form. If the calling form had been called by a higher form, Oracle Forms keeps the

higher call active and treats it as a call to the new form. Oracle Forms releases memory (such

as database cursors) that the terminated form was using.

Oracle Forms runs the new form with the same Runform options as the parent form. If the

parent form was a called form, Oracle Forms runs the new form with the same options as the

parent form.

NEW_FORM (formmodule_name VARCHAR2,

rollback_mode,query_mode,data_mode,paramlist_name )

formmodule_name

Specifies the formmodule name of the called form. The name must be enclosed in single

quotes. The data type of the name is CHAR.

rollback_mode

TO_SAVEPOINT Oracle Forms rolls back all uncommitted changes (including posted changes)

to the current form’s savepoint.

NO_ROLLBACK Oracle Forms exits the current form without rolling back to a savepoint. You

can leave the top level form without performing a rollback, which means that you retain any

locks across a NEW_FORM operation. These locks can also occur when invoking Oracle Forms

from an external 3GL program. The locks are still in effect when you regain control from Oracle

Forms.

FULL_ROLLBACK Oracle Forms rolls back all uncommitted changes (including posted

changes) that were made during the current Runform session. You cannot specify a

FULL_ROLLBACK from a form that is running in post–only mode. (Post–only mode can occur

when your form issues a call to another form while unposted records exist in the calling form.

To avoid losing the locks issued by the calling form, Oracle Forms prevents any commit

processing in the called form.)

query_mode

Takes one of the following constants as an argument:

NO_QUERY_ONLY Runs the indicated form normally, allowing the operator to perform

inserts, updates, and deletes in the form.

QUERY_ONLY Runs the indicated form as a query–only form.

paramlist_id

Specifies the unique ID Oracle Forms assigns when it creates the parameter list. Specify a

parameter list when you want to pass parameters from the calling form to the new form. The

data type of the ID is PARAMLIST.

A parameter list passed to a form via NEW_FORM cannot contain parameters of type

DATA_PARAMETER (a pointer to record group).

paramlist_name

The name you gave the parameter list object when you defined it. The data type of the name

is CHAR. A parameter list passed to a form via NEW_FORM cannot contain parameters of type

DATA_PARAMETER (a pointer to record group).

CALL_FORM.

Runs an indicated form while keeping the parent form active. Oracle Forms runs the called

form with the same Runform preferences as the parent form. When the called form is exited

Oracle Forms processing resumes in the calling form at the point from which you initiated the

call to CALL_FORM.

CALL_FORM (formmodule_name VARCHAR2, display NUMBER, switch_menu

NUMBER, query_mode NUMBER, data_mode NUMBER, paramlist_name

VARCHAR2);

Parameters:

formmodule_name

Specifies the formmodule name of the called form. The name must be enclosed in single

quotes. The data type of the name is CHAR.

display

Specify one of the following constants as an argument:

HIDE Causes Oracle Forms to clear the calling form from the screen before drawing the called

form. HIDE is the default parameter.

NO_HIDE Causes Oracle Forms to display the called form without clearing the calling form

from the screen.

switch_menu

Takes one of the following constants as an argument:

NO_REPLACE Causes Oracle Forms to keep the default menu application of the calling form

active for the called form.

DO_REPLACE Causes Oracle Forms to replace the default menu application of the calling form

with the default menu application of the called form.

query_mode

Takes one of the following constants as an argument:

NO_QUERY_ONLY Causes Oracle Forms to run the indicated form in normal mode, allowing

the operator to perform inserts, updates, and deletes from within the called form.

QUERY_ONLY Causes Oracle Forms to run the indicated form in Query Only mode, allowing

the operator to query, but not to insert, update, or delete records.

paramlist_id

Specifies the unique ID Oracle Forms assigns when it creates the parameter list. You can

optionally include a parameter list as initial input to the called form. The data type of the ID is

PARAMLIST.

paramlist_name

The name you gave the parameter list object when you defined it. The data type of the name

is CHAR.

Call_Form(’lookcust’,NO_HIDE,DO_REPLACE,QUERY_ONLY);

OPEN_FORM

Opens the indicated form. Call OPEN_FORM to create multiple–form applications, that is,

applications that open more than one form at the same time.

OPEN_FORM (form_name VARCHAR2, activate_mode NUMBER, session_mode

NUMBER, data_mode NUMBER, paramlist_id PARAMLIST);

form_name

Specifies the CHAR name of the form to open.

activate_mode

ACTIVATE Sets focus to the form to make it the active form in the application.

NO_ACTIVATE Opens the form but does not set focus to the form. The current form remains

current.

session_mode

NO_SESSION Specifies that the opened form should share the same database session as the

current form. A COMMIT operation in any form will cause validation and commit processing to

occur for all forms running in the same session.

SESSION Specifies that a new, separate database session should be created for the opened

form.

paramlist_name

Specifies the CHAR name of a parameter list to be passed to the opened form.

paramlist_id

Specifies the unique ID that Oracle Forms assigns to the parameter list at the time it is

created. Use the GET_PARAMETER_LIST function to return the ID to a variable of type

PARAMLIST.

OPEN_FORM( form_name);

OPEN_FORM( form_name,activate_mode);

OPEN_FORM( form_name,activate_mode,session_mode);

OPEN_FORM( form_name,activate_mode,session_mode,paramlist_name);

OPEN_FORM( form_name,activate_mode,session_mode,paramlist_id);

REPLACE_MENU

Replaces the current menu with the specified menu, but does not make the new menu active.

REPLACE_MENU also allows you to change the way the menu displays and the role.

SET_FORM_PROPERTY

Sets a property of the given form.

Syntax:

SET_FORM_PROPERTY( formmodule_id, property, value);

SET_FORM_PROPERTY( formmodule_name, property, value);

Description:

The GET_APPLICATION_PROPERTY built–in returns information about the current Oracle Forms

application. You must call this built–in once for each value you want to retrieve.

tm_name := Get_Application_Property(TIMER_NAME);

Example 2:

** Built–in: GET_APPLICATION_PROPERTY

** Example: Capture the username and password of the ** currently logged–on user, for use

in calling ** another Tool.

PROCEDURE Get_Connect_Info( the_username IN OUT VARCHAR2,

the_password IN OUT VARCHAR2,

the_connect IN OUT VARCHAR2) IS

BEGIN

the_username := Get_Application_Property(USERNAME);

the_password := Get_Application_Property(PASSWORD);

the_connect := Get_Application_Property(CONNECT_STRING);

END;

Form- System Variables

1.SYSTEM.CURRENT_FORM

SYSTEM.CURRENT_FORM represents the name of the form that Form Builder is executing. The

value is always a character string.

PROCEDURE STORE_FORMNAME IS

BEGIN

:GLOBAL.Calling_Form := :System.Current_Form;

END;

2. SYSTEM.FORM_STATUS

SYSTEM.FORM_STATUS represents the status of the current form. The value can be one of

three character strings:

CHANGED Indicates that the form contains at least one block with a Changed

record. The value of SYSTEM.FORM_STATUS becomes CHANGED

only after at least one record in the form has been changed and

the associated navigation unit has also changed.

NEW Indicates that the form contains only New records.

QUERY Indicates that a query is open. The form contains at least one

block with QUERY records and no blocks with CHANGED records.

IF :System.Form_Status = ’CHANGED’THEN

Commit_Form;

END IF;

Clear_Form;

3. SYSTEM. MODE

SYSTEM.MODE indicates whether the form is in Normal, Enter Query, or Fetch Processing

mode. The value is always a character string.

NORMAL Indicates that the form is currently in normal processing mode.

ENTER-QUERYIndicates that the form is currently in Enter Query mode.

QUERY Indicates that the form is currently in fetch processing mode, meaning

that a query is currently being processed.

Example:

Assume that you want Oracle Forms to display an LOV when the operator enters query

mode and the input focus is in a particular text item. The following trigger accomplishes

that operation.

/* ** When–New–Item–Instance Trigger */

BEGIN

IF :System.Cursor_Item = ’EMP.EMPNO’ and :System.Mode = ’ENTER–QUERY’ THEN

IF NOT Show_Lov(’my_lov’) THEN

RAISE Form_Trigger_Failure;

END IF;

End if;

END;

BLOCKS

Block is logical owner of items. It provides a mechanism for grouping related items into a

functional unit for storing, displaying and manipulating records.

Types of Blocks

1. Data Blocks

Data blocks are associated with data (table columns) within a database.

By default, the association between a data block and the database allows operators to

automatically query, update, insert, and delete rows within a database.

Data blocks can be based on database tables, views, procedures, or transactional

triggers.

2. Control Blocks

A control block is not associated with the database, and the items in a control

block do not relate to table columns within a database.

All blocks are either single-record or multi-record blocks:

A single-record block displays one record at a time.

A multi-record block displays more than one record at a time.

In addition, a data block can also be a master or detail block:

Master block displays a master record associated with detail records displayed in a

detail block.

A detail block displays detail records associated with a master record displayed in

master block.

Block Built - ins

1. BLOCK_MENU built-in

Displays a list of values (LOV) containing the sequence number and names of valid blocks in

your form. Form Builder sets the input focus to the first enterable item in the block you select

from the LOV.

Example:

** Built–in: BLOCK_MENU ** Example: Calls up the list of blocks in the form when the

** user clicks a button, and prints a message if ** the user chooses a new block out of the list

to ** which to navigate. */

DECLARE

prev_blk VARCHAR2(40) := :System.Cursor_Block;

BEGIN

BLOCK_MENU;

IF :System.Cursor_Block <> prev_blk THEN

Message(’You successfully navigated to a new block!’);

END IF;

END;

2. CLEAR_BLOCK built-in

Causes Form Builder to remove all records from, or "flush," the current block.

Clear_Block(No_Validate);

COMMIT_MODE

The optional action parameter takes the following possible constants as arguments:

ASK_COMMIT

Form Builder prompts the end user to commit the changes during CLEAR_BLOCK

processing.

DO_COMMIT

Form Builder validates the changes, performs a commit, and flushes the current block

without prompting the end user.

NO_COMMIT

Form Builder validates the changes and flushes the current block without performing a

commit or prompting the end user.

NO_VALIDATE

Form Builder flushes the current block without validating the changes, committing the

changes, or prompting the end user.

3. FIND_BLOCK

Searches the list of valid blocks and returns a unique block ID. You must define an

appropriately typed variable to accept the return value. Define the variable with a type

of Block.

4. GET_BLOCK_PROPERTY

Returns information about a specified block. You must issue a call to the built-in once

for each property value you want to retrieve.

Syntax:

GET_BLOCK_PROPERTY( block_id, property);

GET_BLOCK_PROPERTY( block_name, property);

** Determine the (1) Current Record the cursor is in,

** (2) Current Record which is visible at the

** first (top) line of the multirecord

** block.

cur_rec := Get_Block_Property( bk_id, CURRENT_RECORD);

top_rec := Get_Block_Property( bk_id, TOP_RECORD);

5. GO_BLOCK

GO_BLOCK navigates to an indicated block. If the target block is non-enterable , an

error occurs.

6. ID_NULL

Returns a BOOLEAN value that indicates whether the object ID is available.

7. NEXT_BLOCK

Navigates to the first navigable item in the next enterable block in the navigation

sequence

8.PREVIOUS_BLOCK

Navigates to the first navigable item in the previous enterable block in the navigation

sequence

9.SET_BLOCK_PROPERTY

Sets the given block characteristic of the given block.

Syntax:

SET_BLOCK_PROPERTY( block_id, property, value);

SET_BLOCK_PROPERTY( block_name, property, value);

Example:

/* ** Built–in: SET_BLOCK_PROPERTY

** Example: Prevent future inserts, updates, and deletes to ** queried records in the block

whose name is ** passed as an argument to this procedure. */

PROCEDURE Make_Block_Query_Only( blk_name IN VARCHAR2 )

blk_id Block;

BEGIN

/* Lookup the block’s internal ID */

blk_id := Find_Block(blk_name);

/* ** If the block exists (ie the ID is Not NULL) then set ** the three properties for this block.

Otherwise signal ** an error. */

IF NOT Id_Null(blk_id) THEN

Set_Block_Property(blk_id,INSERT_ALLOWED,PROPERTY_FALSE);

Set_Block_Property(blk_id,UPDATE_ALLOWED,PROPERTY_FALSE);

Set_Block_Property(blk_id,DELETE_ALLOWED,PROPERTY_FALSE);

ELSE

Message(’Block ’||blk_name||’ does not exist.’);

RAISE Form_Trigger_Failure;

END IF;

END;

Block - System Variables

1.SYSTEM.BLOCK_STATUS

SYSTEM.BLOCK_STATUS represents the status of a Data block where the cursor is located, or

the current data block during trigger processing. The value can be one of three character

strings:

CHANGED Indicates that the block contains at least one Changed record.

NEW Indicates that the block contains only New records.

QUERY Indicates that the block contains only Valid records that have been retrieved

from the database.

Example:

Assume that you want to create a trigger that performs a commit before clearing a block if

there are changes to commit within that block.

The following Key–CLRBLK trigger performs this function.

IF :System.Block_Status = ’CHANGED’

THEN Commit_Form;

END IF;

Clear_Block;

2.SYSTEM.CURRENT_BLOCK

The value that the SYSTEM.CURRENT_BLOCK system variable represents depends on the

current navigation unit:

If the current navigation unit is the block, record, or item (as in the Pre- and Post- Item,

Record, and Block triggers), the value of SYSTEM.CURRENT_BLOCK is the name of the block

that Form Builder is processing or that the cursor is in.

If the current navigation unit is the form (as in the Pre- and Post-Form triggers), the value of

SYSTEM.CURRENT_BLOCK is NULL.

3.SYSTEM.CURSOR_BLOCK

The value that the SYSTEM.CURSOR_BLOCK system variable represents depends on the

current navigation unit:

If the current navigation unit is the block, record, or item (as in the Pre- and Post- Item,

Record, and Block triggers), the value of SYSTEM.CURSOR_BLOCK is the name of the block

where the cursor is located. The value is always a character string.

If the current navigation unit is the form (as in the Pre- and Post-Form triggers), the value of

SYSTEM.CURSOR_BLOCK is NULL.

Example:

Assume that you want to create a Key–NXTBLK trigger at the form level that navigates

depending on what the current block is. The following trigger performs this function, using

:SYSTEM.CURSOR_BLOCK stored in a local variable.

DECLARE

curblk VARCHAR2(30);

BEGIN

curblk := :System.Cursor_Block;

IF curblk = ’ORDERS’ THEN

Go_Block(’ITEMS’);

ELSIF curblk = ’ITEMS’ THEN

Go_Block(’CUSTOMERS’);

ELSIF curblk = ’CUSTOMERS’ THEN

Go_Block(’ORDERS’);

END IF;

END;

4. SYSTEM.MASTER_BLOCK

This system variable works with its companion SYSTEM.COORDINATION_OPERATION to help

an On-Clear-Details trigger determine what type of coordination-causing operation fired the

trigger, and on which master block of a master/detail relation.

5. SYSTEM.TRIGGER_BLOCK

SYSTEM.TRIGGER_BLOCK represents the name of the block where the cursor was located

when the current trigger initially fired. The value is NULL if the current trigger is a Pre- or

Post-Form trigger. The value is always a character string.

Example:

Assume that you want to write a form–level procedure that navigates to the block where the

cursor was when the current trigger initially fired. The following statement performs this

function.

Go_Block(Name_In(’System.Trigger_Block’));

Block – Based Triggers [Block Processing Trigger]

When-Create-Record, When-Clear-Block, When-Database-Record, When-Remove-Record

MASTER-DETAIL RELATIONSHIP

A master-detail relationship is an association between two data blocks that reflects a primaryforeign

key relationship between the database tables on which the two data blocks are based.

The master data block is based on the table with the primary key, and the detail data block is

based on the table with the foreign key. A master-detail relationship equates to the one-tomany

relationship in the entity relationship diagram.

A Detail Block Can Be a Master

You can create block relationships in which the detail of one master-detail link is the master

for another link.

What Is a Relation?

A relation is a Form Builder object that handles the relationship between two associated

blocks.

You can create a relation either:

• Implicitly with a master-detail form module

• Explicitly in the Object Navigator

Implicit Relations

When you create a master-detail form module, a relation is automatically created. This relation

is named masterblock_detailblock, for example, S_ORD_S_ITEM.

Explicit Relations

If a relation is not established when default blocks are created, you can create your own by

setting the properties in the New Relation dialog box. Like implicitly created relations, PL/SQL

program units and triggers are created automatically when you explicitly create a relation.

Master Deletes

You can prevent, propagate, or isolate deletion of a record in a master block when

corresponding records exist in the detail block by setting the Master Deletes property.

For example, you can delete all corresponding line items when an order is deleted.

Property Use

 Non-Isolated Prevents the deletion of the master record when the detail records exist

 Cascading Deletes the detail records when a master record is deleted

 Isolated Deletes only the master record

What Happens When You Modify a Relation?

• Changing the Master Deletes property from the default of Non-Isolated to Cascading

replaces the On-Check-Delete-Master trigger with the Pre- Delete trigger.

• Changing the Master Deletes property from the default of Non-Isolated to Isolated

results in the removal of the On-Check-Delete-Master trigger.

MASTER DELETES

PROPERTY

RESULTING TRIGGERS

Non-Isolated (the

default)

On-Check-Delete-Master

On-Clear-Details

On-Populate-Details

Cascading On-Clear-Details

On-Populate-Details

Pre-Delete

Isolated On-Clear-Details

On-Populate-Details

Coordination

You can control how the detail records are displayed when a master block is queried by setting

the coordination property. For example, you can defer querying the line items for an order

until the operator navigates to the item block.

Default [Immediate]

The default setting. When a coordination-causing event occurs, the detail records are fetched

immediately. (Deferred False, Auto-Query False)

Deferred with Auto Query

Oracle Forms defers fetching the associated detail records until the operator navigates to the

detail data block.

Deferred Without Auto Query

When coordination-causing event occurs, Oracle Forms does not automatically fetch the detail

records. To fetch the detail records, the operator must navigate to the detail data block and

explicitly execute a query.

Prevent Masterless Operation

Ensures that the detail data block cannot be queried or used to insert records when a master

record is not currently displayed.

Join Condition

Use to:

• Create links between blocks using SQL

• Alter links between blocks using SQL Define using:

• Usual SQL equi-join condition syntax

• Block names instead of the base table names

• Item names that exist in the form module instead of base table column names

 Master-detail triggers

On-Check-Delete-Master, On-Populate-Details, On-Clear-Details

RECORD GROUP

This object represents an internal Form Builder data structure that has a column/row

framework similar to a database table.

Query record group

A query record group is a record group that has an associated SELECT statement. The

columns in a query record group derive their default names, data types, and lengths from the

database columns referenced in the SELECT statement. The records in a query record group

are the rows retrieved by the query associated with that record group. Query record groups

can be created and modified at design time or at runtime.

Non-query record group

A non-query record group is a group that does not have an associated query, but whose

structure and values can be modified programmatically at runtime. Non-query record groups

can be created and modified only at runtime.

Static record group

A static record group is not associated with a query; instead, you define its structure and row

values at design time, and they remain fixed at runtime. Static record groups can be created

and modified only at design time.

Record Group built-in subprograms

Creating and deleting groups:

A] CREATE_GROUP (recordgroup_name VARCHAR2, scope NUMBER, array_fetch_size

NUMBER)

Creates a non-query record group with the given name

B] CREATE_GROUP_FROM_QUERY (recordgroup_name VARCHAR2, query VARCHAR2,

scope NUMBER, array_fetch_size NUMBER);

Creates a record group with the given name. The record group has columns

representing each column you include in the select list of the query

C] DELETE_GROUP (recordgroup_name VARCHAR2);

Deletes a programmatically created record group.

Modifying a group's structure:

ADD_GROUP_COLUMN (recordgroup_name VARCHAR2, groupcolumn_name

VARCHAR2,column_type NUMBER, column_width NUMBER)

Adds a column of the specified type to the given record group.

ADD_GROUP_ROW (recordgroup_name VARCHAR2, row_number NUMBER);

Adds a row to the given record group.

DELETE_GROUP_ROW (recordgroup_id RecordGroup, row_number NUMBER)

Deletes the indicated row or all rows of the given record group. Form Builder

automatically decrements the row numbers of all rows that follow a deleted row. When

rows are deleted, the appropriate memory is freed and available to Form Builder.

Populating Groups:

POPULATE_GROUP (recordgroup_id RecordGroup);

Executes the query associated with the given record group and returns a number

indicating success or failure of the query. Upon a successful query, POPULATE_GROUP

returns a 0 (zero). An unsuccessful query generates an ORACLE error number that

corresponds to the particular SELECT statement failure. The rows that are retrieved as

a result of a successful query replace any rows that exist in the group.

POPULATE_GROUP_WITH_QUERY (recordgroup_id RecordGroup, query

VARCHAR2)

Populates a record group with the given query. The record group is cleared and rows

that are fetched replace any existing rows in the record group.

SET_GROUP_CHAR_CELL(groupcolumn_id GroupColumn,row_number

NUMBER,cell_value VARCHAR2)

Sets the value for the record group cell identified by the given row and column.

SET_GROUP_DATE_CELL (groupcolumn_id GroupColumn, row_number NUMBER,

cell_value DATE);

Sets the value for the record group cell identified by the given row and column.

SET_GROUP_NUMBER_CELL(groupcolumn_id GroupColumn, row_number

NUMBER,cell_value NUMBER);

Sets the value for the record group cell identified by the given row and column.

Getting cell values:

GET_GROUP_CHAR_CELL (groupcolumn_id GroupColumn, row_number NUMBER);

Returns the VARCHAR2 or LONG value for a record group cell identified by the given

row and column. A cell is an intersection of a row and column.

GET_GROUP_DATE_CELL (function)

GET_GROUP_NUMBER_CELL (function)

Processing rows:

GET_GROUP_ROW_COUNT (function)

GET_GROUP_SELECTION_COUNT (function)

GET_GROUP_SELECTION (function)

RESET_GROUP_SELECTION (procedure)

SET_GROUP_SELECTION (procedure)

UNSET_GROUP_SELECTION (procedure)

Object ID functions:

FUNCTION FIND_GROUP (recordgroup_name VARCHAR2);

Searches the list of record groups and returns a record group ID when it finds a valid

group with the given name. You must define an appropriately typed variable to accept

the return value. Define the variable with a type of RecordGroup.

FIND_COLUMN (function)

Example:

/* ** Built–in: CREATE_GROUP

** Example: Creates a record group and populates its values ** from a query.*/

DECLARE

rg_name VARCHAR2(40) := ’Salary_Range’;

rg_id RecordGroup;

gc_id GroupColumn;

errcode NUMBER;

BEGIN

/* ** Make sure the record group does not already exist. */

rg_id := Find_Group(rg_name);

/* ** If it does not exist, create it and add the two

** necessary columns to it. */

IF Id_Null(rg_id) THEN

rg_id := Create_Group(rg_name);

/* Add two number columns to the record group */

gc_id := Add_Group_Column(rg_id, ’Base_Sal_Range’,

NUMBER_COLUMN);

gc_id := Add_Group_Column(rg_id, ’Emps_In_Range’,

NUMBER_COLUMN);

END IF;

** Populate group with a query

errcode := Populate_Group_With_Query( rg_id,

’SELECT SAL–MOD(SAL,1000),COUNT(EMPNO) ’

||’FROM EMP ’

||’GROUP BY SAL–MOD(SAL,1000) ’

||’ORDER BY 1’);

END;

LOV [ LIST OF VALUES ]

An LOV is a scrollable popup window that provides the end user with either a single or

multi-column selection list.

Default Key for LOV – F9

LOVs provide the following functionality:

 LOVs can be displayed by end user request (when an LOV is available), when the

end user navigates to a text item with an associated LOV, or programmatically,

independent of any specific text item.

 LOV auto-reduction and search features allow end users to locate specific values.

 LOV values that are selected by the end user can be assigned to form items

according to the return items you designate.

 At design time, an LOV can be attached to one or more text items in the form.

 LOV values are derived from record groups.

LOV Built-in subprograms

1.LIST_VALUES

LIST_VALUES displays the list of values for the current item, as long as

the input focus is in a text item that has an attached LOV. The list of values

remains displayed until the operator dismisses the LOV or selects a value.

2.SHOW_LOV

Displays a list of values (LOV) window at the given coordinates, and

returns TRUE if the operator selects a value from the list, and FALSE if the

operator Cancels and dismisses the list.

SHOW_LOV( lov_id);

SHOW_LOV( lov_id, x, y);

SHOW_LOV( lov_name);

SHOW_LOV( lov_name, x, y);

Example:

/* ** Built–in: SHOW_LOV ** Example: Display a named List of Values

(LOV) */

DECLARE

a_value_chosen BOOLEAN;

BEGIN

a_value_chosen := Show_Lov(’my_employee_status_lov’);

IF NOT a_value_chosen THEN

Message(’You have not selected a value.’);

Bell;

RAISE Form_Trigger_Failure;

END IF;

LOV Properties

1. GET_LOV_PROPERTY (lov_id, property LOV);

Returns information about a specified list of values (LOV).

2. SET_LOV_PROPERTY(lov_id LOV, property NUMBER, value NUMBER);

Sets the given LOV property for the given LOV.

3.GROUP_NAME

Specifies the name of the record group on which an LOV is based.

Set_LOV_Property('my_lov',GROUP_NAME,'new_group');

4. SET_LOV_COLUMN_PROPERTY

Sets the given LOV property for the given LOV.

SET_LOV_COLUMN_PROPERTY (lov_id LOV,colnum NUMBER, property

NUMBER,value VARCHAR2);

5. Automatic Skip (LOV) property

Moves the cursor to the next navigable item when the operator makes a

selection from an LOV to a text item. When Automatic Skip is set to No, the

focus remains in the text item after the operator makes a selection from the

LOV.

6.Column Mapping

This property is used to specify the return item.>If it is not specified then value

from Lov cannot assigned to the block.

7. Long List

It is used to append a ‘ where clause ‘ to the select statement. When this

property is true a dialog box appear. In this dialog box any value can be

entered, from this value entered the where clause constructed.

8. LOV for Validation [ Yes / No ]

If it is true so that the system check value entered with the list of values

Validation from LOV – text item property - Validation from Lov to text item.

9.Title property

Specifies the title to be displayed for the object.

10.Automatic Refresh property

Determines whether Form Builder re-executes the query to populate an LOV

that is based on a query record group.

11.Automatic Display property

Specifies whether Form Builder displays the LOV automatically when the

operator or the application navigates into a text item to which the LOV is

attached.

12.Automatic Select property

Specifies what happens when an LOV has been invoked and the user reduces

the list to a single choice when using auto-reduction or searching:

When Automatic Confirm is set to Yes, the LOV is dismissed automatically and

column values from the single row are assigned to their corresponding return

items.

When Automatic Confirm is set to No, the LOV remains displayed, giving the

operator the option to explicitly select the remaining choice or dismiss the LOV.

Key-LISTVAL Trigger: List_Values;

This trigger, as written, is of little value since it merely duplicates default Form

Builder functionality. However, using a Key-LISTVAL trigger allows you to add

subsequent PL/SQL statements that execute after the LOV is displayed, but

before Form Builder returns to the normal event sequence.

In the following example, an IF statement is added to the previous trigger:

Key-LISTVAL Trigger: List_Values;

IF :customer.id IS NULL THEN

Go_Item ('customer.id');

END IF;

PARAMETER LIST

List of Parameter or list that contains parameter names and their values

Input values required for a form as startup are provided by parameters.

Types

1. Data Parameter

It is name of the record group present in the current form. It is passed between oracle

products. But not between forms.

2. Text Parameter

It is passed between forms. The value of the text parameter is a character string. It

can also passed between different oracle products.

Data type – char – 2555 char maximum

Global variables are visible across multiple forms

Parameter values are not visible across multiple forms.

Parameter – Built Ins

1. ADD_PARAMETER (list VARCHAR2, key VARCHAR2, paramtype VARCHAR2,

VARCHAR2);

Adds parameters to a parameter list. Each parameter consists of a key, its type, and an

associated value.

2.CREATE_PARAMETER_LIST (name VARCHAR2);

Creates a parameter list with the given name.

3. DELETE_PARAMETER (list VARCHAR2, key VARCHAR2)

Deletes the parameter with the given key from the parameter list.

list or name Specifies the parameter list, either by list ID or name. The actual parameter

can be either a parameter list ID of type PARAMLIST, or the VARCHAR2 name of

the parameter list.

key The name of the parameter. The data type of the key is VARCHAR2.

4.PROCEDURE DESTROY_PARAMETER_LIST (list VARCHAR2)

Deletes a dynamically created parameter list and all parameters it contains.

5. GET_PARAMETER_ATTR (list VARCHAR2, key VARCHAR2, paramtype NUMBER,value

VARCHAR2);

Returns the current value and type of an indicated parameter in an indicated parameter list.

list or name

Specifies the parameter list to which the parameter is assigned. The actual parameter

can be either a parameter list ID of type PARAMLIST, or the VARCHAR2 name of the

parameter list.

Key

The VARCHAR2 name of the parameter.

Paramtype

An OUT parameter of type NUMBER. The actual parameter you supply must be a

variable of type NUMBER, and cannot be an expression. Executing the parameter sets

the value of the variable to one of the following numeric constants:

DATA_PARAMETER Indicates that the parameter's value is the name of a record group.

TEXT_PARAMETER Indicates that the parameter's value is an actual data value.

Value

An OUT parameter of type VARCHAR2. If the parameter is a data type parameter, the

value is the name of a record group. If the parameter is a text parameter, the value is

an actual text parameter.

6.SET_PARAMETER_ATTR (list PARAMLIST, key VARCHAR2, paramtype NUMBER)

Sets the type and value of an indicated parameter in an indicated parameter list.

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.

Syntax:

RUN_PRODUCT( product, document, commmode, execmode, location,list, display);

RUN_PRODUCT( product, document, commmode, execmode, location,name, display);

product

Specifies a numeric constant for the Oracle product you want to invoke: FORMS specifies a

Runform session. GRAPHICS specifies Oracle Graphics. REPORTS specifies Oracle Reports.

BOOK specifies Oracle Book.

document

Specifies the CHAR name of the document or module to be executed by the called product.

Valid values are the name of a form module, report, Oracle Graphics display, or Oracle Book

document. The application looks for the module or document 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 Oracle Forms only after the called product

has been exited. The operator 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 Oracle Reports and

Oracle Graphics, execmode can be either BATCH or RUNTIME. When you run Oracle Forms,

always set execmode to RUNTIME.

location

Specifies the location of the document 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.

list or name

Specifies the parameter list to be passed to the called product. Valid values for this parameter

are the CHAR name of the parameter list, the ID of the parameter list, or NULL. To specify a

parameter list ID, use a variable of type PARAMLIST.

display

Specifies the CHAR name of the Oracle Forms chart item that will contain the display (such as

a pie chart, bar chart, or graph) generated by Oracle Graphics. 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 Oracle Graphics chart item in a form.)

Note: 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 Oracle Reports and

Oracle Graphics in SYNCHRONOUS mode.

(SYNCHRONOUS mode is required when invoking Oracle Graphics to return an Oracle

Graphics display that will be displayed in a form chart item.)

Note: You can prevent Oracle Graphics 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 Oracle Reports. Data passing is

supported only for master queries.

Example:

** Built–in: RUN_PRODUCT

** Example: Call an Oracle Reports 2.5 report, passing the ** data in record group

’EMP_RECS’ to substitute ** for the report’s query named ’EMP_QUERY’. ** Presumes the

Emp_Recs record group already ** exists and has the same column/data type

** structure as the report’s Emp_Query query.

PROCEDURE Run_Emp_Report IS

pl_id ParamList;

BEGIN

/* ** Check to see if the ’tmpdata’ parameter list exists. */

pl_id := Get_Parameter_List(’tmpdata’);

/* ** If it does, then delete it before we create it again in ** case it contains parameters that

are not useful for our ** purposes here. */

IF NOT Id_Null(pl_id) THEN

Destroy_Parameter_List( pl_id );

END IF;

/* ** Create the ’tmpdata’ parameter list afresh. */

pl_id := Create_Parameter_List(’tmpdata’);

/* ** Add a data parameter to this parameter list that will ** establish the relationship

between the named query ** ’EMP_QUERY’ in the report, and the record group named

** ’EMP_RECS’ in the form.

Add_Parameter(pl_id,’EMP_QUERY’,DATA_PARAMETER,’EMP_RECS’);

/* ** Run the report synchronously, passing the parameter list */

Run_Product(REPORTS, ’empreport’, SYNCHRONOUS, RUNTIME,FILEYSTEM, pl_id, NULL);

END;

Example:

/* ** Built–in: CREATE_PARAMETER_LIST

** Example: Create a parameter list named ’TEMPDATA’. First ** make sure the list does not already exist, then **

attempt to create a new list. Signal an error ** if the list already exists or if creating the ** list fails. */

DECLARE

pl_id ParamList;

pl_name VARCHAR2(10) := ’tempdata’;

BEGIN

pl_id := Get_Parameter_List(pl_name);

IF Id_Null(pl_id) THEN

pl_id := Create_Parameter_List(pl_name);

IF Id_Null(pl_id) THEN

Message(’Error creating parameter list ’||pl_name);

RAISE Form_Trigger_Failure;

END IF;

ELSE

Message(’Parameter list ’||pl_name||’ already exists!’);

RAISE Form_Trigger_Failure;

END IF;

END;

Example:

/* ** Built–in: ADD_PARAMETER

** Example: Add a value parameter to an existing Parameter ** List ’TEMPDATA’, then add a

data parameter to ** the list to associate named query ’DEPT_QUERY’ ** with record group

’DEPT_RECORDGROUP’. */

DECLARE

pl_id ParamList;

BEGIN

pl_id := Get_Parameter_List(’tempdata’);

IF NOT Id_Null(pl_id) THEN

Add_Parameter(pl_id,’number_of_copies’,TEXT_PARAMETER,’19’);

Add_Parameter(pl_id, ’dept_query’, DATA_PARAMETER,

’dept_recordgroup’);

END IF;

END;

Example:

/* ** Built–in: DELETE_PARAMETER

** Example: Remove the ’NUMBER_OF_COPIES’ parameter from the ** already existing

’TEMPDATA’ parameter list.

BEGIN

Delete_Parameter(’tempdata’,’number_of_copies’);

End;

Example:

/* ** Built–in: DESTROY_PARAMETER_LIST

** Example: Remove the parameter list ’tempdata’ after first ** checking to see if it exists */

DECLARE

pl_id ParamList;

BEGIN

pl_id := Get_Parameter_List(’tempdata’);

IF NOT Id_Null(pl_id) THEN

Destroy_Parameter_List(pl_id);

END IF;

END;

Example 2:

PROCEDURE Run_Report_For_Last_Query IS pl ParamList; wc VARCHAR2(2000);

–– The Where Clause to Pass

BEGIN

/* ** Create a parameter list for parameter passing */

pl := Create_Parameter_List(’tmp’);

/* ** Get the Where Clause from the Last Query ** using a user–defined function */

wc := Last_Where_Clause;

/* ** If there is a Non–NULL Last Where clause to ** pass, add a text parameter to the

parameter ** list to specify the parameter name and its ** value. In this case the report

definition has ** a parameter named ’the_Where_Clause’ that ** it’s expecting.

IF wc IS NOT NULL THEN

Add_Parameter(pl, –– Handle to the ParamList

’the_Where_Clause’, –– Name of Parameter in the Report

TEXT_PARAMETER, –– Type of Parameter

wc –– String Value

–– for Parameter

);

END IF;

/* ** Launch the report, passing parameters in the ** parameter list. */

Run_Product(REPORTS, –– The Product to call

’rep0058.rdf’, –– The name of the report definition

SYNCHRONOUS, –– The communications mode

BATCH, –– The Execution Mode

FILESYSTEM, –– The Location of the reports document

pl ); –– The Handle to the parameter list

/* Delete the parameter list */

Destroy_Parameter_List(pl);

END;

Referencing Form Builder items indirectly

1.NAME_IN

The NAME_IN function returns the contents of an indicated variable or item. Use the

NAME_IN function to get the value of an item without referring to the item directly.

IF :emp.ename = 'smith' -- direct reference

IF NAME_IN('emp.ename') = 'smith' -- indirect reference

2.COPY built-in

Copies a value from one item or variable into another item or global variable.

COPY(NAME_IN(source), destination);

cur_val VARCHAR2(40);

Copy( cur_val, 'Emp.Empno' );

VARIABLES

It is used to store values from form items.

1.Local Variables

The local variable is PL/SQL variable whose value is only accessible with in the trigger

or user named sub Programs

2.Global Variable

Whose value is accessible to trigger and subprograms in any modules – limit – 255

char length

3.System Variable

It is used to track of runtime status condition.

Destroy global variable - erase(‘global.a’);

System Variable

1. SYSTEM.BLOCK_STATUS

SYSTEM.BLOCK_STATUS represents the status of a Data block where the cursor is

located, or the current data block during trigger processing. The value can be one of

three character strings:

CHANGED Indicates that the block contains at least one Changed record.

NEW Indicates that the block contains only New records.

QUERY Indicates that the block contains only Valid records that have been

retrieved from the database.

2. SYSTEM.COORDINATION_OPERATION

This system variable works with its companion SYSTEM.MASTER_BLOCK to help an On-

Clear-Details trigger determine what type of coordination-causing operation fired the

trigger, and on which master block of a master/detail relation.

3.SYSTEM.CURRENT_BLOCK s

The value that the SYSTEM.CURRENT_BLOCK system variable represents depends on

the current navigation unit:

If the current navigation unit is the block, record, or item (as in the Pre- and Post-

Item, Record, and Block triggers), the value of SYSTEM.CURRENT_BLOCK is the name

of the block that Form Builder is processing or that the cursor is in.

If the current navigation unit is the form (as in the Pre- and Post-Form triggers), the

value of SYSTEM.CURRENT_BLOCK is NULL.

4. SYSTEM.CURRENT_DATETIME

SYSTEM.CURRENT_DATETIME is a variable representing the operating system date.

The value is a CHAR string in the following format:

DD-MON-YYYY HH24:MM:SS

5. SYSTEM.CURRENT_FORM

SYSTEM.CURRENT_FORM represents the name of the form that Form Builder is

executing. The value is always a character string.

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

7. SYSTEM.CURSOR_RECORD

SYSTEM.CURSOR_RECORD represents the number of the record where the cursor is

located. This number represents the record's current physical order in the block's list

of records. The value is always a character string.

8. SYSTEM.CURSOR_VALUE

SYSTEM.CURSOR_VALUE represents the value of the item where the cursor is located.

The value is always a character string.

9. SYSTEM.FORM_STATUS

SYSTEM.FORM_STATUS represents the status of the current form. The value can be

one of three character strings:

CHANGED Indicates that the form contains at least one block with a Changed

record. The value of SYSTEM.FORM_STATUS becomes CHANGED only

after at least one record in the form has been changed and the

associated navigation unit has also changed.

NEW Indicates that the form contains only New records.

QUERY Indicates that a query is open. The form contains at least one block with

QUERY records and no blocks with CHANGED records.

10. SYSTEM.LAST_QUERY

SYSTEM.LAST_QUERY represents the query SELECT statement that Form Builder most

recently used to populate a block during the current Runform session. The value is

always a character string.

11. SYSTEM.MASTER_BLOCK

This system variable works with its companion SYSTEM.COORDINATION_OPERATION to

help an On-Clear- Details trigger determine what type of coordination-causing

operation fired the trigger, and on which master block of a master/detail relation.

12. SYSTEM.MODE s

SYSTEM.MODE indicates whether the form is in Normal, Enter Query, or Fetch

Processing mode. The value is always a character string.

NORMAL Indicates that the form is currently in normal processing mode.

ENTER-QUERYIndicates that the form is currently in Enter Query mode.

QUERY Indicates that the form is currently in fetch processing mode,

meaning that a query is currently being processed.

13. SYSTEM.MOUSE_ITEM

If the mouse is in an item, SYSTEM.MOUSE_ITEM represents the name of that item as

a CHAR value. For example, if the mouse is in Item1 in Block2, the value for

SYSTEM.MOUSE_ITEM is :BLOCK2.ITEM1.

SYSTEM.MOUSE_ITEM is NULL if:

 the mouse is not in an item

 the operator presses the left mouse button, then moves the mouse

 the platform is not a GUI platform.

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

15. SYSTEM.TRIGGER_BLOCK

SYSTEM.TRIGGER_BLOCK represents the name of the block where the cursor was

located when the current trigger initially fired. The value is NULL if the current trigger

is a Pre- or Post-Form trigger. The value is always a character string.

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

PROPERTY CLASS

This object is a named object that contains a list of properties and their associated settings.

Once you create a property class you can base other objects on it. An object based on a

property class can inherit the settings of any property in the class that is appropriate for that

object.

The Property Palette is where you set the properties of objects you create in form and menu

modules.

There are 2 ways to creating property class

a. Object Navigator method.

b. Property Window method

Property class can not be change programmatically.

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

It can be changed dynamically.

Visual attribute name is connected to an object by setting visual attribute name

property

Set_item_property(‘text1’,current_record_attribute,’v1’);

Visual Attribute Types

1. Default

Setting the Visual Attribute Group property to Default specifies that that the object

should be displayed with default color, pattern, and font settings. When Visual Attribute

Group is set to Default, the individual attribute settings reflect the current system

defaults. The actual settings are determined by a combination of factors, including the

type of object, the resource file in use, and the window manager.

2. Custom

When the attribute of an objects are changed at design tome, they are custom VAT

3. Named

Setting the Visual Attribute Group property to a named visual attribute defined in the

same module specifies that the object should use the attribute settings defined for the

named visual attribute. A named visual attribute is a separate object in a form or menu

module that defines a collection of visual attribute properties. Once you create a

named visual attribute, you can apply it to any object in the same module, much like

styles in a word processing program.

EDITOR

This object enables the operator to edit text. There are three types of editor objects:

default editor, system editor, and user-named editor.

1. System Editor

The system editor to be used is defined by the FORMS60_EDITOR environment

variable. The editor specified must use the ASCII text format. For information on

environment variables and system editor availability, refer to the Form Builder

documentation for your operating system.

FORMS60_EDITOR = C:\WINDOWS\NOTEPAD.EXE

2. Default Editor

Default editor is invoked at runtime, Form Builder determines its display size and

position dynamically based on the size and position of the text item from which the

editor was invoked.

3. User-Named Editor

A user-named editor has the same text editing functionality as the default editor. You

create a user-named editor when you want to display the editor programmatically with

SHOW_EDITOR, or when you want to specify custom editor attributes such as scroll bar

and title.

SHOW_EDITOR(editor_name, message_in, x, y, message_out, result);

The SHOW_EDITOR procedure displays a user-named editor at the specified display

coordinates. SHOW_EDITOR takes message_in and message_out parameters that

allow you to pass a text string in to the editor and to get the edited text string back

when the operator accepts the editor.

Edit_Textitem(x, y, width, height);

The EDIT_TEXTITEM procedure invokes the editor associated with the current text item

in the form (the default editor, a user-named editor, or the current system editor).

Example:

** Built–in: SHOW_EDITOR

** Example: Accept input from the operator in a user–defined ** editor. Use the

system editor if the user has ** checked the ”System_Editor” menu item under the

** ”Preferences” menu in our custom menu module. */

DECLARE

ed_id Editor;

mi_id MenuItem;

ed_name VARCHAR2(40);

val VARCHAR2(32000);

ed_ok BOOLEAN;

BEGIN

mi_id := Find_Menu_Item(’PREFERENCES.SYSTEM_EDITOR’);

IF Get_Menu_Item_Property(mi_id,CHECKED) = ’TRUE’ THEN

ed_name := ’system_editor’;

ELSE

ed_name := ’my_editor1’;

END IF;

ed_id := Find_Editor( ed_name );

/* ** Show the appropriate editor at position (10,14) on the ** screen. Pass the

contents of the :emp.comments item ** into the editor and reassign the edited

contents if ** ’ed_ok’ returns boolean TRUE. */

val := :emp.comments;

Show_Editor( ed_id, val, 10,14, val, ed_ok);

IF ed_ok THEN

:emp.comments := val;

END IF;

END;

Example:

/* ** Built–in: EDIT_TEXTITEM

** Example: Determine the x–position of the current item ** then bring up the editor

either on the left ** side or right side of the screen so as to not ** cover the item on

the screen. */

DECLARE

itm_x_pos NUMBER;

BEGIN

itm_x_pos := Get_Item_Property(:System.Cursor_Item,X_POS);

IF itm_x_pos > 40 THEN

Edit_TextItem(1,1,20,8);

ELSE

Edit_TextItem(60,1,20,8);

END IF;

END;

CANVAS

This object represents a background entity on which you place interface items, such as check

boxes, radio groups, and text items. There are four types of canvas objects: Content,

Stacked, Horizontal Toolbar, and Vertical Toolbar.

1.Content Canvas

The most common canvas type is the content canvas (the default type). A content

canvas is the "base" view that occupies the entire content pane of the window in which

it is displayed. You must define at least one content canvas for each window you

create.

2.Stacked Canvas

A stacked canvas is displayed atop—or stacked on—the content canvas assigned to the

current window. Stacked canvases obscure some part of the underlying content

canvas, and often are shown and hidden programmatically. You can display more than

one stacked canvas in a window at the same time.

3.Tab Canvas

A tab canvas—made up of one or more tab pages —allows you to group and display a

large amount of related information on a single dynamic Form Builder canvas object.

Like stacked canvases, tab canvases are displayed on top of a content canvas, partly

obscuring it. Tab pages (that collectively comprise the tab canvas) each display a

subset of the information displayed on the entire tab canvas.

4.Toolbar Canvas

A toolbar canvas often is used to create toolbars for individual windows. You can create

two types of toolbar canvases: horizontal or vertical. Horizontal toolbar canvases are

displayed at the top of a window, just under its menu bar, while vertical toolbars are

displayed along the far left edge of a window.

Showing and hiding a canvas programmatically

SHOW_VIEW('a_stack'); or SET_VIEW_PROPERTY('a_stack', visible, property_true);

HIDE_VIEW('a_stack'); or SET_VIEW_PROPERTY('a_stack', visible, property_false);

WINDOW

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.

There are two window styles:

Document

Document Windows Document windows typically display the main canvases and work

areas of your application where most data entry, and data retrieval is performed.

Dialog

Dialog Windows Dialog windows are free-floating, containers typically used for modal

dialogs that require immediate user interaction.

Window Modality

1.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-ontop"

windows that cannot be layered behind modeless windows.

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

Hide on Exit property

For a modeless window, determines whether Form Builder hides the window

automatically when the end user navigates to an item in another window.

MDI and SDI windows

1. 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.Document

windows always are displayed within the MDI application window frame.

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

REPLACE_CONTENT_VIEW built-in

Replaces the content canvas currently displayed in the indicated window with a different

content canvas.

REPLACE_CONTENT_VIEW (window_name VARCHAR2, view_name VARCHAR2);

** Built-in: REPLACE_CONTENT_VIEW

** Example: Replace the 'salary' view with the 'history'

** view in the 'employee_status' window. */

BEGIN

Replace_Content_View('employee_status','history');

END;

Trigger - Windows

When-Window-Activated , When-Window-Deactivated , When-Window-Closed , When-

Window-Resized

ALERT

An alert is a modal window that displays a message notifying the operator of some application

condition.

Use alerts to advise operators of unusual situations or to warn operators who are about to

perform an action that might have undesirable or unexpected consequences.

There are three styles of alerts: Stop, Caution, and Note. Each style denotes a different level

of message severity. Message severity is represented visually by a unique icon that displays in

the alert window.

FIND_ALERT (alert_name VARCHAR2);

Searches the list of valid alerts in Form Builder. When the given alert is located, the

subprogram returns an alert ID. You must return the ID to an appropriately typed

variable. Define the variable with a type of Alert.

ID_NULL (Alert BOOLEAN);

Returns a BOOLEAN value that indicates whether the object ID is available.

SET_ALERT_BUTTON_PROPERTY(alert_id ALERT,button NUMBER,property VARCHAR2,

value VARCHAR2);

Changes the label on one of the buttons in an alert.

SET_ALERT_PROPERTY (alert_id ALERT, property NUMBER, message

VARCHAR2);

Changes the message text for an existing alert.

SHOW_ALERT (alert_id Alert);

Displays the given alert, and returns a numeric value when the operator selects one of

three alert buttons.

** Built-in: SET_ALERT_PROPERTY

** Example: Places the error message into a user-defined alert ** named

'My_Error_Alert' and displays the alert.

** Trigger: On-Error

DECLARE

err_txt VARCHAR2(80) := Error_Text;

al_id Alert;

al_button Number;

BEGIN

al_id := Find_Alert('My_Error_Alert');

Set_Alert_Property(al_id, alert_message_text, err_txt );

al_button := Show_Alert( al_id );

END;

OBJECT GROUPS

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.

You define an object group when you want to package related objects for copying or sub

classing in another module. You can use object groups to bundle numerous objects into

higher-level building blocks that you can use again in another application.

Using Object Groups

• Blocks include:

Items

Item-level triggers

Block-level triggers

Relations

• Object groups cannot include other object groups

• Deleting:

An object group does not affect the objects

An object affects the object group

Copying an Object

Copying an object creates a separate, unique version of that object in the target module. Any

objects owned by the copied object are also copied.

Use copying to export the definition of an object to another module.

• Changes made to a copied object in the source module do not affect the copied object

in the target module.

Subclassing

Subclassing is an object-oriented term that refers to the following capabilities:

• Inheriting the characteristics of a base class (Inheritance)

• Overriding properties of the base class (Specialization)

OBJECT LIBRARY

This object 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.

In addition, by using Object Libraries, you can rapidly create applications by dragging and

dropping predefined objects to your form.

• Is a convenient container of objects for reuse

• Simplifies reuse in complex environments

• Supports corporate, project, and personal standards

• Simplifies the sharing of reusable components

Object libraries are convenient containers of objects for reuse. They simplify reuse in complex

environments, and they support corporate, project, and personal standards.

An object library can contain simple objects, property classes, object groups, and program

units, but they are protected against change in the library. Objects can be used as standards

(classes) for other objects.

Object libraries simplify the sharing of reusable components. Reusing components enables you to:

• Apply standards to simple objects, such as buttons and items, for a consistent look and feel

• Reuse complex objects such as a Navigator

Benefits of the Object Library

• Simplifies the sharing and reuse of objects

• Provides control and enforcement of standards

• Eliminates the need to maintain multiple referenced forms

SMARTCLASS

A SmartClass is a special member of an Object Library. Unlike other Object Library members,

it can be used to subclass existing objects in a form using the SmartClass option from the

right mouse button popup menu. Object Library members which are not SmartClasses can

only be used to create new objects in form modules into which they are added.

If you frequently use certain objects as standards, such as standard buttons, date items, and

alerts, you can mark them as SmartClasses by selecting each object in the object library and

choosing Object—>SmartClass.

You can mark many different objects that are spread across multiple object libraries as

SmartClasses.

• Is an object in an object library that is frequently used as a class

• Can be applied easily and rapidly to existing objects

• Can be defined in many object libraries

You can have many SmartClasses of a given object

PL/SQL Libraries

A library is a collection of PL/SQL program units, including procedures, functions, and

packages. A single library can contain many program units that can be shared among the

Oracle Developer modules and applications that need to use them.

A library:

 Is produced as a separate module and stored in either a file or the database

 Provides a convenient means of storing client-side code and sharing it among

applications

 Means that a single copy of program units can be used by many form,menu, report, or

graphic modules

 Supports dynamic loading of program units

FUNCTION locate_emp(bind_value IN NUMBER) RETURN VARCHAR2 IS

v_ename VARCHAR2(15);

BEGIN

SELECT ename INTO v_ename FROM emp WHERE empno = bind_value;

RETURN(v_ename);

END;

Reasons to share objects and code:

 Increased productivity

 Increased modularity

 Decreased maintenance

 Maintaining standards

.PLL PL/SQL Library Module Binary

.PLD PL/SQL Library Module Text

.PLX PL/SQL Library Module Executable

.MMB Menu Module Binary

.MMT Menu Module Text

.MMX Menu Module Executable

Form Builder Built-in Package

EXEC_SQL Provides built-ins for executing dynamic SQL within PL/SQL procedures

VBX

Provides built-ins for controlling and interacting with VBX controls; this package works only in

a 16-bit environment and is provided for backward compatibility

WEB

Provides built-ins for the Web environment

OLE2

Provides a PL/SQL API for creating, manipulating, and accessing attributes of OLE2 automation

objects

SUBPROGRAM

A subprogram can be either a procedure or a function. Built-in subprograms are therefore

called in two distinct ways:

• Built-in procedures:

Called as a complete statement in a trigger or program unit with mandatory arguments.

• Built-in functions:

Called as part of a statement, in a trigger or program unit, at a position where the function’s

return value will be used. Again, the function call must include any mandatory arguments.

TRIGGER

Triggers are blocks of PL/SQL code that are written to perform tasks when a specific event

occurs within an application. In effect, a Form Builder trigger is an event-handler written in

PL/SQL to augment (or occasionally replace) the default processing behavior. Every trigger

has a name, and contains one or more PL/SQL statements. A trigger encapsulates PL/SQL

code so that it can be associated with an event and executed and maintained as a distinct

object.

Trigger Scope

1.Form Level

The trigger belongs to the form and can fire due to events across the entire form.

2.Block Level

The trigger belongs to a block and can only fire when this block is the current block.

3.Item Level

The trigger belongs to an individual item and can only fore when this item is the current item.

Trigger Properties

Execution Style

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.

What are triggers used for?

 Validate data entry

 Protect the database from operator errors

 Limit operator access to specified forms

 Display related field data by performing table lookups

 Compare values between fields in the form

 Calculate field values and display the results of those calculations

 Perform complex transactions, such as verifying totals

 Display customized error and information messages to the operator

 Alter default navigation

 Display alert boxes

 Create, initialize, and increment timers

Groups of triggers

GROUP FUNCTION

When-triggers Execute in addition to default processing

On-triggers Replace default processing

Pre- and Post-triggers Add processing before or after an event

Key-trigger Change default processing assigned to a specific key

Trigger Categories

 Block-processing triggers

o When-Create-Record

o When-Clear-Block

o When-Database-Record

o When-Remove-Record

 Interface event triggers

o When-Button-Pressed

o When-Checkbox-Changed

o When-Image-Activated

o When-Image-Pressed

o When-Radio-Changed

o When-Timer-Expired

o When –List-Changed

o When –List-Activated

o When –Tree-Note-Activated

o When –Tree-Note-Expanded

o When –Tree-Note-Selected

o Key- [all]

o When-Window-Activated

o When-Window-Closed

o When-Window-Deactivated

o When-Window-Resized

 Master-detail triggers

o On-Check-Delete-Master

o On-Clear-Details

o On-Populate-Details

 Message-handling triggers

o On-Error

o On-Message

 Navigational triggers

o Pre- and Post- Triggers

 Pre-Form

 Pre-Block

 Pre-Record

 Pre-Text-Item

 Post-Text-Item

 Post-Record

 Post-Block

 Post-Form

o When-New-Instance-Triggers

 When-New-Form-Instance

 When-New-Block-Instance

 When-New-Record-Instance

 When-New-Item-Instance

 Query-time triggers

o Pre-Query

o Post-Query

 Transactional triggers.

o On-Count

o On-Delete.

o On-Insert.

o On-Lock.

o On-Logon.

o On-Logout.

o On-Select.

o On-Update.

o Post-Database-Commit.

o Post-Delete.

o Post-Forms-Commit.

o Post-Insert.

o Post – Select.

o Post-Update.

o Pre-Commit.

o Pre-Delete.

o Pre-Insert.

o Pre-Select.

o Pre-Update.

 Validation triggers

o When-Validate-Item

o When-Validate-Record

 Mouse Event Triggers

o When-Custom-Item-Event

o When-Mouse-Click

o When-Mouse-Double Click

o When-Mouse-Down

o When-Mouse-Enter

o When-Mouse-Leave

o When-Mouse-Move

o When-Mouse-Up

 Key-Fn Trigger

o A Key-Fn trigger fires when an operator presses the associated key.

o Use Key-Fn triggers to create additional function keys for custom functions.

 Calling user-named triggers

TRIGGER CATEGORIES

 A. BLOCK-PROCESSING TRIGGERS

Block processing triggers fire in response to events related to record management in a block.

1. When-Create-Record

Fires when Form Builder creates a new record. For example, when the operator presses the [Insert] key,

or navigates to the last record in a set while scrolling down, Form Builder fires this trigger.

Used For

· Perform an action whenever Form Builder attempts to create a new record in a block. For

example, to set complex, calculated, or data-driven default values that must be specified at

runtime, rather than design time.

Fires In

· CREATE_RECORD

WHEN-CREATE-RECORD TRIGGER

This example assigns data-driven or calculated default values without marking the record as changed.

DECLARE

CURSOR ship_dflt IS SELECT val FROM cust_pref WHERE Custid = :Customer.Custid AND pref = 'SHIP';

BEGIN

/* ** Default Invoice Due Date based on Customer's ** Net Days Allowed value from the Customer block. */

:Invoice.Due_Date := SYSDATE + :Customer.Net_Days_Allowed;

/* ** Default the shipping method based on this customers ** preference, stored in a preference table. We could

** use SELECT...INTO, but explicit cursor is more ** efficient. */

OPEN ship_dflt;

FETCH ship_dflt INTO :Invoice.Ship_Method;

CLOSE ship_dflt;

END;

2. When-Clear-Block

Perform an action whenever Form Builder flushes the current block; that is, removes all records from the

block.

The When-Clear-Block trigger does not fire when Form Builder clears the current block during the

CLEAR_FORM event.

Used For

· Use a When-Clear-Block trigger to perform an action every time Form Builder flushes the current

block. For example, you might want to perform an automatic commit whenever this condition

occurs.

· In a When-Clear-Block trigger, the value of SYSTEM.RECORD_STATUS is unreliable because there

is no current record. An alternative is to use GET_RECORD_PROPERTY to obtain the record

status. Because GET_RECORD_PROPERTY requires reference to a specific record, its value is

always accurate.

Fires In

· CLEAR_BLOCK

· COUNT_QUERY

· ENTER_QUERY

Clear_Block(No_Validate);

3. When-Database-Record

Fires when Form Builder first marks a record as an insert or an update. That is the trigger fires as soon

as Form Builder determines through validation that the record should be processed by the next post or

commit as an insert or update. This generally occurs only when the operator modifies the first item in a

record, and after the operator attempts to navigate out of the item.

Used For

· Perform an action whenever Form Builder changes a record's status to Insert or Update, thus

indicating that the record should be processed by the next COMMIT_FORM operation

· Use a When-Database-Record trigger to perform an action every time a record is first marked

as an insert or an update.

4. When-Remove-Record

Fires whenever the operator or the application clears or deletes a record.

Perform an action whenever a record is cleared or deleted. For example, to adjust a running total that is

being calculated for all of the records displayed in a block.

Fires In

· CLEAR_RECORD

· DELETE_RECORD

B.INTERFACE EVENT TRIGGERS

Interface event triggers fire in response to events that occur in the form interface. Some of these

trigger, such as When-Button-Pressed, fire only in response to operator input or manipulation. Others,

like When-Window-Activated, can fire in response to both operator input and programmatic control.

1. When-Button-Pressed

Fires when an operator selects a button, by clicking with a mouse, or using the keyboard.

Usage Notes

· Use a When-Button-Pressed trigger to perform navigation, to calculate text item values, or for

other item, block, or form level functionality.

This example executes a COMMIT_FORM if there are changes in the form.

BEGIN

IF :System.Form_Status = 'CHANGED' THEN

Commit_Form;

/* If the Form_Status is not back to 'QUERY' ** following a commit, then the

commit was not successful. */

IF :System.Form_Status <> 'QUERY' THEN

Message('Unable to commit order to database...');

RAISE Form_Trigger_Failure;

END IF;

END;

2. When-Checkbox-Changed

Fires when an operator changes the state of a check box, either by clicking with the mouse, or using the

keyboard.

Initiate an action when the operator toggles the state of a check box, either with the mouse or

through keyboard selection

Usage Notes

· Use a When-Checkbox-Changed trigger to initiate a task dependent upon the state of a check box.

· When an operator clicks in a check box, the internal value of that item does not change until

navigation is completed successfully. Thus, the When-Checkbox-Changed trigger is the first

trigger to register the changed value of a check box item. So for all navigation triggers that fire

before the When-Checkbox-Changed trigger, the value of the check box item remains as it was

before the operator navigated to it.

When-Checkbox-Changed Trigger examples

This trigger on the :S_ord.order_filled item prevents the date_shipped item from being updated

if the user marks the order as filled ( checked on ).If the check box is set off, then the Date_Shipped

item is enabled.

Begin

If checkbox_checked(‘s_ord.filled’) then

Set_Item_Property(‘s_ord.date_shipped’,Update_allowed’,property_false);

Else

Set_Item_Property(‘s_ord.date_shipped’,Update_allowed’,property_true);

End if;

End;

3. When-Image-Activated

Initiate an action whenever the operator double-clicks an image item.

Fires when an operator uses the mouse to:

· Single-click on an image item.

 Double-click on an image item.

Note : That When-Image-Pressed also fires on a double-click.

4. When-Image-Pressed

Initiate an action whenever an operator clicks on an image item.

Fires when an operator uses the mouse to:

· Single-click on an image item

· Double-click on an image item

Note : That When-Image-Activated also fires on a double-click.

Usage Notes

· Use a When-Image-Pressed trigger to perform an action when an operator clicks or double clicks

on an image item.

Begin

READ_IMAGE_FILE(‘ST_’||TO_CHAR(:STMAST.STID)||’.JPG’,’JPG’,’STMAST:STIMAGE’);

End;

The above When_Image_Pressed trigger on the stimage item displays a image of the current

student (in the stmast block) when the user clicks the image item.

5. When-List-Activated Trigger

Fires when an operator double-clicks on an element in a list item that is displayed as a T-list.

Usage Notes

· A When-List-Activated trigger fires only for T-list style list items, not for dropdown

lists or combo box style list items. The display style of a list item is

determined by the List Style property.

6. When-List-Changed Trigger

Description

Fires when an end user selects a different element in a list item or de-selects the currently selected

element. In addition, if a When-List-Changed trigger is attached to a combo box style list item, it fires

each time the end user enters or modifies entered text.

Usage Notes

· Use a When-List-Changed trigger to initiate an action when the value of the list is changed directly

by the end user. The When-List-Changed trigger is not fired if the value of the list is changed

programmatically such as by using the DUPLICATE_ITEM built-in, or if the end user causes a

procedure to be invoked which changes the value. For example, the When-List- Changed trigger

will not fire if an end user duplicates the item using a key mapped to the DUPLICATE_ITEM builtin.

Begin

Select stname into :stname from the stmast where stid=:stid;

Exception

When no_data_found then

Message(‘Invalid Student ‘);

End;

Populate student name based on the selected student id.

7. When-Radio-Changed

Description

A fire when an operator selects a different radio button in a radio group, or de-selects the currently

selected radio button, either by clicking with the mouse, or using the keyboard.

Initiate an action when an operator changes the current radio button selected in a radio group

item.

Usage Notes

· Use a When-Radio-Changed trigger to perform an action depending on the state of a radio group.

(De-selecting a radio button in a radio group sets the radio group value to NULL; operators use

this technique in Enter Query mode to exclude a radio group from a query.)

· When an operator clicks an item in a radio group, the internal value of that item does not change

until navigation is completed successfully. Thus, the When-Radio-Changed trigger is the first

trigger to register the changed value of a radio group. For all navigation triggers that fire before

the When-Radio-Changed trigger, the value of the radio group remains as it was before the

operator navigated to it.

When-Radio-Changed Trigger examples

When the user selects credit as the payment type for an order, this trigger immediately confirms

whether the customer has a good or excellent credit rating. If not, then the payment type is set to cash.

Declare

v_credit customer.credit_rate%type;

Begin

If :s_ord.payment_type=’CREDIT’ then

Select credit_rate into v_credit from customer where custid=:s_ord.custid;

If v_credit NOT_IN(‘GOOD’,’EXCELLENT’) then

:s_ord.payment_type:=’CASH’;

message(‘Warning – Customer must pay cash‘);

End if;

End;

8. When-Timer-Expired

Description

Fires when a timer expires.

Initiate an action when a programmatic timer expires.

Usage Notes

Timers are created programmatically by calling the CREATE_TIMER built-in procedure.

· The When-Timer-Expired trigger can not fire during trigger, navigation, or transaction processing.

· Use a When-Timer-Expired trigger to initiate an event, update item values, or perform any task

that should occur after a specified interval.

· You can call GET_APPLICATION_PROPERTY(TIMER_NAME) in a When-Timer-Expired trigger to

determine the name of the most recently expired timer.

Fires In

· Process Expired Timer

When-Timer-Expired Trigger examples

Example

The following example displays a message box each time a repeating timer expires. The following example

is from a telemarketing application, in which sales calls are timed, and message boxes are displayed to

prompt the salesperson through each stage of the call. The message box is displayed each time a repeating

timer expires.

DECLARE

timer_id TIMER;

alert_id ALERT;

call_status NUMBER;

msg_1 VARCHAR2(80) := 'Wrap up the first phase of your presentation';

msg_2 VARCHAR2(80) := 'Move into your close.';

msg_3 VARCHAR2(80) := 'Ask for the order or repeat the close.'

two_minutes NUMBER(6) := (120 * 1000);

one_and_half NUMBER(5) := (90 * 1000);

BEGIN

:GLOBAL.timer_count := 1

BEGIN

timer_id := FIND_TIMER('tele_timer');

alert_id := FIND_ALERT('tele_alert');

IF :GLOBAL.timer_count = 1 THEN

Set_Alert_Property(alert_id, ALERT_MESSAGE_TEXT, msg_1);

call_status := Show_Alert(alert_id);

IF call_status = ALERT_BUTTON1 THEN

Delete_Timer(timer_id);

Next_Record;

ELSIF call_status = ALERT_BUTTON2 THEN

:GLOBAL.timer_count := 0;

ELSE

Set_Timer(timer_id, two_minutes, NO_CHANGE);

END IF;

ELSIF :GLOBAL.timer_count = 2 THEN

Change_Alert_Message(alert_id, msg_2);

call_status := Show_Alert(alert_id);

IF call_status = ALERT_BUTTON1 THEN

Delete_Timer(timer_id);

Next_Record;

ELSIF

call_status = ALERT_BUTTON2 THEN

:GLOBAL.timer_count := 0;

ELSE

Set_Timer(timer_id, one_and_half, NO_CHANGE);

END IF;

ELSE

Change_Alert_Message(alert_id, msg_3);

call_status := Show_Alert(alert_id);

IF call_status = ALERT_BUTTON1 THEN

Delete_Timer(timer_id);

Next_Record;

ELSIF call_status = ALERT_BUTTON2 THEN

:GLOBAL.timer_count := 0;

ELSE

Set_Timer(timer_id, NO_CHANGE, NO_REPEAT);

END IF;

:GLOBAL.timer_count = 2;

END;

9. When-Window-Activated

Initiate an action whenever an operator or the application activates a window.

Fires when a window is made the active window. This occurs at form startup and whenever a different

window is given focus.

Note that on some window managers, a window can be activated by, say, clicking on its title bar. This

operation is independent of navigation to an item in the window. Thus, navigating to an item in a

different window always activates that window, but window activation can also occur independently of

navigation.

Usage Notes:

Use this trigger to perform the following types of tasks:

· Capture initial settings of window properties, by way of the GET_WINDOW_PROPERTY

built–in.

· Enforce navigation to a particular item whenever a window is activated.

· Keep track of the most recently fired window trigger by assigning the value from

SYSTEM.EVENT_WINDOW to a variable or global variable.

Example:

Begin

GET_WINDOW_PROPERTY( window_name, property);

End;

10 .When-Window-Closed

Initiate an action whenever an operator closes a window with the window manager's Close

command.

Usage Notes:

· Use this trigger to programmatically close a window when the operator issues the

window–manager Close command.

· You can close a window with the HIDE_WINDOW , SET_WINDOW_PROPERTY, and

EXIT_FORM built–in subprograms.

· You can hide the window that contains the current item.

Example:

The following example of a call to SET_WINDOW_PROPERTY from this trigger closes a

window whenever the operator closes it by way of the window manager operation:

Set_Window_Property(’window_name’, VISIBLE, PROPERTY_OFF);

11. When-Window-Deactivated

Initiate an action whenever a window is deactivated as a result of another window becoming

the active window.

Fires when an operator deactivates a window by setting the input focus to another window.

Usage Notes:

Use this trigger to audit the state of a window whenever the operator deactivates the window

by setting the input focus in another window.

12. When-Window-Resized

Initiate an action whenever a window is resized, either by the operator or programmatically.

Fires when a window is resized, either by the operator or programmatically through a call to

RESIZE_WINDOW or SET_WINDOW_PROPERTY. (Even if the window is not currently

displayed, resizing the window programmatically fires the When–Window–Resized trigger.) This

trigger also fires at form startup, when the root window is first drawn. It does not fire when a

window is iconified.

Usage Notes:

Use this trigger to perform any one of the following types of tasks:

· Capture the changed window properties, such as width, height, x coordinate, or y

coordinate.

· Audit the actions of an operator.

· Set input focus in an item on the target window.

· Maintain certain visual standards by resetting window size if the window was

improperly resized.

Example:

/* ** Built–in: RESIZE_WINDOW

** Example: Set Window2 to be the same size as Window1 */

PROCEDURE Make_Same_Size_Win( Window1 VARCHAR2, Window2 VARCHAR2)

wn_id1 Window;

w NUMBER;

h NUMBER;

BEGIN

/* ** Find Window1 and get it’s width and height. */

wn_id1 := Find_Window(Window1);

w := Get_Window_Property(wn_id1,WIDTH);

h := Get_Window_Property(wn_id1,HEIGHT);

/* ** Resize Window2 to the same size */

Resize_Window( Window2, w, h );

END;

C. KEY- [ALL] ( KEY TRIGGERS )

Key Triggers have a one-to-one relationship with specific keys.

i.e, the trigger fires when operator presses a specific key or key-sequence.

Replace the default function associated with a function key. For example, you can

define a Key-EXIT trigger to replace the default functionality of the [Help] key.

Key Triggers and Function Keys

KEY TRIGGER ASSOCIATED FUNCTION KEY

Key–CLRBLK [Clear Block]

Key–CLRFRM [Clear Form]

Key–CLRREC [Clear Record]

Key–COMMIT [Accept]

Key–CQUERY [Count Query Hits]

Key–CREREC [Insert Record]

Key–DELREC [Delete Record]

Key–DOWN [Down]

Key–DUP–ITEM [Duplicate Item]

Key–DUPREC [Duplicate Record]

Key–EDIT [Edit]

Key–ENTQRY [Enter Query]

Key–EXEQRY [Execute Query]

Key–EXIT [Exit]

Key–HELP [Help]

Key–LISTVAL [List of Values]

Key–MENU [Block Menu]

Key–NXTBLK [Next Block]

Key–NXT–ITEM [Next Item]

Key–NXTKEY [Next Primary Key]

Key–NXTREC [Next Record]

Key–NXTSET [Next Set of Records]

Key–PRINT [Print]

Key–PRVBLK [Previous Block]

Key–PRV–ITEM [Previous Item]

Key–PRVREC [Previous Record]

Key–SCRDOWN [Scroll Down]

Key–SCRUP [Scroll Up]

Key–UP [Up]

Key–UPDREC Equivalent to Record, Lock command on the default menu

1.Key–Fn Trigger

A Key–Fn trigger fires when an operator presses the associated key.

You can attach Key–Fn triggers to 10 keys or key sequences that normally do not perform any

Oracle Forms operations. These keys are referred to as Key–F0 through Key–F9. Before you can

attach key triggers to these keys, you or the DBA must use Oracle Terminal to map the keys to

the appropriate functions.

Usage Notes:

Use Key–Fn triggers to create additional function keys for custom functions.

2. Key–Others Trigger

A Key–Others trigger fires when an operator presses the associated key.

A Key–Others trigger is associated with all keys that can have key triggers associated with them but are

not currently defined by function key triggers (at any level).

A Key–Others trigger overrides the default behavior of a Runform function key (unless one of the

following restrictions apply). When this occurs, however, Oracle Forms still displays the function key’s

default entry in the Show Keys screen.

Usage Notes:

Use Key–Others triggers to limit an operator’s possible actions.

Specifically, use Key–Others triggers to perform the following tasks:

· Disable all keys that are not relevant in a particular situation.

· Perform one specific action whenever an operator presses any key.

KEY-COMMIT TRIGGER AT FORM LEVEL: EMPLOYEES FORM

BEGIN

IF GET_APPLICATION_PROPERTY( CALLING_FORM ) IS NULL THEN

COMMIT_FORM;

ELSE

POST;

END IF;

END;

KEY-DELREC TRIGGER ON S_CUSTOMER BLOCK

DELETE_RECORD;

KEY-EXIT AT FORM LEVEL

SET_ALERT_PROPERTY

(’question_alert’, ALERT_MESSAGE_TEXT, ’Do you really want to leave the form?’);

IF SHOW_ALERT (’question_alert’) = ALERT_BUTTON1 THEN

EXIT_FORM;

END IF;

D.MASTER-DETAIL TRIGGERS

Form Builder generates master/detail triggers automatically when a master/detail relation is defined between blocks.

The default master/detail triggers enforce coordination between records in a detail block and the master record in a

master block.

1. On-Check-Delete-Master

Fires when Form Builder attempts to delete a record in a block that is a master block in a master/detail relation.

DECLARE

the_sum NUMBER;

BEGIN

SELECT SUM(dollar_amt) INTO the_sum FROM po_distribution WHERE po_number = :purchase_order.number;

IF the_sum <> :purchase_order.total THEN

Message(’PO Distributions do not reconcile.’);

RAISE Form_Trigger_Failure;

END IF;

END;

2. On-Clear-Details

Fires when Form Builder needs to clear records in a block that is a detail block in a master/detail relation because those

records no longer correspond to the current record in the master block.

Usage Notes:

Oracle Forms creates the On–Clear–Details trigger automatically when you define a master–

detail block relation.

3. On-Populate-Details

Fires when Form Builder needs to fetch records into a block that is the detail block in a master/detail relation so that

detail records are synchronized with the current record in the master block.

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.

• 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, Oracle Forms marks the blocks as

needing to be coordinated.

• If you intend to manage block coordination yourself, you can call the

SET_BLOCK_PROPERTY(COORDINATION_STATUS) built–in.

E.MESSAGE-HANDLING TRIGGERS

Form Builder automatically issues appropriate error and informational messages in response to runtime

events. Message handling triggers fire in response to these default messaging events.

1. On-Error

Replace a default error message with a custom error message, or to trap and recover from an error.

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 Oracle

Forms 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;

2. On-Message

To trap and respond to a message; for example, to replace a default message issued by Form Builder

with a custom message.

Usage Notes:

Use an On–Message trigger for the following purposes:

· To trap and respond to an informative message

· To replace a standard informative message with a custom message

· To exclude an inappropriate message

Example:

The following example responds to an error message by displaying an alert that gives the user

a message and gives the user the choice to continue or to stop:

DECLARE

alert_button NUMBER;

lv_errtype VARCHAR2(3) := MESSAGE_TYPE;

lv_errcod NUMBER := MESSAGE_CODE;

lv_errtxt VARCHAR2(80) := MESSAGE_TEXT;

BEGIN

IF lv_errcod = 40350 THEN

alert_button := Show_Alert(’continue_alert’);

IF alert_button = ALERT_BUTTON1 THEN

...

ELSE

...

END IF;

ELSE

Message(lv_errtyp||’–’||to_char(lv_errcod)||’: ’||lv_errtxt);

RAISE Form_Trigger_Failure;

END IF;

END;

F.QUERY-TIME TRIGGERS

Query-time triggers fire just before and just after the operator or the application executes a query in a block.

1. Pre-Query

Validate the current query criteria or provide additional query criteria programmatically, just before

sending the SELECT statement to the database.

This Pre-Query trigger on the S_ORD block only permits queries if there is a restriction on either the

Order ID, Date Ordered, or Date Shipped. This prevents attempts at very large queries.

• To test the operator’s query conditions, and to fail the query process if the conditions are not

satisfactory for the application

• To add criteria for the query by assigning values to base table items

· A Pre-Query trigger fires before a query executes. Use it to check or modify query conditions.

Make sure the user has given one of the two Columns which we have indexed in their search criteria,

otherwise fail the query with a helpful message

A] IF :Employee.Ename IS NULL AND :Employee.Mgr IS NULL THEN

Message(’Supply Employee Name and/or Manager Id ’||’for Query.’);

RAISE Form_Trigger_Failure;

END IF;

B] [ exact_match – Check Box

User can specify if or not a query condition for a customer name should exactly match the

table value. [ Set the initial value property to “Y”. ]

IF nvl(:control.exact_match,’Y’)=’N’ then

:S_Customer.name:=’%’||:S_customer.name || ‘%’;

END IF;

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

This trigger is defined at block level or above. Post-Query fires for each record that is fetched into the

block as a result of a query. Note that the trigger only fires on the initial fetch of a record not when a

record is subsequently scrolled back into view a second or third time.

Use Post-Query as follows:

· To populate non database items as records are returned from a query

· To calculate statistics

· A Post-Query trigger fires as each record is fetched (except array processing). Use it to

perform calculations and populate additional items.

This Post-Query trigger on the S_ORD block selects the total count of line items for the current Order,

and displays this number as a summary value in the non base table item :Lineitem_count.

Begin

SELECT COUNT(ord_id) INTO :S_ORD.lineitem_count FROM S_ITEM WHERE ord_id = :S_ORD.id;

End;

Example:

This example retrieves descriptions for code fields, for display in non–database items in the current block.

DECLARE

CURSOR lookup_payplan IS SELECT Payplan_Desc FROM Payplan WHERE Payplan_Id =

:Employee.Payplan_Id;

CURSOR lookup_area IS SELECT Area_Name FROM Zip_Code WHERE Zip = :Employee.Zip;

BEGIN

/* Lookup the Payment Plan Description given the Payplan_Id in the Employee Record just fetched.

** Use Explicit Cursor for highest efficiency.*/

OPEN lookup_payplan;

FETCH lookup_payplan INTO :Employee.Payplan_Desc_Nondb;

CLOSE lookup_payplan;

/* ** Lookup Area Descript given the Zipcode in ** the Employee Record just fetched. Use Explicit

** Cursor for highest efficiency. */

OPEN lookup_area;

FETCH lookup_area INTO :Employee.Area_Desc_Nondb;

CLOSE lookup_area;

END;

A query fetched 10 records How many times does a PRE-QUERY Trigger and POST-QUERY Trigger will get executed?

PRE-QUERY fires once.

POST-QUERY fires 10 times.

· A Pre-Query trigger fires before a query executes. Use it to check or modify query conditions.

· A Post-Query trigger fires as each record is fetched (except array processing). Use it to perform

calculations and populate additional items.

What is a difference between pre-select and pre-query?

Fires during the execute query and count query processing after oracle forms constructs the select

statement to be issued, but before the statement is actually issued.

The pre-query trigger fires just before oracle forms issues the select statement to the database after the

operator as define the example records by entering the query criteria in enter query mode.

Pre-query trigger fires before pre-select trigger.

G.NAVIGATIONAL TRIGGERS

Navigational triggers fire in response to navigational events. For instance, when the operator

clicks on a text item in another block, navigational events occur as Form Builder moves the

input focus from the current item to the target item.

I ] PRE- AND POST- TRIGGERS

Fire as Form Builder navigates internally through different levels of the object hierarchy.

When Do Pre- and Post-Navigation Triggers Fire?

The Pre- and Post- navigation triggers fire during navigation, that is just before entry

to or just after exit from the object specified as part of the trigger name.

Example

The Pre-Text-Item trigger fires just before entering a text item.

When Do Navigation Triggers Not Fire?

The Pre- and Post- navigation triggers do not fire if they belong to a unit that is smaller than the current

validation unit. For instance, if the validation unit is Record, Pre- and Post-Text-Item triggers do not fire.

What Happens When a Navigation Trigger Fails?

If a Pre- or Post navigation trigger fails, the input focus returns to its initial location (where it was prior to

the trigger firing). To the user, it appears that the input focus has not moved at all.

1. Pre-Form

Perform an action just before Form Builder navigates to the form from "outside" the form,

such as at form startup.

IF not (DBMS_SESSION.IS_ROLE_ENABLED(’ADMINISTRATIVE’) or

(DBMS_SESSION.IS_ROLE_ENABLED(’TECHNICAL’) THEN

MESSAGE(’You are not authorized to run this application’);

PAUSE;

RAISE form_trigger_failure;

END IF;

2. Pre-Block

Perform an action before Form Builder navigates to the block level from the form level.

Fires during the Enter the Block process, during navigation from one block to another.

Usage Notes:

Use a Pre–Block trigger to:

· Allow or disallow access to a block

· Set variable values

Disabling stock_button when leaving CONTROL block:

begin

SET_ITEM_PROPERTY(’CONTROL.stock_button’, enabled, property_false);

End;

3. Pre-Record

Perform an action before Form Builder navigates to the record level from the block level.

Usage Notes:

· Fires during the Enter the Record process, during navigation to a different record.

· Use a Pre–Record trigger to keep a running total.

The following trigger prevents the user from entering a new record given some dynamic condition and

the status of SYSTEM.RECORD_STATUS evaluating to NEW.

IF (( dynamic–condition) AND :System.Record_Status = ’NEW’) THEN

RAISE Form_Trigger_Failure;

END IF;

4. Pre-Text-Item

Perform an action before Form Builder navigates to a text item from the record level.

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.

5. Post-Form

Perform an action before Form Builder navigates to "outside" the form, such as when exiting

the form.

Fires during the Leave the Form process, when a form is exited.

Usage Notes:

You can use a Post–Form trigger for the following tasks:

· To clean up the form before exiting. For example, use a Post–Form trigger to erase any

global variables that the form no longer requires.

· To display a message to the operator upon form exit.

This trigger does not fire when the form is exited abnormally, for example, if validation fails in

the form.

6. Post-Block

Manipulate the current record when Form Builder leaves a block and navigates to the form

level.

Usage Notes:

· Use a Post–Block trigger to validate the block’s current record; that is, the record that had input focus when

the Leave the Block event occurred.

· You might also use this trigger to test a condition and prevent the user from leaving a block based on that

condition.

Example

Set_item_property (‘control.cmdsave’, enabled, property_false);

7. Post-Record

Manipulate a record when Form Builder leaves a record and navigates to the block level.

Fires during the Leave the Record process. Specifically, the Post–Record trigger fires whenever

the operator or the application moves the input focus from one record to another. The Leave

the Record process can occur as a result of numerous operations, including INSERT_RECORD,

DELETE_RECORD, NEXT_RECORD, NEXT_BLOCK, CREATE_RECORD, PREVIOUS_BLOCK, etc.

Usage Notes:

Use a Post–Record trigger when you want to perform an action whenever the operator or the

application moves the input focus from one record to another. For example, if you want to set

a visual attribute for an item as the operator scrolls down through a set of records, you can do

so from within this trigger.

Example:

/* ** Built–in: NEXT_RECORD

** Example: If the current item is the last item in the ** block, then skip to the next record instead of

** the default of going back to the first item in ** the same block

** Trigger: Key–Next–Item */

DECLARE

cur_itm VARCHAR2(80) := :System.Cursor_Item;

cur_blk VARCHAR2(80) := :System.Cursor_Block;

lst_itm VARCHAR2(80);

BEGIN

lst_itm := cur_blk||’.’||Get_Block_Property(cur_blk,LAST_ITEM);

IF cur_itm = lst_itm THEN

Next_Record;

ELSE

Next_Item;

END IF;

END;

8. Post-Text-Item

Manipulate an item when Form Builder leaves a text item and navigates to the record level.

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.

II] WHEN-NEW-INSTANCE-TRIGGERS

Fire at the end of a navigational sequence that places the input focus on a different item.

Specifically, these triggers fire just after Form Builder moves the input focus to a different

item, when the form returns to a quiet state to wait for operator input.

When Do When-New- “object”-Instance Triggers Fire?

The When-New-”object”-Instance triggers fire immediately after navigation to the object specified as part

of the trigger name.

Example

The When-New-Item-Instance trigger fires immediately after navigation to a new instance of an item.

1. When-New-Form-Instance

Perform an action at form start-up. (Occurs after the Pre-Form trigger fires).

Perform a query of all orders, when the ORDERS form is run, by including the following code in your

When-New-Form-Instance trigger:

1a. EXECUTE_QUERY;

1b. BEGIN

DEFAULT_VALUE(’’, ’'GLOBAL.where_cls’);

SET_BLOCK_PROPERTY(’prod_lov_blk’, DEFAULT_WHERE,:GLOBAL.where_cls);

EXECUTE_QUERY;

END;

1c. BEGIN

EXECUTE_QUERY;

:GLOBAL.width_win_order := GET_WINDOW_PROPERTY(’win_order’, WIDTH);

:GLOBAL.height_win_order := GET_WINDOW_PROPERTY(’win_order’,HEIGHT);

:GLOBAL.width_win_inventory := GET_WINDOW_PROPERTY(’win_inventory’,WIDTH);

:GLOBAL.height_win_inventory := GET_WINDOW_PROPERTY(’win_inventory’,HEIGHT);

END;

1d. When-New-Form-Instance Trigger at Form Level

BEGIN

SET_WINDOW_PROPERTY( forms_mdi_window, WINDOW_STATE, MAXIMIZE );

SET_WINDOW_PROPERTY( forms_mdi_window, TITLE, ’Summit Sporting Goods Application’);

END;

Example

This code could be used in a WHEN-NEW-FORM-INSTANCE trigger to initially populate the hierarchical

tree with data. The example locates the hierarchical tree first. Then, a record group is created and the

hierarchical tree is populated.

DECLARE

htree ITEM;

v_ignore NUMBER;

rg_emps RECORDGROUP;

BEGIN

htree := Find_Item('tree_block.htree3');

rg_emps := Create_Group_From_Query('rg_emps',’select 1, level, ename, NULL, to_char(empno) ’

||' from emp ' ||'connect by prior empno = mgr ' ||’start with job = ’’PRESIDENT’’’);

v_ignore := Populate_Group(rg_emps);

Ftree.Set_Tree_Property(htree, Ftree.RECORD_GROUP, rg_emps);

END;

2. When-New-Block-Instance

Perform an action immediately after the input focus moves to an item in a block other than the

block that previously had input focus.

Usage Notes:

Use a When–New–Block–Instance trigger to perform an action every time Oracle Forms

instantiates a new block.

Example

The following example of a When-New-Block-Instance trigger conditionally sets the DELETE ALLOWED

property to FALSE.

IF GET_APPLICATION_PROPERTY(username) = ’SCOTT’ THEN

SET_BLOCK_PROPERTY(’S_ITEM’,DELETE_ALLOWED, PROPERTY_FALSE);

END IF;

3. When-New-Record-Instance

Perform an action immediately after the input focus moves to an item in a different record. If

the new record is in a different block, fires after the When-New-Block-Instance trigger, but

before the When-New-Item-Instance trigger.

Usage Notes:

Use a When–New–Record–Instance trigger to perform an action every time Oracle Forms instantiates a

new record. For example, when an operator presses [Down] to scroll through a set of records, Oracle

Forms fires this trigger each time the input focus moves to the next record, in other words, each time

Oracle Forms instantiates a new record in the block.

Example

The Cursor arrives in each record of the S_Item block, and populates the product_image item

with a picture of the products, if one exists

1] Declare

Filename varchar2(20);

Begin

Filename:=get_product_image(:S_ITEM.PRODUCT_ID);

If Filename= ‘ No file’ then

Null;

Else

Read_Image_File(filename,’tiff’,’S_ITEM.product_image’);

End if

End;

2] 1.a Example: Brings up the debugging window for a particular ** value of the ’JOB’ item anytime the user

changes records.*/

BEGIN

IF :Emp.Job = ’CLERK’ THEN

Break;

Call_Form(’clerk_timesheet’);

Break;

END IF;

END;

3.] BEGIN

IF (:global.cancel_query = 'Y' and :system.mode = 'ENTER-QUERY') THEN

Exit_Form;

:global.cancel_query = 'N';

END IF;

END;

4.When-New-Item-Instance

Fires when the input focus moves to an item. Specifically, it fires after navigation to an item,

when Form Builder is ready to accept input in an item that is different than the item that

previously had input focus.

Perform an action immediately after the input focus moves to a different item. If the new item

is in a different block, fires after the When-New-Block-Instance trigger.

Usage Notes

Use a When-New-Item-Instance trigger to perform an action whenever an item gets input focus. The When-

New-Item-Instance trigger is especially useful for calling restricted (navigational) built-ins.

1] IF CHECKBOX_CHECKED(’S_ORD.order_filled’)THEN

SET_ITEM_PROPERTY(’S_ORD.date_shipped’,UPDATE_ALLOWED, property_true);

GO_ITEM(’S_ORD.date_shipped’);

END IF;

2] Built–in: CLEAR_ITEM - Example: Clear the current item if it does not represent ** the first day of a

month.

BEGIN

IF TO_CHAR(:Emp.Hiredate,’DD’) <> ’01’ THEN

Clear_Item;

Message(’This date must be of the form 01–MON–YY’);

END IF;

END;

3]. BEGIN

IF :Emp.Empno IS NOT NULL THEN

:Global.Employee_Id := :Emp.Empno;

Clear_Block(No_Validate);

END IF;

END;

4]. Assume that you want Oracle Forms to display an LOV when the operator enters query mode and the

input focus is in a particular text item. The following trigger accomplishes that operation.

BEGIN

IF :System.Cursor_Item = ’EMP.EMPNO’ and :System.Mode = ’ENTER–QUERY’ THEN

IF NOT Show_Lov(’my_lov’) THEN

RAISE Form_Trigger_Failure;

End if;

END IF;

END;

H. VALIDATION TRIGGERS

Validation triggers fire when Form Builder validates data in an item or record. Form Builder

performs validation checks during navigation that occurs in response to operator input,

programmatic control, or default processing, such as a Commit operation.

Validation occurs at item, record, block, and form levels.

• Validation happens when:

– [Enter] Key or ENTER built-in is activated

– Control leaves the validation unit due to navigation or commit

Validation Process

Form Builder performs a validation process at several levels to ensure that records and individual values

follow appropriate rules. If validation fails, then control is passed back to the appropriate level, so that

the operator can make corrections. Validation occurs at:

• Item level:

Form Builder records a status for each item to determine whether it is currently valid. If an item has

been changed and is not yet marked as valid, then Form Builder first performs standard validation

checks to ensure that the value conforms to the item’s properties. These checks are carried out before

firing any When-Validate-Item triggers that you have defined. Standard checks include the following:

- Format mask

- Required (if so, then is the item null?)

- Data type

- Range (Lowest-Highest Allowed Value)

- Validate from List (see later in this lesson)

• Record level:

After leaving a record, Form Builder checks to see whether the record is valid. If not, then the status of

each item in the record is checked, and a When-Validate-Record trigger is then fired, if present. When

the record passes these checks, it is set to valid.

• Block and form level:

At block or form level, all records below that level are validated. For example, if you commit (save)

changes in the form, then all records in the form are validated, unless you have suppressed

this action.

When Does Validation Occur?

Form Builder carries out validation for the validation unit under the following conditions:

• The [Enter] key is (ENTER command is not necessary mapped to the key that is physically labeled

Enter) pressed or the ENTER built-in procedure is run (whose purpose is to force validation immediately).

• The operator or a trigger navigates out of the validation unit. This includes when changes are

committed. The default validation unit is item, but can also be set to record, block, or form by the

designer. The validation unit is discussed in the next section.

Using LOVs for Validation

When you attach an LOV to a text item by setting the LOV property of the item, you can optionally use

the LOV contents to validate data entered in the item. Do this by setting the Validate from List property

to Yes for the item. At validation time, Form Builder then automatically uses the item value as a non

case-sensitive search string on the LOV contents. The following events then occur, depending on the

circumstances:

• If the value in the text item matches one of the values in the first column of the LOV, validation

succeeds, the LOV is not displayed, and processing continues normally.

• If the item’s value causes a single record to be found in the LOV, but is a partial value of the LOV value,

then the full LOV column value is returned to the item (providing that the item is defined as the return

item in the LOV). The item then passes this validation phase.

• If the item value causes multiple records to be found in the LOV, Form Builder displays the LOV and

uses the text item value as the search criteria to automatically reduce the list, so that the operator must

choose.

• If no match is found, then the full LOV contents are displayed to the operator.

Validation Triggers

• Item level

When-Validate-Item

• Block level

When-Validate-Record

1. When – Validate -Item

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 Defer_Required_Enforcement property postpones enforcement of the Required property from

item validation to record validation. When an item has the Required property set to Yes, by default

Form Builder will not allow navigation out of the item until a valid value is entered. Setting the

Defer_Required_Enforcement property to Yes allows the operator to move freely among the items in

the record.

When-Validate-Item Trigger

You have already used this trigger to add item-level validation. The trigger fires after standard item

validation, and input focus is returned to the item if the trigger fails.

Example

The SELECT...INTO statement must return an error if more than one row is retrieved that matches the

criteria. This implies PL/SQL may attempt to fetch data twice from the table in question to insure that there

aren't two matching rows.

BEGIN

SELECT description INTO :Employee.Commplan_Desc FROM commplan WHERE commcode =

:Employee.Commcode;

EXCEPTION

WHEN No.Data_Found THEN

Message('Invalid Commission Plan, Use <List> for help');

RAISE Form_Trigger_Failure;

WHEN Too_Many_Rows THEN

Message('Error. Duplicate entries in COMMPLAN table!');

RAISE Form_Trigger_Failure;

END;

2. When – Validate -Record

Fires during the Validate the Record process. Specifically, it fires as the last part of record

validation for records with the New or Changed validation status.

Use a When-Validate-Record trigger to supplement Form Builder default record validation

processing.

Note that it is possible to write a When-Validate-Record trigger that changes the value of an

item in the record that Form Builder is validating. If validation succeeds, Form Builder marks

the record and all of the fields as Valid and does not re-validate. 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.

When-Validate-Record Trigger

This trigger fires after standard record-level validation, when the operator has left a new or changed

record. Because Form Builder has already checked that required items for the record are valid, you can

use this trigger to perform additional checks that may involve more than one of the record’s items, in the

order they were entered. When-Validate-Record must be defined at block level or above.

Example

The following example verifies that Start_Date is less than End_Date. Since these two text items have values

that are related, it's more convenient to check the combination of them once at the record level, rather than

check each item separately. This code presumes both date items are mandatory and that neither will be

NULL.

/* Method 1: Hardcode the item names into the trigger. ** Structured this way, the chance this code will **

be reusable in other forms we write is pretty low because of dependency on block and item ** names.*/

BEGIN

IF :Experiment.Start_Date > :Experiment.End_Date THEN

Message('Your date range ends before it starts!');

RAISE Form_Trigger_Failure;

END IF;

END;

I.TRANSACTIONAL TRIGGERS

Transactional triggers fire in response to a wide variety of events that occur as a form interacts

with the data source.

Transaction processing includes two phases:

• Post:

– Writes record changes to base tables

– Fires transactional triggers

• Commit: Performs database commit

Errors result in:

• Rollback of the database changes

• Error message

The Commit Sequence of Events

The commit sequence of events (when the Array DML size is 1) is as follows:

1 Validate the form.

2 Process save point.

3 Fire the Pre-Commit trigger.

4 Validate the block (for all blocks in sequential order).

For all deleted records of the block (in reverse order of deletion):

- Fire the Pre-Delete trigger.

- Delete the row from the base table or fire the On-Delete trigger.

- Fire the Post-Delete trigger.

For all inserted or updated records of the block in sequential order:

If it is an inserted record:

- Copy Value From Item.

- Fire the Pre-Insert trigger.

- Check the record uniqueness.

- Insert the row into the base table or fire the On-Insert trigger.

- Fire the Post-Insert trigger.

If it is an updated record:

- Fire the Pre-Update trigger.

- Check the record uniqueness

- Update the row in the base table or fire the On-Update trigger.

- Fire the Post-Update trigger.

5 Fire the Post-Forms-Commit trigger.

If the current operation is COMMIT, then:

6 Issue an SQL-COMMIT statement.

7 Fire the Post-Database-Commit trigger.

Commit Triggers Uses

1. Pre-Commit

Check user authorization; set up special locking

Pre-Commit Fires once during commit processing, before base table blocks are processed; fires if there

are changes to base table items in the form or if changes have been posted but not yet committed (This

trigger always fires in case of uncommitted posts, even if there are no changes to post.)

Usage Notes:

Use a Pre–Commit trigger to perform an action, such as setting up special locking requirements, anytime

a database commit is going to occur.

Pre-Commit: Fires once if form changes are made or uncommitted changes are posted

2. Pre-Delete

Journaling; implement foreign-key delete rule

Fires during the Post and Commit Transactions process, before a row is deleted. It fires once for each record

that is marked for delete.

Usage Notes

· Use a Pre-Delete trigger to delete the detail record of a master record.

· Use a Pre-Delete trigger to prevent the deletion of a record if that record is the master record for

detail records that still exist.

PRE-DELETE TRIGGER -- Final checks before row deletion

DECLARE

CURSOR C1 IS SELECT ’anything’ FROM S_ORD WHERE customer_id = :S_CUSTOMER.id;

BEGIN

OPEN C1;

FETCH C1 INTO :GLOBAL.dummy;

IF C1%FOUND THEN

MESSAGE(’There are orders for this customer!’);

RAISE form_trigger_failure;

ELSE

CLOSE C1;

END IF;

END;

3. Pre-Insert

Fires during the Post and Commit Transactions process, before a row is inserted. It fires once

for each record that is marked for insert.

Generate sequence numbers; journaling; automatically generated columns; check constraints

Usage Notes:

Use a Pre–Insert trigger to perform the following tasks:

· change item values

· keep track of the date a record is created and store that in the record prior to committing

Example:1

This Pre-Insert trigger on the S_ORD block assigns an Order ID from the sequence S_ORD_ID, which will be written to

the ID column when the row is subsequently inserted.

Begin

SELECT S_ORD_ID.nextval INTO :S_ORD.id FROM SYS.dual;

End;

Note:

The Insert Allowed and Keyboard Navigable properties on :S_ORD.id should be No, so that the user does

not enter an ID manually.

You can also assign sequence numbers from a table. If you use this method, then two transactional

triggers are usually involved:

• Use Pre-Insert to select the next available number from the sequence table (locking the row to prevent

other users from selecting the same value) and increment the value by the required amount.

• Use Post-Insert to update the sequence table, recording the new upper value for the sequence.

Example:2

This example assigns a primary key field based on a sequence number, and then writes a row into an

auditing table, flagging creation of a new order.

DECLARE

CURSOR next_ord IS SELECT orderid_seq.NEXTVAL FROM dual;

BEGIN

/* ** Fetch the next sequence number from the ** explicit cursor directly into the item in

** the Order record. Could use SELECT...INTO, ** but explicit cursor is more efficient. */

OPEN next_ord;

FETCH next_ord INTO :Order.OrderId;

CLOSE next_ord;

/* ** Make sure we populated a new order id ok... */

IF :Order.OrderId IS NULL THEN

Message(’Error Generating Next Order Id’);

RAISE Form_Trigger_Failure;

END IF;

/* ** Insert a row into the audit table */

INSERT INTO ord_audit( orderid, operation, username, timestamp )

VALUES ( :Order.OrderId, ’New Order’, USER,SYSDATE );

END;

4. Pre-Update

Fires during the Post and Commit Transactions process, before a row is updated. It fires once

for each record that is marked for update.

Journaling; implement foreign-key update rule; auto-generated columns; check constraints

Usage Notes:

Use a Pre–Update trigger to audit transactions.

Example:

The following example writes a row into an Audit Table showing old discount and new discount for a given

customer, including timestamp and username making the change.

DECLARE

old_discount NUMBER;

new_discount NUMBER := :Customer.Discount_Pct;

oper_desc VARCHAR2(80);

CURSOR old_value IS SELECT discount_pct FROM customer WHERE CustId = :Customer.CustId;

BEGIN

/* ** Fetch the old value of discount percentage from the database by CustomerId. We need to do this since

the value of :Customer.Discount_Pct will be the *new* value we’re getting ready to commit and we want to

record for posterity the old and new values. We could use SELECT...INTO but choose an explicit cursor for

efficiency. */

OPEN old_value;

FETCH old_value INTO old_discount;

CLOSE old_value;

/* ** If the old and current values are different, then we need to write out an audit record */

IF old_discount <> new_discount THEN

/* Construct a string that shows the operation of Changing the old value to the new value. e.g.

** ’Changed Discount from 13.5% to 20%’ */

oper_desc := ’Changed Discount from ’||

TO_CHAR(old_discount)||’% to ’||

TO_CHAR(new_discount)||’%’;

/* ** Insert the audit record with timestamp and user */

INSERT INTO cust_audit( custid, operation, username, timestamp ) VALUES

( :Customer.CustId,oper_desc,USER,SYSDATE );

END IF;

END;

5. On-Commit

Fires whenever Oracle Forms would normally issue a database commit statement to finalize a

transaction. By default, this operation occurs after all records that have been marked as

updates, inserts, and deletes have been posted to the database.

Usage Notes:

· Use an On–Commit trigger to change the conditions of normal Oracle Forms commit

processing to fit the particular requirements of a commit to a non–ORACLE database.

· To perform the default processing from this trigger, call to the COMMIT_FORM built–in.

Example:

This example disables the commit operation when running against a datasource that does not support

transaction control. If the application is running against ORACLE, the commit operation behaves normally.

BEGIN

IF Get_Application_Property(DATA_SOURCE) = ’ORACLE’ THEN

Commit_Form;

END IF; /* ** otherwise, no action is performed */

END;

6. On-Delete

Fires during the Post and Commit Transactions process. Specifically, it fires after the Pre–Delete trigger

fires and before the Post–Delete trigger fires, replacing the actual database delete of a given row. The

trigger fires once for each row that is marked for deletion from the database.

Usage Notes:

· Use an On–Delete trigger to replace the default Oracle Forms processing for handling deleted

records during transaction posting.

· To perform the default Oracle Forms processing from this trigger, that is, to delete a record from

your form or from the database, include a call to the DELETE_RECORD built–in.

Example:

This example updates the employee table to set the Termination_Date, rather than actually deleting the employee from

the database.

BEGIN

UPDATE emp SET termination_date = SYSDATE WHERE empno = :Emp.Empno;

END;

7. On-Insert

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.

Description

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.

Syntax

PROCEDURE INSERT_RECORD;

/* ** Built-in: INSERT_RECORD ** Example : Perform Form Builder standard insert processing ** based on

a global flag setup at startup by the ** form, perhaps based on a parameter. ** Trigger: On-Insert */

BEGIN

/* ** Check the global flag we setup at form startup */

IF :Global.Using_Transactional_Triggers = 'TRUE' THEN

User_Exit('my_insrec block=EMP');

/* ** Otherwise, do the right thing. */

ELSE

Insert_Record;

END IF;

END;

8. On-Update

Fires during the Post and Commit Transactions process. Specifically, it fires after the Pre–Update trigger

fires and before the Post–Update trigger fires, when Oracle Forms would normally update a record in the

database. It fires once for each row that is marked for update in the form.

Usage Notes:

· Use an On–Update trigger to replace the default Oracle Forms processing for handling updated

records during transaction posting.

· To perform the default Oracle Forms processing from this trigger, include a call to the

UPDATE_RECORD built–in.

Begin

UPDATE RECORD;

End;

When called from an On-Update trigger, initiates the default Form Builder processing for updating a record

in the database during the Post and Commit Transaction process. This built-in is included primarily for

applications that run against a non-ORACLE data source.

DML Statements Issued During Commit Processing

INSERT INTO base_table ( base_column, base_column,...) VALUES ( :base_item, :base_item, ...)

UPDATE base_table SET base_column = :base_item, base_column = :base_item, ...WHERE ROWID = :ROWID

DELETE FROM base_table WHERE ROWID = :ROWID

DML Statements Issued During Commit Processing Rules:

• DML statements may fire database triggers.

• Form Builder uses and retrieves ROWID.

• The Update Changed Columns Only and Enforce Column Security properties affect UPDATE

statements.

• Locking statements are not issued.

9. Post – Database Commit

Description

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;

Post-Database-Commit Determines if commit was successful; determines if there are

posted, uncommitted changes

10. Post – Form - Commit

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.

· Post-Forms-Commit Checks complex multirow constraints

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;

11. Post – Delete

Fires during the Post and Commit Transactions process, after a row is deleted. It fires once for

each row that is deleted from the database during the commit process. form or block

Usage Notes:

Use a Post–Delete trigger to audit transactions.

Example - 1

Begin

INSERT INTO delete_audit (id, timestamp, who_did_it)VALUES ( :S_ORD.id, SYSDATE, USER );

End;

Example - 2

Begin

Delete from S_ORD SET WHERE id = :S_ORD.id;

IF SQL%NOTFOUND THEN

MESSAGE(’Record not found in database’);

RAISE form_trigger_failure;

Else

Messafe(SQL%rowcount|| “ rows Deleted”);

END IF;

End;

12. Post – Insert

Fires during the Post and Commit Transactions process, just after a record is inserted. It fires

once for each record that is inserted into the database during the commit process.

· Use a Post-Insert trigger to audit transactions.

· Write changes to nonbase tables.

· Gather statistics on applied changes.

Example 1

Keeping an Audit Trail

:GLOBAL.insert_tot := TO_CHAR(TO_NUMBER(:GLOBAL.insert_tot)+1);

Example 2

To handle exceptions, include EXCEPTION section in trigger. Post-Insert trigger:

Begin

INSERT INTO LOG_TAB (LOG_VAL, LOG_USER) VALUES(:S_DEPT.id,:GLOBAL.username);

EXCEPTION

WHEN OTHERS THEN

MESSAGE(’Error! ’,||SQLERRM);

End;

13. Post – Update

Fires during the Post and Commit Transactions process, after a row is updated. It fires once for

each row that is updated in the database during the commit process.

Usage Notes: Use a Post–Update trigger to audit transactions.

This Post-Update trigger writes the current record ID to the UPDATE_AUDIT table,along with a time stamp and the

user who performed the update.

Example - 1

Begin

INSERT INTO update_audit (id, timestamp, who_did_it)VALUES ( :S_ORD.id, SYSDATE, USER );

End;

Example - 2

Begin

UPDATE S_ORD SET date_shipped = SYSDATE WHERE id = :S_ORD.id;

IF SQL%NOTFOUND THEN

MESSAGE(’Record not found in database’);

RAISE form_trigger_failure;

END IF;

End;

Query Processing Triggers Uses

14. Pre – Select

Fires during Execute Query and Count Query processing, after Form Builder constructs the SELECT

statement to be issued, but before the statement is actually issued. Note that the SELECT statement can

be examined in a Pre-Select trigger by reading the value of the system variable SYSTEM.LAST_QUERY

Fires after Form Builder has constructed the block SELECT statement based on the query conditions, but

before it issues this statement

Use a Pre-Select trigger to prepare a query prior to execution against a non-ORACLE data source.

This example assigns a primary key field based on a sequence number, and then writes a row into an auditing table,

flagging creation of a neworder.

DECLARE

CURSOR next_ord IS SELECT orderid_seq.NEXTVAL FROM dual;

BEGIN

/** Fetch the next sequence number from the Explicit cursor directly into the item in the Order record. Could

use SELECT...INTO, ** but explicit cursor is more efficient. */

OPEN next_ord;

FETCH next_ord INTO :Order.OrderId;

CLOSE next_ord;

IF :Order.OrderId IS NULL THEN

Message(’Error Generating Next Order Id’);

RAISE Form_Trigger_Failure;

END IF;

/** Insert a row into the audit table

INSERT INTO ord_audit( orderid, operation, username, timestamp )

VALUES ( :Order.OrderId,’New Order’,USER,SYSDATE );

END;

15. On - Select

Fires when Form Builder would normally execute the open cursor, parse, and execute phases

of a query, to identify the records in the database that match the current query criteria.

On-Select replaces open cursor, parse, and execute phases.

Usage Notes

· Use an On-Select trigger to open and execute the database cursor. Specifically, use this trigger

when you are retrieving data from a non-ORACLE data source. The On-Select trigger can be

used in conjunction with the On-Fetch trigger to replace the processing that normally occurs in

the EXECUTE_QUERY built-in subprogram.

· To perform the default Form Builder processing from this trigger, include a call to the

SELECT_RECORDS built-in.

Example - 1

In the following example, the On-Select trigger is used to call a user exit, 'Query,' and a built-in subprogram,

SELECT_RECORDS, to perform a query against a database.

Begin

IF Get_Application_Property(DATASOURCE) = 'DB2' THEN

User_Exit ( 'Query' );

IF Form_Failure OR Form_Fatal THEN

ABORT_QUERY;

END IF;

ELSE

/* ** Perform the default Form Builder task of opening the query. */

Select_Records;

END IF;

End;

16. Post-Select Trigger

Description

Fires after Form Builder has constructed and issued the block SELECT statement, but before it

fetches the records

The Post-Select trigger fires after the default selection phase of query processing, or after the

successful execution of the On-Select trigger. It fires before any records are actually retrieved

through fetch processing.

Usage Note:

Use the Post-Select trigger to perform an action based on the outcome of the Select phase of query

processing such as an action based on the number of records that match the query criteria.

15. On – Fetch

Fires when Form Builder performs a fetch for a set of rows (You can use the

CREATE_QUERIED_RECORD built-in to create queried records if you want to replace default

fetch processing.)

• On-Fetch continues to fire until:

– It fires without executing CREATE_QUERIED_RECORD.

– The query is closed by the user or by ABORT_QUERY.

– It raises FORM_TRIGGER_FAILURE.

The trigger will fire once for each record that is to be fetched. On–Fetch:

DECLARE

j NUMBER := Get_Block_Property(blk_name, RECORDS_TO_FETCH);

emprow emp%ROWTYPE;

BEGIN

FOR ctr IN 1..j LOOP

/* ** Try to get the next row. */

EXIT WHEN NOT MyPackage.Get_Next_Row(emprow);

Create_Queried_Record;

:Emp.rowid := emprow.ROWID;

:Emp.empno := emprow.EMPNO;

:Emp.ename := emprow.ENAME;

END LOOP;

IF form_fatal OR form_failure THEN

raise form_trigger_failure;

END IF;

END;

16. On – Count

Fires when Form Builder would usually perform default Count Query processing to determine

the number of rows that match the query conditions

Fires when Form Builder would normally perform default Count Query processing to determine

the number of rows in the database that match the current query criteria. When the

On-Count trigger completes execution, Form Builder issues the standard query hits message:

FRM-40355: Query will retrieve <n> records.

Usage Notes

· Use an On-Count trigger to replace default Count Query processing in an application running

against a non-ORACLE data source.

· To perform the default Form Builder processing from this trigger, include a call to the built-in.

· If you are replacing default processing, you can set the value of the Query_Hits block property to

indicate the number of records in the non-ORACLE data source that match the query criteria.

· Form Builder will display the query hits message (FRM-40355) even if the On-Count trigger fails to

set the value of the Query_Hits block property. In such a case, the message reports 0 records

identified.

Example - 1

This example calls a user-named subprogram to count the number of records to be retrieved by

the current query criteria, and sets the Query_Hits property appropriately.

DECLARE

j NUMBER;

BEGIN

j := Recs_Returned('DEPT',Name_In('DEPT.DNAME'));

Set_Block_Property('DEPT',QUERY_HITS,j);

END;

Example 2

/* ** Built-in: COUNT_QUERY ** Example: Display the number of records that will be retrieved ** by the

current query. */

BEGIN

Count_Query;

END;

Example 3

/* ** Built-in: COUNT_QUERY

** Example: Perform Form Builder count query hits processing. Decide whether to use this Built-in or a user

** exit based on a global flag setup at startup by the form, perhaps based on a parameter.

* Trigger: On-Count */

BEGIN

/* ** Check the global flag we set during form startup */

IF :Global.Using_Transactional_Triggers = 'TRUE' THEN

/* ** User exit returns query hits count back into the ** CONTROL.HITS item. */

User_Exit('my_count');

/* ** Deposit the number of query hits in the appropriate ** block property so Form Builder can display its normal **

status message. */

Set_Block_Property(:System.Trigger_Block,QUERY_HITS,:control.hits);

/* ** Otherwise, do the right thing. */

ELSE

Count_Query;

END IF;

END;

17.On-Sequence-Number Trigger

Description

Fires when Form Builder would normally perform the default processing for generating

sequence numbers for default item values. Replaces the default series of events that occurs

when Form Builder interacts with the database to get the next value from a SEQUENCE object

defined in the database.

Usage Notes

· When a SEQUENCE is used as a default item value, Form Builder queries the database to

get the next value from the SEQUENCE whenever the Create Record event occurs.

Suppress or override this functionality with an On-Sequence-Number trigger.

· To perform the default Form Builder processing from this trigger, call the

GENERATE_SEQUENCE_NUMBER built-in.

Example:

** Built–in: GENERATE_SEQUENCE_NUMBER

** Example: Perform Oracle Forms standard sequence number processing based on a global flag setup at

** startup by the form, perhaps based on a parameter.

** Trigger: On–Sequence–Number */

BEGIN

/* ** Check the global flag we setup at form startup */

IF :Global.Using_Transactional_Triggers = ’TRUE’ THEN

User_Exit(’my_seqnum seq=EMPNO_SEQ’);

/* ** Otherwise, do the right thing. */

ELSE

Generate_Sequence_Number;

END IF;

END;

18. On-Check-Unique Trigger

Description

During a commit operation, the On-Check-Unique trigger fires when Form Builder normally

checks that primary key values are unique before inserting or updating a record in a base

table.

It fires once for each record that has been inserted or updated.

Replaces the default processing for checking record uniqueness. When a block has the

PRIMKEYB property set to Yes, Form Builder, by default, checks the uniqueness of a record by

constructing and executing the appropriate SQL statement to select for rows that match the

current record's primary key values. If a duplicate row is found, Form Builder displays

message FRM-40600: Record has already been inserted.

For a record that has been marked for insert, Form Builder always checks for unique primary

key values. In the case of an update, Form Builder checks for unique primary key values only

if one or more items that have the Primary Key item property have been modified.

Usage Notes

To perform the default processing from this trigger, call the CHECK_RECORD_UNIQUENESS

built-in.

On-Check-Unique Trigger examples

The following example verifies that the current record in question does not already exist in the

DEPT table.

DECLARE

CURSOR chk_unique IS SELECT 'x' FROM dept WHERE deptno = :dept.deptno;

tmp VARCHAR2(1);

BEGIN

OPEN chk_unique;

FETCH chk_unique INTO tmp;

CLOSE chk_unique;

IF tmp IS NOT NULL THEN

Message('This department already exists.');

RAISE Form_Trigger_Failure;

END IF;

END;

19. On-Close Trigger

Description

Fires when an operator or the application causes a query to close. By default, Form Builder

closes a query when all of the records identified by the query criteria have been fetched, or

when the operator or the application aborts the query.

The On-Close trigger augments the normal Form Builder "close cursor" phase of a query.

Usage Notes

· Use an On-Close trigger after using the On-Select or On-Fetch triggers, specifically, to

close files, close cursors, and free memory.

· The On-Close trigger fires automatically when the ABORT_QUERY built-in is called from

an On-Select trigger.

Example

The following example releases memory being used by a user-defined data access method via

the transactional triggers.

BEGIN

IF NOT my_data source_open('DX110_DEPT') THEN

my_datasource_close('DX110_DEPT');

END IF;

END;

20. On-Column-Security Trigger

Description

Fires when Form Builder would normally enforce column-level security for each block that has

the Enforce Column Security block property set On.

By default, Form Builder enforces column security by querying the database to determine the

base table columns to which the current form operator has update privileges. For columns to

which the operator does not have update privileges, Form Builder makes the corresponding

base table items in the form non-updateable by setting the Update Allowed item property Off

dynamically. Form Builder performs this operation at form startup, processing each block in

sequence.

Usage Notes

To perform the default processing from this trigger, call the ENFORCE_COLUMN_SECURITY

built-in.

Example

The following example sets salary and commission text items in the current block to disabled

and non-updateable, unless the SUPERUSER role is enabled. Only users with the user-defined

SUPERUSER role can change these number fields.

DECLARE

itm_id Item;

on_or_off NUMBER;

BEGIN

IF NOT role_is_set('SUPERUSER') THEN

on_or_off := PROPERTY_OFF;

ELSE

on_or_off := PROPERTY_ON;

END IF;

itm_id := Find_Item('Emp.Sal');

Set_Item_Property(itm_id,ENABLED,on_or_off);

Set_Item_Property(itm_id,UPDATEABLE,on_or_off);

itm_id := Find_Item('Emp.Comm');

Set_Item_Property(itm_id,ENABLED,on_or_off);

Set_Item_Property(itm_id,UPDATEABLE,on_or_off);

END;

21. On-Rollback Trigger

Description

Fires when Form Builder would normally issue a ROLLBACK statement, to roll back a

transaction to the last savepoint that was issued.

Usage Notes

Use an On-Rollback trigger to replace standard Form Builder rollback processing.

To perform default Form Builder processing from this trigger, include a call to the

ISSUE_ROLLBACK built-in.

ISSUE_ROLLBACK examples

/* ** Built-in: ISSUE_ROLLBACK

** Example: Perform Form Builder standard Rollback processing.

** Decide whether to use this built-in based on a ** global flag setup at startup by the form. ** perhaps based

on a parameter.

** Trigger: On-Rollback */

DECLARE

sp_name VARCHAR2(80);

BEGIN

/* Get the name of the savepoint to which Form Builder needs to ** rollback. (NULL = Full Rollback)*/

sp_name := Get_Application_Property(SAVEPOINT_NAME);

/* ** Check the global flag we setup at form startup */

IF :Global.Using_Transactional_Triggers = 'TRUE' THEN

User_Exit('my_rollbk name='||sp_name);

ELSE

Issue_Rollback(sp_name);

END IF

;

END;

J.LOGON TRANSACTION TRIGGERS

1. Pre-Logon Trigger

Fires just before Form Builder initiate a logon procedure to the data source.

Usage Notes

Use a Pre-Logon trigger to prepare the form for the logon procedure, particularly to a non-

ORACLE data source.

2. On-Logon Trigger

Fires once per logon when Oracle Forms normally initiates the logon sequence.

Usage Notes:

Use an On–Logon trigger to initiate a logon procedure to a non–ORACLE data source.

· Pre–Logon and Post–Logon triggers fire as part of the logon procedure.

· You can supply a NULL command to this trigger to bypass the connection to a data source, if you want to

create an application that does not require a data source.

· To perform the default Oracle Forms processing from this trigger, include a call to the LOGON built–in.

DECLARE

connected BOOLEAN:=FALSE;

tries NUMBER:=3;

un NUMBER;

pw VARCHAR2(30);

cs VARCHAR2(30);

BEGIN

SET_APPLICATION_PROPERTY(CURSOR_STYLE,’DEFAULT’);

WHILE CONNECTED = FALSE AND tries > 0

LOOP

LOGON_SCREEN;

un:=GET_APPLICATION_PROPERTY( USERNAME );

pw:=GET_APPLICATION_PROPERTY( PASSWORD );

cs:= GET_APPLICATION_PROPERTY( CONNECTION_STRING );

LOGON( un, pw || ‘@’ || CS , FALSE );

IF FORM_SUCESS THEN

Connected:=TRUE;

END IF;

Tries:=tries-1;

END LOOP;

IF NOT CONNECTED THEN

MESSAGE(‘Too many tries’);

RAISE FORM_TRIGGER_FAILURE;

END IF;

END;

3. POST-LOGON TRIGGER

Description

Fires after either of the following events:

· The successful completion of Form Builder default logon processing.

· The successful execution of the On-Logon trigger.

Example

This example calls a user exit to log the current username and time to an encrypted audit trail

file on the file system, which for security reasons is outside the database.

BEGIN

User_Exit('LogCrypt '|| USER||' ' ||TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS'));

END;

4. Pre-Logout Trigger

Fires once before Form Builder initiate a logout procedure.

Usage Notes

· Use a Pre-Logout trigger to prepare the form for logging out from the data source,

particularly a non-ORACLE data source.

· If you call certain built-ins from within one of the Logout triggers, the results are

undefined. For example, the COPY built-in cannot be called from a Pre-Logout trigger

because Pre-Logout fires after the Leave the Form event. Because the form is no longer

accessible at this point, the COPY operation is not possible.

5. On-Logout Trigger

Fires when Form Builder normally initiates a logout procedure from Form Builder and from

the RDBMS.

Usage Notes

· Use an On-Logout trigger to replace the default logout processing either from the

RDBMS or from a non-ORACLE data source.

· To perform the default Form Builder processing from this trigger, include a call to the

LOGOUT built-in.

· If you call certain built-ins from within one of the Logout triggers, the results are

undefined. For example, you cannot call the COPY built-in from a Pre-Logout trigger

because Pre-Logout fires after the Leave the Form event. Because the form is no longer

accessible, a COPY operation is not possible.

6. Post-Logout Trigger

Description

Fires after either of the following events:

· Form Builder successfully logs out of ORACLE.

· The successful execution of the On-Logout trigger.

Usage Notes

· Use a Post-Logout trigger to audit or to perform tasks on an Form Builder application that does

not require or affect the RDBMS or other data source.

· If you call certain built-ins from within one of the Logout triggers, the results are undefined. For

example, you cannot call COPY from a Pre-Logout trigger because Pre-Logout fires after the

Leave the Form event. Because the form is no longer accessible, a COPY operation is not possible.

This example calls a user exit to log the current username and time to an encrypted audit trail file on the file system,

which for security reasons is outside the database.

BEGIN

User_Exit(’LogCrypt ’||USER||’ ’ ||

TO_CHAR(SYSDATE,’YYYYMMDDHH24MISS’));

END;

J.MOUSE TRIGGERS

1. When-Mouse-Click Trigger

Description

Fires after the operator click the mouse if one of the following events occurs:

· if attached to the form, when the mouse is clicked within any canvas or item in the

form

· if attached to a block, when the mouse is clicked within any item in the block

· if attached to an item, when the mouse is clicked within the item

Three events must occur before a When-Mouse-Click trigger will fire:

· Mouse down

· Mouse up

· Mouse click

Any trigger that is associated with these events will fire before the When-Mouse-Click trigger

fires.

Usage Notes

Use the When-Mouse-Click trigger to perform an action every time the operator clicks the

mouse within an item and/or canvas.

2. When-Mouse-DoubleClick Trigger

Description

Fires after the operator double-clicks the mouse if one of the following events occurs:

· if attached to the form, when the mouse is double-clicked within any canvas or item in

the form

· if attached to a block, when the mouse is double-clicked within any item in the block

· if attached to an item, when the mouse is double-clicked within the item

Six events must occur before a When-Mouse-DoubleClick trigger will fire:

· Mouse down

· Mouse up

· Mouse click

· Mouse down

· Mouse up

· Mouse double-click

Any trigger that is associated with these events will fire before the When-Mouse-DoubleClick

trigger fires.

Usage Notes

Use a When-Mouse-DoubleClick trigger to perform an action every time the operator

DoubleClick the mouse within an item and/or canvas.

When-Mouse-DoubleClick Trigger examples

Example

Assume that an application requires Behavior A when the operator clicks the mouse and

Behavior B when the operator double-clicks the mouse. For example, if the operator clicks the

mouse, a product information window must appear. If the operator double-clicks the mouse,

an online help window must appear.

3. When-Mouse-Down Trigger

Description

Fires after the operator presses down the mouse button if one of the following events occurs:

· if attached to the form, when the mouse is pressed down within any canvas or item in

the form

· if attached to a block, when the mouse is pressed down within any item in the block

· if attached to an item, when the mouse is pressed within the item

Usage Notes

· Use a When-Mouse-Down trigger to perform an action every time the operator presses

down the mouse button within an item and/or canvas.

Note: The mouse down event is always followed by a mouse up event.

4. When-Mouse-Enter Trigger

Description

Fires when the mouse enters an item or canvas if one of the following events occurs:

· if attached to the form, when the mouse enters any canvas or item in the form

· if attached to a block, when the mouse enters any item in the block

· if attached to an item, when the mouse enters the item

Usage Notes

Use a When-Mouse-Enter trigger to perform an action every time the mouse enters an item or

canvas.

Do not use the When-Mouse-Enter trigger on a canvas that is larger than the window. Iconic

buttons and items on the canvas below the initial window cannot be selected. The user is able

to scroll the canvas to see the items. However, as soon as the mouse enters that area, the

trigger fires and returns focus to the previous target, so the user is never able to click on

those items. Changing a tooltip's property in a When-Mouse-Enter trigger cancels the tooltip

before it is ever shown.

Be careful when calling a modal window from a When-Mouse-Enter trigger. Doing so may

cause the modal window to appear unnecessarily.

For example, assume that your When-Mouse-Enter trigger causes Alert_One to appear

whenever the mouse enters Canvas_One. Assume also that your application contains two

canvases, Canvas_One and Canvas_Two. Canvas_One and Canvas_Two do not overlap each

other, but appear side by side on the screen. Further, assume that Alert_One displays within

Canvas_Two's border.

Finally, assume that the mouse has entered Canvas_One causing the When-Mouse-Enter

trigger to fire which in turn causes Alert_One to appear.

When the operator dismisses the message box, Alert_One will appear again unnecessarily if

the operator subsequently enters Canvas_One with the mouse. In addition, when the operator

moves the mouse out of Canvas_Two, any When-Mouse-Leave triggers associated with this

event will fire. This may not be the desired behavior.

begin

:GLOBAL.save_item_name := :SYSTEM.CURSOR_ITEM;

GO_ITEM(’s_ord.id’);

SHOW_VIEW(’cv_help’);

End;

WHEN-MOUSE-ENTER at Form Level

begin

IF :SYSTEM.MOUSE_CANVAS = ’CV_ORDER’ THEN

:control.show_help_button := ’?’;

END IF;

End;

5. When-Mouse-Leave Trigger

Description

Fires after the mouse leave an item or canvas if one of the following events occurs:

· if attached to the form, when the mouse leaves any canvas or item in the form

· if attached to a block, when the mouse leaves any item in the block

· if attached to an item, when the mouse leaves the item

Usage Notes

Use a When-Mouse-Leave trigger to perform an action every time the mouse leaves an item

and/or canvas.

WHEN-MOUSE-LEAVE trigger on control.show_help_button

begin

HIDE_VIEW(’cv_help’);

GO_ITEM(:GLOBAL.save_item_name);

End;

6. When-Mouse-Move Trigger

Description

Fires each time the mouse moves if one of the following events occurs:

· if attached to the form, when the mouse moves within any canvas or item in the form

· if attached to a block, when the mouse moves within any item in the block

· if attached to an item, when the mouse moves within the item

Usage Notes

· Use the When-Mouse-Move trigger to perform an action every time the operator moves the

mouse.

The When-Mouse-Move trigger may have performance implications because of the number of

times this trigger can potentially fire.

7. When-Mouse-Up Trigger

Description

Fires each time the operator presses down and releases the mouse button if one of the

following events occurs:

· if attached to the form, when the mouse up event is received within any canvas or item in

a form

· if attached to a block, when the mouse up event is received within any item in a block

· if attached to an item, when the mouse up event is received within an item

Two events must occur before a When-Mouse-Up trigger will fire:

· Mouse down

· Mouse up

Usage Notes

Use the When-Mouse-Up trigger to perform an action every time the operator presses and

releases the mouse.

The mouse up event is always associated with the item that received the mouse down event.

For example, assume that there is a When-Mouse-Up trigger attached to Item_One. If the

operator presses down the mouse on Item_One, but then releases the mouse on Item_Two,

the mouse up trigger will fire for Item_One, rather than for Item_Two.

K.OTHER TRIGGERS

1. User-Named Trigger

A user–named trigger is a trigger that you define yourself in a form, and then call explicitly

from other triggers or user–named subprograms. Each user–named trigger defined at the

same definition level must have a unique name.

To execute a user–named trigger, you must call the EXECUTE_TRIGGER built–in procedure, as

shown here:

Execute_Trigger(’my_user_named_trigger’);

Note: You can write user–named PL/SQL subprograms to perform almost any task for which

you might use a user–named trigger.

Usage Notes

User-named PL/SQL subprograms can be written to perform almost any task for which one

might use a user-named trigger.

As with all triggers, the scope of a user-named trigger is the definition level and below. When

more than one user-named trigger has the same name, the trigger defined at the lowest level

has precedence.

It is most practical to define user-named triggers at the form level.

Create a user-named trigger to execute user-named subprograms defined in a form document

from menu PL/SQL commands and user-named subprograms. (User-named subprograms

defined in a form cannot be called directly from menu PL/SQL, which is defined in a different

document.) In the menu PL/SQL, call the EXECUTE_TRIGGER built-in to execute a usernamed

trigger, which in turn calls the user-named subprogram defined in the current form.

DO_KEY built-in

Executes the key trigger that corresponds to the specified built-in subprogram. If no such key

trigger exists, then the specified subprogram executes. This behavior is analogous to pressing

the corresponding function key.

Syntax

PROCEDURE DO_KEY

(built-in_subprogram_name VARCHAR2);

DO_KEY restrictions

DO_KEY accepts built-in names only, not key names: DO_KEY(ENTER_QUERY). To accept a

specific key name, use the EXECUTE_TRIGGER built-in: EXECUTE_TRIGGER('KEY_F11').

DO_KEY examples

/* ** Built-in: DO_KEY

** Example: Simulate pressing the [Execute Query] key. */

BEGIN

Do_Key('Execute_Query');

END;

2. On-Savepoint Trigger

Fires when Form Builder would normally issue a Savepoint statement. By default, Form Builder

issues savepoints at form startup, and at the start of each Post and Commit Transaction

process.

Usage Notes

To perform default Form Builder processing from this trigger, include a call to the

ISSUE_SAVEPOINT built-in.

In an On-Savepoint trigger, the Savepoint_Name application property returns the name of the

next savepoint that Form Builder would issue by default, if no On-Savepoint trigger were

present. In an On-Rollback trigger , Savepoint_Name returns the name of the savepoint to

which Form Builder would roll back.

Suppress default savepoint processing by setting the Savepoint Mode form document property

to Off. When Savepoint Mode is Off, Form Builder does not issue savepoints and,consequently,

the On-Savepoint trigger never fires.

/* ** Built-in: ISSUE_SAVEPOINT

** Example: Perform Form Builder standard savepoint processing.

** Decide whether to use this built-in based on a global flag setup at startup by the form,perhaps based on a

parameter.

** Trigger: On-Savepoint */

DECLARE

sp_name VARCHAR2(80);

BEGIN

/* Get the name of the savepoint Form Builder needs to issue */

sp_name := Get_Application_Property(SAVEPOINT_NAME);

/* Check the global flag we setup at form startup */

IF :Global.Using_Transactional_Triggers = 'TRUE' THEN

User_Exit('my_savept name='||sp_name);

/* Otherwise, do the right thing. */

ELSE

Issue_Savepoint(sp_name);

END IF;

END;

3. Post-Change Trigger

Fires when any of the following conditions exist:

· The Validate the Item process determines that an item is marked as Changed and is

not NULL.

· An operator returns a value into an item by making a selection from a list of values,

and the item is not NULL.

· Form Builder fetches a non-NULL value into an item. In this case, the When-Validate-

Item trigger does not fire. If you want to circumvent this situation and effectively get

rid of the Post-Change trigger, you must include a Post-Query trigger

in addition to your When-Validate-Item trigger. See "Usage Notes" below.

Usage Notes

· The Post-Change trigger is included only for compatibility with previous versions of

Form Builder. Its use is not recommended in new applications.

· The Post-Query trigger does not have the restrictions of the Post-Change trigger. You

can use Post-Query to make changes to the fetched database values. Given such

changes, Form Builder marks the corresponding items and records as changed.

3. On-Lock Trigger

Fires whenever Oracle Forms would normally attempt to lock a row, such as when an operator

presses a key to modify data in an item. The trigger fires between the key press and the

display of the modified data.

Usage Notes:

· Use an On–Lock trigger to replace the default Oracle Forms processing for locking rows.

For example, if you are designing an application for use on a single–user system, you can

use the On–Lock trigger to speed processing by bypassing all lock processing. Also, use

On–Lock if you are accessing a non–ORACLE data source directly, not by way of Open

Gateway.

· When the On–Lock trigger fires as a result of an operator trying to modify data, the trigger

fires only the first time the operator tries to modify an item in the record. The trigger does

not fire during subsequent modifications to items in the same record. In other words, for

every row that is to be locked, the trigger fires once.

· To perform the default Oracle Forms processing from this trigger, include a call to the

LOCK_RECORD built–in.

· Use this trigger to lock underlying tables for non–updateable views.

/* ** Built-in: LOCK_RECORD

** Example: Perform Form Builder standard record locking on the queried record which has just been

deleted or updated. Decide whether to use default processing or a user exit by consulting a global flag setup

at startup by the form,perhaps based on a parameter.

** Trigger: On-Lock */

BEGIN

/* ** Check the global flag we set up at form startup */

IF :Global.Non_Oracle_Datasource = 'TRUE' THEN

User_Exit('my_lockrec block=EMP');

/* ** Otherwise, do the right thing. */

ELSE

Lock_Record;

END IF;

END;

5. Pre-Popup-Menu Trigger

This trigger is called when a user causes a pop-up menu to be displayed. (In a Microsoft

Windows environment, this occurs when a user presses the right mouse button.) Actions

defined for this trigger are performed before the pop-up menu is displayed.

Usage Notes

Use this trigger to enable or disable menu items on a pop-up menu before it is displayed.

6. Query-Procedure Trigger

Automatically created by Form Builder when the query data source is a stored procedure. This

trigger is called when a query operation is necessary. Think of this as an On-Query trigger that

is called by the system instead of doing default query operations.

Usage Notes

When constructing a query, any of the items may be used, but the Query Data Source

Columns property must be set so that those items can be passed to the query stored

procedure. Then, the query stored procedure has to use those values to filter the data. This

means that the enter query mode does not happen automatically unless you specify it.

7. Update-Procedure Trigger

Automatically created by Form Builder when the update data source is a stored procedure.

This trigger is called when a update operation is necessary. Think of this as an On-Update

trigger that is called by the system instead of doing default update operations.

8. When-Custom-Item-Event Trigger

Fires whenever a VBX control sends an event to Oracle Forms.

Usage Notes:

Use a When–Custom–Item–Event trigger to respond to a selection or change of value for a

VBX control. The system variable SYSTEM.CUSTOM_ITEM_EVENT stores the case–sensitive

name of the event that occurred, and the system variable

SYSTEM.CUSTOM_ITEM_EVENT_PARAMETERS stores a parameter name that contains the

supplementary arguments for an event that is fired by a VBX control.

Example:

This is an example of a procedure that can be called when Oracle Forms fires the When–Custom–Item–Event Trigger.

DECLARE

TabEvent varchar2(80);

TabNumber Number;

BEGIN

TabEvent := :system.custom_item_event;

/* ** After detecting a Click event, identify the ** tab selected, and use the user–defined

Goto_Tab_Page procedure to navigate to the selected page. */

IF (UPPER(TabEvent) = ’CLICK’) THEN

TabNumber := VBX.Get_Property(’TABCONTROL’,’CurrTab’);

Goto_Tab_Page(TabNumber);

END IF;

END;

9. When-Form-Navigate Trigger

Fires whenever any peer form navigation takes place. form

Use a When–Form–Navigate trigger to perform actions when any cross form navigation takes

place without relying on window activate and window deactivate events.

This is an example of a procedure that can be called when Oracle Forms fires the When–Form–

Navigate Trigger.

DECLARE

win_id WINDOW := FIND_WINDOW(’WINDOW12’);

BEGIN

if (GET_WINDOW_PROPERTY(win_id,WINDOW_STATE) = ’MAXIMIZE’ THEN

SET_WINDOW_PROPERTY(win_id,WINDOW_STATE,MINIMIZE);

else

SET_WINDOW_PROPERTY(win_id,WINDOW_STATE,MAXIMIZE);

end if;

END;

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

Example

/* Use a When-Tab-Page-Changed trigger to dynamically change a tab page's label from lower- to uppercase

(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;

11. When-Tree-Node-Activated Trigger

Fires when an operator double-clicks a node or presses Enter when a node is selected.

Usage Notes

· SYSTEM.TRIGGER_NODE is the node the user clicked on. SYSTEM.TRIGGER_NODE

returns a value of type NODE.

· No programmatic action will cause the When-Tree-Node-Activated trigger to fire. Only

end-user action will generate an event.

12. When-Tree-Node-Expanded Trigger

Fires when a node is expanded or collapsed.

Usage Notes

· SYSTEM.TRIGGER_NODE is the node the user clicked on. SYSTEM.TRIGGER_NODE returns a value of type NODE.

· No programmatic action will cause the When-Tree-Node-Expanded trigger to fire. Only end-user action will

generate an event.

13. When-Tree-Node-Selected Trigger

Fires when a node is selected or deselected.

Usage Notes

· SYSTEM.TRIGGER_NODE is the node the user clicked on. SYSTEM.TRIGGER_NODE returns

a value of type NODE.

· No programmatic action will cause the When-Tree-Node-Selected trigger to fire. Only enduser

action will generate an event.

Defining list items

A list item displays a predefined set of choices that

 are mutually exclusive

 can be displayed as either a poplist, text list, or combo box

LIST ITEM DESCRIPTION

Poplist Appears initially as a single field (similar to a text item field). When the

operator selects the list icon, a list of available choices appears.

Text List Appears as a rectangular box which displays a fixed number of values. When

the text list contains values that cannot be displayed (due to the displayable

area of the item), a vertical scroll bar appears, allowing the operator to view

and select undisplayed values.

Combo

Box

Combines the features found in list and text items. Unlike the poplist or the

text list style list items, the combo box style list item will display fixed values

and accept one operator-entered value.

The combo box list item appears as an empty box with an icon to the right.

The user can enter text directly into the combo field or click the list icon to

display a list of available values.

Pages

Friday, November 18, 2011

Oracle Forms Material

No comments:

Post a Comment