Tuesday 25 October 2011
What is suspending a database
What is online table redefinition
Oracle 9i provides a new DBMS_REDEFINITION PL/SQL package to redefine tables online. When a table is redefined online, it is accessible to DML during much of the redefinition process.
What is new in Oracle9i
Some of the more important ones are:
• Traditional rollback segments (RBS) are still available, but can be replaced with automated System Managed Undo (SMU). Using SMU, Oracle will create it's own "Rollback Segments" and size them automatically without any DBA involvement.
• Flashback query (dbms_flashback.enable) - one can query data as it looked at some point in the past. This feature will allow users to correct wrongly committed transactions without contacting the DBA to do a database restore.
• Use Oracle Ultra Search for searching databases, file systems, etc. The Ultra Search crawler fetches data and hand it to Oracle Text to be indexed.
• Oracle Name server is still available, but deprecate in favor of LDAP Naming (using the Oracle Internet Directory Server). A name server proxy is provided for backwards compatibility, as pre-8i client cannot resolve names from an LDAP server.
• Oracle Parallel Server's (OPS) scalability was improved - now called Real Application Clusters (RAC). Full Cache Fusion implemented. Any application can scale in a database cluster. Applications don't need to be cluster aware anymore.
• The Oracle Standby DB feature renamed to Oracle Data Guard. New Logical Standby databases replay SQL on standby site allowing the database to be used for normal read writes operations. The Data Guard Broker allows single step fail-over when disaster strikes.
• Scrolling cursor support. Oracle9i allows fetching backwards in a result set.
• Dynamic Memory Management - Buffer Pools and shared pool can be resized on-the-fly. This eliminates the need to restart the database each time parameter changes were made.
• On-line table and index reorganization.
• VI (Virtual Interface) protocol support, an alternative to TCP/IP, available for use with Oracle Net (SQL*Net). VI provides fast communications between components in a cluster.
• Build in XML Developers Kit (XDK). New data types for XML (XMLType), URI's, etc. XML integrated with AQ.
• Cost Based Optimizer now also considers memory and CPU, not only disk access cost as before.
• PL/SQL programs can be natively compiled to binaries. <P<
• Deep data protection - fine-grained security and auditing. Put security on DB level. SQL access does not mean unrestricted access.
• Resumable backups and statements - suspend statement instead of rolling back immediately.
• List Partitioning - partitioning on a list of values.
• ETL (eXtract, transformation, load) Operations - with external tables and pipelining.
• OLAP - Express functionality included in the DB.
• Data Mining - Oracle Darwin's features included in the DB.
What are new SQL & PLSQL features
• Inheritance, type evolution and dynamic method dispatch are supported in Oracle 9i.
• CASE WHEN statement, ANSI compliant joins, and reserved name versioning is supported. (New ANSI requirements).
• Scrolling cursor support to migrate from non-Oracle databases.
PL/SQL Features include:
• Native compilation.
• An integrated front-end for SQL compilation that supports all SQL syntax changes for SQL embedded in PL/SQL.
Stored procedures written in 3GL languages (PL/SQL, Java, as well as external routines) can utilize SQL parallel query mechanism and can now pipeline (incrementally return) data to the calling SQL statement.
Validation
Validation unit
A form's Validation Unit property controls the granularity of validation in the form. The validation unit can be an item, record, block, or form. Most Form Builder applications validate at the item level.
When validation occurs
Form Builder performs validation when
Form Builder navigates out of the validation unit. This occurs when the end user presses certain function keys or clicks the mouse outside the validation unit, or a trigger executes certain built-ins.
A trigger invokes the ENTER built-in, or the end user presses [Enter].
A trigger invokes the COMMIT_FORM built-in, or the end user presses [Commit]. In this case Form Builder validates the form regardless of the validation unit.
A trigger invokes the VALIDATE built-in.
Form Builder does not perform validation in Enter Query mode. This allows the end user to enter query criteria that Form Builder would find invalid.
How validation proceeds
Internally, Form Builder maintains a status for each item, record, block, and form. For purposes of validation, each potential validation unit has a validation status with one of two values:
Valid
Unvalidated
The Valid status means that Form Builder does not need to examine the validation unit. The Unvalidated status, a combination of states known internally as New and Changed, means that Form Builder needs to validate the items in the validation unit. The distinction between New and Changed appears only rarely in the validation process (see the note in the Record entry in the table that follows).
A third state occurs if validation fails, but in that case Form Builder does not proceed until the situation is corrected, so the failed status does not enter into the validation process.
Form Builder validates a validation unit according to following processes:
Item
If the item's status is Valid, the process stops. If the status is Unvalidated, Form Builder performs standard validation checks, then fires the WHEN-VALIDATE-ITEM trigger. (See, however, the Defer Required Enforcement property.)
Record
If the record's status is Valid, the process stops. If the status is Unvalidated, Form Builder validates each item in the record, then fires the WHEN-VALIDATE-RECORD trigger.
Note: if the validation is as a result of a call to the VALIDATE trigger and the record's internal status is New, the process stops.
Block
Form Builder validates all records in the block.
Form
Form Builder validates all blocks in the form.
Item validation status
Unvalidated
Form Builder sets the status of an item to Unvalidated when any of the following occurs:
Form Builder creates a record.
The end user types a value into the item, or a trigger causes a value to be stored into the item.
Note: Form Builder sets the status of the item to Unvalidated even if the new value is the same as the old value. For example, if an item with Valid status has the value 10 and the end user types 10 into the item, Forms Builder sets the status of the item to Unvalidated.
? When Form Builder duplicates a record (in response to the DUPLICATE_RECORD built-in or the [Duplicate Record] key), any item that has Unvalidated status in the original has Unvalidated status in the duplicate.
Valid
Form Builder sets the status of an item to Valid in the following cases:
? When Form Builder successfully validates an item, it sets the item's status to Valid, even if validation changes the item's value (through a WHEN-VALIDATE-ITEM, WHEN-VALIDATE-RECORD, or POST-CHANGE trigger). Form Builder does not re-validate the changed value.
Caution: This behavior avoids validation loops, but a trigger error can cause Form Builder to commit an invalid value to the database.
? When Form Builder fetches records from the database, it sets their status to Valid, even if a Post-Change trigger that fires during the fetch changes item values.
? When Form Builder successfully commits data to the database, it sets the status of all items in the form to Valid. Form Builder does not validate changes caused by triggers that fire during the commit transaction.
Note: See Changing Data During Commit Processing for exceptions to this rule.
? When Form Builder duplicates a record (in response to the DUPLICATE_RECORD built-in or the [Duplicate Record] key), any item that has Valid status in the original has Valid status in the duplicate.
Record validation status
Each record has a validation status of Unvalidated or Valid.
Note: a record's validation status is not equivalent to the value of the SYSTEM.RECORD_STATUS system variable.
Unvalidated
Form Builder sets the status of a record to Unvalidated in the following cases:
? When Form Builder creates a record, it sets its status to Unvalidated.
? When Form Builder sets the status of an item in the record to Unvalidated, it sets the status of the record to Unvalidated.
? When Form Builder duplicates a record (in response to the DUPLICATE_RECORD built-in or the [Duplicate Record] key), it sets the status of the duplicate record to Unvalidated.
Valid
Form Builder sets the status of a record to Valid in the following cases:
When Form Builder validates a record, it validates each item that does not already have a status of Valid. If all items have Valid status at the completion of this process, and if the WHEN-VALIDATE-RECORD trigger, if any, returns without raising the Form_Trigger_Failure exception, Form Builder sets the status of the record to Valid.
Form Builder sets to Valid the status of records fetched from the database.
When Form Builder successfully commits data to the database, it sets the status of each record in the form to Valid. Form Builder does not validate changes caused by triggers that fire during the commit transaction.
Note: See Changing Data During Commit Processing for exceptions to this rule.
? When Form Builder (in response to the DUPLICATE_RECORD built-in or the [Duplicate Record] key) duplicates a record that has Valid status, it sets the status of the duplicate record to Valid.
Standard validation checks
Form Builder uses the requirements specified in the Data and List of Values (LOV) sections of the property palette to validate an item.
Validation sequence for text items
For text items, Form Builder performs one of the following sequences of validation steps, depending on whether the item's value is null or not-null. If the text item fails a validation step, Form Builder notes the failure and omits the subsequent validation steps.
When the item's value is null:
1. If the item's Required property has a setting of Yes, the item fails validation -- unless item-level validation is occurring and one of the following is true:
? The form's Defer Required Enforcement property has the setting Yes or 4.5. (For a discussion of validation behavior in these cases, see Defer Required Enforcement property.)
? The item instance does not allow end-user update.
Under either of the above exceptional conditions, Form Builder defers validating the null item until record-level validation. (Note that if the item instance does not allow end-user update, validation is deferred even if Defer Required Enforcement is set to No.)
2 If the item's Required property has a setting of No, then the WHEN-VALIDATE-ITEM trigger is fired. If the trigger raises the Form_Trigger_Failure exception, the item fails this validation step.
When the item's value is not null:
1. Check data type. If the value does not match the item's data type, the item fails this validation step.
Form Builder tries to convert a user-entered value to the item's data type.
If the item has a format mask, Form Builder uses it. If the item has no format mask and its data type is DATE or DATETIME, Form Builder uses the input format mask derived at Form Builder startup.
If the item has no format mask and is of data type NUMBER or ALPHA, Form Builder checks for a valid numeric or alphabetic text string.
2. Check length. If the value does not match the item's Fixed Length property, the item fails this validation step.
3. Check range. If the value does not match the item's Lowest Allowed Value and Highest Allowed Value properties, the item fails this validation step.
4 If the item's Use LOV for Validation property has the value Yes, verify that the value appears in the item's list of values.
5. Fire the POST-CHANGE trigger. If the trigger raises the Form_Trigger_Failure exception, the item fails this validation step.
6. Fire the WHEN-VALIDATE-ITEM trigger. If the trigger raises the Form_Trigger_Failure exception, the item fails this validation step.
Validation sequence for non-text items
If the item's Item Type property does not have the value Text Item, Form Builder follows the above sequence, but omits steps 3 through 5. It also omits step 2 unless the Item Type property has the value List Item and the List Style property has the value Combo Box.
Validation of synchronized items
If items, say B and C, have their Synchronize with Item properties set to the name of another item, say A, then A, B, and C form a set of synchronized items. A is the master item of the set, and B and C are subordinate items. Synchronized items were called mirror items in earlier versions of Form Builder.
The items of a synchronized set share a common data value, but can have different item properties. Form Builder takes data-specific properties and triggers from the master item and ignores those properties and triggers for the subordinate items.
When validating the value assigned to a synchronized set of items, Form Builder uses the following data-specific properties and triggers from the master item:
? Required property
? Highest Allowed Value property
? Lowest Allowed Value property
? Initial Value
? POST-CHANGE trigger
? WHEN-VALIDATE-ITEM trigger
If the subordinate item has values for these properties or specifications for these triggers, Form Builder ignores them and issues a warning.
Form Builder takes the remaining validation properties from the item through which the data value is set—either programmatically or by end user action. In particular, Form Builder uses the Fixed Length and Validate from List properties of that item.
The SYSTEM.TRIGGER_ITEM variable contains the name of the item through which the data value was set. This allows code in the POST-CHANGE or WHEN-VALIDATE-ITEM trigger to refer to properties of that item, even though these triggers are defined for the master item.
Form Builder considers a value that has not been set programmatically or by the end user to be set by the master item, because that is the source Form Builder uses for the initial value.
During record-level validation, Form Builder validates the item through which the data value was set. If validation fails, Form Builder gives focus to that item, unless the item instance does not allow end user input. In that case, Form Builder tries to give focus to another item of the synchronized set that does allow end user input. If it fails to find one, it tries to give focus to an item of the set for which the Enabled property has the value Yes.
Runtime Compatibility Mode property
Queries
Select * from EMP where rowid in (select decode (mod (rownum, 2), 0,rowid, null) from emp);
2. Select odd row from emp table
Select * from EMP where rowid in (select decode (mod (rownum, 2), 1,rowid, null) from emp);
3. Select Nth row from emp table where N=10
Select * from EMP where rowid=(select rowid from EMP where ROWNUM <= 10
MINUS
Select rowid from EMP where ROWNUM < 10)
4. Select rows X to Y from emp table where X=5 and Y=7
Select * from EMP where rowid in (select rowid from EMP
Where rowNUM <= 7
MINUS
Select rowid from EMP where rownum < 5);
5. All even, Odd, Nth rows from a table
Select * from emp where (rowid, 0) in (select rowid, mod (rownum,&n) from emp);
6. Select N rows from a table
Select * from EMP a
Where &n>(select count (distinct (sal)) from emp b where a. Sal >= b.sal)
Order by sal desc;
Please check this query for the correctness for n=1,2,3,4,5
7. Select Employee having maximum salary in department from emp table
Select deptno, empno, sal from EMP a where (deptno, sal) in (
Select deptno, max (sal) from EMP b
Where a.deptno = b.deptno
Group by b.deptno) order by 1
New Join Syntax
INNER Join:
SQL> select p.part_id, s.supplier_name
From part p inner join supplier s
On p.supplier_id = s.supplier_id;
PART SUPPLIER_NAME
---- --------------------
P1 Supplier#1
P2 Supplier#2
Remember, if we want to retain all the parts in the result set, irrespective of whether any supplier supplies them or not, then we need to perform an outer join. The corresponding outer join query using the new syntax will be:
OUTER JOIN
SQL> select p.part_id, s.supplier_name from part p left outer join supplier s on p.supplier_id = s.supplier_id;
PART SUPPLIER_NAME
---- --------------------
P1 Supplier#1
P2 Supplier#2
P4
P3
This is called a "left outer join" because all the rows from the table on the left (PART) are retained in the result set. If we want to retain all the suppliers in the result set, irrespective of whether they supply any part or not, then we need to perform a "right outer join". That would look like:
SQL> select p.part_id, s.supplier_name from part p right outer join supplier s on p.supplier_id = s.supplier_id;
PART SUPPLIER_NAME
---- --------------------
P1 Supplier#1
P2 Supplier#2
Supplier#3
However, the biggest advantage of the new join syntax is its support for full outer joins. Introduction of the ANSI standard join syntax in Oracle9i greatly simplifies the full outer join query. We are no longer limited by unidirectional outer join, and no longer need to use the UNION operation to perform the full outer join.
FULL OUTER JOIN
Oracle9i introduced the full outer join operation to carry out such operations, as in the following example:
SQL> select p.part_id, s.supplier_name
From part p full outer join supplier s
On p.supplier_id = s.supplier_id;
PART SUPPLIER_NAME
---- --------------------
P1 Supplier#1
P2 Supplier#2
P4
P3
Supplier#3
The above SQL statement is not only smaller in size, it is much more elegant and intuitive as well. This ANSI join syntax is also more efficient than the UNION method of achieving a full outer join.
NATURAL Joins. A natural join, as its name implies, can be invoked when two or more tables share exactly the same columns needed for a successful equijoin. For example, these queries will return all Region and Country information for all countries whose name that contains the string "united":
Multitable Insert in Oracle 9i
-- Unconditional insert into ALL tables
INSERT ALL
INTO sal_history VALUES (empid, hiredate, sal)
INTO mgr_history VALUES (empid, mgr, sysdate)
SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR
FROM employees WHERE employee_id > 200;
-- Pivoting insert to split non-relational data
INSERT ALL
NTO Sales_info VALUES (employee_id, week_id, sales_MON)
INTO Sales_info VALUES (employee_id, week_id, sales_TUE)
INTO Sales_info VALUES (employee_id, week_id, sales_WED)
INTO Sales_info VALUES (employee_id, week_id, sales_THUR)
NTO Sales_info VALUES (employee_id, week_id, sales_FRI)
SELECT EMPLOYEE_ID, week_id, sales_MON, sales_TUE,
Sales_WED, sales_THUR, sales_FRI
FROM Sales_source_data;
-- Conditionally insert into ALL tables
INSERT ALL
WHEN SAL>10000 THEN
INTO sal_history VALUES (EMPID, HIREDATE, SAL)
WHEN MGR>200 THEN
INTO mgr_history VALUES (EMPID, MGR, SYSDATE)
SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR
FROM employees WHERE employee_id > 200;
-- Insert into the FIRST table with a matching condition
INSERT FIRST
WHEN SAL > 25000 THEN
INTO special_sal VALUES (DEPTID, SAL)
WHEN HIREDATE like ('%00%') THEN
INTO hiredate_history_00 VALUES (DEPTID, HIREDATE)
WHEN HIREDATE like ('%99%') THEN
INTO hiredate_history_99 VALUES (DEPTID, HIREDATE)
ELSE
INTO hiredate_history VALUES (DEPTID, HIREDATE)
SELECT department_id DEPTID, SUM (salary) SAL,
MAX (hire_date) HIREDATE
FROM employees GROUP BY department_id;
The restrictions on multi-table Inserts are:
Multi-table inserts can only be performed on tables, not on views or materialized views.
You cannot perform a multi-table insert via a DB link.
You cannot perform multi-table inserts into nested tables.
The sum of all the INTO columns cannot exceed 999.
Sequences cannot be used in the subquery of the multi-table insert statement.
Multiple block sizes in the same database
2K, 4K, 8K, 16K, 32K (operating system dependent).
•CREATE TABLESPACE MY_TBS ... BLOCKSIZE 16K;
•CREATE TABLESPACE MY_TBS ... BLOCKSIZE 8192;
•The field BLOCK_SIZE has been added to the DBA_TABLESPACES view.
•The DB_BLOCK_SIZE parameter is still needed in the init.ora during database creation. This value is the default blocksize for new tablespaces as well as the required size for the system tablespace, undo tablespace and all temporary tablespaces.
•As with previous releases, DB_BLOCK_SIZE cannot be changed without recreating the database.
•All partitions of a partitioned object must be in tablespaces with the same block size.
•Indexes can be in a tablespace with a different block size than its corresponding table.
Item Level Properties
Specifies the type of item. An item can be one of the following types:
ActiveX Control (32-bit Windows platforms)
Bean Area
Chart Item
Check Box
Display Item
Hierarchical Tree
Image
List Item
OLE Container
Push Button
Radio Group
Sound
Text Item
User Area
VBX Control (Microsoft Windows 3.1 only)
Subclass Information property
Specifies the following information about the source object and source module for referenced objects.
Module: The name of the source module.
Storage: The source module type (Form or Menu) and location (File System or Database)
Name: The name of the source object in the source module. (The name of a reference object can be different than the name of its source object.)
Enabled (Item) property
Determines whether end users can use the mouse to manipulate an item.
On most window managers, Enabled set to No grays out the item.
Justification property
Specifies the text justification within the item.
Left: Left justified, regardless of Reading Order property.
Center: Centered, regardless of Reading Order property.
Right: Right justified, regardless of Reading Order property.
Start: Item text is aligned with the starting edge of the item-bounding box. The starting edge depends on the value of the item's Reading Order property. Start is evaluated as Right alignment when the reading order is Right To Left, and as Left alignment when the reading order is left to Right.
End: Item text is aligned with the ending edge of the item-bounding box. The ending edge depends on the value of the item's Reading Order property. End is evaluated as Left alignment when the reading order is Right To Left, and as Right alignment when the reading order is left to Right.
Multi-Line property
Determines whether the text item is a single-line or multi-line editing region.
Setting the Multi-line property Yes allows a text item to store multiple lines of text, but it does not automatically make the item large enough to display multiple lines. It is up to you to set the Width, Height, Font Size, and Maximum Length properties to ensure that the desired number of lines and characters are displayed.
Single-line: Pressing the carriage return key while the input focus is in single-line text item initiates a [Next Item] function.
Multi-line: Pressing the carriage return key while the input focus is in a multi-line text item starts a new line in the item.
Wrap Style property
Specifies how text is displayed when a line of text exceeds the width of a text item or editor window.
NONE: No wrapping: text exceeding the right border is not shown.
CHARACTER: Text breaks following the last visible character, and wraps to the next line.
WORD: Text breaks following last visible complete word, and wraps to the next line.
Case Restriction property
Specifies the case for text entered in the text item or menu substitution parameter.
MIXED: Text appears as typed.
UPPER: Lower case text converted to upper case as it is typed.
LOWER: Upper case text converted to lower case as it is typed.
Conceal Data property
Hides characters that the operator types into the text item. This setting is typically used for password protection.
Yes Disables the echoing back of data entered by the operator.
No Enables echoing of data entered by the operator.
Keep Cursor Position property
Specifies that the cursor position be the same upon re-entering the text item as when last exited.
Automatic Skip (Item) property
Moves the cursor to the next navigable item when adding or changing data in the last character of the current item. The last character is defined by the Maximum Length property.
Popup Menu property
Specifies the popup menu to display for the canvas or item.
Keyboard Navigable property
Determines whether the end user or the application can place the input focus in the item during default navigation. When set to Yes for an item, the item is navigable. When set to No, Form Builder skips over the item and enters the next navigable item in the default navigation sequence.
At runtime, when the Enabled property is set to PROPERTY_FALSE, the Keyboard_Navigable property is also set to PROPERTY_FALSE. However, if the Enabled property is subsequently set back to PROPERTY_TRUE, the keyboard Navigable property is NOT set to PROPERTY_TRUE, and must be changed explicitly.
When Keyboard Navigable is specified at multiple levels (item instance, item, and block), the values are ANDed together. This means that setting Keyboard Navigable to Yes (or NAVIGABLE to PROPERTY_TRUE for runtime) has no effect at the item instance level unless it is set consistently at the item level. For example, your user cannot navigate to an item instance if Keyboard Navigable is true at the instance level, but not at the item level.
You can use the GO_ITEM built-in procedure to navigate to an item that has Keyboard Navigable property set to (PROPERTY_FALSE) for runtime.
Previous Navigation Item property
Specifies the name of the item that is defined as the"previous navigation item" with respect to the current item. By default, this is the item with the next lower sequence in the form, as indicated by the order of items in the Object Navigator.
Next Navigation Item property
Specifies the name of the item that is defined as the "next navigation item" with respect to this current item. By default, the next navigation item is the item with the next higher sequence as indicated by the order of items in the Object Navigator.
Copy Value from Item property
Specifies the source of the value that Form Builder uses to populate the item. When you define a master-detail relation, Form Builder sets this property automatically on the foreign key item(s) in the detail block. In such cases, the Copy Value from Item property names the primary key item in the master block whose value gets copied to the foreign key item in the detail block whenever a detail record is created or queried.
Specify this property in the form <block_name>.<block_item_name>.
Setting the Copy Value from Item property does not affect record status at runtime, because the copying occurs during default record processing.
To prevent operators from de-enforcing the foreign key relationship, set the Enabled property to No for the foreign key items.
To get the Copy Value from Item property programmatically with GET_ITEM_PROPERTY, use the constant ENFORCE_KEY.
Synchronize with Item property
Specifies the name of the item from which the current item should derive its value. Setting this property synchronizes the values of the two items, so that they effectively mirror each other. When the end user or the application changes the value of either item, the value of the other item changes also.
In earlier releases, this property was called the Mirror Item property.
You can set Synchronize with Item for base table or control blocks. When Synchronize with Item is specified, the current item's Base Table Item property is ignored, and the item derives its value from the mirror item specified, rather than from a column in the database.
If you use the GET_ITEM_PROPERTY built-in to obtain a Base Table Item property, it will obtain the value from the mirror item specified.
You can use mirror item to create more than one item in a block that display the same database column value.
Calculation Mode property
Specifies the method of computing the value of a calculated item.
None (default): Indicates the item is not a calculated item.
Formula: Indicates the item's value will be calculated as the result of a user-written formula. You must enter a single PL/SQL expression for an item's formula. The expression can compute a value, and also can call a Form Builder or user-written subprogram.
Summary: Indicates the item's value will be calculated as the result of a summary operation on a single form item. You must specify the summary type, and the item to be summarized.
Primary Key (Item) property
Indicates that the item is a base table item in a data block and that it corresponds to a primary key column in the base table. Form Builder requires values in primary key items to be unique.
Query Only property
Specifies that an item can be queried but that it should not be included in any INSERT or UPDATE statement that Form Builder issues for the block at runtime.
Query Length property
Specifies the number of characters an end user is allowed to enter in the text item when the form is Enter Query mode.
You can make the query length greater than the Maximum Length when you want to allow the end user to enter complex query conditions. For example, a query length of 5 allows an end user to enter the query condition !=500 in a text item with a Maximum Length of 3.
Case Insensitive Query property
Determines whether the operator can perform case-insensitive queries on the text item.
Case-insensitive queries are optimized to take advantage of an index. For example, assume you perform the following steps:
Create an index on the EMP table.
Set the Case Insensitive Query property on ENAME to Yes.
In Enter Query mode, enter the name 'BLAKE' into: ENAME.
Execute the query.
Form Builder constructs the following statement:
SELECT * FROM EMP WHERE UPPER (ENAME) = 'BLAKE' AND (ENAME LIKE 'Bl%' OR ENAME LIKE 'bL%' OR ENAME LIKE 'BL%' OR ENAME LIKE 'bl%');
The last part of the WHERE clause is performed first, making use of the index. Once the database finds an entry that begins with bl, it checks the UPPER (ENAME) = 'BLAKE' part of the statement, and makes the exact match.
Update Only if NULL property
Indicates that operators can modify the value of the item only when the current value of the item is NULL.
Lock Record property
Specifies that Form Builder should attempt to lock the row in the database that corresponds to the current record in the block whenever the text item's value is modified, either by the end user or programmatically.
Set this property to Yes when the text item is a control item but you still want Form Builder to lock the row in the database that corresponds to the current record in the block.
Useful for lookup text items where locking underlying record is required.
To set the Lock Record property with SET_ITEM_PROPERTY, use the constant LOCK_RECORD_ON_CHANGE.
..
List of Values property
Specifies the list of values (LOV) to attach to the text item. When an LOV is attached to a text item, end users can navigate to the item and press [List of Values] to invoke the LOV.
Validate from List property
Specifies whether Form Builder should validate the value of the text item against the values in the attached LOV.
When Validate from List is Yes, Form Builder compares the current value of the text item to the values in the first column displayed in the LOV whenever the validation event occurs:
If the value in the text item matches one of the values in the first column of the LOV, validation succeeds, the LOV is not displayed, and processing continues normally.
If the value in the text item does not match one of the values in the first column of the LOV, Form Builder displays the LOV and uses the text item value as the search criteria to automatically reduce the list.
Note, however, that a When-Validate-Item trigger on the item still fires, and any validation checks you perform in the trigger still occur.
Editor property
Specifies that one of the following editors should be used as the default editor for this text item:
A user-named editor that you defined in the form or
A system editor outside of Form Builder that you specified by setting the SYSTEM_EDITOR environment variable
Visible property
Indicates whether the object is currently displayed or visible. Set Visible to Yes or No to show or hide a canvas or window.
You cannot hide the canvas that contains the current item.
You can hide a window that contains the current item.
When you use GET_WINDOW_PROPERTY to determine window visibility, Form Builder uses the following rules:
A window is considered visible if it is displayed, even if it is entirely hidden behind another window.
A window that has been iconified (minimized) is reported as visible to the operator because even though it has a minimal representation, it is still mapped to the screen.
When you use GET_VIEW_PROPERTY to determine canvas visibility, Form Builder uses the following rules:
A view is reported as visible when it is a) in front of all other views in the window or b) only partially obscured by another view.
A view is reported as not visible when it is a) a stacked view that is behind the content view in the window or b) completely obscured by a single stacked view.
The display state of the window does not affect the setting of the canvas VISIBLE property. That is, a canvas may be reported visible even if the window in which it is displayed is not currently mapped to the screen.
Rendered property
Use the Rendered property to conserve system resources. A rendered item does not require system resources until it receives focus. When a rendered item no longer has focus, the resources required to display it are released.
Hint (Item) property
Specifies item-specific help text that can be displayed on the message line of the root window at runtime. Hint text is available when the input focus is in the item.
Display Hint Automatically property
Determines when the help text specified by the item property, Hint, is displayed:
? Set Display Hint Automatically to Yes to have Form Builder display the hint text whenever the input focus enters the item.
? Set Display Hint Automatically to No to have Form Builder display the hint text only when the input focus is in the item and the end user presses [Help] or selects the Help command on the default menu.
Tooltip property
Specifies the help text that should appear in a small box beneath the item when the mouse enters the item.
Initialization
Note: If a trigger assigns a value to an item in a null record, the record is no longer null. Form Builder never assigns an initial value to any item in the record. For example, a PRE-FORM trigger fires while the form contains only null records. If it assigns a value to an item, it must ensure that all other items of the record receive appropriate initial values.
Special considerations apply to certain types of items.
Check boxes
The initial value determines whether the check box is initially checked or unchecked. You must specify a valid initial value unless one of the following is true:
? The item's Mapping of Other Values property is set to Checked or Unchecked.
? The value associated with Checked or Unchecked is Null.
Note: A check box in a null record appears unchecked.
List items
You must specify a valid initial value unless one of the following is true:
? The item's Mapping of Other Values property has a non-null value.
? The value associated with one of the list elements is Null.
Note: A list item in a null record appears blank.
Radio groups
You must specify a valid initial value unless one of the following is true:
? The item's Mapping of Other Values property has a non-null value.
? The value associated with one of the radio buttons in the group is Null.
Note: A radio group in a null record appears with no radio button selected.
Synchronized items
If items, say B and C, have their Synchronize with Item properties set to the name of another item, say A, then A, B, and C form a set of synchronized items. A is the master item of the set, and B and C are subordinate items. Synchronized items were called mirror items in earlier versions of Form Builder.
The items of a synchronized set share a common data value, but can have different item properties. Form Builder takes data-specific properties and triggers from the master item and ignores those properties and triggers for the subordinate items.
Form Builder initializes synchronized items from the master item's Initial Value property. Form Builder also uses the master item's ON-SEQUENCE-NUMBER trigger.
If the designer specifies an Initial Value property or an ON-SEQUENCE-NUMBER trigger for a subordinate item, Form Builder ignores them and issues a warning.
Form Builder ignores an item's Initial Value property if all of the following are true for the item (or an item synchronized with it):
The item is a check box, poplist (drop-down list), Tlist (Text list), or radio group.
The item has no element corresponding to the specified initial value.
The item's Mapping of Other Values property has a null value.
How to retrieve DDL from Database
DBMS_METADATA.GET_DDL (object_type, name, schema)
DBMS_METADATA.GET_XML (object_type, name, schema)
SELECT DBMS_METADATA.GET_DDL ('TABLE', 'EMP', 'SCOTT') from dual;
Form Level Properties
Specifies the name of the window that should display the Form Builder console. The console includes the status line and message line, and is displayed at the bottom of the window.
On Microsoft Windows, the console is always displayed on the MDI application window, rather than on any particular window in the form; however, you must still set this property to the name of a form window to indicate that you want the console to be displayed.
Menu Source property
Menu Source allows you to specify the location of the .MMX run file when you attach a custom menu to a form module. Form Builder loads the .MMX file at form startup.
Menu Module property
Specifies the name of the menu to use with this form.
Initial Menu property
Specifies the name of the individual menu in the menu module that Form Builder should use as the main, or top-level, menu for this invocation.
Defer Required Enforcement property
It specifies whether Form Builder should defer enforcement of the required item attribute until the record is validated.
This property applies only when item-level validation is in effect. By default, when an item has required set to true, Form Builder will not allow navigation out of the item until a valid value is entered. This behavior will be in effect if you set Defer Required Enforcement to No.
If you set Defer Required Enforcement to Yes or 4.5, you allow the end user to move freely among the items in the record, even if they are null, postponing enforcement of the required attribute until validation occurs at the record level.
When Defer Required Enforcement is set to 4.5, null-valued required items are not validated when navigated out of, and the item's Item Is Valid property is unchanged. However, the WHEN-VALIDATE-ITEM trigger (if any) does fire. If it fails (raises Form_Trigger_Failure), the item is considered to have failed validation and Form Builder will issue an error. If the trigger ends normally, processing continues normally. If the item value is still null when record-level validation occurs later, Form Builder will issue an error at that time.
Mouse Navigation Limit property
Determines how far outside the current item an end user can navigate with the mouse.
Form (The default): Allow end users to navigate to any item in the current form.
Block: Allows end users to navigate only to items that are within the current block.
Record: Allows end users to navigate only to items that are within the current record.
Item: Prevents end users from navigating out of the current item. This setting prevents end users from navigating with the mouse at all.
First Navigation Block property
Specifies the name of the block to which Form Builder should navigate at form startup and after a CLEAR_FORM operation.
Current Record Visual Attribute Group property
Specifies the named visual attribute used when an item is part of the current record. This property can be set at the form, block, or item level, or at any combination of levels. If you specify named visual attributes at each level, the item-level attribute overrides all others, and the block-level overrides the form-level.
Validation Unit property
The validation unit defines the maximum amount of data that an operator can enter in the form before Form Builder initiates validation.
Interaction Mode property
Interaction mode dictates how a user can interact with a form during a query. If Interaction Mode is set to Blocking, then users are prevented from resizing or otherwise interacting with the form until the records for a query are fetched from the database. If set to Non-Blocking, then end users can interact with the form while records are being fetched.
Non-blocking interaction mode is useful if you expect the query will be time-consuming and you want the user to be able to interrupt or cancel the query. In this mode, the Forms runtime will display a dialog that allows the user to cancel the query.
You cannot set the interaction mode programmatically, however, you can obtain the interaction mode programmatically using the GET_FORM_PROPERTY built-in.
Maximum Query Time property
Provides the option to abort a query when the elapsed time of the query exceeds the value of this property.
Maximum Records Fetched property
Specifies the number of records fetched when running a query before the query is aborted.
Isolation Mode property
Specifies whether or not transactions in a session will be serializable. If Isolation Mode has the value serializable, the end user sees a consistent view of the database for the entire length of the transaction, regardless of updates committed by other users from other sessions. If the end user queries and changes a row, and a second user updates and commits the same row from another session, the first user sees Oracle error (ORA-08177: Cannot serialize access.).
Coordinate System property
Specifies whether object size and position values should be interpreted as character cell values, or as real units (centimeters, inches, pixels, or points).
Character: Sets the coordinate system to a character cell-based measurement. The actual size and position of objects will depend on the size of a default character on your particular platform.
Real: Sets the coordinate system to the unit of measure specified by the Real Unit property (centimeters, inches, pixels, or points.)
Changing the coordinate system for the form changes the ruler units displayed on Form Editor rulers, but does not change the grid spacing and snap-points settings.
Use 3D Controls property
On Microsoft Windows, specifies that Form Builder display items with a 3-dimensional, beveled look.
When Use 3D Controls is set to Yes, any canvas that has Visual Attribute Group set to Default will automatically be displayed with background color grey.
In addition, when Use 3D Controls is set to Yes, the bevel for each item automatically appears lowered, even if an item-level property is set, for example, to raised.
Form Horizontal Toolbar Canvas property
On Microsoft Windows, specifies the canvas that should be displayed as a horizontal toolbar on the MDI application window. The canvas specified must have the Canvas Type property set to Horizontal Toolbar.
Form Horizontal Toolbar Canvas property
On Microsoft Windows, specifies the toolbar canvas that should be displayed as a vertical toolbar on the MDI application window. The canvas specified must have the Canvas Type property set to Vertical Toolbar.
Direction property
This property is used to define the test justification for all items.
Value Description
Default Direction based on the property shown in the table.
Right-To-Left Direction is right-to-left.
Left-To-Right Direction is left-to-right.
External Tables
Limitations on external tables
Because external tables are new, Oracle has not yet perfected their use. In Oracle9i the feature has several limitations, including:
• No support for DML. External tables are read-only, but the base data can be edited in any text editor.
• Poor response for high-volume queries. External tables have a processing overhead and are not suitable for large tables.
Example: The example below describes how to create external files, create external tables, query external tables and create views.
Step I: Creating the flat files, which will be queried
The file "emp_ext1.dat" contains the following sample data:
101,Andy,FINANCE,15-DEC-1995
102,Jack,HRD,01-MAY-1996
103,Rob,DEVELOPMENT,01-JUN-1996
104,Joe,DEVELOPMENT,01-JUN-1996
The file "emp_ext2.dat" contains the following sample data:
105,Maggie,FINANCE,15-DEC-1997
106,Russell,HRD,01-MAY-1998
107,Katie,DEVELOPMENT,01-JUN-1998
108,Jay,DEVELOPMENT,01-JUN-1998
Copy these files under "C:\EXT_TABLES"
Step II: Create a Directory Object where the flat files will reside
SQL> CREATE OR REPLACE DIRECTORY EXT_TABLES AS 'C:\EXT_TABLES';
Directory created.
Step III: Create metadata for the external table
SQL> CREATE TABLE emp_ext
(
empcode NUMBER(4),
empname VARCHAR2(25),
deptname VARCHAR2(25),
hiredate date
)
ORGANIZATION EXTERNAL
(
TYPE ORACLE_LOADER
DEFAULT DIRECTORY ext_tables
ACCESS PARAMETERS
(
RECORDS DELIMITED BY NEWLINE
FIELDS TERMINATED BY ','
MISSING FIELD VALUES ARE NULL
)
LOCATION ('emp_ext1.dat','emp_ext2.dat')
)
REJECT LIMIT UNLIMITED;
Table created.
The REJECT LIMIT clause specifies that there is no limit on the number of errors that can occur during a query of the external data.
Step V: Creating Views
SQL> CREATE VIEW v_empext_dev AS
SELECT * FROM emp_ext
WHERE deptname = 'DEVELOPMENT';
View created.
Dropping External Tables
For an external table, the DROP TABLE statement removes only the table metadata in the database. It has no affect on the actual data, which resides outside of the database.
Canvas Level Properties
Specifies the canvas that is to be the window's primary content view. At runtime, Form Builder always attempts to display the primary view in the window. For example, when you display a window for the first time during a session by executing the SHOW_WINDOW built-in procedure, Form Builder displays the window with its primary content view.
If, however, Form Builder needs to display a different content view because of navigation to an item on that view, the primary content view is superseded by the target view.
Window Style property
Specifies whether the window is a Document window or a Dialog window. Document and dialog windows are displayed differently on window managers that support a Multiple Document Interface (MDI) system of window management.
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 remain within the application window frame. If the operator resizes the application window so that it is smaller than a document window, the document window is clipped. An operator can maximize a document window so that is occupies the entire workspace of the application window.
Dialog windows are free-floating, and the operator can move them outside the application window if they were defined as Movable. If the operator resizes the application window so that it is smaller than a dialog window, the dialog window is not clipped.
Modal property
Specifies whether a window is to be modal. Modal windows require an end user to dismiss the window before any other user interaction can continue.
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.
Close Allowed property
Specifies whether the window manager-specific Close command is enabled or disabled for a window.
? Setting Close Allowed to Yes enables the Close command so that the Close Window event can be sent to Form Builder when the operator issues the Close command. However, to actually close the window in response to this event, you must write a When-Window-Closed trigger that explicitly closes the window. You can close a window programmatically by calling HIDE_WINDOW, SET_WINDOW_PROPERTY, or EXIT_FORM.
? On Microsoft Windows, if the operator closes the MDI parent window, Form Builder executes DO_KEY('Exit_Form') by default.
Move Allowed property
Specifies whether or not the window can be moved. Windows can be moved from one location to another on the screen by the end user or programmatically by way of the appropriate built-in subprogram.
Resize Allowed property
Specifies that the window is to be a fixed size and cannot be resized at runtime. The Resize Allowed property prevents an end user from resizing the window, but it does not prevent you from resizing the window programmatically with RESIZE_WINDOW or SET_WINDOW_PROPERTY.
Maximize Allowed property
Specifies that end users can resize the window by using the zooming capabilities provided by the runtime window manager.
Minimize Allowed property
Specifies that a window can be iconified on window managers that support this feature.
Minimized Title property
Specifies the text string that should appear below an iconified window.
Icon Filename property
Specifies the name of the icon resource that you want to represent the iconic button, menu item, or window.
Icon Filename property
Specifies the name of the icon resource that you want to represent the iconic button, menu item, or window.
Canvas Type property
Specifies the type of canvas. The type determines how the canvas is displayed in the window to which it is assigned, and determines which properties make sense for the canvas.
Content: The default. Specifies that the canvas should occupy the entire content area of the window to which it is assigned. Most canvases are content canvases.
Stacked: Specifies that the canvas should be displayed in its window at the same time as the window's content canvas. Stacked views are usually displayed programmatically and overlay some portion of the content view displayed in the same window.
Vertical Toolbar Canvas: Specifies that the canvas should be displayed as a vertical toolbar under the menu bar of the window. You can define iconic buttons, pop-lists, and other items on the toolbar as desired.
Horizontal Toolbar Canvas: Specifies that the canvas should be displayed as a horizontal toolbar at the left side of the window to which it is assigned.
Raise on Entry property
For a canvas that is displayed in the same window with one or more other canvases, Raise on Entry specifies how Form Builder should display the canvas when the end user or the application navigates to an item on the canvas.
? When Raise on Entry is No, Form Builder raises the view in front of all other views in the window only if the target item is behind another view.
? When Raise on Entry is Yes, Form Builder always raises the view to the front of the window when the end user or the application navigates to any item on the view.
Block Level Properties
Specifies how a Next Item or Previous Item operation is processed when the input focus is in the last navigable item or first navigable item in the block, respectively.
Same Record (Default): A Next Item operation from the block's last navigable item moves the input focus to the first navigable item in the block, in that same record.
Change Record: A Next Item operation from the block's last navigable item moves the input focus to the first navigable item in the block, in the next record. If the current record is the last record in the block and there is no open query, Form Builder creates a new record. If there is an open query in the block (the block contains queried records), Form Builder retrieves additional records as needed.
Change Block: A Next Item operation from the block's last navigable item moves the input focus to the first navigable item in the first record of the next block. Similarly, a Previous Item operation from the first navigable item in the block moves the input focus to the last item in the current record of the previous block. The Next Navigation Block and Previous Navigation Block properties can be set to redefine a block's "next" or "previous" navigation block.
Previous Navigation Block property
Specifies the name of the block that is defined as the "previous navigation block" with respect to this block. By default, this is the block with the next lower sequence in the form, as indicated by the order of blocks in the Object Navigator.
Next Navigation Block property
Specifies the name of the block that is defined as the "next navigation block" with respect to this block. By default, this is the block with the next higher sequence number in the form, as indicated by the order of blocks listed in the Object Navigator.
Query Array Size property
Specifies the maximum number of records that Form Builder should fetch from the database at one time.
Number of Records Buffered property
Specifies the minimum number of records buffered in memory during a query in the block.
Number of Records Displayed property
Specifies the maximum number of records that the block can display at one time. The default is 1 record. Setting Number of Records Displayed greater than 1 creates a multi-record block.
Query All Records property
Specifies whether all the records matching the query criteria should be fetched into the data block when a query is executed.
Yes - Fetches all records from query; equivalent to executing the EXECUTE_QUERY (ALL_RECORDS) built-in.
No - Fetches the number of records specified by the Query Array Size block property.
Record Orientation property
Determines the orientation of records in the block, either horizontal records or vertical records. When you set this property, Form Builder adjusts the display position of items in the block accordingly.
Single Record property
Specifies that the control block always should contain one record. Note that this differs from the number of records displayed in a block.
Database Block property
Specifies that the block is based on any of the following block data source types: table, procedure, transactional trigger, or sub-query. Yes or No.
Enforce Primary Key (Block) property
Indicates that any record inserted or updated in the block must have a unique key in order to avoid committing duplicate rows to the block's base table.
Query Allowed (Block) property
Specifies whether Form Builder should allow the end user or the application to execute a query in the block. When Query Allowed is No, Form Builder displays the following message if the end user attempts to query the block:
FRM-40360: Cannot query records here.
Query Data Source Type property
Specifies the query data source type for the block. A query data source type can be a Table, Procedure, Transactional Trigger, or FROM clause query.
Query Data Source Columns property
Specifies the names and datatypes of the columns associated with the block's query data source. The Query Data Source Columns property is valid only when the Query Data Source Type property is set to Table, Sub-query, or Procedure.
Query Data Source Arguments property
Specifies the names, datatypes, and values of the arguments that are to be passed to the procedure for querying data. The Query Procedure Arguments property is valid only when the Query Data Source Type property is set to Procedure.
Alias property
Establishes an alias for the table that the data block is associated with. The Data Block wizard sets the Alias property to the first letter of the table name. (For example, a table named DEPT would have a default alias of D.)
Include REF Item property
Creates a hidden item called REF for this block. This item is used internally to coordinate master-detail relationships built on a REF link. This item also can be used programmatically to access the object Id (OID) of a row in an object table.
WHERE Clause/ORDER BY Clause
The defaults WHERE Clause and default ORDER BY Clause properties specify standard SQL clauses for the default SELECT statement associated with a data block. These clauses are automatically appended to the SELECT statement that Form Builder constructs and issues whenever the operator or the application executes a query in the block.
Optimizer Hint property
Specifies a hint string that Form Builder passes on to the RDBMS optimizer when constructing queries. Using the optimizer can improve the performance of database transactions.
Insert Allowed (Block) property
Specifies whether records can be inserted in the block.
Update Allowed (Block) property
Determines whether end users can modify the values of items in the block that have the Update Allowed item property set to Yes. (Setting Update Allowed to No for the block overrides the Update Allowed setting of any items in the block.)
Locking Mode property
Specifies when Form Builder tries to obtain database locks on rows that correspond to queried records in the form. The following table describes the allowed settings for the Locking Mode property:
Automatic (default): Identical to Immediate if the datasource is an Oracle database. For other datasources, Form Builder determines the available locking facilities and behaves as much like Immediate as possible.
Immediate: Form Builder locks the corresponding row as soon as the end user presses a key to enter or edit the value in a text item.
Delayed: Form Builder locks the row only while it posts the transaction to the database, not while the end-user is editing the record. Form Builder prevents the commit action from processing if values of the fields in the block have changed when the user causes a commit action.
Delete Allowed property
Specifies whether records can be deleted from the block.
Key Mode property
Specifies how Form Builder uniquely identifies rows in the database. By default, the ORACLE database uses unique ROWID values to identify each row. Non-ORACLE databases do not include the ROWID construct, but instead rely solely on unique primary key values to identify unique rows. If you are creating a form to run against a non-ORACLE data source, you must use primary keys, and set the Key Mode block property accordingly.
Automatic (default): Specifies that Form Builder should use ROWID constructs to identify unique rows in the datasource but only if the datasource supports ROWID.
Non-Updateable: Specifies that Form Builder should not include primary key columns in any UPDATE statements. Use this setting if your database does not allow primary key values to be updated.
Unique: Instructs Form Builder to use ROWID constructs to identify unique rows in an ORACLE database.
Updateable: Specifies that Form Builder should issue UPDATE statements that include primary key values. Use this setting if your database allows primary key columns to be updated and you intend for the application to update primary key values.
Update Changed Columns Only property
When queried records have been marked as updates, specifies that only columns whose values were actually changed should be included in the SQL UPDATE statement that is sent to the database during a COMMIT. By default, Update Changed Columns Only is set to No, and all columns are included in the UPDATE statement.
Enforce Column Security property
Specifies when Form Builder should enforce update privileges on a column-by-column basis for the block's base table. If an end user does not have update privileges on a particular column in the base table, Form Builder makes the corresponding item non-updateable for this end user only, by turning off the Update Allowed item property at form startup.
Yes: Form Builder enforces the update privileges that are defined in the database for the current end user.
No: Form Builder does not enforce the defined update privileges.
DML Data Target Type property
Specifies the block's DML data target type. A DML data target type can be a Table, Procedure, or Transactional Trigger.
DML Data Target Name property
Specifies the name of the block's DML data target. The DML Data Target Name property is valid only when the DML Data Target Type property is set to Table.
Visual Attribute Group property
Specifies how the object's individual attribute settings (Font Name, Background Color, Fill Pattern, etc.) are derived.
Default: Specifies 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 platform.
Named visual attribute: Specifies a named visual attribute that should be applied to the object. Named visual attributes are separate objects that you create in the Object Navigator and then apply to interface objects, much like styles in a word processing program. When Visual Attribute Group is set to a named visual attribute, the individual attribute settings reflect the attribute settings defined for the named visual attribute object. When the current form does not contain any named visual attributes, the poplist for this property will show Default.
White on Black property
Specifies that the object is to appear on a monochrome bitmap display device as white text on a black background.
Listed in Data Block Menu/Data Block Description
Specifies whether the block should be listed in the block menu and, if so, the description that should be used for the block.
Form Builder has a built-in block menu that allows end users to invoke a list of blocks in the current form by pressing [Block Menu]. When the end user selects a block from the list, Form Builder navigates to the first enterable item in the block.
Analytic Functions
Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic clause. For each row, a "sliding" window of rows is defined. The window determines the range of rows used to perform the calculations for the "current row". Window sizes can be based on either a physical number of rows or a logical interval such as time.
Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.
The Syntax
The Syntax of analytic functions is rather straightforward in appearance
Analytic-Function (<Argument>, <Argument>,...)
OVER (
<Query-Partition-Clause>
<Order-By-Clause>
<Windowing-Clause>
)
• Analytic-Function
Specify the name of an analytic function, Oracle actually provides many analytic functions such as AVG, CORR, COVAR_POP, COVAR_SAMP, COUNT, CUME_DIST, DENSE_RANK, FIRST, FIRST_VALUE, LAG, LAST, LAST_VALUE, LEAD, MAX, MIN, NTILE, PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC, RANK, RATIO_TO_REPORT, STDDEV, STDDEV_POP, STDDEV_SAMP, SUM, VAR_POP, VAR_SAMP, VARIANCE.
• Arguments
Analytic functions take 0 to 3 arguments.
• Query-Partition-Clause
The PARTITION BY clause logically breaks a single result set into N groups, according to the criteria set by the partition expressions. The words "partition" and "group" are used synonymously here. The analytic functions are applied to each group independently; they are reset for each group.
• Order-By-Clause
The ORDER BY clause specifies how the data is sorted within each group (partition). This will definitely affect the outcome of any analytic function.
• Windowing-Clause
The windowing clause gives us a way to define a sliding or anchored window of data, on which the analytic function will operate, within a group. This clause can be used to have the analytic function compute its value based on any arbitrary sliding or anchored window within a group.
Commit processing is the way Form Builder attempts to make the data in the database identical to the data in the form. Form Builder's normal cycle of operation is:
1 Read records from the database.
2 Allow the end user to make tentative insertions, updates, and deletions. The tentative changes appear only in the form. The database remains unchanged.
3 Post changes to the database. Form Builder does all of its remaining processing and sends the data to the database. After posting the data, Form Builder can only roll back the changes (via the [Clear Form] function key or CLEAR_FORM built-in) or commit them.
4 Form Builder commits the posted changes. They become permanent changes to the database.
The term commit processing refers to steps 3 and 4 of the above cycle. Normally these steps occur together. In response to the [Commit] key or invocation of the COMMIT_FORM built-in, Form Builder firsts posts, then commits the data to the database.
Posting can occur separately before committing. End users cannot issue posting commands, but triggers can invoke the POST built-in.
Note: Form Builder does not support a commit that does not include the normal Form Builder commit processing. For example, Form Builder does not support a commit issued from an Oracle Precompiled user exit or from a stored procedure.
Processing inserts, updates, and deletes
Posting consists of sending tentative changes from the form to the database. These are records that have been marked for insertion, update, or deletion since the last post. During posting, Form Builder processes inserts, updates, and deletes for all blocks in a form. Form Builder has a standard way to process these changes. Triggers provide a flexible mechanism for altering the standard behavior.
Insert: An insert is the pending insertion of a row in the database. Each insert has an associated SQL statement, which Form Builder executes when it posts the insert. The statement has the following form:
INSERT INTO table [(column, column, . . .)] VALUES (value, value, . . .);
Table The name of the base table for the current block.
Column A column corresponding to a base table item. If an item is a derived column, it does not appear in the column clause.
Value The value to insert into the corresponding column.
Update: An update is the pending update of an existing row in the database. Each update has an associated SQL statement, which Form Builder executes when it posts the update. The statement has the following form:
UPDATE table SET (column=value, column=value, . . .) WHERE ROWID=rowid_value;
Table The name of the base table for the current block.
Column A column corresponding to a base table item. If an item is a derived column, it does not appear in the column clause.
Value The value to update the corresponding column.
Rowid_value The ROWID value for the row Form Builder is updating.
Note:
The WHERE clause specifies only a ROWID value. This identifies the unique row that the database should update. Form Builder uses the ROWID construct only when the block's Key Mode property has the value Unique_Key (the default).
If an end user does not have update privileges for a column, and the block's Enforce Column Security property has the value Yes, Form Builder does not include the column in the UPDATE statement. Different end users can have different UPDATE statements, depending on their privileges, but the statement remains unchanged during an end user's Form Builder session.
Delete: A delete is the pending deletion of a row in the database. Each delete has an associated SQL statement, which Form Builder executes when it posts the delete. The statement has the following form:
DELETE FROM table WHERE ROWID=rowid_value;
Table The name of the base table for the current block.
Rowid_value The ROWID value for the row Form Builder is deleting.
Note: The WHERE clause specifies only a ROWID value. This identifies the unique row that the database should delete. Form Builder does not use the ROWID value for non-Oracle data sources.
Caution: If a commit fails, the ROWID value may not have a null value. Use record status rather than the ROWID value to test for success or failure of the commit.
When commit processing occurs
Form Builder performs commit processing when
A trigger or the [Commit] key invokes the COMMIT_FORM built-in.
Database items in the current block have changed since the last commit, and a trigger invokes the CLEAR_BLOCK built-in (with the DO_COMMIT argument).
Database items in the form have changed since the last commit, and a trigger invokes the CLEAR_FORM built-in (with the DO_COMMIT argument).
An end user answers Yes to the alert that asks
Do you want to commit the changes you have made?
The alert appears as a result of any of the following:
Database items in the current block have changed since the last commit, and any of the following events occurs:
CLEAR_BLOCK (with the ASK_COMMIT argument or with no argument)
COUNT_QUERY
ENTER_QUERY
EXECUTE_QUERY
NEW_FORM
Database items in any block of the form have changed since the last commit, and any of the following events occurs:
CLEAR_FORM (with the ASK_COMMIT argument or with no argument)
Leave the Form
Note: When a PL/SQL block issues a database commit from within Form Builder (via the SQL COMMIT statement), Form Builder commit processing occurs as if the COMMIT_FORM built-in had been invoked.
Changing data during commit processing
Commit processing performs validation and can fire triggers. As a result, a commit event can change database items. For some triggers Form Builder attempts to commit those changes to the database during the current commit event.
Caution: Form Builder can commit un-validated database changes made by the PRE-DELETE, PRE-INSERT, and PRE-UPDATE triggers.
For example, assume that a PRE-INSERT trigger selects a value into a base table item.
If Form Builder has not already processed the affected record's updates, it commits the changes during the current commit event without validating them.
If Form Builder has already processed the affected record's updates, it does not commit the changes, but it marks the records as having come from the database. Subsequently attempting to update the record before it is committed causes an error.
Caution: Form Builder can commit un-validated database changes made by the POST-COMMIT, POST-DELETE, POST-INSERT, and POST-UPDATE triggers.
For example, assume that a POST-INSERT trigger selects a value into a base table item.
If the affected record is in a block that the commit has processed or is processing, Form Builder does not commit the changes during the current commit event. If the commit succeeds, Form Builder marks all items and records as Valid. This results in an error during subsequent processing.
If the affected record is in a block that the commit has not yet processed, Form Builder validates the changes and commits them during the current commit event.
Replacing standard commit processing
The ON-INSERT, ON-UPDATE, and ON-DELETE triggers replace standard commit processing.
9I SQL FEATURES
NVL2 lets you determine the value returned by a query based on whether a specified expression is null or not null. If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3. The argument expr1 can have any data type. The arguments expr2 and expr3 can have any data types except LONG.
If the data types of expr2 and expr3 are different, then Oracle converts expr3 to the data type of expr2 before comparing them unless expr3 is a null constant. In that case, a data type conversion is not necessary.
The data type of the return value is always the same as the data type of expr2, unless expr2 is character data, in which case the return value's data type is VARCHAR2.
NULLIF (expr1, expr2) –Returns NULL if the first argument is equal to the second, otherwise returns the first argument.
COALESCE (expr1, expr2, expr3, ...) –Returns the first non-null argument.
SELECT... FOR UPDATE Enhancements
Selecting a record for update that is already locked causes the current session to hang indefinitely until the lock is released. If this situation is unacceptable the NOWAIT keyword can be used to instantly return an error if the record is locked. Oracle9i adds more flexibility by allowing the programmer to specify a maximum time limit to wait for a lock before returning an error. This gets round the problem of indefinite waits, but reduces the chances of lock errors being returned:
SELECT * FROM employees WHERE empno = 20 FOR UPDATE WAIT 30;
Merge Statement
The MERGE statement can be used to conditionally insert or update data depending on its presence. This method reduces table scans and can perform the operation in parallel. Consider the following example where data from the HR_RECORDS table is merged into the EMPLOYEES table:
MERGE INTO employees e
USING hr_records h
ON (e.id = h.emp_id)
WHEN MATCHED THEN
UPDATE SET e.address = h.address
WHEN NOT MATCHED THEN
INSERT (id, address)
VALUES (h.emp_id, h.address);
Explicitly Named Indexes On Keys
In Oracle9i the index used to support primary and unique keys can be defined independently of the constraint itself by using the CREATE INDEX syntax within the USING UNDEX clause of the CREATE TABLE statement:
CREATE TABLE employees
(
Empno NUMBER (6),
Name VARCHAR2 (30),
Deptno NUMBER (2),
CONSTRAINT emp_pk primary key (empno)
USING INDEX
(CREATE INDEX emp_pk_idx ON employee (empno))
);
The constraint can subsequently be dropped without dropping the index using either syntax:
ALTER TABLE employees DROP PRIMARY KEY KEEP INDEX;
ALTER TABLE employees DROP CONSTRAINT empno_pk;
Share Locks On Unindexed FKs
In previous versions a share lock was issued on the entire child table while the parent table was being updated if the foreign key between them was unindexed. This had the affect of preventing DML operations on the child table until the parent table transaction was complete.
In Oracle9i this situation has been altered such that a table level share lock is issued and instantly released. This action allows Oracle to check that there are no pending changes on the child table, but the instant release means that DML can resume almost instantly once the parent table update has initiated. If multiple keys are updated Oracle issues a share lock and release on the child table for each row.
PK Lookup During FK Insertion
During insertions foreign key values are checked against the primary keys of referenced tables. This process is optimized in Oracle9i by caching the first 256 PK values of the referenced table on insertion of the second record of a multiple insert. The process is done on the second record to prevent the overhead of managing the cache on a single insert.
Function Based Index Enhancements
Function Based Indexes are now capable of doing an index-only scan. In previous versions this was only possible if the index creation statement explicitly prevented NULL values. Since each built-in operator knows implicitly whether it can produce null values when all it's input parameters are not null, Oracle can deduce if nulls can be produced and therefore decide if index-only scans are possible based on the columns queried using the function based index.
View Constraints
Declarative primary key, unique key and foreign key constraints can now be defined against views. The NOT NULL constraint is inherited from the base table so it cannot be declared explicitly. The constraints are not validated so they must be defined with the DISABLE NOVALIDATE clause:
CREATE VIEW Emp_view
(Id PRIMARY KEY DISABLE NOVALIDATE, firstname)
AS SELECT employee_id, first_name
FROM employees
WHERE department_id = 10;
ALTER VIEW Emp_view
ADD CONSTRAINT emp_view_unq
UNIQUE (first_name) DISABLE NOVALIDATE;
Analytic Functions
Analytic functions compute an aggregate value based on a group of rows. They differ from aggregate functions in that they return multiple rows for each group. The group of rows is called a window and is defined by the analytic clause. For each row, a "sliding" window of rows is defined. The window determines the range of rows used to perform the calculations for the "current row". Window sizes can be based on either a physical number of rows or a logical interval such as time.
Analytic functions are the last set of operations performed in a query except for the final ORDER BY clause. All joins and all WHERE, GROUP BY, and HAVING clauses are completed before the analytic functions are processed. Therefore, analytic functions can appear only in the select list or ORDER BY clause.
The Syntax
The Syntax of analytic functions is rather straightforward in appearance
Analytic-Function (<Argument>, <Argument>,...)
OVER (
<Query-Partition-Clause>
<Order-By-Clause>
<Windowing-Clause>
)
• Analytic-Function
Specify the name of an analytic function, Oracle actually provides many analytic functions such as AVG, CORR, COVAR_POP, COVAR_SAMP, COUNT, CUME_DIST, DENSE_RANK, FIRST, FIRST_VALUE, LAG, LAST, LAST_VALUE, LEAD, MAX, MIN, NTILE, PERCENT_RANK, PERCENTILE_CONT, PERCENTILE_DISC, RANK, RATIO_TO_REPORT, STDDEV, STDDEV_POP, STDDEV_SAMP, SUM, VAR_POP, VAR_SAMP, VARIANCE.
• Arguments
Analytic functions take 0 to 3 arguments.
• Query-Partition-Clause
The PARTITION BY clause logically breaks a single result set into N groups, according to the criteria set by the partition expressions. The words "partition" and "group" are used synonymously here. The analytic functions are applied to each group independently; they are reset for each group.
• Order-By-Clause
The ORDER BY clause specifies how the data is sorted within each group (partition). This will definitely affect the outcome of any analytic function.
• Windowing-Clause
The windowing clause gives us a way to define a sliding or anchored window of data, on which the analytic function will operate, within a group. This clause can be used to have the analytic function compute its value based on any arbitrary sliding or anchored window within a group.
Commit processing is the way Form Builder attempts to make the data in the database identical to the data in the form. Form Builder's normal cycle of operation is:
1 Read records from the database.
2 Allow the end user to make tentative insertions, updates, and deletions. The tentative changes appear only in the form. The database remains unchanged.
3 Post changes to the database. Form Builder does all of its remaining processing and sends the data to the database. After posting the data, Form Builder can only roll back the changes (via the [Clear Form] function key or CLEAR_FORM built-in) or commit them.
4 Form Builder commits the posted changes. They become permanent changes to the database.
The term commit processing refers to steps 3 and 4 of the above cycle. Normally these steps occur together. In response to the [Commit] key or invocation of the COMMIT_FORM built-in, Form Builder firsts posts, then commits the data to the database.
Posting can occur separately before committing. End users cannot issue posting commands, but triggers can invoke the POST built-in.
Note: Form Builder does not support a commit that does not include the normal Form Builder commit processing. For example, Form Builder does not support a commit issued from an Oracle Precompiled user exit or from a stored procedure.
Processing inserts, updates, and deletes
Posting consists of sending tentative changes from the form to the database. These are records that have been marked for insertion, update, or deletion since the last post. During posting, Form Builder processes inserts, updates, and deletes for all blocks in a form. Form Builder has a standard way to process these changes. Triggers provide a flexible mechanism for altering the standard behavior.
Insert: An insert is the pending insertion of a row in the database. Each insert has an associated SQL statement, which Form Builder executes when it posts the insert. The statement has the following form:
INSERT INTO table [(column, column, . . .)] VALUES (value, value, . . .);
Table The name of the base table for the current block.
Column A column corresponding to a base table item. If an item is a derived column, it does not appear in the column clause.
Value The value to insert into the corresponding column.
Update: An update is the pending update of an existing row in the database. Each update has an associated SQL statement, which Form Builder executes when it posts the update. The statement has the following form:
UPDATE table SET (column=value, column=value, . . .) WHERE ROWID=rowid_value;
Table The name of the base table for the current block.
Column A column corresponding to a base table item. If an item is a derived column, it does not appear in the column clause.
Value The value to update the corresponding column.
Rowid_value The ROWID value for the row Form Builder is updating.
Note:
The WHERE clause specifies only a ROWID value. This identifies the unique row that the database should update. Form Builder uses the ROWID construct only when the block's Key Mode property has the value Unique_Key (the default).
If an end user does not have update privileges for a column, and the block's Enforce Column Security property has the value Yes, Form Builder does not include the column in the UPDATE statement. Different end users can have different UPDATE statements, depending on their privileges, but the statement remains unchanged during an end user's Form Builder session.
Delete: A delete is the pending deletion of a row in the database. Each delete has an associated SQL statement, which Form Builder executes when it posts the delete. The statement has the following form:
DELETE FROM table WHERE ROWID=rowid_value;
Table The name of the base table for the current block.
Rowid_value The ROWID value for the row Form Builder is deleting.
Note: The WHERE clause specifies only a ROWID value. This identifies the unique row that the database should delete. Form Builder does not use the ROWID value for non-Oracle data sources.
Caution: If a commit fails, the ROWID value may not have a null value. Use record status rather than the ROWID value to test for success or failure of the commit.
When commit processing occurs
Form Builder performs commit processing when
A trigger or the [Commit] key invokes the COMMIT_FORM built-in.
Database items in the current block have changed since the last commit, and a trigger invokes the CLEAR_BLOCK built-in (with the DO_COMMIT argument).
Database items in the form have changed since the last commit, and a trigger invokes the CLEAR_FORM built-in (with the DO_COMMIT argument).
An end user answers Yes to the alert that asks
Do you want to commit the changes you have made?
The alert appears as a result of any of the following:
Database items in the current block have changed since the last commit, and any of the following events occurs:
CLEAR_BLOCK (with the ASK_COMMIT argument or with no argument)
COUNT_QUERY
ENTER_QUERY
EXECUTE_QUERY
NEW_FORM
Database items in any block of the form have changed since the last commit, and any of the following events occurs:
CLEAR_FORM (with the ASK_COMMIT argument or with no argument)
Leave the Form
Note: When a PL/SQL block issues a database commit from within Form Builder (via the SQL COMMIT statement), Form Builder commit processing occurs as if the COMMIT_FORM built-in had been invoked.
Changing data during commit processing
Commit processing performs validation and can fire triggers. As a result, a commit event can change database items. For some triggers Form Builder attempts to commit those changes to the database during the current commit event.
Caution: Form Builder can commit un-validated database changes made by the PRE-DELETE, PRE-INSERT, and PRE-UPDATE triggers.
For example, assume that a PRE-INSERT trigger selects a value into a base table item.
If Form Builder has not already processed the affected record's updates, it commits the changes during the current commit event without validating them.
If Form Builder has already processed the affected record's updates, it does not commit the changes, but it marks the records as having come from the database. Subsequently attempting to update the record before it is committed causes an error.
Caution: Form Builder can commit un-validated database changes made by the POST-COMMIT, POST-DELETE, POST-INSERT, and POST-UPDATE triggers.
For example, assume that a POST-INSERT trigger selects a value into a base table item.
If the affected record is in a block that the commit has processed or is processing, Form Builder does not commit the changes during the current commit event. If the commit succeeds, Form Builder marks all items and records as Valid. This results in an error during subsequent processing.
If the affected record is in a block that the commit has not yet processed, Form Builder validates the changes and commits them during the current commit event.
Replacing standard commit processing
The ON-INSERT, ON-UPDATE, and ON-DELETE triggers replace standard commit processing.
9I SQL FEATURES
NVL2 lets you determine the value returned by a query based on whether a specified expression is null or not null. If expr1 is not null, then NVL2 returns expr2. If expr1 is null, then NVL2 returns expr3. The argument expr1 can have any data type. The arguments expr2 and expr3 can have any data types except LONG.
If the data types of expr2 and expr3 are different, then Oracle converts expr3 to the data type of expr2 before comparing them unless expr3 is a null constant. In that case, a data type conversion is not necessary.
The data type of the return value is always the same as the data type of expr2, unless expr2 is character data, in which case the return value's data type is VARCHAR2.
NULLIF (expr1, expr2) –Returns NULL if the first argument is equal to the second, otherwise returns the first argument.
COALESCE (expr1, expr2, expr3, ...) –Returns the first non-null argument.
SELECT... FOR UPDATE Enhancements
Selecting a record for update that is already locked causes the current session to hang indefinitely until the lock is released. If this situation is unacceptable the NOWAIT keyword can be used to instantly return an error if the record is locked. Oracle9i adds more flexibility by allowing the programmer to specify a maximum time limit to wait for a lock before returning an error. This gets round the problem of indefinite waits, but reduces the chances of lock errors being returned:
SELECT * FROM employees WHERE empno = 20 FOR UPDATE WAIT 30;
Merge Statement
The MERGE statement can be used to conditionally insert or update data depending on its presence. This method reduces table scans and can perform the operation in parallel. Consider the following example where data from the HR_RECORDS table is merged into the EMPLOYEES table:
MERGE INTO employees e
USING hr_records h
ON (e.id = h.emp_id)
WHEN MATCHED THEN
UPDATE SET e.address = h.address
WHEN NOT MATCHED THEN
INSERT (id, address)
VALUES (h.emp_id, h.address);
Explicitly Named Indexes On Keys
In Oracle9i the index used to support primary and unique keys can be defined independently of the constraint itself by using the CREATE INDEX syntax within the USING UNDEX clause of the CREATE TABLE statement:
CREATE TABLE employees
(
Empno NUMBER (6),
Name VARCHAR2 (30),
Deptno NUMBER (2),
CONSTRAINT emp_pk primary key (empno)
USING INDEX
(CREATE INDEX emp_pk_idx ON employee (empno))
);
The constraint can subsequently be dropped without dropping the index using either syntax:
ALTER TABLE employees DROP PRIMARY KEY KEEP INDEX;
ALTER TABLE employees DROP CONSTRAINT empno_pk;
Share Locks On Unindexed FKs
In previous versions a share lock was issued on the entire child table while the parent table was being updated if the foreign key between them was unindexed. This had the affect of preventing DML operations on the child table until the parent table transaction was complete.
In Oracle9i this situation has been altered such that a table level share lock is issued and instantly released. This action allows Oracle to check that there are no pending changes on the child table, but the instant release means that DML can resume almost instantly once the parent table update has initiated. If multiple keys are updated Oracle issues a share lock and release on the child table for each row.
PK Lookup During FK Insertion
During insertions foreign key values are checked against the primary keys of referenced tables. This process is optimized in Oracle9i by caching the first 256 PK values of the referenced table on insertion of the second record of a multiple insert. The process is done on the second record to prevent the overhead of managing the cache on a single insert.
Function Based Index Enhancements
Function Based Indexes are now capable of doing an index-only scan. In previous versions this was only possible if the index creation statement explicitly prevented NULL values. Since each built-in operator knows implicitly whether it can produce null values when all it's input parameters are not null, Oracle can deduce if nulls can be produced and therefore decide if index-only scans are possible based on the columns queried using the function based index.
View Constraints
Declarative primary key, unique key and foreign key constraints can now be defined against views. The NOT NULL constraint is inherited from the base table so it cannot be declared explicitly. The constraints are not validated so they must be defined with the DISABLE NOVALIDATE clause:
CREATE VIEW Emp_view
(Id PRIMARY KEY DISABLE NOVALIDATE, firstname)
AS SELECT employee_id, first_name
FROM employees
WHERE department_id = 10;
ALTER VIEW Emp_view
ADD CONSTRAINT emp_view_unq
UNIQUE (first_name) DISABLE NOVALIDATE;
Form Level Properties
Specifies the name of the window that should display the Form Builder console. The console includes the status line and message line, and is displayed at the bottom of the window.
On Microsoft Windows, the console is always displayed on the MDI application window, rather than on any particular window in the form; however, you must still set this property to the name of a form window to indicate that you want the console to be displayed.
Menu Source property
Menu Source allows you to specify the location of the .MMX run file when you attach a custom menu to a form module. Form Builder loads the .MMX file at form startup.
Menu Module property
Specifies the name of the menu to use with this form.
Initial Menu property
Specifies the name of the individual menu in the menu module that Form Builder should use as the main, or top-level, menu for this invocation.
Defer Required Enforcement property
It specifies whether Form Builder should defer enforcement of the required item attribute until the record is validated.
This property applies only when item-level validation is in effect. By default, when an item has required set to true, Form Builder will not allow navigation out of the item until a valid value is entered. This behavior will be in effect if you set Defer Required Enforcement to No.
If you set Defer Required Enforcement to Yes or 4.5, you allow the end user to move freely among the items in the record, even if they are null, postponing enforcement of the required attribute until validation occurs at the record level.
When Defer Required Enforcement is set to 4.5, null-valued required items are not validated when navigated out of, and the item's Item Is Valid property is unchanged. However, the WHEN-VALIDATE-ITEM trigger (if any) does fire. If it fails (raises Form_Trigger_Failure), the item is considered to have failed validation and Form Builder will issue an error. If the trigger ends normally, processing continues normally. If the item value is still null when record-level validation occurs later, Form Builder will issue an error at that time.
Mouse Navigation Limit property
Determines how far outside the current item an end user can navigate with the mouse.
Form (The default): Allow end users to navigate to any item in the current form.
Block: Allows end users to navigate only to items that are within the current block.
Record: Allows end users to navigate only to items that are within the current record.
Item: Prevents end users from navigating out of the current item. This setting prevents end users from navigating with the mouse at all.
First Navigation Block property
Specifies the name of the block to which Form Builder should navigate at form startup and after a CLEAR_FORM operation.
Current Record Visual Attribute Group property
Specifies the named visual attribute used when an item is part of the current record. This property can be set at the form, block, or item level, or at any combination of levels. If you specify named visual attributes at each level, the item-level attribute overrides all others, and the block-level overrides the form-level.
Validation Unit property
The validation unit defines the maximum amount of data that an operator can enter in the form before Form Builder initiates validation.
Interaction Mode property
Interaction mode dictates how a user can interact with a form during a query. If Interaction Mode is set to Blocking, then users are prevented from resizing or otherwise interacting with the form until the records for a query are fetched from the database. If set to Non-Blocking, then end users can interact with the form while records are being fetched.
Non-blocking interaction mode is useful if you expect the query will be time-consuming and you want the user to be able to interrupt or cancel the query. In this mode, the Forms runtime will display a dialog that allows the user to cancel the query.
You cannot set the interaction mode programmatically, however, you can obtain the interaction mode programmatically using the GET_FORM_PROPERTY built-in.
Maximum Query Time property
Provides the option to abort a query when the elapsed time of the query exceeds the value of this property.
Maximum Records Fetched property
Specifies the number of records fetched when running a query before the query is aborted.
Isolation Mode property
Specifies whether or not transactions in a session will be serializable. If Isolation Mode has the value serializable, the end user sees a consistent view of the database for the entire length of the transaction, regardless of updates committed by other users from other sessions. If the end user queries and changes a row, and a second user updates and commits the same row from another session, the first user sees Oracle error (ORA-08177: Cannot serialize access.).
Coordinate System property
Specifies whether object size and position values should be interpreted as character cell values, or as real units (centimeters, inches, pixels, or points).
Character: Sets the coordinate system to a character cell-based measurement. The actual size and position of objects will depend on the size of a default character on your particular platform.
Real: Sets the coordinate system to the unit of measure specified by the Real Unit property (centimeters, inches, pixels, or points.)
Changing the coordinate system for the form changes the ruler units displayed on Form Editor rulers, but does not change the grid spacing and snap-points settings.
Use 3D Controls property
On Microsoft Windows, specifies that Form Builder display items with a 3-dimensional, beveled look.
When Use 3D Controls is set to Yes, any canvas that has Visual Attribute Group set to Default will automatically be displayed with background color grey.
In addition, when Use 3D Controls is set to Yes, the bevel for each item automatically appears lowered, even if an item-level property is set, for example, to raised.
Form Horizontal Toolbar Canvas property
On Microsoft Windows, specifies the canvas that should be displayed as a horizontal toolbar on the MDI application window. The canvas specified must have the Canvas Type property set to Horizontal Toolbar.
Form Horizontal Toolbar Canvas property
On Microsoft Windows, specifies the toolbar canvas that should be displayed as a vertical toolbar on the MDI application window. The canvas specified must have the Canvas Type property set to Vertical Toolbar.
Direction property
This property is used to define the test justification for all items.
Value Description
Default Direction based on the property shown in the table.
Right-To-Left Direction is right-to-left.
Left-To-Right Direction is left-to-right.