PL/SQL Libraries, Trigger

PL/SQL Libraries

A library is a collection of PL/SQL program units, including procedures, functions, and packages. A single library can contain many program units that can be shared among the Oracle Developer modules and applications that need to use them.

A library:

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

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

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

v    Supports dynamic loading of program units

 FUNCTION locate_emp(bind_value IN NUMBER) RETURN VARCHAR2 IS

v_ename VARCHAR2(15);

BEGIN

SELECT ename INTO v_ename FROM emp WHERE empno = bind_value;

RETURN(v_ename);

END;

Reasons to share objects and code:

v     Increased productivity

v    Increased modularity

v    Decreased maintenance

v    Maintaining standards

.PLL PL/SQL Library Module Binary

.PLD PL/SQL Library Module Text

.PLX PL/SQL Library Module Executable

.MMB Menu Module Binary

.MMT Menu Module Text

.MMX Menu Module Executable

Form Builder Built-in Package

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

VBX

Provides built-ins for controlling and interacting with VBX controls; this package works only in a 16-bit environment and is provided for backward compatibility

WEB

Provides built-ins for the Web environment

OLE2

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

SUBPROGRAM

A subprogram can be either a procedure or a function. Built-in subprograms are therefore called in two distinct ways:

v    Built-in procedures:

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

v    Built-in functions:

Called as part of a statement, in a trigger or program unit, at a position where the function’s return value will be used. Again, the function call must include any mandatory arguments.

TRIGGER

Triggers are blocks of PL/SQL code that are written to perform tasks when a specific event occurs within an application. In effect, a Form Builder trigger is an event-handler written in PL/SQL to augment (or occasionally replace) the default processing behavior. Every trigger has a name, and contains one or more PL/SQL statements. A trigger encapsulates PL/SQL code so that it can be associated with an event and executed and maintained as a distinct object.

Trigger Scope

1.Form Level

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

2.Block Level

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

3.Item Level

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

Trigger Properties

Execution Style

Execution Hierarchy property

Specifies how the current trigger code should execute if there is a trigger with the same name defined at a higher level in the object hierarchy.

The following settings are valid for this property:

Override

Specifies that the current trigger fire instead of any trigger by the same name at any higher scope. This is known as "override parent" behavior.

Before

Specifies that the current trigger fire before firing the same trigger at the next-higher scope. This is known as "fire before parent" behavior.

After

Specifies that the current trigger fire after firing the same trigger at the next-higher scope. This is known as "fire after parent" behavior.

What are triggers used for?

v    Validate data entry

v    Protect the database from operator errors

v    Limit operator access to specified forms

v    Display related field data by performing table lookups

v    Compare values between fields in the form

v    Calculate field values and display the results of those calculations

v    Perform complex transactions, such as verifying totals

v    Display customized error and information messages to the operator

v    Alter default navigation

v    Display alert boxes

v    Create, initialize, and increment timers

 Groups of triggers

GROUP	FUNCTION
When-triggers	Execute in addition to default processing
On-triggers	Replace default processing
Pre- and Post-triggers	Add processing before or after an event
Key-trigger	Change default processing assigned to a specific key

Open Oracle Form 10g:

Navigation : Start Menu -> All Programs -> Oracle Developer Suite – 10g -> Form Developer -> Form Builder

Object Groups, Object Library

OBJECT GROUPS

An object group is a container for a group of objects. You define an object group when you want to package related objects so you can copy or subclass them in another module. Object groups provide a way to bundle objects into higher-level building blocks that can be used in other parts of an application and in subsequent development projects.

You define an object group when you want to package related objects for copying or sub classing in another module. You can use object groups to bundle numerous objects into higher-level building blocks that you can use again in another application.

Using Object Groups

v    Blocks include:

Items

Item-level triggers

Block-level triggers

Relations

v    Object groups cannot include other object groups

v    Deleting:

An object group does not affect the objects

An object affects the object group

Copying an Object

Copying an object creates a separate, unique version of that object in the target module. Any objects owned by the copied object are also copied.

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

v    Changes made to a copied object in the source module do not affect the copied object in the target module.

Subclassing

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

