Using SET_ITEM_INSTANCE_PROPERTY for Specific Records in Oracle Forms

In Oracle Forms, developers often need to apply dynamic changes to individual records in a multi-record block rather than affecting all records at once. The built-in SET_ITEM_INSTANCE_PROPERTY makes this possible. With this built-in, you can alter the appearance, functionality, and accessibility of a particular item instance tied to a specific record, giving you greater control over user interaction and form behavior.

This tutorial explains how to use SET_ITEM_INSTANCE_PROPERTY effectively, demonstrates practical examples, and highlights scenarios where it can significantly improve the usability of your Oracle Forms applications.

A. Understanding SET_ITEM_INSTANCE_PROPERTY

SET_ITEM_INSTANCE_PROPERTY is used when you want to manipulate a single occurrence of an item in a multi-record block. Unlike SET_ITEM_PROPERTY, which affects the entire item across all records, the instance-level version applies changes to just one record at a time.

A.1 Syntax

SET_ITEM_INSTANCE_PROPERTY (
   item_name      VARCHAR2,
   instance_number NUMBER,
   property       NUMBER,
   value          NUMBER
);

• item_name: The block and item name (e.g., EMPLOYEES.SALARY).
• instance_number: Identifies the specific record instance (row number within the block).
• property: The property constant you want to change (e.g., VISUAL_ATTRIBUTE, ENABLED, NAVIGABLE).
• value: The new value for the property (e.g., PROPERTY_TRUE, PROPERTY_FALSE, or a visual attribute name).

B. Commonly Used Properties

When working with SET_ITEM_INSTANCE_PROPERTY, these are the most useful properties you can set:

1. ENABLED / DISABLED – Allow or restrict data entry for a particular record.
2. NAVIGABLE – Make an item instance available or unavailable for navigation.
3. VISIBLE – Show or hide a field instance in a record.
4. VISUAL_ATTRIBUTE – Apply a specific visual style (color, font, background) to highlight or differentiate records.
5. UPDATE_ALLOWED – Permit or prevent updates for certain rows.

C. Practical Examples

C.1 Highlighting Records Based on a Condition

Suppose you want to highlight employees whose salary is above 10,000. You can apply a visual attribute only to those records:

DECLARE
   rec NUMBER;
BEGIN
   rec := 1;
   GO_BLOCK('EMPLOYEES');
   FIRST_RECORD;

   LOOP
      IF :EMPLOYEES.SALARY > 10000 THEN
         SET_ITEM_INSTANCE_PROPERTY('EMPLOYEES.SALARY', rec,
                                    VISUAL_ATTRIBUTE, 'HIGH_SALARY');
      END IF;

      EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE';
      NEXT_RECORD;
      rec := rec + 1;
   END LOOP;
END;

• Here, only the qualifying rows are highlighted, improving readability for the end-user.

C.2 Making Specific Records Read-Only

You may want to restrict updates for employees in a certain department. For instance, Department 10 employees should not be editable:

DECLARE
   rec NUMBER := 1;
BEGIN
   GO_BLOCK('EMPLOYEES');
   FIRST_RECORD;

   LOOP
      IF :EMPLOYEES.DEPTNO = 10 THEN
         SET_ITEM_INSTANCE_PROPERTY('EMPLOYEES.SALARY', rec,
                                    UPDATE_ALLOWED, PROPERTY_FALSE);
         SET_ITEM_INSTANCE_PROPERTY('EMPLOYEES.SALARY', rec,
                                    ENABLED, PROPERTY_FALSE);
      END IF;

      EXIT WHEN :SYSTEM.LAST_RECORD = 'TRUE';
      NEXT_RECORD;
      rec := rec + 1;
   END LOOP;
END;

• This ensures users cannot update salaries of employees in department 10 while still being able to view them.

C.3 Controlling Navigation Per Record

You may want to skip navigation for specific rows. For example, if an employee’s status is “INACTIVE,” you can prevent the cursor from entering those fields:

IF :EMPLOYEES.STATUS = 'INACTIVE' THEN
   SET_ITEM_INSTANCE_PROPERTY('EMPLOYEES.ENAME', :SYSTEM.TRIGGER_RECORD,
                              NAVIGABLE, PROPERTY_FALSE);
END IF;

• This avoids accidental modifications and keeps navigation smooth.

D. Differences Between SET_ITEM_PROPERTY and SET_ITEM_INSTANCE_PROPERTY

It is important to know when to use each built-in:

• SET_ITEM_PROPERTY: Changes the property for the entire item across all records (global effect).
• SET_ITEM_INSTANCE_PROPERTY: Changes the property for a specific record only (local effect).

For example:

• Making the entire SALARY item non-editable → use SET_ITEM_PROPERTY.
• Making only one record of SALARY non-editable → use SET_ITEM_INSTANCE_PROPERTY.

E. Best Practices

When using SET_ITEM_INSTANCE_PROPERTY, follow these best practices:

1. Initialize Visual Attributes – Define visual attributes in Form Builder and reuse them for clarity.
2. Minimize Hardcoding – Use variables or constants for property values to make maintenance easier.
3. Use System Variables – :SYSTEM.CURSOR_RECORD and :SYSTEM.TRIGGER_RECORD simplify working with the current record.
4. Performance Considerations – Avoid unnecessary looping; apply properties only when required.
5. Combine with Triggers – Place code in triggers like WHEN-NEW-RECORD-INSTANCE or POST-QUERY to apply changes dynamically.

F. Typical Use Cases

• Highlight overdue invoices in red while showing paid invoices in green.
• Disable editing of financial transactions that are already posted.
• Hide certain fields for specific roles or statuses.
• Prevent cursor navigation into inactive customer records.
• Apply row-level data security by disabling sensitive fields for unauthorized users.

Conclusion

SET_ITEM_INSTANCE_PROPERTY is one of the most powerful built-ins in Oracle Forms for record-level customization. Unlike its global counterpart, it gives fine-grained control over individual records, enabling developers to apply conditional formatting, disable editing, control navigation, and enhance user experience. By using it wisely with triggers like POST-QUERY and WHEN-NEW-RECORD-INSTANCE, you can make your forms dynamic, user-friendly, and aligned with business rules.

Mastering this built-in is essential for any Oracle Forms developer who wants to build applications that are both flexible and secure at the record level.