Column Validation not firing in Interactive Grid Oracle APEX

Question

I'm developing a simple application in Oracle APEX and one of the requirements is to create an error message if a duplicate column (which is also the UK of the table) is saved to the Interactive Grid. I've created a support package and then a column validation in APEX that calls the function in the support package.

When I first implemented the code and the call, everything was working and the correct error message was displayed. However, I think I unknowingly modified a property setting or something because now I cannot get the validation to fire -- if the user enters a duplicate column value and presses save, they get the generic "constraint violation" error message that Oracle raises. The only IG Process in this app is the Automatic Row Processing - Save button.

Does anyone have know why the column validation is being ignored? I have gone through the error stack of the "constraint violation" and all I am seeing is the Save process and Oracles generic error messages.

Support Package Function:

`FUNCTION Prod_Family_Exists (
     i_id                    IN NUMBER
    ,i_prod_family           IN VARCHAR2
        ) RETURN BOOLEAN IS
  v_cnt        NUMBER;
BEGIN
  SELECT COUNT(*)
    INTO v_cnt
    FROM adm_prod_families 
   WHERE prod_family = i_prod_family
     AND id <> i_id
   ;
   
   IF v_cnt = 0 THEN
    RETURN(FALSE);
   ELSE
    RETURN(TRUE);
   END IF;

EXCEPTION
  WHEN OTHERS THEN
    RETURN(FALSE);
    
END Prod_Family_Exists;

END Z_TEST;`

Validation Call in APEX - PL/SQL Function (Returning Boolean):

BEGIN 

    IF z_test.prod_family_exists( i_id                => :id
                                 ,i_prod_family       => :prod_family)
    THEN 
      RETURN FALSE;
    ELSE
      RETURN TRUE;
    END IF;
END;

Based on the image below and the code I've provided, my custom error message would only be raised if the result from the validation returned FALSE right? Is the code bad?

Session for inserting a record with duplicate column value - returns true

mookie_bombs mookie_bombs · Accepted Answer · 2020-10-26T19:09:52

I figured this out. The code for the validation call to the support package function needed to be modified. New code:

BEGIN 
    IF z_test.prod_family_exists( i_id             => NVL(:id,0)
                                 ,i_prod_family    => :prod_family)
    THEN 
      RETURN FALSE;
    ELSE
      RETURN TRUE;
    END IF;
END;

Column Validation not firing in Interactive Grid Oracle APEX

1 Answers