Wednesday, July 8, 2009

COMMIT_FORM built-in

Description

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.
If the end user has posted data to the database during the current Runform session, a call to the COMMIT_FORM built-in commits this data to the database.
Following a commit operation, Form Builder treats all records in all base-table blocks as if they are queried records from the database. Form Builder does not recognize changes that occur in triggers that fire during commit processing.

Syntax

PROCEDURE COMMIT_FORM;
Built-in Type restricted procedure
Enter Query Mode no


** Built-in: COMMIT_FORM
** Example: If there are records in the form to be
** committed, then do so. Raise an error if the
** commit was not successful.
*/
BEGIN
/*
** Force validation to happen first
*/
Enter;
IF NOT Form_Success THEN
RAISE Form_Trigger_Failure;
END IF;
/*
** Commit if anything is changed
*/
IF :System.Form_Status = 'CHANGED' THEN
Commit_Form;
/*
** A successful commit operation sets Form_Status back
** to 'QUERY'.
*/
IF :System.Form_Status <> 'QUERY' THEN
Message('An error prevented your changes from being
committed.');
Bell;
RAISE Form_Trigger_Failure;
END IF;
END IF;
END;

Example 2

/*
** Built-in: COMMIT_FORM
** Example: Perform Form Builder database commit during commit
** processing. Decide whether to use this Built-in
** or a user exit based on a global flag setup at
** startup by the form, perhaps based on a
**
** Trigger: On-Commit
*/
BEGIN
/*
** Check the global flag we set during form startup
*/
IF :Global.Using_Transactional_Triggers = 'TRUE' THEN
User_Exit('my_commit');
/*
** Otherwise, do the right thing.
*/
ELSE
Commit_Form;
END IF;
END;

EXECUTE_TRIGGER built-in

Description

EXECUTE_TRIGGER executes an indicated trigger.

Syntax

PROCEDURE EXECUTE_TRIGGER
(trigger_name VARCHAR2);
Built-in Type restricted procedure (if the user-defined trigger calls any restricted built-in subprograms)
Enter Query Mode yes
Note: EXECUTE_TRIGGER is not the preferred method for executing a user-named trigger: writing a user-named subprogram is the preferred method.

Parameters

trigger_name Specifies the name of a valid user-named trigger.

Usage Notes

Because you cannot specify scope for this built-in, Form Builder always looks for the trigger starting at the lowest level, then working up.
To execute a built-in associated with a key, use the DO_KEY built-in instead of EXECUTE_TRIGGER. For example, rather than:

Execute_Trigger ('KEY-NEXT-ITEM');

Use instead:

Do_Key('NEXT_ITEM');

DO_KEY built-in

Description

Executes the key trigger that corresponds to the specified built-in subprogram. If no such key trigger exists, then the specified subprogram executes. This behavior is analogous to pressing the corresponding function key.

Syntax

PROCEDURE DO_KEY
(built-in_subprogram_name VARCHAR2);
Built-in Type restricted procedure
Enter Query Mode yes

Parameters

built-in_subprogram_name Specifies the name of a valid built-in subprogram.

Built-in Key Trigger Associated Function Key
BLOCK_MENU Key-MENU [Block Menu]
CLEAR_BLOCK Key-CLRBLK [Clear Block]
CLEAR_FORM Key-CLRFRM [Clear Form]
CLEAR_RECORD Key-CLRREC [Clear Record]
COMMIT_FORM Key-COMMIT [Commit]
COUNT_QUERY Key-CQUERY [Count Query Hits]
CREATE_RECORD Key-CREREC [Insert Record]
DELETE_RECORD Key-DELREC [Delete Record]
DOWN Key-DOWN [Down]
DUPLICATE_ITEM Key-DUP-ITEM [Duplicate Item]
DUPLICATE_RECORD Key-DUPREC [Duplicate Record]
EDIT_TEXTITEM Key-EDIT [Edit]
ENTER Key-ENTER [Enter]
ENTER_QUERY Key-ENTQRY [Enter Query]
EXECUTE_QUERY Key-EXEQRY [Execute Query]
EXIT_FORM Key-EXIT [Exit/Cancel]
HELP Key-HELP [Help]
LIST_VALUES Key-LISTVAL [List]
LOCK_RECORD Key-UPDREC [Lock Record]
NEXT_BLOCK Key-NXTBLK [Next Block]
NEXT_ITEM Key-NEXT-ITEM [Next Item]
NEXT_KEY Key-NXTKEY [Next Primary Key Fld]
NEXT_RECORD Key-NXTREC [Next Record]
NEXT_SET Key-NXTSET [Next Set of Records]
PREVIOUS_BLOCK Key-PRVBLK [Previous Block]
PREVIOUS_ITEM Key-PREV-ITEM [Previous Item]
PREVIOUS_RECORD Key-PRVREC [Previous Record]
PRINT Key-PRINT [Print]
SCROLL_DOWN Key-SCRDOWN [Scroll Down]
SCROLL_UP Key-SCRUP [Scroll Up]
UP Key-UP [Up]


** Built-in: DO_KEY
** Example: Simulate pressing the [Execute Query] key.
*/
BEGIN
Do_Key('Execute_Query');
END;

EXIT_FORM built-in

Description

Provides a means to exit a form, confirming commits and specifying rollback action.

n In most contexts, EXIT_FORM navigates “outside” the form. If there are changes in the current form that have not been posted or committed, Form Builder prompts the operator to commit before continuing EXIT_FORM processing.

n If the operator is in Enter Query mode, EXIT_FORM navigates out of Enter Query mode, not out of the form.

n During a CALL_INPUT, EXIT_FORM terminates the CALL_INPUT function.

Syntax

PROCEDURE EXIT_FORM;
PROCEDURE EXIT_FORM
(commit_mode NUMBER);
PROCEDURE EXIT_FORM
(commit_mode NUMBER,
rollback_mode NUMBER);

Built-in Type restricted procedure
Enter Query Mode yes

Parameters

commit_mode ASK_COMMIT Form Builder prompts the operator to commit the changes during EXIT_FORM processing.
However, if RECORD_STATUS is INSERT but the record is not valid, Form Builder instead asks the operator if the form should be closed. If the operator says yes, the changes are rolled back before the form is closed.

DO_COMMIT Form Builder validates the changes, performs a commit, and exits the current form without prompting the operator.
NO_COMMIT Form Builder validates the changes and exits the current form without performing a commit or prompting the operator.
NO_VALIDATE Form Builder exits the current form without validating the changes, committing the changes, or prompting the operator.

rollback_mode TO_SAVEPOINT Form Builder rolls back all uncommitted changes (including posted changes) to the current form's savepoint.

FULL_ROLLBACK Form Builder 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 prevent losing the locks issued by the calling form, Form Builder prevents any commit processing in the called form.)
NO_ROLLBACK Form Builder 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 the locks across a NEW_FORM operation. These locks can also occur when running Form Builder from an external 3GL program. The locks remain in effect when Form Builder returns control to the program.

