DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: PL/SQL if found insert to table and send email

  1. #1
    Join Date
    Jun 2003
    Posts
    132

    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

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    pointers on what? Which of the 3 things are you getting stuck on?
    Jeff Hunter

  3. #3
    Join Date
    Apr 2004
    Location
    Boston MA
    Posts
    90

    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.

  4. #4
    Join Date
    Jun 2003
    Posts
    132
    Thank you

    Roman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width