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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29

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; /