Usage Notes

Because the default parameters of EXIT_FORM are ASK_COMMIT for commit_mode and TO_SAVEPOINT for rollback_mode, invoking EXIT_FORM without specifying any parameters in some contexts may produce undesired results. For example, if the form is in POST only mode and EXIT_FORM is invoked without parameters, the user will be prompted to commit the changes. However, regardless of the user’s input at that prompt, the default rollback_mode of TO_SAVEPOINT rolls back the changes to the form despite a message confirming that changes have been made. To avoid conflicts explicitly specify parameters.

Diff Between Post – Change, When-Validate-Item, Post-Test-Item

Post-Test-Item” Trigger fires every time when focus moves from one item to another item.

“Post – Change”, ”When-Validate-Item” à Both Triggers Have the same functionality except
that Post-Change doesn’t fire in one case when existing value is changed to NULL While in that case When-Validate-Item trigger fires. And all other functionality of these both triggers are same in the manner that both fires when the status of the item becomes ‘CHANGED’. If status remains ‘UNCHANGED’ then these both trigger doesn’t fires.

Key-next-item à Fires every time with the keyboard stroke.

Pre-Logon trigger

Pre-Logon trigger

Description

Fires just before Form Builder initiates a logon procedure to the data source.
Definition Level form

Legal Commands

SELECT statements, unrestricted built-ins
Enter Query Mode no

Usage Notes

Use a Pre-Logon trigger to prepare the form for the logon procedure, particularly to a non-ORACLE data source.

On Failure

The results of a failure depend on which of the following conditions applies:

n If Form Builder is entering the form for the first time and the trigger fails, the form is exited gracefully, but no other triggers are fired.

n If the trigger fails while Form Builder is attempting to execute the LOGON built-in from within the trigger, Form Builder raises the FORM_TRIGGER_FAILURE exception.

Tuesday, July 7, 2009

OTHERS


1. When-New-Record-Instance/When-Validate-Record

When-New-Record-Instance
Perform an action immediately after the input focus moves to an item in a different record. If the new record is in a different block, fires after the When-New-Block-Instance trigger, but before the When-New-Item-Instance trigger.
Specifically, it fires after navigation to an item in a record, when Form Builder is ready to accept input in a record that is different than the record that previously had input focus.
Use a When-New-Record-Instance trigger to perform an action every time
Form Builder instantiates a new record. For example, when an operator presses [Down] to scroll through a set of records, Form Builder fires this trigger each time the input focus moves to the next record, in other words, each time Form Builder instantiates a new record in the block.
When-Validate-Record
It Augments default validation of a record. Fires during the Validate the Record process. Specifically, it fires as the last part of record validation for records with the New or Changed validation status.
Use a When-Validate-Record trigger to supplement Form Builder default record validation processing.
Note that it is possible to write a When-Validate-Record trigger that changes the value of an item in the record that Form Builder is validating. If validation succeeds, Form Builder marks the record and all of the fields as Valid and does not re-validate. While this behavior is necessary to avoid validation loops, it does make it possible for your application to commit an invalid value to the database.
On Failure
If fired as part of validation initiated by navigation, navigation fails, and the focus remains on the original item.
2. Difference in Tabular & Form types of reports

Tabular Means that labels are above fields, and are outside the repeating frame containing the fields.

Form Means that labels are to the left of fields, and are inside the repeating frame containing the fields.

3. Post-Text-Item/When-Validate-Item

Post-Text-Item
It manipulates an item when Form Builder leaves a text item and navigates to the record level. Fires during the Leave the Item process for a text item. Specifically, this trigger fires when the input focus moves from a text item to any other item.
Use a Post-Text-Item trigger to calculate or change item values. It Fires every time On Failure Navigation fails and focus remains in the text item.
When-Validate-Item
It augments default validation of an item.
Fires during the Validate the Item process. Specifically, it fires as the last part of item validation for items with the New or Changed validation status.
Use a When-Validate-Item trigger to supplement Form Builder default item validation processing.
It is possible to write a When-Validate-Item trigger that changes the value of an item that Form Builder is validating. If validation succeeds, Form Builder marks the changed item as Valid and does not re-validate it. While this behavior is necessary to avoid validation loops, it does make it possible for your application to commit an invalid value to the database. On failure if fired as part of validation initiated by navigation, navigation fails, and the focus remains on the original item.

