Interactive Grid duplicate values – a Zero-JavaScript solution to a common problem

Contents

A Business Requirement that comes up from time to time is to prevent duplicate values across the columns of an Interactive Grid in Oracle APEX. It sounds simple enough but often developers start tearing their hair out with frustration when things don’t exactly turn out as they should. In this article, I’m going to propose a solution that you can easily introduce into your applications rather than spending an eternity debugging and fine-tuning a custom approach.

Whilst you can easily introduce a unique index at the Database level, the Interactive Grid will still allow the user to enter duplicate values and then report back with an ORA error when it snags on that unique index on save. Handling that error message can be challenging for the developer. Perfection would be a validation that handles duplicates values across Interactive Grid rows. Well, you’re in for a treat, guess what’s coming next?

My name is Matt Mulvaney, I’m an Oracle ACE, and I have extensive experience with Oracle Application Express (APEX). After hours of work, I finally got to the root of the issue and found a solution. In this article, I’ll show you why the problem occurs and how to deal with it using a Zero-JavaScript approach.

APEX Interactive Grid Columns – the issue

I believe it’s best to show the problem with a working example, so we’ll head straight into one.

I have a TBL_DAYS table that has a unique index on the DAY column. I’ve also included a SESSION_ID column so that the readers can try out the final example shown in this article in my demonstration application.

I then create an editable Interactive Grid, based on TBL_DAYS, with a Where Clause – as shown below.

A screen showing an editable table with a Where Clause.

Now, when a user tries to add a duplicate day, the table is fully protected by the unique index. This is great, however, the user sees this awful error message.

Interactive Grid duplicate values – What won’t help you resolve this issue

So, what are we going to do about it? First, there are some incorrect choices you can make, so let’s look at those:

Have a page-level error handler for just this Interactive Grid?

It seems a bit much just for this single region, doesn’t it? We won’t really be able to highlight the error row and, at best, it’s just masking the error message.

Somehow use setThemeHooks to exchange the error message for a more readable one?

Not a good idea either, the reasons being the same as in the above case. And, besides, wasn’t this supposed to be a blog post about a Zero-JavaScript solution?

Add a Validation checking for any duplicates in the DB?

There’s also a close-but-not-close-enough approach: we can add a Validation that checks for any duplicates on the database.

Sounds sensible enough, right? If we ignore the ID we are updating, then we can prevent the row from falsely detecting itself as a duplicate. Additionally, this approach can even detect a change to an existing row which will then cause it to duplicate with another row (e.g., updating THURSDAY to MONDAY, where MONDAY already exists as another record).

It works when I add a record one at a time and click save…

A screen showing the Validation works partially.

… however, there are two big flaws:

Flaw #1 Adding two, or more, records at the same time (for example two MONDAYs) and then clicking save shows that same ugly ORA error that we are trying our best to avoid.

A screen showing yet another error message.

For now, let’s call this situation the “double-MONDAY” issue. This “double-MONDAY” issue occurs because:

On the first MONDAY record, the Validation asks the DB if it’s aware of any other MONDAYs in the DB. The DB says No.
On the second MONDAY record, the Validation asks the DB if it’s aware of any other MONDAYs in the DB. The DB says No.
The Automated Process runs for the first MONDAY & inserts without a problem
The Automated Process runs for the second MONDAY & gets a nasty ORA error

Flaw #2 We cannot delete a FRIDAY and add a FRIDAY back in, as the Interactive Grid isn’t clever enough to detect that one record would be deleted, only to be replaced by a new one.

Aargh, what a mess!

We’re going to need a super slick solution here.

Investigation – finding the cause

The battle we have here is that we do not know the order in which the IG is going to process the records in.

If we knew that order, we could identify that it’s OK to accept an insert of a duplicate record, if we knew that the next record to be processed is a delete which makes that inserted record no longer duplicate.

Often the order is unpredictable, especially when the grid is heavily customised. Or, when the order follows the user’s inputs with the IG, they could always delete a record first, before replacing it with an identical record (or vice versa).

We need a way of tracking what the automated process would do, before it runs, and then using that tracking within the validation, to determine which records should validate, and which shouldn’t.

Interactive Grid in Oracle APEX – The Validation that detects Duplicates

Let’s focus on the tracker process first.

Begin by creating an After Submit Process:

Attribute	Value
Name	Monitor IG Execution (Process)
Editable Region	<your interactive grid region> e.g. Days of the Week

Code:

DECLARE
  -- Author: Matt Mulvaney @ Pretius
  -- Licence: MIT


  -- IG Paramters
  l_pk        CONSTANT NUMBER
              DEFAULT :ID; -- IG Primary Key
  l_col       CONSTANT VARCHAR2(32767)
              DEFAULT apex_string.join( apex_t_varchar2(
                                        :DAY  -- First Unique Column
                                       ), ':');  


  -- Constants                                                
  l_collection  CONSTANT apex_collections.collection_name%TYPE DEFAULT 'IG_RECORDS';


