Working with Checkbox Lists and Lookup Tables in Oracle APEX

For this example, I will show you how to work with colon separated lists created from using checkboxes in Oracle APEX. This is a solution for when your checkbox can have multiple values and the checkbox field is a lookup table.

The first table holds information on a hospital stay by storing an ID, patient name, and a list of labs they needed during the stay.

create table hospital_stayinfo (
    stayinfo_id number not null constraint stayinfo_id_pk primary key,
    patient_name varchar2(500),
    labs_issued varchar2(500)
);

Table two has the information on the labs and stores an ID, lab name, and cost of the lab.

create table labinfo (
    labinfo_id number not null constraint labinfo_id_pk primary key,
    lab_name varchar2(50),
    lab_cost number
);

Let’s load in some data so we can verify the solution later

insert into hospital_stayinfo values (953249, 'Michael Smith', '120:190')

insert into labinfo (labinfo_id, lab_name, lab_cost)
    select 120, 'Blood Test', 100.84 from dual
    union all select 190, 'Urine Test', 328.76 from dual
    union all select 534, 'Adrenaline test', 693.94 from dual

In the situation, we want to calculate the total cost of a hospital stay by adding up the costs of all the labs the patient needed.  Let’s create a function to get the lab_cost when the labinfo_id is given

CREATE OR REPLACE function calcMedLabCost (var_lab_id IN number)
return number

IS

med_lab_cost number := 0;

cursor c1 is
SELECT lab_cost
FROM labinfo
WHERE labinfo_id = var_lab_id;

BEGIN

open c1;
fetch c1 into med_lab_cost;

if c1%notfound then
    med_lab_cost := 0;
end if;

return med_lab_cost;

close c1;

END;
    

This function calls on the other function we created, calcMedLabCost, to get the cost of each item in the list and add them together

CREATE OR REPLACE function calcHospitalStayTotalCost (patient_lab_list IN varchar2)

return number

IS

patientlabtotalcost    number := 0;
convertlablisttocomma  varchar2(1000) := REPLACE(patient_lab_list, ':', ',');

start_index   binary_integer;
end_index     binary_integer;

listitem_currentvalue  varchar2(1000);

BEGIN

start_index  :=     1;
end_index    :=     regexp_count(convertlablisttocomma, ',');

while(end_index >= 0) loop

listitem_currentvalue := regexp_substr(convertlablisttocomma, '[^,]+', 1, start_index);
patientlabtotalcost := patientlabtotalcost + calcMedLabCost(to_number(listitem_currentvalue));

start_index  :=  start_index + 1;
end_index    :=  end_index -1;

end loop;

return patientlabtotalcost;

END;
    

Time to test it out with a select statement

select

stayinfo_id, patient_name, labs_issued, calcHospitalStayTotalCost(labs_issued)

from hospital_stayinfo
    

Learn More Tips About Oracle APEX with Traust Consulting

Are you looking to learn more helpful tips about Oracle APEX? Our blog regularly features topics such as the one above. You can learn about User Self Registration, Oracle Forms, and more by visiting our blog.

References

• There is some unsung hero out there that provided the original code that I based mine off of, but unfortunately, I cannot find the site online anymore to give them credit. If you let me know, I will gladly link them in this section
• Please let me know if I did not properly give credit to someone for any of the code used

Requirements

• APEX 18.1 or higher
• Oracle DB 12.2.0.1 or higher

Disclaimer: We do not take responsibility for any unintended or unwanted consequences in your instance of Oracle, Oracle APEX, or related products as a result of reading our blogs or following our guides. Though the information is fully tested and generally safe to use, our lawyers really have a thing against admitting potential wrongdoing. If it makes you feel any better, one time they dropped a dollar in change after buying coffee.  Instead of returning the money, I put it in the tip jar. The barista earned it anyways, our lawyers complained when they didn’t have paprika for their coffee! Are you kidding me? Paprika!