On particular condition disable whole record. How
Multiple Values in one item
WHEN/CALL Clause in Triggers

The WHEN trigger_condition specifies the conditions that must be met for the trigger to fire. Stored functions and object methods are not allowed in the trigger condition.
Optionally, a trigger restriction can be included in the definition of a row trigger by specifying a Boolean SQL expression in a WHEN clause (a WHEN clause cannot be included in the definition of a statement trigger).
If included, the expression in the WHEN clause is evaluated for each row that the trigger affects.
If the expression evaluates to TRUE for a row, the trigger body is fired on behalf of that row.
However, if the expression evaluates to FALSE or NOT TRUE (that is, unknown, as with nulls) for a row, the trigger body is not fired for that row.
The evaluation of the WHEN clause does not have an effect on the execution of the triggering SQL statement (that is, the triggering statement is not rolled back if the expression in a WHEN clause evaluates to FALSE).
For example, in the PRINT_SALARY_CHANGES trigger, the trigger body would not be executed if the new value of EMPNO is zero, NULL, or negative.
In more realistic examples, you might test if one column value is less than another.
The expression in a WHEN clause of a row trigger can include correlation names, which are explained below.
The expression in a WHEN clause must be a SQL expression and cannot include a subquery.
You cannot use a PL/SQL expression (including user-defined functions) in the WHEN clause.

Call Clause is used for calling a Procedure in a Trigger Body

Sequence of trigger when Form is opened closed

Run Form
1. Pre-Logon Form
2. On-Logon Form
3. Post-Logon Form
4. Pre-Form Form
5. When-Create-Record Block
6. Pre-Block Block
7. Pre-Record Block
8. Pre-Text-Item Item
9. When-New-Form-Instance Form
10. When-New-Block-Instance Block
11. When-New-Record-Instance Block
12. When-New-Item-Instance Item
Exit
1. Post-Text-Item Item
2. Post-Record Block
3. Post-Block Block
4. Post-Form Form
5. On-Rollback Form
6. Pre-Logout Form
7. On-Logout Form
8. Post-Logout Form

1. Sequence of trigger when Navigation from one item to another

· Key-next -1
· Post Change –1
· When validate -1
· Post text -1
· Pre – text - 2
· When new item instance - 2


2. Database Link
A database link is a named object that describes a "path" from one database to another. Types are Private Database Link, Public Database Link & Network Database Link.
Private database link is created on behalf of a specific user. A private database link can be used only when the owner of the link specifies a global object name in a SQL statement or in the definition of the owner's views or procedures.
Public database link is created for the special user group PUBLIC. A public database link can be used when any user in the associated database specifies a global object name in a SQL statement or object definition.
Network database link is created and managed by a network domain service. A network database link can be used when any user of any database in the network specifies a global object name in a SQL statement or object definition.

Different Optimization Techniques

Optimizer:
Optimization is the process of choosing the most efficient way to execute an SQL statement. When tuning SQL, it’s very important to understand WHY and WHEN a particular type of optimization should be used.
When a SQL Select, Update, Insert or Delete statement is processed, Oracle must access the data referenced by the statement. The Optimizer portion of Oracle is used to determine an efficient path to reference data so that minimal I/O and Processing time is required. For statement execution the Optimizer formulates execution plans and chooses the most efficient plan before executing a statement.
The Optimizer uses one of two techniques:
Rule Based Approach: Using the rule-based approach, the optimizer chooses an execution plan based on the access paths available and the ranks of these access paths. If there is more than one way to execute an SQL statement, the rule-based approach always uses the operation with the lower rank. Usually, operations of lower rank execute faster than those associated with constructs of higher rank. The rule-based optimizer is no longer being enhanced by Oracle. The rule-based optimizer (RBO) Uses a fixed ranking, therefore, it’s essential to list the correct Table Order in the FROM clause. Normally, the (LAST or Right Most) Table listed in the FROM clause is the driving table. The data distribution and number of records is not taken into account. The RBO execution plan can’t be changed by hints.




Cost Based Approach: Using the cost-based approach, the optimizer determines which execution plan is most efficient by considering available access paths and factoring in information based on statistics in the data dictionary for the schema objects (tables, clusters or indexes) accessed by the statement. The cost-based approach also considers hints, or optimization suggestions placed in a Comment in the statement. By default, the goal of the cost-based approach is the best throughput, or minimal resource use necessary to process all rows accessed by the statement. The cost-based approach uses statistics to estimate the cost of each execution plan. These statistics quantify the data distribution and storage characteristics of tables, columns, indexes, and partitions. These statistics are generated by using the ANALYZE command. The cost-based optimizer was built to make tuning easier by choosing the best paths for poorly written queries. Normally, the (FIRST or Left Most) Table listed in the FROM clause is the driving table. Just opposite of RBO. The CBO decisions are only as good as the type and frequency of analyzes made on the Table and associated Indexes. To Tune SQL Queries using CBO you must also have an understanding of Table Access Methods, Table Join Methods and Hints.
The DRIVING Table


