-
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?
Jeff Hunter
-
CAUTION !!!Not debugged!!!
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;
Last edited by ddrozdov; 04-16-2004 at 11:37 AM.
-
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|