PL/SQL if found insert to table and send email

[ddrozdov]

CREATE OR REPLACE PROCEDURE BlablaCreateNewClassmaskDetail
IS
classmask_type srp_classmask.classmask_id%TYPE;
TYPE classmask_tabtyp IS TABLE of classmask_type INDEX BY binary_integer;

bunchaclassmasks classmask_tabtyp;
new_classmasks classmask_tabtyp;

function get_buncha_existing_classmasks return classmask_tabtyp
is
retval classmask_tabtyp;
cursor ref_cm is
select classmask_id
from other_db_schema.ref_classmask@other_db;
begin
for rec in ref_cm loop
retval(ref_cm%ROWCOUNT) := rec.classmask_id;
end loop;
return retval;
end get_buncha_existing_classmasks;

function get_buncha_classmasks return classmask_tabtyp
is
retval classmask_tabtyp;
cursor ref_cm is
select distinct classmask_id
from srp_classmask;
begin
for rec in ref_cm loop
retval(ref_cm%ROWCOUNT) := rec.classmask_id;
end loop;
return retval;
end get_buncha_classmasks;

BEGIN
-- pull all the classmasks from the other database
-- to avoid distributed join
bunchaclassmasks := get_buncha_existing_classmasks;
-- get all classmasks, not all are new yet
new_classmasks := get_buncha_classmasks;
-- loop through new_classmasks and delete existing ones
for ind in 1..new_classmasks.COUNT loop
for i in bunchaclassmasks.COUNT loop
if new_classmasks(ind) = bunchaclassmasks(i) then
new_classmasks.DELETE(ind);
-- exit statement in for loop, sorry :-(
exit;
end if;
end loop;
end loop;
-- flush that
bunchaclassmasks.DELETE;

-- okay, now new_classmasks is a sparsely filled array of the
-- classmasks that need to be created
-- rkiss, can you take over from here?????

-- put in code to read sparsely filled array and send the email
-- with the list of masks

end BlablaCreateNewClassmaskDetail;