On checking input values

U

Usually I see (and do) evaluations at the start of the procedure. When you check for multiple things, it can get lengthy and the reusability of the code is very low.

Does this look familiar?

DECLARE
    in_user_id      CONSTANT VARCHAR2(30) := 'USER_NAME';
BEGIN
    -- check inputs
    IF in_user_id IS NULL THEN
        RAISE_APPLICATION_ERROR(-20000, 'USER_ID_MANDATORY');
    END IF;
    --
    IF LENGTH(in_user_id) < 3 THEN
        RAISE_APPLICATION_ERROR(-20000, 'USER_ID_MIN_LENGTH');
    END IF;
    --
    IF NOT REGEXP_LIKE(in_user_id, '@') THEN
        RAISE_APPLICATION_ERROR(-20000, 'USER_ID_FAILED');
    END IF;

    -- continue with your code
    NULL;
END;
/

Imagine this instead:

DECLARE
    in_user_id      CONSTANT VARCHAR2(30) := 'USER_NAME';
BEGIN
    -- check inputs
    assert.is_not_null('USER_ID_MANDATORY',     in_user_id);
    assert.is_false('USER_ID_MIN_LENGTH',       LENGTH(in_user_id) >= 3);
    assert.is_true('USER_ID_FAILED',            NOT REGEXP_LIKE(in_user_id, '@'));

    -- continue with your code
    NULL;
END;
/

It is shorter, it is easier to read and understand. And you can reuse these functions. So if you decide to add a function for checking email address and later you will change the pattern inside, you dont need to go through the code and change all occurences.

And you can catch or propagate these errors easily:

DECLARE
    in_user_id      CONSTANT VARCHAR2(30) := 'USER_NAME';
BEGIN
    -- check inputs
    assert.is_valid_email('USER_MAIL_FAIL',     in_user_id);    -- add this function yourself

    -- continue with your code
    NULL;
    --
EXCEPTION
WHEN assert.assert_exception THEN
    --
    -- here you can catch failed asserts or propagate them higher
    --
    RAISE;
END;
/

You can also search for all asserts in your code:

SELECT s.*
FROM user_source s
WHERE s.text LIKE '%assert.%'
ORDER BY 1, 2, 3;

If you like it, here is the package:
(feel free to add the check you do often)

CREATE OR REPLACE PACKAGE assert AS

    -- start error message with this
    c_assert_message            CONSTANT VARCHAR2(30)   := 'ASSERT_FAILED|';

    -- define assert exception
    c_assert_exception_code     CONSTANT PLS_INTEGER    := -20000;
    assert_exception            EXCEPTION;
    --
    PRAGMA EXCEPTION_INIT(assert_exception, c_assert_exception_code);



    PROCEDURE is_true (
        in_error_message        VARCHAR2,
        in_bool_expression      BOOLEAN
    );

    PROCEDURE is_false (
        in_error_message        VARCHAR2,
        in_bool_expression      BOOLEAN
    );

    PROCEDURE is_not_null (
        in_error_message        VARCHAR2,
        in_value                VARCHAR2
    );

END;
/

CREATE OR REPLACE PACKAGE BODY assert AS

    PROCEDURE is_true (
        in_error_message        VARCHAR2,
        in_bool_expression      BOOLEAN
    )
    AS
    BEGIN
        IF in_bool_expression THEN
            RAISE_APPLICATION_ERROR(c_assert_exception_code, c_assert_message || in_error_message);
        END IF;
    END;



    PROCEDURE is_false (
        in_error_message        VARCHAR2,
        in_bool_expression      BOOLEAN
    )
    AS
    BEGIN
        IF NOT in_bool_expression THEN
            RAISE_APPLICATION_ERROR(c_assert_exception_code, c_assert_message || in_error_message);
        END IF;
    END;



    PROCEDURE is_not_null (
        in_error_message        VARCHAR2,
        in_value                VARCHAR2
    )
    AS
    BEGIN
        IF in_value IS NULL THEN
            RAISE_APPLICATION_ERROR(c_assert_exception_code, c_assert_message || in_error_message);
        END IF;
    END;

END;
/