14.6.5 Coding Business Rules for Reuse
Put shared business rules in a package so APEX pages and REST APIs can reuse the same logic.
To reuse common action item business logic in your app and REST APIs, put it
in a separate package. For example,
ACTION_ITEMS_COMMON below has
validation functions for action items. They return either a:
BOOLEAN– withfalserepresenting invalid, orVARCHAR2– withnullrepresenting success, or an error message on failure.
The validation checks include functions:
action_exists– checks whether an action items with a given ID existsensure_null_id_for_new_action– asserts a new action item's ID is nullensure_action_exists– returns message if action does not existone_lead_per_action_team– returns message if action item team has no leads or more than one leadno_duplicate_team_members– returns message if duplicate team members exists
package action_items_common is
-----------------------------------------------------------------------
-- Return true if the action item with provide id exists
-----------------------------------------------------------------------
function action_exists(
p_action_id in number)
return boolean;
-----------------------------------------------------------------------
-- Ensure id passed in is null, or return error message
-----------------------------------------------------------------------
function ensure_null_id_for_new_action(
p_id in number)
return varchar2;
-----------------------------------------------------------------------
-- Ensure action item exists, or return error message
-----------------------------------------------------------------------
function ensure_action_exists(
p_action_id in number)
return varchar2;
-----------------------------------------------------------------------
-- Ensure action item has exactly one Lead, or return error message
-----------------------------------------------------------------------
function one_lead_per_action_team(
p_action_id in number)
return varchar2;
-----------------------------------------------------------------------
-- Ensure action item has no duplicate members, or return error message
-----------------------------------------------------------------------
function no_duplicate_team_members(
p_action_id in number)
return varchar2;
-----------------------------------------------------------------------
-- Ensure role value is legal, or return error message
-----------------------------------------------------------------------
function is_valid_role(
p_role_value in varchar2)
return varchar2;
end action_items_common;You can study the simple code that implements the variable functions and procedures below. Notice how validations can easily enforce conditions about the state of a parent Action Item and its child Action Item Team Members using simple SQL statements. Two key examples to review:
- To count team leads,
one_lead_per_action_teamuses:COUNT(*)withWHERE ROLE='LEAD' - To find duplicate team members,
no_duplicate_team_membersuses:GROUP BYandHAVING COUNT(*) > 1
Tip:
These kinds of simple SQL-based aggregate checks are possible because both your APEX application and REST API PL/SQL can perform checks after the data changes are saved but before they are committed. Signaling an error in either context is enough to rollback the transaction and let the user know the problem with a helpful error message in their current language.
package body action_items_common is
------------------------------------------------------------
-- Constant for error message keys
------------------------------------------------------------
c_msg_team_must_have_lead constant varchar2(25) := 'TEAM_MUST_HAVE_A_LEAD';
c_msg_team_max_one_lead constant varchar2(25) := 'TEAM_MAX_ONE_LEAD';
c_msg_team_no_duplicates constant varchar2(25) := 'TEAM_NO_DUPE_MEMBERS';
c_msg_action_item_not_found constant varchar2(25) := 'ACTION_ITEM_NOT_FOUND';
c_msg_illegal_role_value constant varchar2(25) := 'INVALID_ROLE_TYPE';
c_msg_no_id_for_new_action constant varchar2(25) := 'ID_UNEXPECTED_FOR_INSERT';
c_no_error constant varchar2(1) := null;
------------------------------------
function ensure_null_id_for_new_action(
p_id in number)
return varchar2
is
begin
if p_id is not null then
return app_common.error_message(c_msg_no_id_for_new_action);
end if;
return c_no_error;
end ensure_null_id_for_new_action;
------------------------------------
function ensure_action_exists(
p_action_id in number)
return varchar2
is
begin
if not action_exists(p_action_id) then
return app_common.error_message(c_msg_action_item_not_found,
apex_t_varchar2('id',p_action_id));
end if;
return c_no_error;
end ensure_action_exists;
------------------------------------
function action_exists(
p_action_id in number)
return boolean
is
begin
for j in (select null
from action_items
where id = p_action_id)
loop
return true;
end loop;
return false;
end action_exists;
------------------------------------
function one_lead_per_action_team(
p_action_id in number)
return varchar2
is
l_lead_count number;
begin
-- Ensure the action id still exists since it might
-- have been deleted. If it exists, perform the check
-- on the children rows.
for j in (select id
from action_items
where id = p_action_id) loop
select count(*)
into l_lead_count
from action_item_team_members
where action_id = p_action_id
and role = 'LEAD';
if l_lead_count != 1 then
return app_common.error_message(
case
when l_lead_count < 1
then c_msg_team_must_have_lead
else c_msg_team_max_one_lead
end);
end if;
end loop;
return c_no_error;
end;
------------------------------------
function no_duplicate_team_members(
p_action_id in number)
return varchar2
is
begin
-- Ensure the action id still exists since it might
-- have been deleted. If it exists, perform the check
-- on the children rows.
for j in (select id
from action_items
where id = p_action_id) loop
for k in (select user_id, count(*)
from action_item_team_members
where action_id = p_action_id
group by user_id
having count(*) > 1
fetch first row only) loop
return app_common.error_message(c_msg_team_no_duplicates);
exit;
end loop;
end loop;
return c_no_error;
end;
------------------------------------
function is_valid_role(
p_role_value in varchar2)
return varchar2
is
begin
if p_role_value not in ('MEMBER','LEAD') then
return app_common.error_message(
c_msg_illegal_role_value,
apex_t_varchar2('value',p_role_value));
end if;
return c_no_error;
end is_valid_role;
end action_items_common;Parent topic: Enforcing Business Logic in REST APIs