How APEX Processes Various Conditions and Validations

I was recently teaching an Intro to APEX course and the students had some questions about what to put in the Expression 1 text area for different conditions and validations based on the Type selected. If you're new to APEX the various options can be confusing.

To help clear things up I've included a list of the various APEX conditions and validations along with code that demonstrates how APEX processes the value in Expression 1 based on the given Type. I didn't include the definition for each type as they are already well documented in the APEX documentation and popup help.

Note: the calls to DBMS_OUTPUT are there to show you if the validation/condition is true or false.

Exists

DECLARE
  l_rows pls_integer;
BEGIN
  SELECT count(1)
  INTO l_rows
  FROM (
    -- Start Expression 1
    SELECT 1 
    FROM emp 
    WHERE sal > :p1_sal
    -- End Expression 1
  );
  
  IF l_rows > 0 THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/

Not Exists

DECLARE
  l_rows pls_integer;
BEGIN
  SELECT count(1)
  INTO l_rows
  FROM (
    -- Start Expression 1
    SELECT 1 
    FROM emp 
    WHERE sal > :p1_sal
    -- End Expression 1
  );
  
  IF l_rows = 0 THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/

SQL Expression
Note: SQL Expression and PL/SQL Expression are very similar but some SQL expressions can't be used in PL/SQL. Example: Decode

DECLARE
  l_rows pls_integer;
BEGIN
  SELECT count(1)
  INTO l_rows
  FROM dual
  WHERE 
    -- Start Expression 1
    :p1_sal > 500
    -- End Expression 1
  ;
  
  IF l_rows = 1 THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
END;
/

PL/SQL Expression

DECLARE
BEGIN
  IF (
    -- Start Expression 1
    :p1_sal > 500
    -- End Expression 1
  ) THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
    
END;
/

PL/SQL Function Returning Boolean

DECLARE
  FUNCTION f_apex_condtion RETURN boolean
  AS
  BEGIN
    -- Start Expression 1
    DECLARE
      l_rows pls_integer;
    BEGIN
      SELECT count(*)
      INTO l_rows
      FROM emp
      WHERE JOB = 'PRESIDENT';
      
      IF l_rows > 1 THEN -- only want at most 1 president
        RETURN FALSE;
      ELSE
        RETURN TRUE;
      END IF;
    END;
    -- End Expression 1
  END;
  
BEGIN

  IF f_apex_condtion THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE');
  END IF;
    
END;
/

Conditions Only

Function Returning Error Text

DECLARE
  l_err_msg varchar2(4000);

  FUNCTION f_apex_condtion RETURN varchar2
  AS
  BEGIN
    -- Start Expression 1
    DECLARE
      l_rows pls_integer;
    BEGIN
      SELECT count(*)
      INTO l_rows
      FROM emp
      WHERE JOB = 'PRESIDENT';
      
      IF l_rows > 1 THEN 
        RETURN 'Only 1 president can exist for the company';
      ELSE
        RETURN NULL; -- no error
      END IF;
    END;
    -- End Expression 1
  END;
  
BEGIN
  l_err_msg := f_apex_condtion;
  
  IF l_err_msg IS NULL THEN
    dbms_output.put_line('TRUE');
  ELSE
    dbms_output.put_line('FALSE - Error message: ' || l_err_msg);
  END IF;
    
END;
/

PL/SQL Error

DECLARE

  PROCEDURE sp_apex_condtion AS
  BEGIN
    -- Start Expression 1
    DECLARE
      l_rows pls_integer;
    BEGIN
      SELECT count(*)
      INTO l_rows
      FROM emp
      WHERE JOB = 'PRESIDENT';
      
      IF l_rows > 1 THEN 
        raise_application_error(-20001, 'Only 1 president can exist for the company');
      END IF;
    END;
    -- End Expression 1
  END;
  
BEGIN
  sp_apex_condtion;
  
  dbms_output.put_line('TRUE');
  
exception WHEN others THEN
  dbms_output.put_line('FALSE'); -- Error message is defined in the validation's error message
END;
/