In Oracle the cost-based approach uses various factors in determining which table should be the Driving Table (the table that has the most “restricted” rows). It is the table that drives the query in a multi-table join query. The Driving Table should be listed First in Cost Based and listed Last in Rule Based Optimization. The best thing to remember is that you have control over which table will drive a query through the effective use of the ORDERED hint when using the CBO. The easiest way to determine if your query is using the correct Driving Table is to set the SQL Select to find Zero records. If the return is slow you know the Driving Table is incorrect!

Candidate, Artificial (Derived) Primary Key, Unique Primary Key

Integrity Constraint
An integrity constraint is a declarative way to define a business rule for a column of a table. An integrity constraint is a statement about a table's data that is always true and that follows these rules:
· If an integrity constraint is created for a table and some existing table data does not satisfy the constraint, then the constraint cannot be enforced.
· After a constraint is defined, if any of the results of a DML statement violate the integrity constraint, then the statement is rolled back, and an error is returned.
Integrity constraints are defined with a table and are stored as part of the table's definition in the data dictionary, so that all database applications adhere to the same set of rules. When a rule changes, it only needs be changed once at the database level and not many times for each application.
Candidate key:
A relation (table) may contain more than one key. In this case, one of the keys is selected as the primary key and the remaining keys are called candidate keys. The candidate key constraint indicates that the table cannot contain two different rows with the same values under candidate key columns.

Artificial (Derived) Primary Key
A derived key comes from a sequence. Usually it is used when a concatenated key becomes too cumbersome to use as a foreign key.

UNIQUE Constraints
You can use UNIQUE constraints to ensure that no duplicate values are entered in specific columns that do not participate in a primary key. Although both a UNIQUE constraint and a PRIMARY KEY constraint enforce uniqueness, use a UNIQUE constraint instead of a PRIMARY KEY constraint when you want to enforce the uniqueness of a column, or combination of columns, that is not the primary key.
Multiple UNIQUE constraints can be defined on a table, whereas only one PRIMARY KEY constraint can be defined on a table.

FOREIGN KEY Constraints
A foreign key (FK) is a column or combination of columns used to establish and enforce a link between the data in two tables. A link is created between two tables by adding the column or columns that hold one table's primary key values to the other table. This column becomes a foreign key in the second table.

PRIMARY KEY Constraints
A table usually has a column or combination of columns whose values uniquely identify each row in the table. This column (or columns) is called the primary key of the table and enforces the entity integrity of the table. A table can have only one PRIMARY KEY constraint, and a column that participates in the PRIMARY KEY constraint cannot accept null values.

Procedure/Function (Difference)

Procedure

A procedure is a subprogram that performs a specific action
Procedure Does and Does not return the Value.
Procedure we can use (In, Out, InOut Parameter)
You cannot use the procedure in select Statement.
Execute as a PL/SQL statement
No RETURN clause in the header
Can return none, one, or many values

Function

A function is a subprogram that computes a value
Invoke as part of an expression
Must contain a RETURN clause in the header
Must return a single value
Must contain at least one RETURN statement
Always return the Value.
Function you cannot use the (In, Out, InOut Parameter)
You can use the Function the in select Statement.

Bitmap/B-Tree Index (Difference, Advantages)

A traditional B-Tree (balanced tree) index stores the key values and pointers in an inverted tree structure. The key to good B-Tree index performance is to build the index on columns having a lot of different values. Oracle describes this as "good selectivity" Oracle is able to quickly bypass rows that do not meet the search criteria when searching through indexes built on columns having a high degree of selectivity.
Conversely, bitmapped indexes perform better when the selectivity of an index is poor. The fewer different values a bitmapped index contains, the better it will perform.
Bitmap indexes, in certain situations, can provide impressive performance benefits. Bitmapped indexes are most appropriate for complex and ad-hoc queries that contain lengthy WHERE clauses on columns that have a limited number of different values (poor selectivity).

Standard B-tree indexes are most effective for columns containing a high number of different values (good selectivity) and bitmapped indexes are most appropriate for columns with a limited number (poor selectivity) of possible values.

Autonomous Transactions

Autonomous Transaction is a new feature in ORACLE. It allows setting up independent transactions that can be called from within other transactions. It lets you suspend the main transaction (without committing or rolling back), perform some DML operations, commit or roll back those operations (without any effect on the main transaction), and then return to the main transaction.
Being independent of the main transaction (almost like a separate session), an autonomous transaction does not see the uncommitted changes from the main transaction. It also does not share locks with the main transaction. As a result, it can get into a deadlock with its parent … something the application developer should watch out for.
As expected, changes committed by an autonomous transaction are visible to other sessions/transactions immediately, regardless of whether the main transaction is committed or not. These changes also become visible to the main transaction when it resumes, provided its isolation level is set to READ COMMITTED (which is the default).

Any of the routines can be marked as autonomous simply by using the following syntax anywhere in the declarative section of the routine (putting it at the top is recommended for better readability):
E.g.
PRAGMA AUTONOMOUS_TRANSACTION;
Here is an example of defining a stored procedure as autonomous:
CREATE PROCEDURE process_ord_line_shipment
(p_order_no number, p_line_no number) AS
PRAGMA AUTONOMOUS_TRANSACTION;
l_char_1 varchar2(100);
BEGIN
...END;

Normalization / De-Normalization