v    Inheriting the characteristics of a base class (Inheritance)

v    Overriding properties of the base class (Specialization)

OBJECT LIBRARY

This object provides an easy method of reusing objects and enforcing standards across the entire development organization.

You can use the Object Library to create, store, maintain, and distribute standard and reusable objects.

In addition, by using Object Libraries, you can rapidly create applications by dragging and dropping predefined objects to your form.

v    Is a convenient container of objects for reuse

v    Simplifies reuse in complex environments

v    Supports corporate, project, and personal standards

v    Simplifies the sharing of reusable components

Object libraries are convenient containers of objects for reuse. They simplify reuse in complex environments, and they support corporate, project, and personal standards.

An object library can contain simple objects, property classes, object groups, and program units, but they are protected against change in the library. Objects can be used as standards (classes) for other objects.

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

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

v    Reuse complex objects such as a Navigator

Benefits of the Object Library

v    Simplifies the sharing and reuse of objects

v    Provides control and enforcement of standards

v    Eliminates the need to maintain multiple referenced forms

SMARTCLASS

A SmartClass is a special member of an Object Library. Unlike other Object Library members, it can be used to subclass existing objects in a form using the SmartClass option from the right mouse button popup menu. Object Library members which are not SmartClasses can only be used to create new objects in form modules into which they are added.

 If you frequently use certain objects as standards, such as standard buttons, date items, and alerts, you can mark them as SmartClasses by selecting each object in the object library and choosing Object—>SmartClass.

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

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

v    Can be applied easily and rapidly to existing objects

v    Can be defined in many object libraries

You can have many SmartClasses of a given object

Canvas, Window and Alert Definition

CANVAS

This object represents a background entity on which you place interface items, such as check boxes, radio groups, and text items. There are four types of canvas objects: Content, Stacked, Horizontal Toolbar, and Vertical Toolbar.

1.Content Canvas

The most common canvas type is the content canvas (the default type). A content canvas is the "base" view that occupies the entire content pane of the window in which it is displayed. You must define at least one content canvas for each window you create.

2.Stacked Canvas

A stacked canvas is displayed atop—or stacked on—the content canvas assigned to the current window. Stacked canvases obscure some part of the underlying content canvas, and often are shown and hidden programmatically. You can display more than one stacked canvas in a window at the same time.

3.Tab Canvas

A tab canvas—made up of one or more tab pages —allows you to group and display a large amount of related information on a single dynamic Form Builder canvas object. Like stacked canvases, tab canvases are displayed on top of a content canvas, partly obscuring it. Tab pages (that collectively comprise the tab canvas) each display a subset of the information displayed on the entire tab canvas.

4.Toolbar Canvas

A toolbar canvas often is used to create toolbars for individual windows. You can create two types of toolbar canvases: horizontal or vertical. Horizontal toolbar canvases are displayed at the top of a window, just under its menu bar, while vertical toolbars are displayed along the far left edge of a window.

Showing and hiding a canvas programmatically

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

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

WINDOW

A window is a container for all visual objects that make up a Form Builder application, including canvases. A single form can include any number of windows. While every new form automatically includes a default window named WINDOW1, you can create additional windows as needed by inserting them under the Windows node in the Object Navigator.

There are two window styles:

Document

Document Windows Document windows typically display the main canvases and work areas of your application where most data entry, and data retrieval is performed.

Dialog

Dialog Windows Dialog windows are free-floating, containers typically used for modal dialogs that require immediate user interaction.

Window Modality

1.Modal Windows

Modal windows are usually used as dialogs, and have restricted functionality compared to modeless windows. On some platforms, for example, end users cannot resize, scroll, or iconify a modal window. Modal windows are often displayed with a platform-specific border unique to modal windows. On some platforms, modal windows are "always-ontop" windows that cannot be layered behind modeless windows.

2. Modeless Windows

You can display multiple modeless windows at the same time, and end users can navigate freely among them (provided your application logic allows it). On most GUI platforms, you can layer modeless windows so that they appear either in front of or behind other windows.

Hide on Exit property

For a modeless window, determines whether Form Builder hides the window automatically when the end user navigates to an item in another window.

