-- This script takes the following inputs:
--   i) PROCESS_NAME
--  ii) PASSNAME
--
--
-- And outputs a list of cid's and tables which correspond to this combination.

-- The runs are input by a perl script which hardcodes them into a VARRAY based
-- on this template


spool get_cid_tables.txt
set serveroutput on

declare

  a_process_name VARCHAR(100) := '&1';

  a_passname VARCHAR(20) := '&2';

  a_run NUMBER := 155151;

 
  -- Run array type
  TYPE RUN_ARRAY IS VARRAY(100000) OF NUMBER;
  --PERLTRIGGER
  the_runs RUN_ARRAY := RUN_ARRAY(


  );

  --ANOTHERPERLTRIG

  a_proc_calib_ver NUMBER :=0;

  a_jobset NUMBER :=0;

  the_pass_index NUMBER :=0;

begin

  dbms_output.enable(10000000);

  dbms_output.put_line('1st and 2nd: '||the_runs(1)||' '||the_runs(2));






  -- first go into the PASSES table and get the row corresponding to PROCESS_NAME and PASSNAME where retired
  -- isn't NULL

  declare 

    cursor c0 IS select PASS_INDEX,RETIRED from PASSES where PROCESS_NAME = a_process_name AND PASSNAME = a_passname;

    a_pass_index NUMBER := 0;

    a_retired VARCHAR(20);

  begin

    -- iterate over the different rows from PASSES

    FOR c0_rec IN c0 LOOP

      a_pass_index := c0_rec.PASS_INDEX;
      a_retired := c0_rec.RETIRED;

      --dbms_output.put_line('index,retired: '||a_pass_index||' '||a_retired);


      -- find the one which is NULL (should only be 1)
      IF a_retired IS NULL THEN

        the_pass_index := a_pass_index;

      END IF;

    END LOOP; -- c0_rec loop
    
    --dbms_output.put_line('the pass_index: '||the_pass_index);


    -- Now go into PASSCALIBS and get the PROC_CALIB_VERSION with this PASS_INDEX and the PROCESS_RUN parameter
    -- Should only be one of these.











    -- This is a loop over runs from here on:

    FOR POSTN IN 1..the_run_size LOOP


         -- remember have to handle no data found error: (see exception at the end of this block)
         -- So start a new block for the exception    
         begin
     
           select PROC_CALIB_VERSION 
                into a_proc_calib_ver from PASSCALIBS 
     
                where 
                  LORUN = the_runs(POSTN) AND 
                  HIRUN = the_runs(POSTN) AND 
                  PASS_INDEX = the_pass_index;
     
     
     
           --dbms_output.put_line('the proc-calib-version: '||a_proc_calib_ver);
     
           -- Now go into USED_SETS and get the JOBSET which corresponds to this PROC_CALIB_VERSION,RUN and PROCESS_NAME
     
     
     
     
         
     
           select JOBSET 
                into a_jobset from USED_SETS
     
                where
                  PROCESS_NAME = a_process_name AND
                  PROC_CALIB_VERSION = a_proc_calib_ver AND
                  PROCESS_RUN = the_runs(POSTN);
     
           --dbms_output.put_line('the jobset: '||a_jobset);
     
     
     
           -- Now print out the table/cid combinations for this jobset:
     
           declare
     
             cursor c1 IS select CID from SET_RUN_MAPS where JOBSET = a_jobset;
     
             a_cid NUMBER :=0;
      
             a_table VARCHAR(100);
     
           begin
     
             dbms_output.put_line('================');
             dbms_output.put_line('Run: '||the_runs(POSTN));
             dbms_output.put_line('================');
             dbms_output.put_line('.');


             dbms_output.put_line('STARTPERL');
     
             FOR c1_rec IN c1 LOOP
     
               a_cid := c1_rec.CID;
     
               select CALIB_TABLE into a_table from CALIBRUNLISTS where CID = a_cid;
     
               --dbms_output.put_line('cid: '||a_cid||' table: '||a_table);
     
               dbms_output.put_line(a_cid||' '||a_table);
     
             END LOOP; -- c1 loop
     
             dbms_output.put_line('ENDPERL');
             dbms_output.put_line('.');
             dbms_output.put_line('.');     


           end; -- c1 block
     
     
         exception
     
           WHEN NO_DATA_FOUND THEN
              dbms_output.put_line('No data found for one of the selects attempted for run: '||the_runs(POSTN));
     
         end; -- exception block for no data found




    END LOOP; -- FOR loop over runs 










  end; -- c0 block

end; -- main
/