Normalization: It's the process of efficiently organizing data in a database. There are two goals of the normalization process: eliminate redundant data (for example, storing the same data in more than one table) and ensure data dependencies make sense (only storing related data in a table). Both of these are worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored.
1. Eliminate Repeating Groups - Make a separate table for each set of related attributes, and give each table a primary key.
2. Eliminate Redundant Data - If an attribute depends on only part of a multi-valued key, remove it to a separate table.
3. Eliminate Columns Not Dependent On Key - If attributes do not contribute to a description of the key, remove them to a separate table.
4. Isolate Independent Multiple Relationships - No table may contain two or more 1:n or n:m relationships that are not directly related.
5. Isolate Semantically Related Multiple Relationships - There may be practical constrains on information that justify separating logically related many-to-many relationships.
1st Normal Form (1NF)
Def: A table (relation) is in 1NF if
1. There are no duplicated rows in the table.
2. Each cell is single-valued (i.e., there are no repeating groups or arrays).
3. Entries in a column (attribute, field) are of the same kind.
Note: The order of the rows is immaterial; the order of the columns is immaterial.
Note: The requirement that there be no duplicated rows in the table means that the table has a key (although the key might be made up of more than one column—even, possibly, of all the columns).
2nd Normal Form (2NF)
Def: A table is in 2NF if it is in 1NF and if all non-key attributes are dependent on all of the key.
Note: Since a partial dependency occurs when a non-key attribute is dependent on only a part of the (composite) key, the definition of 2NF is sometimes phrased as, "A table is in 2NF if it is in 1NF and if it has no partial dependencies."
3rd Normal Form (3NF)
Def: A table is in 3NF if it is in 2NF and if it has no transitive dependencies.
Boyce-Codd Normal Form (BCNF)
Def: A table is in BCNF if it is in 3NF and if every determinant is a candidate key.
4th Normal Form (4NF)
Def: A table is in 4NF if it is in BCNF and if it has no multi-valued dependencies.
5th Normal Form (5NF)
Def: A table is in 5NF, also called "Projection-Join Normal Form" (PJNF), if it is in 4NF and if every join dependency in the table is a consequence of the candidate keys of the table.
Domain-Key Normal Form (DKNF)
Def: A table is in DKNF if every constraint on the table is a LOGICAL consequence of the definition of keys and domains.

De-Normalization:
Denormalization is a technique to move from higher to lower normal forms of database modeling in order to speed up database access. You may apply Denormalization in the process of deriving a physical data model from a logical form.

Property Class Visual Attributes (Difference)

A property class is a named object that contains a list of properties and their settings. Once you create a property class you can base other objects on it. An object based on a property class can inherit the setting of any property in the class that makes sense for that object. Property class inheritance is an instance of subclassing. Conceptually, you can consider a property class as a universal subclassing parent. Property classes are separate objects, and, as such, can be copied between modules as needed. Perhaps more importantly, property classes can be subclassed in any number of modules. Property class inheritance is a powerful feature that allows you to quickly define objects that conform to your own interface and functionality standards. Property classes also allow you to make global changes to applications quickly. By simply changing the definition of a property class, you can change the definition of all objects that inherit properties from that class.
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, Color and pattern properties. Every interface object has a Visual Attribute Group property that determines how the object's individual visual attribute settings (Font Size, Foreground Color, etc.) are derived
Named visual attributes define only font, color, and pattern attributes; property classes can contain these and any other properties.
You can change the appearance of objects at runtime by changing the named visual attribute programmatically; property class assignment cannot be changed programmatically.
When an object is inheriting from both a property class and a named visual attribute, the named visual attribute settings take precedence, and any visual attribute properties in the class are ignored.
Property Class has triggers and Visual Attributes don’t have same.

Inline Views Top N Analysis

The Inline view: It is a construct in Oracle SQL where you can place a query in the SQL FROM, clause, just as if the query was a table name.
A common use for in-line views in Oracle SQL is to simplify complex queries by removing join operations and condensing several separate queries into a single query.

Top N Analysis: The task of retrieving the top or bottom N rows from a database table. You can do so either by using the ROWNUM pseudocolumn available in several versions of Oracle or by utilizing new analytic functions available in Oracle 8i: RANK () and DENSE_RANK ().

Using the ROWNUM PseudocolumnOne-Way to solve this problem is by using the Oracle pseudocolumn ROWNUM. For each row returned by a query, the ROWNUM pseudocolumn returns a number indicating the order in which Oracle selects the row from a table or set of joined rows.
E.g. To select top 5 rows
SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM
(SELECT Empno, Ename, Job, Mgr, Hiredate, Sal
FROM Emp
ORDER BY NVL (Sal, 0) DESC)
WHERE ROWNUM <>

Types of Triggers in Forms

1. Types of Triggers in Forms

Block-processing triggers: - Block processing triggers fire in response to events related to record management in a block. E.g. When-Create-Record, When-Clear-Block, When-Database-Record, When-Remove-Record

Interface event triggers: - Interface event triggers fire in response to events that occur in the form interface. Some of these triggers, such as When-Button-Pressed, fire only in response to operator input or manipulation. Others, like When-Window-Activated, can fire in response to both operator input and programmatic control. E.g. When-Button-Pressed, When-Checkbox-Changed, Key- [all], When-Radio-Changed, When-Timer-Expired, When-Window-Activated, When-Window-Resized

Master-detail triggers: - Form Builder generates master-detail triggers automatically when you define a master-detail relation between blocks. The default master-detail triggers enforce coordination between records in a detail block and the master record in a master block. Unless you are developing your own custom block-coordination scheme, you do not need to define these triggers yourself. Instead, simply create a relation object, and let Form Builder generate the triggers required to manage coordination between the master and detail blocks in the relation. E.g. On-Check-Delete-Master, On-Clear-Details, On-Populate-Details

