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!
Andrew is a Senior Oracle Application Express (APEX) developer with more than 10 years of experience leading projects from inception to completion. He has built and maintained more than 750 Oracle APEX forms and reports. His prior work as a technical writer and client educator has uniquely prepared Andrew to identify the needs of a task from the perspectives of the organization, programmer, client, and end-user.