Oracle APEX Part 11- Add validations before saving in interactive Grids

 While APEX often handles the saving automatically, sometimes you need to add custom validation, logging, or perform additional actions before or after the data is committed.

1. Understanding the Default Save Process

When you enable editing on an Interactive Grid, APEX automatically creates a "DML Process" that handles Inserts, Updates, and Deletes based on your table's primary key. We'll modify or add to this.

2. Scenario: Custom Validation for Employee Salary

Let's say we want to ensure that no employee's salary can be set to 0. If it is, we'll prevent the save and show an error.

Step 1: Create a Basic Interactive Grid (if you haven't already)

Follow the previous steps to create an Interactive Grid based on the EMP table, and ensure "Editing" is enabled in the region attributes.

Step 2: Navigate to Page Designer and the Interactive Grid Region

  1. In the APEX Builder, go to your page (e.g., "Manage Employees").

  2. Click on the Page Designer icon.

  3. In the left pane (Rendering tree), expand the Processes section. You should see a process named something like "EMP - Interactive Grid DML". This is the default process.Image of



Step 3: Add a "Validation" Process (Before DML)

Instead of modifying the core DML, it's best practice to add a Validation or a PL/SQL Code Process before the DML process. This ensures data is checked before any changes are committed.

  1. Right-click on the Processes node in the Rendering tree.

  2. Select Create Process.

  3. Set the Name to Validate Salary.

  4. Set the Type to PL/SQL Code.

  5. In the Point attribute, select After Submit - Before Computations and Validations. This ensures it runs after the page is submitted but before the DML.

  6. In the PL/SQL Code area, enter the following (This example assumes you have an EMP table with a SAL column):

    SQL
    -- This PL/SQL code iterates through the changed rows in the Interactive Grid
-- :APEX$ROW_STATUS indicates if the row is 'C' (Create), 'U' (Update), or 'D' (Delete)
-- We are interested in 'C' (new rows) and 'U' (updated rows)
FOR i IN 1..APEX_APPLICATION.G_F01.COUNT
LOOP
    -- Check if the row is being created or updated
    IF APEX_APPLICATION.G_F01(i) = 'C' OR APEX_APPLICATION.G_F01(i) = 'U' THEN
        -- Get the salary value for the current row (adjust FXX to your SAL column)
        -- F02 is usually the second column, adjust based on your grid's column order or by referencing the static ID
        -- For simplicity, let's assume F02 corresponds to SAL.
        -- A more robust way is to use column static IDs in newer APEX versions.
        -- For older APEX or direct bind variable, you might use :SAL directly if the validation is per row.

        -- This example uses the :SAL bind variable directly, which APEX makes available for row-level processing in IG DML.
        -- For validations *before* the DML, we need to access the submitted grid data.
        -- This requires more complex PL/SQL to loop through apex_application.g_fxx arrays
        -- OR, simply create a VALIDATION component directly for the SAL column.

        -- Let's use a simpler and more standard APEX Validation approach for this.
        -- Delete this PL/SQL Process if you created it, and follow Step 4.
        NULL; -- Placeholder
    END IF;
END LOOP;

    (Self-correction: While a custom PL/SQL Process can work, a standard APEX Validation is cleaner for single-column checks like this. Let's switch to that!)

Step 4: Add a Column-Level Validation (The Recommended Way)

For simple validations like ensuring a salary isn't 0, APEX's built-in validations are perfect.

  1. In Page Designer, navigate to the Interactive Grid region (e.g., EMP).

  2. Expand the Columns node within the Interactive Grid region.

  3. Select the SAL (Salary) column.

  4. In the right pane (Properties window), scroll down to the Validation section.

  5. Click the Create Validation button.

  6. Set the Type to PL/SQL Function Body returning Error Text.

  7. In the PL/SQL Code area, enter:

    SQL
    IF :SAL = 0 THEN
    RETURN 'Error: Salary cannot be 0. Please enter a valid amount.';
ELSE
    RETURN NULL; -- No error
END IF;

  8. Set Display Location to On Field And In Notification. This shows the error by the field and in the page message area.

  9. Click Save in Page Designer.



Step 5: Test the Validation

  1. Run the page.

  2. Try editing an existing employee's salary to 0 or adding a new row with 0 for salary.

  3. Click the Save button in the Interactive Grid.

You should now see the error message: "Error: Salary cannot be 0. Please enter a valid amount."Image of



Comments

Post a Comment