PL/SQL if found insert to table and send email

Hi,

I am trying to do the following.

I have a table called srp_classmask in one database with the following attributes: (id, classmask_id).

I created another table in different database called ref_classmask with the following attributes: (id, classmask_id, description, date_inserted, date_updated)

(I will use db link)

I am trying (without luck so far) to do the following with PL/SQL.

- get distinct values from srp_classmask table

SELECT distinct classmask_id
FROM srp_classmask

- check if classmask_id from srp_classmask table exists in ref_classmask table.

- if new value exists, insert the new found srp_classmask.classmask_id into ref_classmask table VALUES (id_seq.NEXTVAL, srp_classmask.classmask_id, NULL, sysdate, null)

- send email to bla@bla.com, with summary information (new classmask_ids inserted into the ref_classmask table).

If someone could give me some pointers that would be great.

Thank You

Regards

Roman

pointers on what? Which of the 3 things are you getting stuck on?

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;

Thank you

Roman