Message-handling triggers: - Form Builder automatically issues appropriate error and informational messages in response to runtime events. Message handling triggers fire in response to these default messaging events. E.g. On-Error, On-Message

Navigational triggers: - Navigational triggers fire in response to navigational events. For instance, when the operator clicks on a text item in another block, navigational events occur as Form Builder moves the input focus from the current item to the target item. Navigational events occur at different levels of the Form Builder object hierarchy (Form, Block, Record, Item). Navigational triggers can be further sub-divided into two categories: Pre- and Post- triggers, and When-New-Instance triggers. Pre- and Post- triggers fire as Form Builder navigates internally through different levels of the object hierarchy. As you might expect, these triggers fire in response to navigation initiated by an operator, such as pressing the [Next Item] key. However, be aware that these triggers also fire in response to internal navigation that Form Builder performs during default processing. To avoid unexpected results, you must consider such internal navigation when you use these triggers. E.g. Pre-Form, Pre-Block, Pre-Text-Item, Post-Text-Item, Post-Record, Post-Block, Post-Form
When-New-Instance triggers fire at the end of a navigational sequence that places the input focus in a different item. Specifically, these triggers fire just after Form Builder moves the input focus to a different item, when the form returns to a quiet state to wait for operator input. Unlike the Pre- and Post- navigational triggers, the When-New-Instance triggers do not fire in response to internal navigational events that occur during default form processing. E.g. When-New-Form-Instance, When-New-Block-Instance, When-New-Record-Instance, When-New-Item-Instance

Query-time triggers: - Query-time triggers fire just before and just after the operator or the application executes a query in a block. E.g. Pre-Query, Post-Query

Transactional triggers: - Transactional triggers fire in response to a wide variety of events that occur as a form interacts with the data source. E.g. On-Delete, On-Insert, On-Lock, On-Logon, On-Update, Post-Database-Commit, Post-Delete, Post-Forms-Commit, Post-Insert, Post-Update, Pre-Commit, Pre-Delete, Pre-Insert, Pre-Update

Validation triggers: - Validation triggers fire when Form Builder validates data in an item or record. Form Builder performs validation checks during navigation that occurs in response to operator input, programmatic control, or default processing, such as a Commit operation. E.g. When-Validate-Item, When-Validate-Record

2. Sequence of Trigger Fire while Committing

Ø KEY Commit
Ø Pre Commit
Ø Pre/On/Post Delete
Ø Pre/On/Post Update
Ø Pre/On/Post Insert
Ø On commit
Ø Post Database Commit

3. Master-Detail Relation (Triggers/Procedures/Properties)

On-Check-Delete-Master: - Fires when Form Builder attempts to delete a record
in a block that is a master block in a master-detail relation.
On-Clear-Details: - Fires when Form Builder needs to clear records in a block
that is a detail block in a master-detail relation because those records no longer
correspond to the current record in the master block.
On-Populate-Details: - Fires when Form Builder needs to fetch records into a block that is the detail block in a master-detail relation so that detail records are synchronized with the current record in the master block.
(i) Isolated: - Masters Can be deleted when Child is existing
Triggers: - On Populate details Block
On Clear Details Form
Procedure
Check Package Failure
Clear all master Detail
Query Master Detail
(ii) Non- Isolated: - Masters Cannot be deleted when Child is existing.
Triggers: - On Populate details Block
On Check Delete master Block
On Clear Details Form
Procedure
Check Package Failure
Clear all master Detail
Query Master Detail
(iii) Cascading: - Child Record Automatically Deleted when Masters is deleted.
Triggers: - On Populate details Block
Pre Delete Block
On Clear Details Form
Procedure
Check Package Failure
Clear all master Detail
Query Master Detail

4. Dynamically create LOV/List Item

You can also add list elements individually at runtime by using the ADD_LIST_ELEMENT built-in subprogram, or you can populate the list from a record group at runtime using the POPULATE_LIST built-in. If you populate the list from a record group, be sure that the record group you are using to populate the list contains the relevant values before you call POPULATE_LIST. If the record group is a static record group, it will already contain the appropriate values. Otherwise, you should populate the group at runtime using one of the record group subprograms.

5. Object Libraries (Use/Benefits)

The Object Library provides an easy method of reusing objects and enforcing standards across the entire development organization.
Object Library can be used to:

1. Create, store, maintain, and distribute standard and reusable objects.
2. Rapidly create applications by dragging and dropping predefined objects to your form.

There are several advantages to using object libraries to develop applications:

1. Object libraries are automatically re-opened when you startup Form Builder, making your reusable objects immediately accessible.
2. You can associate multiple object libraries with an application. For example, you can create an object library specifically for corporate standards, and you can create an object library to satisfy project-specific requirements.
3. Object libraries feature Smart Classes-- objects that you define as being the standard. You use Smart Classes to convert objects to standard objects.

6. Key-next/Post-Text (Difference)

Post-Text–Item: Fires during the Leave the Item process for a text item. Specifically, this trigger fires when the input focus moves from a text item to any other item.

Key-Next-Item: The key-next is fired as a result of the key action. Key next will not fire unless there is a key event.

7. Call From/New Form/Open Form (Difference)

