-- This script: 
--
--     -takes a runnumber (refers to process_run in the used_sets table)
--      as an input.
--
--     - It goes in an finds the jobset in used_sets this process_run 
--       refers to. 
-- 
--     - It goes into set_run_maps to find all the cid's this jobset
--       encompasses.
--
--     - It goes into calibrunlists and finds all the tables each of these
--       cid's refers to.
--
--     - For each table, it does a select * on the cid (ie all the rows with 
--       this cid) and outputs this information to an output file.
-- 
--     - As an extra test, (Anil suggestion) the script also does a global
--       select count(*)   (ie counts the number of rows) for each table
--
--
--     This output file is meant to be compared to another output file 
--     generated by running this script on a different database to see if the
--     data has been replicated properly.
--   
--     Author : Mat Martin (Johns Hopkins University) Feb 2004.
--     Initial Use: Streams testing Feb 2004.

--     NOTE : To run this in test mode where you don't want a REAM of output
--     set the test variable called : test_flag to the value 1.
--     If it is anything else, then the script will run in full mode.
--     The effect of full mode is that all the different tables found for a given
--     jobset will be considered. 
--     test mode (with test_flag set to 1) will exit after the first couple of tables.

--     NOTE ALSO : This script will produce alot of output to the screen, but it is 
--     also outputting to the log file specified by the spool command below.
--     Multiple excutions of the script are handled by a parent script which 
--     concatenates the logfiles 


spool check_sim_results.log
set serveroutput on

declare

  -- next line is test mode:
  --test_flag NUMBER := 1;

  -- this line is full mode:
  test_flag NUMBER := 0;


  --proc_run NUMBER := 138842;

  proc_run NUMBER := '&1';

  a_jobset NUMBER := -1;

  a_table VARCHAR(20);

begin

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

  -- Look for the jobset corresponding to this process_run in used_sets.
  -- But there may well be >1 jobset for this run (shouldn't be in sim, but could be in real life)

  declare 

    cursor c0 IS select jobset from used_sets where process_run = proc_run;

  begin

    FOR us_rec IN c0 LOOP
  
    a_jobset := us_rec.jobset;

    dbms_output.put_line('*-*-*-*-*-*');
    dbms_output.put_line('jobset: '||a_jobset);
    dbms_output.put_line('*-*-*-*-*-*');
    dbms_output.put_line('.');


    -- Grab the list of cids which this jobset encompasses from set_run_maps.
  
    declare
      
      cursor c1 IS select cid from set_run_maps where jobset = a_jobset;
  
      sql_stmt VARCHAR(100);
      a_cid2 NUMBER := -1;

      sql_stmt2 VARCHAR(100);
      a_count NUMBER := 0;

    begin
  
      -- For each cid, get the tablename and go into that table and dump its data
  
      FOR srm_rec IN c1 LOOP
  
        -- Get the table name from calibrunlists
  
        select calib_table into a_table from calibrunlists where cid = srm_rec.cid;
  

        dbms_output.put_line('=================');
        dbms_output.put_line('table : '||a_table);
        dbms_output.put_line('=================');
        dbms_output.put_line('.');

        -- Some hairy pl/sql coming up now.
        -- Putting schema objects in sql is tricky. Must embed them using the 
	-- concatenation operator ( || ).


        

        -- Before the actual data comparison, dump the count(*) for each table

        sql_stmt2 := 'select count(*) from ' ||a_table;

        EXECUTE IMMEDIATE sql_stmt2 INTO a_count;

        dbms_output.put_line('rowcount: '||a_count);
        dbms_output.put_line('.');


        -- Now back to looking at the actual data..

        -- So first build the right sql statement:

	a_cid2 := srm_rec.cid;

        sql_stmt := 'select * from '||a_table||' where cid = '||a_cid2;


        --Output query for debug
	-- dbms_output.put_line('sql query: '||sql_stmt);


        -- Now the block which takes this dynamic query and uses it to fill a cursor:

        declare
          
          TYPE tabCurTyp IS REF CURSOR;
          tab_cv   tabCurTyp;
          a_cid3 NUMBER := -1;
          a_counter NUMBER := 0;
 
          -- This is the number of rows to look at 
          -- Be CAREFUL as allowing this to be too
          -- big will create gigabyte queries
          a_max_counter NUMBER := 9;


          TYPE gen_rec IS RECORD(
          first  VARCHAR(100),
          second VARCHAR(100));

          my_gen_rec gen_rec;

        begin
          
          dbms_output.enable(10000000);

          OPEN tab_cv FOR sql_stmt;

          LOOP
--            FETCH tab_cv INTO a_cid3;
            FETCH tab_cv INTO my_gen_rec;

            dbms_output.put_line(my_gen_rec.first||' '||my_gen_rec.second);

            a_counter := a_counter + 1;

            EXIT WHEN a_counter > a_max_counter;


          END LOOP; -- tab_cv loop

          CLOSE tab_cv;

        end; -- dynamic table query block





        -- This bugs out after 1 table if test_flag is set
        IF (test_flag = 1) THEN
          -- test exit to limit output
          EXIT;
        END IF;




  
      END LOOP;
  
  

    end; -- c1 loop block




    -- This bugs out after 1 jobset if test_flag is set 
    -- (should only be 1 jobset for a process_run for sim though)

    IF (test_flag = 1) THEN
      -- test exit to limit output
      EXIT;
    END IF;



    END LOOP;
  
  end; -- first c0 loop block.

end; -- main block
/