System Variables in Oracle Forms

System variables in Oracle Forms 6i are built-in variables that store values related to the form’s runtime environment. These variables help developers manage the behavior of forms, control navigation, and retrieve useful information.

Categories of System Variables

System variables in Oracle Forms 6i can be categorized into the following types:

1. Form-Level Information
2. Navigation and Block Handling
3. User and Database Information
4. Message and Error Handling
5. Runtime and Environment Information

---

1. Form-Level Information System Variables

These variables provide information about the form, module, or records.

System Variable	Description	Example
:SYSTEM.CURRENT_FORM	Returns the name of the current form.	MESSAGE(:SYSTEM.CURRENT_FORM);
:SYSTEM.CURRENT_BLOCK	Returns the name of the current block.	MESSAGE(:SYSTEM.CURRENT_BLOCK);
:SYSTEM.CURRENT_ITEM	Returns the name of the current item (including block name).	MESSAGE(:SYSTEM.CURRENT_ITEM);
:SYSTEM.CURRENT_RECORD	Returns the number of the current record in the block.	MESSAGE(:SYSTEM.CURRENT_RECORD);

🔹 Use Case:

• If you want to check which form the user is currently working on, you can use:plsqlCopyEditIF :SYSTEM.CURRENT_FORM = 'EMP_FORM' THEN MESSAGE('You are in Employee Form'); END IF;
• If you want to get the name of the active block:plCopyEditMESSAGE('Current Block: ' || :SYSTEM.CURRENT_BLOCK);

---

2. Navigation and Block Handling System Variables

These variables help track navigation inside the form.

System Variable	Description	Example
:SYSTEM.LAST_FORM	Returns the last accessed form.	MESSAGE(:SYSTEM.LAST_FORM);
:SYSTEM.LAST_RECORD	Returns the last visited record in a block.	MESSAGE(:SYSTEM.LAST_RECORD);
:SYSTEM.LAST_BLOCK	Returns the last accessed block.	MESSAGE(:SYSTEM.LAST_BLOCK);
:SYSTEM.LAST_ITEM	Returns the last visited item.	MESSAGE(:SYSTEM.LAST_ITEM);

🔹 Use Case:

• If you want to prevent the user from moving back to the last item:plsqlCopyEditIF :SYSTEM.LAST_ITEM = 'EMPLOYEE.SALARY' THEN MESSAGE('You cannot return to Salary field.'); END IF;
• If you want to check if the user is at the last record:plsqlCopyEditIF :SYSTEM.LAST_RECORD = :SYSTEM.CURRENT_RECORD THEN MESSAGE('This is the last record.'); END IF;

---

3. User and Database Information

These variables return details about the current user session.

System Variable	Description	Example
:SYSTEM.USERNAME	Returns the database user name.	MESSAGE(:SYSTEM.USERNAME);
:SYSTEM.FORM_STATUS	Checks the form status (CHANGED, QUERY, etc.).	MESSAGE(:SYSTEM.FORM_STATUS);
:SYSTEM.COORDINATE	Returns the X, Y coordinates of the cursor.	MESSAGE(:SYSTEM.COORDINATE);
:SYSTEM.OPERATOR_ID	Returns the ID of the connected user.	MESSAGE(:SYSTEM.OPERATOR_ID);

🔹 Use Case:

• If you want to check if the current form has unsaved changes before closing:plCopyEditIF :SYSTEM.FORM_STATUS = 'CHANGED' THEN MESSAGE('Please save your changes before exiting.'); END IF;
• If you want to log the database user:plsqlCopyEditINSERT INTO USER_LOGS (USER_NAME, LOGIN_TIME) VALUES (:SYSTEM.USERNAME, SYSDATE);

---

4. Message and Error Handling

These variables help manage form errors and messages.

System Variable	Description	Example
:SYSTEM.MESSAGE_LEVEL	Controls the level of displayed messages.	:SYSTEM.MESSAGE_LEVEL := 25;
:SYSTEM.LAST_ERROR	Returns the last error number.	MESSAGE(:SYSTEM.LAST_ERROR);
:SYSTEM.LAST_ERROR_TEXT	Returns the last error message.	MESSAGE(:SYSTEM.LAST_ERROR_TEXT);

🔹 Use Case:

• If you want to suppress non-critical messages:plCopyEdit:SYSTEM.MESSAGE_LEVEL := 25; -- Suppresses non-critical messages
• If you want to log errors when an exception occurs:plsqlCopyEditEXCEPTION WHEN OTHERS THEN INSERT INTO ERROR_LOGS (ERROR_CODE, ERROR_MESSAGE) VALUES (:SYSTEM.LAST_ERROR, :SYSTEM.LAST_ERROR_TEXT);

---

5. Runtime and Environment Information

These variables help track environment settings.

System Variable	Description	Example
:SYSTEM.CURSOR_BLOCK	Returns the block where the cursor is placed.	MESSAGE(:SYSTEM.CURSOR_BLOCK);
:SYSTEM.CURSOR_ITEM	Returns the item where the cursor is placed.	MESSAGE(:SYSTEM.CURSOR_ITEM);
:SYSTEM.CURSOR_RECORD	Returns the record number where the cursor is placed.	MESSAGE(:SYSTEM.CURSOR_RECORD);
:SYSTEM.MODE	Returns the current mode (ENTER-QUERY, NORMAL, etc.).	MESSAGE(:SYSTEM.MODE);

🔹 Use Case:

• If you want to restrict a certain operation when in query mode:plCopyEditIF :SYSTEM.MODE = 'ENTER-QUERY' THEN MESSAGE('Cannot perform this action in query mode.'); RAISE FORM_TRIGGER_FAILURE; END IF;

---

Summary & Memory Trick to Remember System Variables

Here’s a simple trick to remember the categories of system variables:

📝 F.U.N.M.E. (Form, User, Navigation, Message, Environment)

• F → Form-Level Variables (CURRENT_FORM, CURRENT_BLOCK)
• U → User & Database Info (USERNAME, FORM_STATUS)
• N → Navigation Handling (LAST_ITEM, LAST_RECORD)
• M → Message & Error Handling (LAST_ERROR, MESSAGE_LEVEL)
• E → Environment & Runtime (CURSOR_ITEM, MODE)

If you remember F.U.N.M.E., you can quickly recall the system variables.

---

Conclusion

System variables in Oracle Forms 6i provide powerful control over form behavior. By mastering these, you can create more responsive and efficient applications. Always test system variables in various scenarios to fully understand their impact.

Would you like me to provide a PDF or an interactive Oracle Forms demo using system variables? 🚀