Call Form: Runs an indicated form while keeping the parent form active. Form Builder runs the called form with the same Runform preferences as the parent form. When the called form is exited Form Builder processing resumes in the calling form at the point from which you initiated the call to CALL_FORM.
PROCEDURE CALL_FORM (formmodule_name VARCHAR2, display NUMBER, switch_menu NUMBER, query_mode NUMBER, data_mode NUMBER, paramlist_name/id VARCHAR2);

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, Form Builder keeps the higher call active and treats it as a call to the new form. Form Builder releases memory (such as database cursors) that the terminated form was using.
Form Builder runs the new form with the same Runform options as the parent form. If the parent form was a called form, Form Builder runs the new form with the same options as the parent form.
PROCEDURE NEW_FORM (formmodule_name VARCHAR2, rollback_mode NUMBER, query_mode NUMBER, data_mode NUMBER, paramlist_name/id VARCHAR2);

Open Form: Opens the indicated form. Use OPEN_FORM to create multiple-form applications, that is, applications that open more than one form at the same time.
PROCEDURE OPEN_FORM (form_name VARCHAR2, activate_mode NUMBER, session_mode NUMBER, data_mode NUMBER, paramlist_id/name PARAMLIST);

8. Types of Canvases (Stacked/Content Difference)

(i) Content Canvas (Default Canvas) [A content canvas is the required on each window you create]
(ii) Stack Canvas [you can display more then one stack canvas in a window at the same time]
(iii) Tab Type Window [In Tab canvas that have tab pages and have one or more then tab page]
(iv) Toolbar Canvas [A toolbar canvas often is used to create Toolbar Windows. There are two type of Toolbar window.
a. Horizontal Toolbar Canvas: - Horizontal Toolbar canvases are displayed at the top of the window, Just Under the Main Menu Bar.
b. Vertical Toolbar Canvas: - While vertical Toolbar are displayed along the Left Edge of the window.

9. Object Groups (Use)

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. For example, you might build an appointment scheduler in a form and then decide to make it available from other forms in your applications. The scheduler would probably be built from several types of objects, including a window and canvas, blocks, and items that display dates and appointments, and triggers that contain the logic for scheduling and other functionality. If you packaged these objects into an object group, you could then copy them to any number of other forms in one simple operation.
You can create object groups in form and menu modules. Once you create an object group, you can add and remove objects to it as desired.

10. Various Block Co-ordination Properties

The various Block Coordination Properties are
a) Immediate
Default Setting. The Detail records are shown when the Master Record are shown.
b) Deffered with Auto Query
Oracle Forms defer fetching the detail records until the operator navigates to the detail block.
c) Deferred with No Auto Query
The operator must navigate to the detail block and explicitly execute a query




11. How to attach same LOV to multiple items

We can use the same LOV for 2 columns by passing the return values in global values and using the global values in the code.

12. Report Level Triggers (Sequence)

· Before parameter form
· After parameter form
· Before Report
· Between Pages
· After Report

13. Static & Dynamic LOV

The static LOV contains the predetermined values while the dynamic LOV contains values that come at run time

14. Format Triggers (What are they)

A format trigger is a PL/SQL function executed before an object is formatted. A trigger can be used to dynamically change the formatting attributes of the object.

15. Flex & Confine Mode in Reports
Confine mode:
a. Switched on by default; change via View® View Options® Layout...
b. It prevents operations which would cause a report not to work e.g. moving a field outside its parent repeating frame
Flex mode:
Moves the object it’s enclosing objects and objects in their push path simultaneously to maintain the same overall relationship in the report. E.g. if you try to move a field outside its repeating frame, the Repeating Frame will grow to accommodate the field and so will any objects around the repeating frame.
Only one object can be moved/resized at one time in flex mode - if you try more than one only one whose control point is clicked on will be done, the other objects will be de-selected.
Objects can be moved/resized horizontally or vertically; not diagonally.
16. Matrix Reports (Matrix By Groups)

A matrix (cross tab) report contains one row of labels, one column of labels, and information in a grid format that is related to the row and column labels. A distinguishing feature of matrix reports is that the number of columns is not known until the data is fetched from the database.
To create a matrix report, you need at least four groups: one group must be a cross-product group, two of the groups must be within the cross-product group to furnish the "labels," and at least one group must provide the information to fill the cells. The groups can belong to a single query or to multiple queries.

A matrix with group report is a group above report with a separate matrix for each value of the master group.

A nested matrix (crosstab) report is a matrix report in which at least one parent/child relationship appears within the matrix grid.
The new Child Dimension property of the nested group enables you to eliminate empty rows and/or columns in your single-query nested matrix.

Types of Matrix Reports
Simple Matrix Report:
Is a matrix with only two dimensions

Nested Matrix Report: Has multiple dimensions going across and/or down the page
Multi-Query Matrix with Break: Is similar to a nested matrix report in that it has more than two dimensions. Does not display records that do not contain data
Matrix Break Reports: Contains a new matrix for each master record


17. Lexical & Bind Parameters in Reports

Lexical Parameters: Lexical references are placeholders for text that you embed in a SELECT statement. You can use lexical references to replace the clauses appearing after SELECT, FROM, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH.
You cannot make lexical references in a PL/SQL statement. You can, however, use a bind reference in PL/SQL to set the value of a parameter that is then referenced lexically in SQL. Look at the example below.
You create a lexical reference by entering an ampersand (&) followed immediately by the column or parameter name. A default definition is not provided for lexical references. Therefore, you must do the following:
Ø Before you create your query, define a column or parameter in the data model for each lexical reference in the query. For columns, you must enter Value if Null, and, for parameters, you must enter Initial Value. Report Builder uses these values to validate a query with a lexical reference.
Ø Create your query containing lexical references.

