Using SYSTEM Variables in Oracle Forms

Oracle Forms provides a wide range of built-in features to make application development easier, and one of the most important among them is SYSTEM variables. These predefined variables give developers instant access to information about the runtime environment, user actions, and current form state. By using them effectively, you can create dynamic, responsive, and intelligent forms without writing extra code for common tasks.

This article will explain what SYSTEM Variables in Oracle Forms are, the different categories available, how to use them with examples, and best practices to follow.

What are SYSTEM Variables in Oracle Forms?

SYSTEM variables are predefined variables provided by Oracle Forms that store the current values of certain attributes of the application environment. These variables are read-only, which means you cannot assign values to them directly, but you can use their values to control the behavior of your form or to provide better user experiences.

For example, you can check whether the form is in Insert or Update mode, display the current block or record number, or retrieve information about the current user session.

Categories of SYSTEM Variables

Oracle Forms provides a wide range of SYSTEM variables, and they can be grouped into categories based on their functionality. Below are some of the most commonly used categories:

1. Transaction Control Variables

These variables help you understand the current transaction state:

• SYSTEM.FORM_STATUS → Shows whether the form is in CHANGED or QUERY mode.
• SYSTEM.RECORD_STATUS → Shows if the current record is NEW, INSERT, or CHANGED.
• SYSTEM.CURSOR_BLOCK → Returns the name of the current block where the cursor is located.

2. Navigation Variables

These variables provide details about where the cursor is in the form:

• SYSTEM.CURSOR_ITEM → Returns the name of the current item.
• SYSTEM.CURSOR_RECORD → Returns the number of the current record.
• SYSTEM.CURSOR_BLOCK → Indicates the block where the cursor is currently active.

3. User Environment Variables

These variables provide runtime environment information:

• SYSTEM.USERNAME → Returns the Oracle database username of the connected user.
• SYSTEM.LAST_QUERY → Stores the last query executed in the block.
• SYSTEM.TRIGGER_ITEM → Shows which item triggered the current event.

4. Query and Transaction Mode Variables

• SYSTEM.MODE → Shows if the form is in NORMAL or ENTER-QUERY mode.
• SYSTEM.LOCK_STATUS → Returns whether the current record is locked or not.

How to Use SYSTEM Variables

You cannot assign values to SYSTEM variables, but you can use them in triggers, procedures, and conditions to build logic.

Example 1: Checking Form Status

IF :SYSTEM.FORM_STATUS = 'CHANGED' THEN
   MESSAGE('You have unsaved changes in this form.');
   MESSAGE(' ');
END IF;

This checks whether the user has made changes in the form but has not yet saved them.

Example 2: Display Current Record Number

MESSAGE('You are currently on record number: ' || :SYSTEM.CURSOR_RECORD);
MESSAGE(' ');

This will display the record number where the user is currently working.

Example 3: Restrict Actions Based on Mode

IF :SYSTEM.MODE = 'ENTER-QUERY' THEN
   MESSAGE('You cannot perform this action in Query Mode.');
   MESSAGE(' ');
   RAISE FORM_TRIGGER_FAILURE;
END IF;

This ensures that a button or process does not run while the form is in query mode.

Example 4: Identifying Logged-In User

MESSAGE('You are logged in as: ' || :SYSTEM.USERNAME);
MESSAGE(' ');

This retrieves the database username of the person currently using the form.

Advantages of Using SYSTEM Variables in Oracle Forms

• Simplifies Development: No need to write extra queries to get session or form state details.
• Improves Control: Helps you manage form behavior based on transaction modes and user actions.
• Enhances User Experience: Provides meaningful messages and feedback to users.
• Supports Validation: Ensures certain actions are only allowed in the right mode (Insert, Update, Query).
• Saves Time: Eliminates the need to declare and maintain custom variables for common system values.

Best Practices for Using SYSTEM Variables

1. Use Conditions to Control Logic
   Always check SYSTEM.MODE or SYSTEM.FORM_STATUS before executing database changes.
2. Keep Messages User-Friendly
   While using SYSTEM variables in messages, provide clear text that end-users can understand.
3. Combine with Triggers
   SYSTEM variables are most powerful when used with triggers like WHEN-NEW-FORM-INSTANCE, PRE-INSERT, WHEN-BUTTON-PRESSED, etc.
4. Avoid Overusing in Performance-Heavy Code
   While SYSTEM variables are efficient, calling them unnecessarily in loops or large queries should be minimized.
5. Document Your Usage
   Since SYSTEM variables often control form logic, always document where and why they are being used for easier maintenance.

Commonly Used SYSTEM Variables at a Glance

• SYSTEM.FORM_STATUS → Form status (CHANGED, QUERY).
• SYSTEM.RECORD_STATUS → Record status (NEW, CHANGED, INSERT).
• SYSTEM.CURSOR_ITEM → Current item in focus.
• SYSTEM.CURSOR_RECORD → Record number in focus.
• SYSTEM.MODE → Form mode (NORMAL, ENTER-QUERY).
• SYSTEM.USERNAME → Database username of the session.
• SYSTEM.LAST_QUERY → Most recent query executed in the block.

See also: Understanding Global Variables in Oracle Forms

Conclusion

SYSTEM Variables in Oracle Forms are powerful built-in variables that give you immediate access to essential information about the form’s current state, user session, and runtime environment. They make it easier to build smarter applications, validate user actions, display meaningful messages, and control the workflow. When used correctly, these variables can significantly improve both the efficiency of your development process and the usability of your applications.

By combining SYSTEM variables with triggers and PL/SQL logic, developers can create highly dynamic Oracle Forms that respond intelligently to user interactions.