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