Oracle Forms

Oracle Forms

Oracle Forms

Forms

Forms environment

Setting forms Environmen

Forms Componen

Wizard

Property Palette

Item Type

Property Type

Editors

Alerts & Message

Property class and visual attribute

Data block

Data block through Wizard

Data block Manually

Data block Using Table and view

Multi Record Data Block

Read only Data block

Data block with Store Procedure

Master- Details Data block

Canvas

Content Canvas

Tab Canvas

Horizontal Canvas

Vertical Canvas

Stack Canvas

Window

Document Window

Dialog Window

Setting Window Dynamically

Trigger and Built-in

Type of Built-in and usage

Form level Trigger

Block Level Trigger

Item Level Trigger

Error Handling

Error handling trigger

Raise form trigger failure

List of Values (LOV)

Fixed LOV

Dynamic setting LOV

Record Group

Record group on Select stmt

Static Record Group

Run time Record group

Using Pl/Sql Objects

Program Unit

PL/Sql Library

Data base PL/Sql objects

Inherit and Subclass

Object Groups

Object Library

Using Copy and subclass

Not Related to Oracle Apps

Special Functions

Zooming a Form with parameter

Zooming a Report with Text parameter

Zooming a Report with Data parameter

DDL Statement in Form

Storing a photo copy through Image Item.

Using the chart item (Chart Control)

Menus

Default Menu and New Menu

Popup Menu

Projects: Super Market Retail Shop

This project will handled all the required transaction for a Retail Shop.

Oracle Apps Forms

New Module/Application Development Process

Creating a new Database schema

Registering the Database

Registering the application

Registration

Table Registration\

Table Registration using Ad-dd package

View Registration

Sequence Registration

Form Registration

Form Functions

Non-Form Functions

New Form Development Process

Template Form Architecture

Appstand Architecture

FNDMENU Architecture

Coding Standards

Name Conventions

Form development process

WHO information tracking

Calendars

Row LOV

Queries find Window

Programming of Non-Form Functions

Creating New Profile

Using the apps built-ins

Flexfields

Introduction to Key and Descriptive FIEXFIEDS

Enabling a DFF

Value sets

DFF Registration

KFF Registration

Define a Key FIEXFIELDS Structure

Invoking DFF Form of New Form

Invoking KFF Form of New Form

Form Customization Process

Customization of Standard Oracle Form

Customization of Custom.pll

Zooming Functionality.

Customization of PLL

Form Personalization Process

Compare Customization of Custom.pll with Form Personalization

The benefit of form personalization

Applying the form personalization in standard form in diff level.

Working with Menu, Zooming, calling Database Procedure, Function

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 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-QUERY   Indicates 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 )

IS

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 primary-foreign 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-to-many 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;