Re: Need to check and remove details

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 2 Jan 2003 12:17:26 -0800
Message-ID: <92eeeff0.0301021217.1207faf9@posting.google.com>

"Jimbo" <threepio23_at_yahoo.com> wrote in message news:<av1a6i$s4a$1_at_venus.btinternet.com>...
> In my database I want to produce some SQL code that checks my supplier
> records on an annual basis to find out if it HASN'T supplied anything to the
> company. If it hasn't, I want it to remove the details of the supplier from
> the company. I am wondering if anyone could tell me what type of code I
> would need to do this, or even better, some example of the code that could
> do this type of thing.
>
> Thanks

You can setup a background job in the database to run once a year (If I understand you correctly) and run a procedure that will do the whole thing in one go.

Without all the information available in your post, something along the lines of this pseudo code could work,

CREATE OR REPLACE PROCEDURE Cleanup_Supplier_Info IS

    Variable declaration.......
    ......
    CURSOR cur_ IS

       SELECT rowid, .....
       FROM supplier_table
       WHERE .......;

BEGIN
    FOR rec_ IN cur_ LOOP

       BEGIN
          SAVEPOINT TMP;
          -- Assumption here is that you already have a way (Some
flag) to
          -- identify suppliers who has not supplied anything.
          IF rec_.supplier_id has not supplied anything to the company
THEN
             -- Delete this supplier. Handle Delete cascades if
required.
             -- Can do either soft or hard delete.
             DELETE FROM Supplier_table
                WHERE rowid = rec_.rowid;
          END IF;
          --
          EXCEPTION
             WHEN OTHERS OR <Your Exception> THEN
                BEGIN
                   ROLLBACK TO TMP;
                   -- Other exception code if any
                END;
             
       END;
    END LOOP;      

END Cleanup_Supplier_Info;
/

Regards
/Rauf Sarwar Received on Thu Jan 02 2003 - 14:17:26 CST