Rem DO NOT ADD HEADER TO THIS FILE SINCE THIS FILE IS REFERENCED FROM
Rem severity_data_upgrade.sql ALREADY.
Rem $Header: severity_data_fix_cycle_guids.sql 20-jul-2005.07:51:27 chyu Exp $
Rem
Rem severity_data_fix_cycle_guids.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      severity_data_fix_cycle_guids.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      <short description of component this file declares/defines>
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    chyu        07/20/05 - remove header
Rem    jsadras     02/08/05 - fix upgrade 
Rem    rpinnama    02/06/05 - rpinnama_add_upgrade_scripts
Rem    jsadras     01/20/05 - jsadras_cycle_guid
Rem    jsadras     01/20/05 - Created
Rem



PROMPT Updating severity cycle guids
SELECT to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;

DECLARE
TYPE rawtab IS TABLE OF RAW(16) INDEX BY BINARY_INTEGER ;
TYPE varchar256tab IS TABLE OF VARCHAR2(256) INDEX BY BINARY_INTEGER ;
TYPE datetab IS TABLE OF DATE INDEX BY binary_integer ;
TYPE numtab is TABLE of NUMBER ;
l_target_guids rawtab ;
l_policy_guids rawtab ;
l_cycle_guids rawtab ;
l_violation_guids rawtab ;
l_key_values  varchar256tab ;
l_violation_levels numtab ;
l_prev_viol_level NUMBER := 15;
l_commit_frequency NUMBER ;
l_cycle_guid RAW(16) ;
l_start_idx NUMBER := 1;
l_end_idx NUMBER ;
l_counter NUMBER := 0 ;
CURSOR violations_cur 
   IS 
    SELECT target_guid,policy_guid,key_value,violation_level,violation_guid
      FROM mgmt_violations
     ORDER BY target_guid,policy_guid,key_value,collection_timestamp ;
l_prev_rec violations_cur%rowtype ;
BEGIN
  BEGIN
    SELECT parameter_value
      INTO l_commit_frequency
      FROM mgmt_parameters
     WHERE parameter_name = 'cycle_guid_upgrade_batchsize' ;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      l_commit_frequency := 10000 ;
  END ;

  OPEN violations_cur ;
  FETCH violations_cur BULK COLLECT INTO
  l_target_guids,l_policy_guids,l_key_values,
  l_violation_levels,l_violation_guids 
  LIMIT l_commit_frequency ;
  IF l_violation_guids IS NOT NULL AND l_violation_guids.COUNT>0
  THEN
    l_prev_rec.target_guid    := l_target_guids(1) ;
    l_prev_rec.policy_guid    := l_policy_guids(1) ;
    l_prev_rec.key_value      := l_key_values(1) ;
    l_prev_rec.violation_level:= 15 ;
    l_prev_rec.violation_guid := l_violation_guids(1) ;
  END IF ;

  WHILE l_violation_guids IS NOT NULL AND l_violation_guids.COUNT > 0
  LOOP
    FOR i IN l_violation_guids.FIRST..l_violation_guids.LAST
    LOOP
      l_counter := l_counter + 1 ;
      -- start a new cycle 
      -- if target/policy/key value changes OR
      -- previous violation is clear
      IF l_prev_rec.violation_level = 15 OR
         l_target_guids(i)         != l_prev_rec.target_guid  OR
         l_policy_guids(i)         != l_prev_rec.policy_guid OR
         l_key_values(i)           != l_prev_rec.key_value
      THEN
        l_cycle_guids(i) := l_violation_guids(i) ;
        l_cycle_guid     := l_violation_guids(i) ;
      ELSE
        l_cycle_guids(i) := l_cycle_guid ;
      END IF ;
     
      IF l_counter = l_commit_frequency OR
         i = l_violation_guids.LAST
      THEN
        BEGIN
          FORALL j IN l_start_idx..i
            UPDATE mgmt_violations
              SET cycle_guid = l_cycle_guids(j)
             WHERE violation_guid = l_violation_guids(j) ;
          COMMIT ;
          l_start_idx := i+1 ;
          l_counter := 0 ;
        END ;
      END IF ;
  
      l_prev_rec.target_guid := l_target_guids(i) ;
      l_prev_rec.policy_guid := l_policy_guids(i) ;
      l_prev_rec.key_value := l_key_values(i) ;
      l_prev_rec.violation_guid := l_violation_guids(i) ;
      l_prev_rec.violation_level := l_violation_levels(i) ;
    END LOOP ; -- for i in ..
    FETCH violations_cur BULK COLLECT INTO
    l_target_guids,l_policy_guids,l_key_values,
    l_violation_levels,l_violation_guids 
    LIMIT l_commit_frequency ;
    l_start_idx := 1 ;
  END LOOP ;
  CLOSE violations_cur ;
END ;
/

PROMPT done updating cycle guids
SELECT to_char(SYSDATE, 'YYYY-MM-DD HH24:MI:SS') FROM DUAL;