BEGIN
  
  IF apex_application.g_x10 IS NULL
  THEN
     -- first iteration? then create/truncate the collection
    apex_collection.create_or_truncate_collection(l_collection);
    apex_application.g_x10 := 0;
  END IF;
  
  apex_collection.add_member(
        p_collection_name => l_collection,
        p_c001            => l_pk,
        p_c002            => l_col,
        p_c003            => :APEX$ROW_STATUS );


END;

Before we dig in further; I want to explain that a Validation on an Editable Region iterates once for each Changed/Modified record within one DB session. This is how I can set the package variable g_x10 to a value (zero in the example above) which then keeps it in session whilst iterating through each row, until the process is fully complete.

Note: if you have any concerns about using apex_application variables, you can always create your own version within a Package Specification.

The rest of the code is straightforward enough. Add a member containing the ID, the Column value, and specifying if it’s a Delete, Update, or Create.

Now that we’ve logged all the changes, let’s focus on the Validation.

Now that I’m armed with my tracked changes collection, I can detect that if I submit two MONDAYs, the 2nd iteration will be aware that the 1st iteration would insert a MONDAY, and this iteration will dupe and therefore must fail this validation.

Furthermore, when I create or update iterations, the Validation must be aware that whilst a changed/modified row may create a duplicate, this would be allowed if the record that was duplicated will be deleted within this operation.

Complicated? Just a little. I can explain better in code 😊

First, create a Validation with these attributes:

Attributes	Value
Name	Prevent Duplicates (Validation)
Editable Region	<your interactive grid region> e.g. Days of the Week
Type	Function Body (returning Boolean)
Always Execute	YES
Associated Column	<your unique column> e.g. DAY

For the PL/SQL Function Body, use the code example below.

Note: for your application, copy the code and then you’ll need to change…

The constants to match your PK and Unique Column from the IG
The data query to match your DB table, PK and Unique Column

DECLARE
  -- Author: Matt Mulvaney @ Pretius
  -- Licence: MIT


  -- IG Paramters
  l_pk        CONSTANT NUMBER
              DEFAULT :ID; -- IG Primary Key
  l_col       CONSTANT VARCHAR2(32767)
              DEFAULT apex_string.join( apex_t_varchar2(
                                        :DAY  -- First Unique Column
                                    ), ':');


  -- Constants                                                
  l_collection  CONSTANT apex_collections.collection_name%TYPE DEFAULT 'IG_RECORDS';


  -- Variables
  l_return      BOOLEAN DEFAULT TRUE;
BEGIN
  
  apex_debug.message( 'Unique IG Column Validation' );


  -- Increment Iteration Counter
  apex_application.g_x10 := NVL( apex_application.g_x10, 0 ) + 1;
  
  IF :APEX$ROW_STATUS != 'D'
  THEN
    -- Look for a dupe in the DB that does not include the ID being editted
    <<loop_db_check>>
    FOR x in (
      WITH data
        AS (
          SELECT id pk,
                 apex_string.join( apex_t_varchar2( DAY
                                                   ), ':') unique_column
            FROM tbl_days
           WHERE session_id = :SESSION_ID
        )
      SELECT pk
        FROM data
        WHERE unique_column = l_col
          AND ( ( l_pk IS NULL ) OR
                ( l_pk IS NOT NULL AND pk <> l_pk ) )
    )
    LOOP
      FOR d IN ( SELECT 1
                   FROM apex_collections
                  WHERE collection_name = l_collection
                    AND ( ( l_pk IS NULL ) OR
                          ( l_pk IS NOT NULL AND c001 <> TO_CHAR( l_pk ) ) )
                    AND c002 = l_col
                    AND c003 = 'D'  )
      LOOP
        -- Found a deleted record
        l_return := TRUE;
        EXIT loop_db_check;
      END LOOP;
      l_return := FALSE;
    END LOOP;


    IF l_return
    THEN
      -- Couldnt find a dupe in the DB, lets look to see if the user is about to add one
      FOR k IN ( SELECT 1
                   FROM apex_collections
                  WHERE collection_name = l_collection
                    AND ( ( l_pk IS NULL ) OR
                          ( l_pk IS NOT NULL AND c001 <> TO_CHAR( l_pk ) ) )
                    AND c002 = l_col
                    AND c003 <> 'D'
                    AND seq_id < apex_application.g_x10 )
      LOOP
        l_return := FALSE;
      END LOOP;
    END IF;


  END IF;
  
  -- If we've processed the last record, we dont need the collection anymore
  IF apex_application.g_x10 = apex_collection.collection_member_count( l_collection )
  THEN
     apex_collection.delete_collection( l_collection );
  END IF;


  RETURN l_return;


END;

How does it work?

Let’s break this down.

For each row iterated we:

check the DB for a potential duplicate whilst ignoring the row we are currently modifying – as it would incorrectly identify itself as its own duplicate.
if we find another row sharing the duplicated DAY that doesn’t exist as a deleted record in our Collection, then the validation must fail.