MDI and SDI windows

1. Multiple Document Interface

MDI applications display a default parent window, called the application window. All other windows in the application are either document windows or dialog windows. Document windows always are displayed within the MDI application window frame.

2. Single Document Interface

Although MDI is the default system of window management during Forms Runtime, Form Builder also provides support for an SDI root window on Microsoft Windows.REPLACE_CONTENT_VIEW built-in Replaces the content canvas currently displayed in the indicated window with a different content canvas.

REPLACE_CONTENT_VIEW (window_name VARCHAR2, view_name VARCHAR2);

** Built-in: REPLACE_CONTENT_VIEW

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

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

BEGIN

Replace_Content_View('employee_status','history');

END;

Trigger - Windows

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

ALERT

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

Use alerts to advise operators of unusual situations or to warn operators who are about to perform an action that might have undesirable or unexpected consequences.

There are three styles of alerts: Stop, Caution, and Note. Each style denotes a different level of message severity. Message severity is represented visually by a unique icon that displays in the alert window.

Record Group, LOV , Property Class, Visual Attributes and Editor

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.

  

LOV [ LIST OF VALUES ]

An LOV is a scrollable popup window that provides the end user with either a single or multi-column selection list. Default Key for LOV – F9

LOVs provide the following functionality:

v    LOVs can be displayed by end user request (when an LOV is available), when the end user navigates to a text item with an associated LOV, or programmatically, independent of any specific text item.

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

v    LOV values that are selected by the end user can be assigned to form items  according to the return items you designate.

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

v    LOV values are derived from record groups.

PROPERTY CLASS

This object is a named object that contains a list of properties and their associated settings. Once you create a property class you can base other objects on it. An object based on a property class can inherit the settings of any property in the class that is appropriate for that object.

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

There are 2 ways to creating property class

a. Object Navigator method.

b. Property Window method

Property class can not be change programmatically.

VISUAL ATTRIBUTES

Visual attributes are the font, color, and pattern properties that you set for form and menu objects that appear in your application's interface. Visual attributes can include the following properties:

 Font properties: Font Name, Font Size, Font Style, Font Width, Font Weight Color and pattern properties: Foreground Color, Background Color Fill Pattern, Charmode Logical Attribute, White on Black It can be changed dynamically.

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

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

Visual Attribute Types

1. Default

Setting the Visual Attribute Group property to Default specifies that that the object  should be displayed with default color, pattern, and font settings. When Visual Attribute Group is set to Default, the individual attribute settings reflect the current system defaults. The actual settings are determined by a combination of factors, including the type of object, the resource file in use, and the window manager.

2. Custom

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

3. Named

Setting the Visual Attribute Group property to a named visual attribute defined in the same module specifies that the object should use the attribute settings defined for the named visual attribute. A named visual attribute is a separate object in a form or menu module that defines a collection of visual attribute properties. Once you create a named visual attribute, you can apply it to any object in the same module, much like styles in a word processing program.

EDITOR

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

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

1. System Editor

The system editor to be used is defined by the FORMS60_EDITOR environment variable. The editor specified must use the ASCII text format. For information on environment variables and system editor availability, refer to the Form Builder documentation for your operating system.

FORMS60_EDITOR = C:\WINDOWS\NOTEPAD.EXE

2. Default Editor

Default editor is invoked at runtime, Form Builder determines its display size and position dynamically based on the size and position of the text item from which the editor was invoked.

3. User-Named Editor

A user-named editor has the same text editing functionality as the default editor. You create a user-named editor when you want to display the editor programmatically with SHOW_EDITOR, or when you want to specify custom editor attributes such as scroll bar and title.

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

The SHOW_EDITOR procedure displays a user-named editor at the specified display coordinates. SHOW_EDITOR takes message_in and message_out parameters that allow you to pass a text string in to the editor and to get the edited text string back when the operator accepts the editor.

Objects of Form and Block Definition

OBJECTS OF FORMS

     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.

     Items

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

     Canvas

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

     Frames

Frames are used to arrange items with in a block.

     Windows

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

     PL/SQL Code Block

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

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

  

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

     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.

     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.

 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