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;
No comments:
Post a Comment