Everything is good so far, however, we have just one more thing to do. We have to check our Collection, to see if any incoming days have been previously seen in another iteration. This is the “double-MONDAY” issue fix. In other words, the first iteration of Monday must pass and the second iteration must fail.

That is the solution for detecting a duplicate in an Interactive column, but what about…

Uniqueness across multiple columns

There’s one more question we need to ask ourselves: what about uniqueness across multiple columns?

In this simple extension, I’ll add an AMPM column and modify my unique index to span both DAY and AMPM (I want to protect my table from all connections, not just APEX). Therefore I will allow two MONDAYs in my table. However, these won’t be duplicates, really, because I’ll specify a condition: one of them must be AM, and the other PM.

A screen showing how to take care of uniqueness across multiple columns.

In my Validation, all I need to do is to introduce a second column in two places in my code. They’re easy to find… Just search in my code for Second Unique Column.

Apart from that, the code’s identical.

DECLARE
  -- Author: Matt Mulvaney @ Pretius
  -- Licence: MIT


  -- IG Paramters
  l_pk        CONSTANT NUMBER
              DEFAULT :ID; -- IG Primary Key
  l_col       CONSTANT VARCHAR2(32767)
              DEFAULT apex_string.join( apex_t_varchar2(
                                        :DAY,  -- First Unique Column
                                        :AMPM  -- Second Unique Column
                                    ), ':');


  -- Constants                                                
  l_collection  CONSTANT apex_collections.collection_name%TYPE DEFAULT 'IG_RECORDS';


  -- Variables
  l_return      BOOLEAN DEFAULT TRUE;
BEGIN
  
  apex_debug.message( 'Unique IG Column Validation' );


  -- Increment Iteration Counter
  apex_application.g_x10 := NVL( apex_application.g_x10, 0 ) + 1;
  
  IF :APEX$ROW_STATUS != 'D'
  THEN
    -- Look for a dupe in the DB that does not include the ID being editted
    <<loop_db_check>>
    FOR x in (
      WITH data
        AS (
          SELECT id pk,
                 apex_string.join( apex_t_varchar2( DAY,  -- First Unique Column
                                                    AMPM  -- Second Unique Column
                                                   ), ':') unique_column
            FROM tbl_days
           WHERE session_id = :SESSION_ID
        )
      SELECT pk
        FROM data
        WHERE unique_column = l_col
          AND ( ( l_pk IS NULL ) OR
                ( l_pk IS NOT NULL AND pk <> l_pk ) )
    )
    LOOP
      FOR d IN ( SELECT 1
                   FROM apex_collections
                  WHERE collection_name = l_collection
                    AND ( ( l_pk IS NULL ) OR
                          ( l_pk IS NOT NULL AND c001 <> TO_CHAR( l_pk ) ) )
                    AND c002 = l_col
                    AND c003 = 'D'  )
      LOOP
        -- Found a deleted record
        l_return := TRUE;
        EXIT loop_db_check;
      END LOOP;
      l_return := FALSE;
    END LOOP;


    IF l_return
    THEN
      -- Couldnt find a dupe in the DB, lets look to see if the user is about to add one
      FOR k IN ( SELECT 1
                   FROM apex_collections
                  WHERE collection_name = l_collection
                    AND ( ( l_pk IS NULL ) OR
                          ( l_pk IS NOT NULL AND c001 <> TO_CHAR( l_pk ) ) )
                    AND c002 = l_col
                    AND c003 <> 'D'
                    AND seq_id < apex_application.g_x10 )
      LOOP
        l_return := FALSE;
      END LOOP;
    END IF;


  END IF;
  
  -- If we've processed the last record, we dont need the collection anymore
  IF apex_application.g_x10 = apex_collection.collection_member_count( l_collection )
  THEN
     apex_collection.delete_collection( l_collection );
  END IF;


  RETURN l_return;


END;

It is also advised to remove the Associated Column attribute in the Error section, as you cannot accurately advise the user which column they should be amending – for example, maybe the DAY is correct but the AM/PM not, or it could be the other way around. Only the user would be able to determine this.

Conclusion and possible improvements

And we’re done. Using the approach I described above you can ensure the uniqueness of several columns in the APEX Interactive Grid.

Could I improve this solution? Yes, a Process Plug-in would be a good idea (sadly APEX Validation Type Plug-ins are not supported), and also fetching/executing the region source instead of duplicating the query would be an improvement.

Could the APEX team improve this? Sure, I’d love to have a declarative option to assign certain columns to a unique grouping of columns. That’d be perfect.

There we go… just bookmark this blog for when you ever get challenged to prevent duplicates in your Interactive Grid Columns. Also, don’t forget my demonstration application.

Happy de-duping! And if you want more information on APEX, check out the other articles on the Pretius blog:

Interactive Grid duplicate values – Learn How to prevent a common problem using a Zero-JavaScript approach