Oracle PL/SQL Functions Returning BOOLEAN Values

A PL/SQL function that returns BOOLEAN is a compact way to answer a yes/no question inside your program logic. Instead of repeating complex conditions throughout a codebase, you encapsulate the rule once and reuse it everywhere you need to make a decision.

In practice, BOOLEAN-returning functions are most valuable for:

• Validation (Is this input acceptable?)
• Business rules (Is this customer eligible for a discount?)
• Security checks (Does this user have the required role?)
• Control flow (Should this procedure continue or exit early?)
• Guard clauses (Stop immediately when a prerequisite is not met.)

A function call can appear inside expressions and conditions. That is exactly why BOOLEAN functions are so useful: they drop cleanly into IF, ELSIF, and loop conditions, improving readability and maintainability.

Use these guidelines to keep BOOLEAN functions predictable and reusable:

1. Prefer “TRUE/FALSE only” — PL/SQL BOOLEAN is tri-valued (TRUE, FALSE, NULL). Returning NULL usually creates ambiguity. If something is unknown, decide explicitly whether your rule should treat it as FALSE or raise an exception.
2. Handle exceptions deliberately — decide whether NO_DATA_FOUND should map to FALSE (common for “exists?” checks) or whether the exception should bubble up.
3. Avoid side effects — a predicate-style function should not update tables, commit, or do unrelated work. Keep it “decision-only.”
4. Use clear naming — names like is_*, has_*, can_*, and should_* make call sites self-documenting.
5. Keep SQL small and indexed — BOOLEAN functions often call SQL; ensure the SQL is selective and uses appropriate indexes to avoid turning the predicate into a bottleneck.

In Oracle 23ai-era deployments, BOOLEAN is a PL/SQL datatype and is primarily intended for PL/SQL control flow. That means you typically cannot use a BOOLEAN-returning PL/SQL function directly in a SQL WHERE clause.

If you need a reusable rule inside SQL, the common pattern is to provide a SQL-friendly wrapper that returns a SQL datatype such as NUMBER(1) or CHAR(1).

Note: Oracle AI Database 26ai introduces a SQL BOOLEAN column type and SQL conversion functions such as TO_BOOLEAN, which changes what is possible in pure SQL in newer environments. If you are targeting 23ai-era stacks, rely on wrapper patterns for SQL compatibility.

CREATE OR REPLACE FUNCTION is_valid_salary(p_salary NUMBER)
  RETURN BOOLEAN
IS
BEGIN
  RETURN p_salary >= 30000;
END is_valid_salary;

DECLARE
  v_salary NUMBER := 35000;
BEGIN
  IF is_valid_salary(v_salary) THEN
    DBMS_OUTPUT.PUT_LINE('Salary accepted.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Salary too low.');
  END IF;
END;

CREATE OR REPLACE FUNCTION employee_exists(p_emp_id NUMBER)
  RETURN BOOLEAN
IS
  v_dummy NUMBER;
BEGIN
  SELECT 1 INTO v_dummy
  FROM employees
  WHERE emp_id = p_emp_id;

  RETURN TRUE;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN FALSE;
END employee_exists;

BEGIN
  IF employee_exists(101) THEN
    DBMS_OUTPUT.PUT_LINE('Employee found.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Employee not found.');
  END IF;
END;

CREATE OR REPLACE FUNCTION has_admin_privileges(p_user_id NUMBER)
  RETURN BOOLEAN
IS
  v_role VARCHAR2(50);
BEGIN
  SELECT role INTO v_role
  FROM user_roles
  WHERE user_id = p_user_id;

  RETURN v_role = 'ADMIN';
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN FALSE;
END has_admin_privileges;

BEGIN
  IF has_admin_privileges(2001) THEN
    DBMS_OUTPUT.PUT_LINE('Access granted.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Access denied.');
  END IF;
END;

CREATE OR REPLACE FUNCTION is_eligible_for_bonus(p_emp_id NUMBER)
  RETURN BOOLEAN
IS
  v_sales NUMBER;
BEGIN
  SELECT total_sales INTO v_sales
  FROM sales
  WHERE emp_id = p_emp_id;

  RETURN v_sales > 50000;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RETURN FALSE;
END is_eligible_for_bonus;

BEGIN
  FOR emp IN (SELECT emp_id FROM employees) LOOP
    IF is_eligible_for_bonus(emp.emp_id) THEN
      DBMS_OUTPUT.PUT_LINE('Bonus eligible: ' || emp.emp_id);
    END IF;
  END LOOP;
END;

When a rule must be reused inside SQL, return a SQL datatype:

CREATE OR REPLACE FUNCTION is_high_salary_sql(p_salary NUMBER)
  RETURN NUMBER
IS
BEGIN
  IF p_salary > 100000 THEN
    RETURN 1;
  ELSE
    RETURN 0;
  END IF;
END is_high_salary_sql;

Example usage in SQL:

SELECT emp_name, salary
FROM employees
WHERE is_high_salary_sql(salary) = 1;

PL/SQL BOOLEAN supports three values: TRUE, FALSE, and NULL. In many business-rule functions, returning NULL tends to create surprising behavior at call sites, so treat “unknown” explicitly.

Example of a BOOLEAN variable assignment from a compound expression:

DECLARE
  l_eligible_for_discount BOOLEAN;
  l_balance               NUMBER := 1200;
  l_min_balance           NUMBER := 1000;
  l_pref_type             VARCHAR2(30) := 'MOST FAVORED';
  l_disc_eligibility      BOOLEAN := TRUE;
BEGIN
  l_eligible_for_discount :=
       l_balance > l_min_balance
   AND l_pref_type = 'MOST FAVORED'
   AND l_disc_eligibility;

  IF l_eligible_for_discount THEN
    DBMS_OUTPUT.PUT_LINE('Discount eligible.');
  ELSE
    DBMS_OUTPUT.PUT_LINE('Discount not eligible.');
  END IF;
END;

The next lesson covers the syntax patterns for defining stored functions, including parameter conventions and return types.