Lesson 5	Defining functions returning a Boolean value
Objective	List uses of a Function that returns a Boolean Value

Oracle Functions returning Boolean Value

In Oracle PL/SQL, functions that return a Boolean value are typically used in control flow, validation, and business logic to determine whether a condition is true or false. Below are some common uses of such functions:

Validation and Data Integrity Checks

Used to validate whether an input or business rule is met.

Example:

FUNCTION is_valid_salary(p_salary NUMBER) RETURN BOOLEAN IS
BEGIN
    RETURN p_salary > 30000; -- Returns TRUE if salary is above 30,000
END is_valid_salary;

Usage in PL/SQL block:

DECLARE
    v_result BOOLEAN;
BEGIN
    v_result := is_valid_salary(35000);
    IF v_result THEN
        DBMS_OUTPUT.PUT_LINE('Salary is valid');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Salary is too low');
    END IF;
END;

Conditional Execution in Control Structures

Boolean functions simplify IF conditions for better readability.

Example:

FUNCTION is_employee_active(p_emp_id NUMBER) RETURN BOOLEAN IS
    v_status VARCHAR2(10);
BEGIN
    SELECT status INTO v_status FROM employees WHERE emp_id = p_emp_id;
    RETURN v_status = 'ACTIVE';
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN FALSE;
END is_employee_active;

Usage:

IF is_employee_active(101) THEN
    DBMS_OUTPUT.PUT_LINE('Employee is active');
ELSE
    DBMS_OUTPUT.PUT_LINE('Employee is inactive');
END IF;

Used in WHERE Clause with TABLE Functions

Boolean functions cannot be directly used in SQL queries but can be incorporated via PL/SQL table functions.

Example:

FUNCTION is_high_salary(p_salary NUMBER) RETURN BOOLEAN IS
BEGIN
    RETURN p_salary > 100000;
END is_high_salary;

Usage inside a PL/SQL block:

DECLARE
    CURSOR emp_cursor IS
        SELECT emp_name, salary FROM employees;
BEGIN
    FOR rec IN emp_cursor LOOP
        IF is_high_salary(rec.salary) THEN
            DBMS_OUTPUT.PUT_LINE(rec.emp_name || ' has a high salary.');
        END IF;
    END LOOP;
END;

Security Checks and Access Control

Used for checking user permissions or roles.

Example:

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;

Usage:

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

Error Handling and Logging Conditions

A function returning BOOLEAN can be used to check if a process has completed successfully.

Example:

FUNCTION is_process_successful(p_process_id NUMBER) RETURN BOOLEAN IS
    v_status VARCHAR2(10);
BEGIN
    SELECT status INTO v_status FROM process_log WHERE process_id = p_process_id;
    RETURN v_status = 'SUCCESS';
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN FALSE;
END is_process_successful;

Usage:

IF is_process_successful(5001) THEN
    DBMS_OUTPUT.PUT_LINE('Process completed successfully.');
ELSE
    DBMS_OUTPUT.PUT_LINE('Process failed or not found.');
END IF;

Used in Loops for Efficient Processing

Example of using a Boolean function in a loop:

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;

Usage inside a loop:

FOR emp IN (SELECT emp_id FROM employees) LOOP
    IF is_eligible_for_bonus(emp.emp_id) THEN
        DBMS_OUTPUT.PUT_LINE('Employee ' || emp.emp_id || ' gets a bonus.');
    END IF;
END LOOP;

Dynamic Execution Logic

Example of enabling/disabling system features based on conditions.

Example:

FUNCTION is_feature_enabled(p_feature_name VARCHAR2) RETURN BOOLEAN IS
    v_enabled CHAR(1);
BEGIN
    SELECT enabled INTO v_enabled FROM system_features WHERE feature_name = p_feature_name;
    RETURN v_enabled = 'Y';
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN FALSE;
END is_feature_enabled;

Usage:

IF is_feature_enabled('NEW_UI') THEN
    DBMS_OUTPUT.PUT_LINE('New UI is enabled');
ELSE
    DBMS_OUTPUT.PUT_LINE('New UI is disabled');
END IF;

Key Takeaways

Boolean functions improve code clarity and maintainability.
Used in conditional execution, validation, security checks, loops, and feature toggling.
Boolean functions cannot be used directly in SQL queries.
Proper exception handling inside these functions is important to avoid runtime errors.

As you probably know, Boolean is a datatype that contains a True or False value. Because of this, you can use functions that return a Boolean value in places where you need a condition evaluated. Basically, you use this kind of function within an IF-THEN-ELSE statement, or in a WHILE loop statement. A function helps you encapsulate complex logic and allows you to re-use this complex logic without coding it multiple times.

Oracle Function Examples

Here are some examples of tasks where you might use a function that returns a Boolean value.

You might use a multi-purpose procedure to handle users and roles. This procedure can call a function to check for an existing user with the same name. The procedure can then issue the CREATE USER command only when needed.
When confirming a user's security access to your Forms application, you might use a PL/SQL block that calls a function to perform the needed validation. This function could be called from within all your forms.
Ater entering information into the database, verify that your client's insurance claim falls within certain pre-determined rules for reasonable costs. The complex logic within the function can be re-used by many different applications.

Remember, you cannot use this kind of function in plain SQL commands.You must use them within a PL/SQL block.

Function - Part of Executable Statement

A function is a module that returns data through its RETURN clause, rather than in an 1) OUT or 2) IN OUT argument. Unlike a procedure call, which is a standalone executable statement, a call to a function can exist only as part of an executable statement, such as

an element in an expression or
the value assigned as the default in a declaration of a variable.

Because a function returns a value, it is said to have a datatype. A function can be used in place of an expression in a PL/SQL statement having the same datatype as the function. Functions are particularly important constructs for building modular code. For example, every single business rule or formula in your application should be placed inside a function. Every single-row query should also be defined within a function, so that it can be easily and reliably reused.
Note: Some programmers prefer to rely less on functions, and more on procedures that return status information through the parameter list. If this holds true for you, make sure that your

business rules,
formulas, and
single-row queries

are tucked away into your procedures.

Booleans

PL/SQL supports a three-value Boolean datatype. A variable of this type can have one of only three values:

TRUE,
FALSE, and
NULL.

Booleans help us write very readable code, especially involving complex logical expressions. Here is an example of a Boolean declaration, along with an assignment of a default value to that variable:

DECLARE
l_eligible_for_discount BOOLEAN :=
customer_in.balance > min_balance AND
customer_in.pref_type = 'MOST FAVORED' AND
customer_in.disc_eligibility;

The next lesson shows you the syntax of a stored function that returns a Boolean value.