Oracle Forms 6i relies heavily on PL/SQL logic blocks to handle events, validations, and business logic efficiently. Below are some of the most useful and commonly used PL/SQL logic blocks for Oracle Forms, along with real-world examples.
1. Using PRE-INSERT Trigger for Default Values
📌 Use Case: Set default values before inserting records.
Example: Automatically setting the created date before inserting a record.
BEGIN
:EMPLOYEES.CREATED_DATE := SYSDATE;
:EMPLOYEES.CREATED_BY := :SYSTEM.USERNAME;
END;
🔹 Why? Ensures consistency across records without requiring manual input.
2. WHEN-VALIDATE-ITEM for Input Validations
📌 Use Case: Validate user input before allowing data entry.
Example: Ensure salary is greater than a minimum threshold.
BEGIN
IF :EMPLOYEES.SALARY < 5000 THEN
MESSAGE('Salary must be at least 5000');
RAISE FORM_TRIGGER_FAILURE;
END IF;
END;
🔹 Why? Prevents incorrect data entry before saving to the database.
3. KEY-NEXT-ITEM for Auto-Fill Logic
📌 Use Case: Automatically fill a field when moving to the next item.
Example: Auto-fill the department name when the department ID is entered.
DECLARE
v_dept_name VARCHAR2(50);
BEGIN
SELECT DEPARTMENT_NAME INTO v_dept_name
FROM DEPARTMENTS
WHERE DEPARTMENT_ID = :EMPLOYEES.DEPARTMENT_ID;
:EMPLOYEES.DEPARTMENT_NAME := v_dept_name;
END;
🔹 Why? Enhances user experience by auto-filling dependent values.
4. WHEN-BUTTON-PRESSED for Custom Actions
📌 Use Case: Execute logic when a button is clicked.
Example: Generate and display an employee report.
BEGIN
RUN_PRODUCT(REPORTS, 'emp_report.rdf', SYNCHRONOUS, RUNTIME, FILESYSTEM, NULL, NULL);
END;
🔹 Why? Automates report generation without manual navigation.
5. POST-QUERY for Displaying Additional Info
📌 Use Case: Show extra computed or lookup data after a query.
Example: Display department name dynamically after querying employee details.
BEGIN
SELECT DEPARTMENT_NAME INTO :EMPLOYEES.DEPARTMENT_NAME
FROM DEPARTMENTS
WHERE DEPARTMENT_ID = :EMPLOYEES.DEPARTMENT_ID;
END;
🔹 Why? Ensures real-time data accuracy after queries.
6. WHEN-NEW-FORM-INSTANCE for Initial Form Setup
📌 Use Case: Initialize values when the form opens.
Example: Set the current date as default in the date field.
BEGIN
:EMPLOYEES.HIRE_DATE := SYSDATE;
END;
🔹 Why? Improves efficiency by preloading default values.
7. ON-ERROR for Custom Error Handling
📌 Use Case: Customize error messages for better understanding.
Example: Show user-friendly error messages instead of standard Oracle errors.
BEGIN
IF ERROR_CODE = '40508' THEN
MESSAGE('Invalid number entered. Please enter a valid value.');
RAISE FORM_TRIGGER_FAILURE;
END IF;
END;
🔹 Why? Helps users understand errors more easily.
8. WHEN-NEW-RECORD-INSTANCE for Default Values
📌 Use Case: Assign default values when inserting a new record.
Example: Set the default job title when adding a new employee.
BEGIN
:EMPLOYEES.JOB_ID := 'SALES_REP';
END;
🔹 Why? Reduces user input time and maintains consistency.
9. POST-FORM for Cleanup Operations
📌 Use Case: Reset temporary values before exiting the form.
Example: Clear global variables before exiting.
BEGIN
:GLOBAL.USER_ID := NULL;
END;
🔹 Why? Prevents incorrect data from persisting across forms.
10. PRE-DELETE to Prevent Unwanted Deletions
📌 Use Case: Restrict deletions under certain conditions.
Example: Prevent deletion if the employee is a manager.
DECLARE
v_count NUMBER;
BEGIN
SELECT COUNT(*) INTO v_count
FROM EMPLOYEES
WHERE MANAGER_ID = :EMPLOYEES.EMPLOYEE_ID;
IF v_count > 0 THEN
MESSAGE('Cannot delete. Employee is a manager.');
RAISE FORM_TRIGGER_FAILURE;
END IF;
END;
🔹 Why? Ensures critical records are not deleted accidentally.
Conclusion
These PL/SQL logic blocks are essential for building powerful Oracle Forms applications. They help improve data integrity, user experience, and automation.
🚀 Next Steps:
- Practice these blocks in a real Oracle Forms 6i application.
- Modify and combine them to suit different business needs.
- Use these consistently to improve Oracle Forms development.
Would you like a cheat sheet or PDF guide for easy reference? 📘🔥