-- This script deletes a CID from calibrunlists and the associated table. 
-- It first looks to see if the CID exists in calibrunlists. If it doesn't it bugs out.
-- Then it checks to make sure the CID doesn't exist in set_run_maps 
-- (ie make sure a valid_set/used_set hasn't been defined based on this CID.)
-- If this test passes (ie DOESN'T find the CID in set_run_maps) then 
-- it proceeds to find out which table the CID appears from (by looking in calibrunlists)
-- and delete all the rows from this table with this CID..
-- and finally to delete the row from calibrunlists.

-- Author : M.S.Martin (Johns Hopkins) January 2004.


spool delete_cid.log
set serveroutput on

declare
  a_cid NUMBER := -1;
  b_cid NUMBER := -1;
  a_tablename VARCHAR(20);

  cid_parm NUMBER := '&1';

begin

  dbms_output.enable(10000000);
  dbms_output.put_line('Hello from script delete_cid.sql !!');

  -- Look for the cid in calibrunlists. If don't find it, bug out with an error message!
 
  select cid into a_cid from calibrunlists where cid = cid_parm;

  IF(a_cid > 0) THEN

      -- Found the cid so now look for it in set_run_maps. 
      -- If find it in set_run_maps, there is a problem because this cid has been
      -- put into a valid_set or used_set....


      -- Have to begin a new block in order to handle the NO_DATA_FOUND exception..
      BEGIN

        select cid into b_cid from set_run_maps where cid = cid_parm;

      EXCEPTION

        WHEN NO_DATA_FOUND THEN
          dbms_output.put_line('No data found. Well aint that a shame!! (Ie we dont care)');

      END;

      IF(b_cid > 0) THEN

        dbms_output.put_line('CID found in set_run_maps..indicating a valid_set or used_set exists with it...bum! Exiting.');

      ELSE

        dbms_output.put_line('CID NOT found in set_run_maps. Good.');

        -- So the CID exists in calibrunlists and DOESN'T in set_run_maps..
        -- So now figure out which table it corresponds to:

        select calib_table into a_tablename from calibrunlists where cid = cid_parm;

        dbms_output.put_line('tablename for this CID: '|| a_tablename );



	EXECUTE IMMEDIATE 'DELETE FROM '|| a_tablename || ' where cid = '|| cid_parm;
        EXECUTE IMMEDIATE 'COMMIT';	

        EXECUTE IMMEDIATE 'DELETE FROM calibrunlists where cid = '|| cid_parm;
        EXECUTE IMMEDIATE 'COMMIT';

        dbms_output.put_line('completed deletion.');

      END IF;

  ELSE

      dbms_output.put_line('CID not found in calibrunlists! Exiting..');


  END IF;

end;
/