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;