Bind Parameters: Bind references (or bind variables) are used to replace a single value in SQL or PL/SQL, such as a character string, number, or date. Specifically, bind references may be used to replace expressions in SELECT, WHERE, GROUP BY, ORDER BY, HAVING, CONNECT BY, and START WITH clauses of queries. Bind references may not be referenced in FROM clauses or in place of reserved words or clauses.
You create a bind reference by entering a colon (:) followed immediately by the column or parameter name. If you do not create a column or parameter before making a bind reference to it in a SELECT statement, Report Builder will create a parameter for you by default.

18. Column Mode Property in Reports

The Column Mode property controls how Report Builder fetches and formats data for instances of repeating frames. With Column Mode set to Yes, the next instance of a repeating frame can begin formatting before the previous instance is completed. With Column Mode set to No, the next instance cannot begin formatting before the previous instance is completed. Column Mode is used mainly for master repeating frames or repeating frames that contain fields that may expand vertically or horizontally (i.e., elasticity is Variable or Expand).

19. Diff b/w Package Spec & Body

Packages provide a method of encapsulating and storing related procedures, funtions and other package constructs as a unit in the database. They offer increased functionality (for example, global package variables can be declared and used by any procedure in the package). They also improve performance (for example, all objects of the package are parsed, compiled, and loaded into memory once).
Package specification contains declarations of public constructs where as the package body contains definitions of all those public constructs and declarations & definitions of private constructs.

20. P/L SQL Tables / Arrays

PL/SQL tables are declared in the declaration portion of the block. A table is a composite datatype in PL/SQL. PL/SQL tables can have one column and a primary key neither of which can be named. The column can be any scalar type but primary key should be a BINARY_INTEGER datatype.
Rules for PL/SQL Tables:
1. A loop must be used to insert values into a PL/SQL Table
2. You cannot use the Delete command to delete the contents of PL/SQL Table. You must assign an empty table to the PL/SQL table being deleted.



21. Various Cursor Attributes

SQL%ROWCOUNT: Number of rows affected by most recent SQL statement.
SQL%FOUND: Boolean attribute that evaluates to TRUE if most recent SQL statement affects one or more rows.
SQL%NOTFOUND: Boolean attribute that evaluates to TRUE if most recent SQL statement does not affect any row.
SQL%ISOPEN: Always evaluates to FALSE because P/L SQL closes implicit cursors immediately after they are executed.

22. Different Database Triggers

Database triggers are PL/SQL, Java, or C procedures that run implicitly whenever a table or view is modified or when some user actions or database system actions occur. Database triggers can be used in a variety of ways for managing your database. For example, they can automate data generation, audit data modifications, enforce complex integrity constraints, and customize complex security authorizations.
Row Triggers
A row trigger is fired each time the table is affected by the triggering statement.
For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement.
If a triggering statement affects no rows, a row trigger is not executed at all.
Row triggers are useful if the code in the trigger action depends on data provided by the triggering statement or rows that are affected.
Statement Triggers
A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects (even if no rows are affected).
For example, if a DELETE statement deletes several rows from a table, a statement-level DELETE trigger is fired only once, regardless of how many rows are deleted from the table.
Statement triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected.
For example, if a trigger makes a complex security check on the current time or user, or if a trigger generates a single audit record based on the type of triggering statement, a statement trigger is used.

BEFORE vs. AFTER Triggers
When defining a trigger, you can specify the trigger timing.
That is, you can specify whether the trigger action is to be executed before or after the triggering statement.
BEFORE and AFTER apply to both statement and row triggers.
BEFORE Triggers BEFORE triggers execute the trigger action before the triggering statement. This type of trigger is commonly used in the following situations:
BEFORE triggers are used when the trigger action should determine whether the triggering statement should be allowed to complete. By using a BEFORE trigger for this purpose, you can eliminate unnecessary processing of the triggering statement and its eventual rollback in cases where an exception is raised in the trigger action.
BEFORE triggers are used to derive specific column values before completing a triggering INSERT or UPDATE statement.

AFTER Triggers AFTER triggers execute the trigger action after the triggering statement is executed. AFTER triggers are used in the following situations:
AFTER triggers are used when you want the triggering statement to complete before executing the trigger action.
If a BEFORE trigger is already present, an AFTER trigger can perform different actions on the same triggering statement.

Combinations
Using the options listed in the previous two sections, you can create four types of triggers:
BEFORE statement trigger Before executing the triggering statement, the trigger action is executed.
BEFORE row trigger Before modifying each row affected by the triggering statement and before checking appropriate integrity constraints, the trigger action is executed provided that the trigger restriction was not violated.
AFTER statement trigger After executing the triggering statement and applying any deferred integrity constraints, the trigger action is executed.
AFTER row trigger After modifying each row affected by the triggering statement and possibly applying appropriate integrity constraints, the trigger action is executed for the current row provided the trigger restriction was not violated. Unlike BEFORE row triggers, AFTER row triggers lock rows.

New Database Triggers
Startup, Shutdown, Logon, Logoff, Alter, Create, Drop

23. List Item Types

Poplist: The poplist style list item appears initially as a single field (similar to a text item field). When the end user selects the list icon, a list of available choices appears.
Tlist: The Tlist style list item appears as a rectangular box, which displays a fixed number of values. When the Tlist contains values that cannot be displayed (due to the displayable area of the item), a vertical scroll bar appears, allowing the end user to view and select undisplayed values.
Combo Box: The combo box style list item combines the features found in poplists and text items. It displays fixed values and can accept a user-entered value.