Rem
Rem $Header: chronos_admin_pkgbody.sql 07-jul-2005.17:05:41 yxie Exp $
Rem
Rem chronos_admin_pkgbody.sql
Rem
Rem Copyright (c) 2002, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      chronos_admin_pkgbody.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    yxie        07/07/05 - fix bug 4475287 
Rem    yxie        06/06/05 - changes depends_on to supports_eum_on
Rem    yxie        04/15/05 - changing contains to depends_on
Rem    eporter     04/13/05 - Apply chronos_set_target_filter to other types 
Rem    adosani     03/14/05 - add run metric dates purge policy
Rem    ramalhot    01/17/05 - g_member_guid->g_contains_guid
Rem    ramalhot    08/30/04 - cutover to new assoc tables 
Rem    eporter     08/13/04 - Allow Chronos data from Apache targets as well 
Rem                           as Webcaches 
Rem    rpinnama    05/15/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/15/02 - Created
Rem

--
-- Defining bodies of procedures and functions
--
CREATE OR REPLACE Package body EMD_CHRONOS_ADMIN                 
AS
PROCEDURE CHRONOS_SET_TARGET_FILTER(
              target_name_in             IN VARCHAR2,
              target_type_in             IN VARCHAR2,
              filter_name_in             IN VARCHAR2,
              filter_value_in            IN VARCHAR2)
IS
v_property_name VARCHAR2(256);
v_target_array SMP_EMD_NVPAIR_ARRAY;
i BINARY_INTEGER;
prop_count INTEGER;
v_target_guid RAW(16);
CURSOR member_targets_cursor(l_target_name VARCHAR2,l_target_type VARCHAR2) IS
    SELECT SMP_EMD_NVPAIR(tm.target_name,tm.target_type)
    FROM MGMT_TARGETS t, MGMT_TARGETS tm, MGMT_TARGET_ASSOCS m
    WHERE t.target_guid=m.source_target_guid
      AND t.target_name=l_target_name
      AND t.target_type=l_target_type
      AND tm.target_guid = m.assoc_target_guid
      AND m.assoc_guid = MGMT_WEBSITE_TARGET.supports_eum_on_guid
      AND (tm.target_type ='oracle_webcache'
        OR tm.target_type ='oracle_apache'
        OR tm.target_type ='generic_apache');
BEGIN
--
-- check for property names to be set
--
If (filter_name_in='max_elapsed_time')
 Then
     v_property_name := 'mgmt_rt_max_elapsed_time';
ElsIf (filter_name_in='min_hits')
 Then
     v_property_name := 'mgmt_rt_min_hits';
ElsIf (filter_name_in='event_window_size')
 Then
     v_property_name := 'mgmt_rt_event_window_sz';
Else
     raise_application_error(-20001,'Property not valid',TRUE);
End If;
--
 OPEN member_targets_cursor(target_name_in,target_type_in);
 FETCH member_targets_cursor BULK COLLECT INTO v_target_array;
 CLOSE member_targets_cursor;

-- add the passed in target itself into v_target_array

i:=v_target_array.COUNT;
v_target_array.extend;
v_target_array(i+1):=SMP_EMD_NVPAIR(target_name_in,target_type_in);

-- OPEN member_targets_guid_cursor(target_name_in,target_type_in);
-- FETCH member_targets_guid_cursor BULK COLLECT INTO v_target_guid_array;
-- CLOSE member_targets_guid_cursor;

FOR i IN 1..v_target_array.COUNT LOOP-- Bulk update all targets
-- prefetch target_guid
  SELECT target_guid INTO v_target_guid
  FROM mgmt_targets
  WHERE target_name=v_target_array(i).name
  AND   target_type=v_target_array(i).value;

  SELECT count(*) INTO prop_count
    FROM mgmt_rt_target_properties
       WHERE target_guid=v_target_guid
       AND property_name=v_property_name;
-- (SELECT target_guid from mgmt_targets
--    WHERE target_name=v_target_array(i).name
--       AND target_type=v_target_array(i).value)
--       AND property_name=v_property_name;
  If ( prop_count = 0 ) Then
     INSERT INTO mgmt_rt_target_properties (target_guid,property_name,property_value)
     VALUES (v_target_guid, v_property_name,filter_value_in);
  Else
     UPDATE mgmt_rt_target_properties
     SET PROPERTY_VALUE=filter_value_in
     WHERE PROPERTY_NAME=v_property_name
     AND target_guid= v_target_guid;
  End If;
END LOOP;
COMMIT;
EXCEPTION
  WHEN OTHERS
   THEN
      dbms_output.put_line('Error in Set Target Filter: ' || SQLERRM);
      If member_targets_cursor%ISOPEN Then
         CLOSE member_targets_cursor;
      End If;
END CHRONOS_SET_TARGET_FILTER;

--

PROCEDURE SET_COLLECTION_SCHEDULE(
              target_name_in       IN VARCHAR2,
              target_type_in       IN VARCHAR2,
              metric_name_in       IN VARCHAR2,
              store_metric_in      IN VARCHAR2,
              schedule_in          IN NUMBER)
IS
BEGIN
   emd_collection.modify_collection(target_name_in,target_type_in,metric_name_in,
                                       store_metric_in,schedule_in);
   COMMIT;
END SET_COLLECTION_SCHEDULE;

--

PROCEDURE CHRONOS_PURGE_TIME_DATA(
              target_name_in  IN VARCHAR2,
              target_type_in  IN VARCHAR2,
              start_period_in IN DATE DEFAULT NULL,
              end_period_in   IN DATE DEFAULT NULL,
              axis_period_in  IN VARCHAR2 DEFAULT NULL)
IS
l_ret_val VARCHAR2(64);
BEGIN

  l_ret_val:=DELETE_RAW_DATA(target_name_in,target_type_in,
                                   start_period_in,end_period_in);
  if (l_ret_val = 'SUCCESS') Then
    if (axis_period_in = 'hourly') Then
       l_ret_val:=DELETE_HOURLY_DATA(target_name_in,target_type_in,
                                 start_period_in,end_period_in) ;
    ElsIf (axis_period_in='daily') Then
       l_ret_val:=DELETE_DAILY_DATA(target_name_in,target_type_in,
                                 start_period_in,end_period_in);
    ElsIf (axis_period_in='dist_hourly') Then
       l_ret_val:=DELETE_HOURLY_DIST_DATA(target_name_in,target_type_in,
                                 start_period_in,end_period_in);
    ElsIf (axis_period_in='dist_daily') Then
       l_ret_val:=DELETE_DAILY_DIST_DATA(target_name_in,target_type_in,
                                 start_period_in,end_period_in);
-- no axis specified, so do them all
    ElsIf (axis_period_in IS NULL) Then
       l_ret_val:=DELETE_HOURLY_DATA(target_name_in,target_type_in,
                                 start_period_in,end_period_in) ;
       l_ret_val:=DELETE_DAILY_DATA(target_name_in,target_type_in,
                                 start_period_in,end_period_in);
       l_ret_val:=DELETE_HOURLY_DIST_DATA(target_name_in,target_type_in,
                                 start_period_in,end_period_in);
       l_ret_val:=DELETE_DAILY_DIST_DATA(target_name_in,target_type_in,
                                 start_period_in,end_period_in);
    End If;
  End If;
END CHRONOS_PURGE_TIME_DATA;

--

FUNCTION DELETE_HOURLY_DATA(
              target_name_in  IN VARCHAR2,
              target_type_in  IN VARCHAR2,
              begin_time_in   IN DATE,
              end_time_in     IN DATE) RETURN VARCHAR2
IS
  TYPE RowIdList IS TABLE OF UROWID
    INDEX BY BINARY_INTEGER;
  CURSOR url_rowid_cursor(begin_time DATE, end_time DATE, limit NUMBER,l_target_guid RAW) IS
    SELECT rowid
    FROM MGMT_RT_URL_1HOUR
    WHERE
      target_guid = l_target_guid
      AND ((begin_time IS NULL) OR rollup_timestamp > begin_time)
      AND ((end_time IS NULL) OR rollup_timestamp < end_time)
      AND ROWNUM <= limit;

  CURSOR domain_rowid_cursor(begin_time DATE, end_time DATE, limit NUMBER,l_target_guid RAW) IS
    SELECT rowid
    FROM MGMT_RT_DOMAIN_1HOUR
    WHERE
      target_guid = l_target_guid
      AND ((begin_time IS NULL) OR rollup_timestamp > begin_time)
      AND ((end_time IS NULL) OR rollup_timestamp < end_time)
    AND ROWNUM <= limit;

  CURSOR ip_rowid_cursor(begin_time DATE, end_time DATE,limit NUMBER,l_target_guid RAW) IS
    SELECT rowid
    FROM MGMT_RT_IP_1HOUR
    WHERE
      target_guid = l_target_guid
      AND ((begin_time IS NULL) OR rollup_timestamp > begin_time)
      AND ((end_time IS NULL) OR rollup_timestamp < end_time)
    AND ROWNUM <= limit;
  v_rowIds RowIdList;
  i BINARY_INTEGER;
  target_guid_out RAW(16);
BEGIN

  SELECT target_guid INTO target_guid_out FROM mgmt_targets
  WHERE target_name=target_name_in AND target_type=target_type_in;

  LOOP
    OPEN url_rowid_cursor(begin_time_in,end_time_in,
                          g_PurgeBatch,target_guid_out);
    FETCH url_rowid_cursor BULK COLLECT INTO v_rowIds;
    EXIT WHEN v_rowIds.COUNT <= 0;  -- Exit when nothing to purge

    FORALL i IN v_rowIds.FIRST..v_rowIds.LAST -- Bulk delete all rows by id.
      DELETE FROM MGMT_RT_URL_1HOUR WHERE rowid = v_rowIds(i);
    COMMIT;
    CLOSE url_rowid_cursor;
    v_rowIds.DELETE;  -- Clear the temporary row id list.
  END LOOP;

  IF url_rowid_cursor%ISOPEN THEN
    CLOSE url_rowid_cursor;
  END IF;
  LOOP
    OPEN ip_rowid_cursor(begin_time_in,end_time_in,g_PurgeBatch,
                         target_guid_out);
    FETCH ip_rowid_cursor BULK COLLECT INTO v_rowIds;
    EXIT WHEN v_rowIds.COUNT <= 0;  -- Exit when nothing to purge

    FORALL i IN v_rowIds.FIRST..v_rowIds.LAST -- Bulk delete all rows by id.
      DELETE FROM MGMT_RT_IP_1HOUR WHERE rowid = v_rowIds(i);
    COMMIT;
    CLOSE ip_rowid_cursor;
    v_rowIds.DELETE;  -- Clear the temporary row id list.
  END LOOP;

  IF ip_rowid_cursor%ISOPEN THEN
    CLOSE ip_rowid_cursor;
  END IF;
  LOOP
    OPEN domain_rowid_cursor(begin_time_in,end_time_in,g_PurgeBatch,target_guid_out);
    FETCH domain_rowid_cursor BULK COLLECT INTO v_rowIds;
    EXIT WHEN v_rowIds.COUNT <= 0;  -- Exit when nothing to purge

    FORALL i IN v_rowIds.FIRST..v_rowIds.LAST -- Bulk delete all rows by id.
      DELETE FROM MGMT_RT_DOMAIN_1HOUR WHERE rowid = v_rowIds(i);
    COMMIT;
    CLOSE domain_rowid_cursor;
    v_rowIds.DELETE;  -- Clear the temporary row id list.
  END LOOP;

  IF domain_rowid_cursor%ISOPEN THEN
    CLOSE domain_rowid_cursor;
  END IF;
  RETURN 'SUCCESS';
  EXCEPTION
    WHEN OTHERS
    THEN
      dbms_output.put_line('Error in Hourly Delete: ' || SQLERRM);
      IF domain_rowid_cursor%ISOPEN THEN
         CLOSE domain_rowid_cursor;
      END IF;
      IF ip_rowid_cursor%ISOPEN THEN
        CLOSE ip_rowid_cursor;
      END IF;
      IF url_rowid_cursor%ISOPEN THEN
         CLOSE url_rowid_cursor;
      END IF;
    RETURN NULL;
END DELETE_HOURLY_DATA;

--

FUNCTION DELETE_RAW_DATA(
              target_name_in  IN VARCHAR2,
              target_type_in  IN VARCHAR2,
              begin_time_in   IN DATE,
              end_time_in     IN DATE) RETURN VARCHAR2
IS
  TYPE RowIdList IS TABLE OF UROWID
    INDEX BY BINARY_INTEGER;
  CURSOR rowid_cursor(begin_time DATE, end_time DATE, limit NUMBER,target_guid_out RAW) IS
    SELECT rowid
    FROM MGMT_RT_METRICS_RAW 
    WHERE
      target_guid=target_guid_out
      AND ((begin_time IS NULL) OR collection_timestamp > begin_time)
      AND ((end_time IS NULL) OR collection_timestamp < end_time)
      AND ROWNUM <= limit;

  v_rowIds RowIdList;
  i BINARY_INTEGER;
  target_guid_out RAW(16);
BEGIN

  SELECT target_guid INTO target_guid_out FROM mgmt_targets
  WHERE target_name=target_name_in AND target_type=target_type_in;

  LOOP
    OPEN rowid_cursor(begin_time_in,end_time_in,g_PurgeBatch,target_guid_out);
    FETCH rowid_cursor BULK COLLECT INTO v_rowIds;
    EXIT WHEN v_rowIds.COUNT <= 0;  -- Exit when nothing to purge

    FORALL i IN v_rowIds.FIRST..v_rowIds.LAST -- Bulk delete all rows by id.
      DELETE FROM MGMT_RT_METRICS_RAW WHERE rowid = v_rowIds(i);
    COMMIT;
    CLOSE rowid_cursor;
    v_rowIds.DELETE;  -- Clear the temporary row id list.
  END LOOP;

  IF rowid_cursor%ISOPEN THEN
    CLOSE rowid_cursor;
  END IF;
  RETURN 'SUCCESS';
  EXCEPTION
    WHEN OTHERS
    THEN
      dbms_output.put_line('Error in Raw Delete: ' || SQLERRM);
      IF rowid_cursor%ISOPEN THEN
         CLOSE rowid_cursor;
      END IF;
      RETURN NULL;
END DELETE_RAW_DATA;

--

FUNCTION DELETE_DAILY_DATA(
              target_name_in  IN VARCHAR2,
              target_type_in  IN VARCHAR2,
              begin_time_in   IN DATE,
              end_time_in     IN DATE) RETURN VARCHAR2
IS
  TYPE RowIdList IS TABLE OF UROWID
    INDEX BY BINARY_INTEGER;
  CURSOR url_rowid_cursor(begin_time DATE, end_time DATE, limit NUMBER,l_target_guid RAW) IS
    SELECT rowid
    FROM MGMT_RT_URL_1DAY
    WHERE
      target_guid=l_target_guid
-- guid = (SELECT target_guid from MGMT_TARGETS WHERE
--              target_name=l_target_name AND target_type=l_target_type)
      AND ((begin_time IS NULL) OR rollup_timestamp > begin_time)
      AND ((end_time IS NULL) OR rollup_timestamp < end_time)
      AND ROWNUM <= limit;

  CURSOR domain_rowid_cursor(begin_time DATE, end_time DATE,limit NUMBER, l_target_guid RAW) IS
    SELECT rowid
    FROM MGMT_RT_DOMAIN_1DAY
    WHERE
      target_guid = l_target_guid
      AND ((begin_time IS NULL) OR rollup_timestamp > begin_time)
      AND ((end_time IS NULL) OR rollup_timestamp < end_time)
      AND ROWNUM <= limit;

  CURSOR ip_rowid_cursor(begin_time DATE,end_time DATE,limit NUMBER,l_target_guid RAW) IS
    SELECT rowid
    FROM MGMT_RT_IP_1DAY
    WHERE
      target_guid = l_target_guid
      AND ((begin_time IS NULL) OR rollup_timestamp > begin_time)
      AND ((end_time IS NULL) OR rollup_timestamp < end_time)
      AND ROWNUM <= limit;
  v_rowIds RowIdList;
  i BINARY_INTEGER;
  target_guid_out RAW(16);
BEGIN

  SELECT target_guid INTO target_guid_out FROM mgmt_targets
  WHERE target_name=target_name_in AND target_type=target_type_in;

  LOOP
    OPEN url_rowid_cursor(begin_time_in,end_time_in,g_PurgeBatch,target_guid_out);
    FETCH url_rowid_cursor BULK COLLECT INTO v_rowIds;
    EXIT WHEN v_rowIds.COUNT <= 0;  -- Exit when nothing to purge

    FORALL i IN v_rowIds.FIRST..v_rowIds.LAST -- Bulk delete all rows by id.
      DELETE FROM MGMT_RT_URL_1DAY WHERE rowid = v_rowIds(i);
    COMMIT;
    CLOSE url_rowid_cursor;
    v_rowIds.DELETE;  -- Clear the temporary row id list.
  END LOOP;

  IF url_rowid_cursor%ISOPEN THEN
    CLOSE url_rowid_cursor;
  END IF;


  LOOP
    OPEN ip_rowid_cursor(begin_time_in,end_time_in,g_PurgeBatch,target_guid_out);
    FETCH ip_rowid_cursor BULK COLLECT INTO v_rowIds;
    EXIT WHEN v_rowIds.COUNT <= 0;  -- Exit when nothing to purge

    FORALL i IN v_rowIds.FIRST..v_rowIds.LAST -- Bulk delete all rows by id.
      DELETE FROM MGMT_RT_IP_1DAY WHERE rowid = v_rowIds(i);
    COMMIT;
    CLOSE ip_rowid_cursor;
    v_rowIds.DELETE;  -- Clear the temporary row id list.
  END LOOP;

  IF ip_rowid_cursor%ISOPEN THEN
    CLOSE ip_rowid_cursor;
  END IF;


  LOOP
    OPEN domain_rowid_cursor(begin_time_in,end_time_in,g_PurgeBatch,target_guid_out);
    FETCH domain_rowid_cursor BULK COLLECT INTO v_rowIds;
    EXIT WHEN v_rowIds.COUNT <= 0;  -- Exit when nothing to purge

    FORALL i IN v_rowIds.FIRST..v_rowIds.LAST -- Bulk delete all rows by id.
      DELETE FROM MGMT_RT_DOMAIN_1DAY WHERE rowid = v_rowIds(i);
    COMMIT;
    CLOSE domain_rowid_cursor;
    v_rowIds.DELETE;  -- Clear the temporary row id list.
  END LOOP;

  IF domain_rowid_cursor%ISOPEN THEN
    CLOSE domain_rowid_cursor;
  END IF;

  RETURN 'SUCCESS';
  EXCEPTION
    WHEN OTHERS
    THEN
      dbms_output.put_line('Error in Daily Delete: ' || SQLERRM);
      IF domain_rowid_cursor%ISOPEN THEN
         CLOSE domain_rowid_cursor;
      END IF;
      IF ip_rowid_cursor%ISOPEN THEN
        CLOSE ip_rowid_cursor;
      END IF;
      IF url_rowid_cursor%ISOPEN THEN
         CLOSE url_rowid_cursor;
      END IF;
  RETURN NULL;
END DELETE_DAILY_DATA;

--

FUNCTION DELETE_HOURLY_DIST_DATA(
              target_name_in  IN VARCHAR2,
              target_type_in  IN VARCHAR2,
              begin_time_in   IN DATE,
              end_time_in     IN DATE) RETURN VARCHAR2
IS
  TYPE RowIdList IS TABLE OF UROWID
    INDEX BY BINARY_INTEGER;
  CURSOR url_rowid_cursor(begin_time DATE, end_time DATE, limit NUMBER, l_target_guid RAW) IS
    SELECT rowid
    FROM MGMT_RT_URL_DIST_1HOUR
    WHERE
      target_guid = l_target_guid
      AND ((begin_time IS NULL) OR rollup_timestamp > begin_time)
      AND ((end_time IS NULL) OR rollup_timestamp < end_time)
      AND ROWNUM <= limit;

  CURSOR domain_rowid_cursor(begin_time DATE, end_time DATE, limit NUMBER,l_target_guid RAW) IS
    SELECT rowid
    FROM MGMT_RT_DOMAIN_DIST_1HOUR
    WHERE
      target_guid = l_target_guid
      AND ((begin_time IS NULL) OR rollup_timestamp > begin_time)
      AND ((end_time IS NULL) OR rollup_timestamp < end_time)
      AND ROWNUM <= limit;

  CURSOR ip_rowid_cursor(begin_time DATE, end_time DATE, limit NUMBER,l_target_guid RAW) IS
    SELECT rowid
    FROM MGMT_RT_IP_DIST_1HOUR
    WHERE
      target_guid = l_target_guid
      AND ((begin_time IS NULL) OR rollup_timestamp > begin_time)
      AND ((end_time IS NULL) OR rollup_timestamp < end_time)
      AND ROWNUM <= limit;
  v_rowIds RowIdList;
  i BINARY_INTEGER;
  target_guid_out RAW(16);
BEGIN

  SELECT target_guid INTO target_guid_out FROM mgmt_targets
  WHERE target_name=target_name_in AND target_type=target_type_in;

  LOOP
    OPEN url_rowid_cursor(begin_time_in,end_time_in,g_PurgeBatch,target_guid_out);
    FETCH url_rowid_cursor BULK COLLECT INTO v_rowIds;
    EXIT WHEN v_rowIds.COUNT <= 0;  -- Exit when nothing to purge

    FORALL i IN v_rowIds.FIRST..v_rowIds.LAST -- Bulk delete all rows by id.
      DELETE FROM MGMT_RT_URL_DIST_1HOUR WHERE rowid = v_rowIds(i);
    COMMIT;
    CLOSE url_rowid_cursor;
    v_rowIds.DELETE;  -- Clear the temporary row id list.
  END LOOP;

  IF url_rowid_cursor%ISOPEN THEN
    CLOSE url_rowid_cursor;
  END IF;


  LOOP
    OPEN ip_rowid_cursor(begin_time_in,end_time_in,g_PurgeBatch,target_guid_out);
    FETCH ip_rowid_cursor BULK COLLECT INTO v_rowIds;
    EXIT WHEN v_rowIds.COUNT <= 0;  -- Exit when nothing to purge

    FORALL i IN v_rowIds.FIRST..v_rowIds.LAST -- Bulk delete all rows by id.
      DELETE FROM MGMT_RT_IP_DIST_1HOUR WHERE rowid = v_rowIds(i);
    COMMIT;
    CLOSE ip_rowid_cursor;
    v_rowIds.DELETE;  -- Clear the temporary row id list.
  END LOOP;

  IF ip_rowid_cursor%ISOPEN THEN
    CLOSE ip_rowid_cursor;
  END IF;


  LOOP
    OPEN domain_rowid_cursor(begin_time_in,end_time_in,g_PurgeBatch,target_guid_out);
    FETCH domain_rowid_cursor BULK COLLECT INTO v_rowIds;
    EXIT WHEN v_rowIds.COUNT <= 0;  -- Exit when nothing to purge

    FORALL i IN v_rowIds.FIRST..v_rowIds.LAST -- Bulk delete all rows by id.
      DELETE FROM MGMT_RT_DOMAIN_DIST_1HOUR WHERE rowid = v_rowIds(i);
    COMMIT;
    CLOSE domain_rowid_cursor;
    v_rowIds.DELETE;  -- Clear the temporary row id list.
  END LOOP;

  IF domain_rowid_cursor%ISOPEN THEN
    CLOSE domain_rowid_cursor;
  END IF;

  RETURN 'SUCCESS';
  EXCEPTION
    WHEN OTHERS
    THEN
      dbms_output.put_line('Error in Hourly Distribution Delete: ' || SQLERRM);
      IF domain_rowid_cursor%ISOPEN THEN
         CLOSE domain_rowid_cursor;
      END IF;
      IF ip_rowid_cursor%ISOPEN THEN
        CLOSE ip_rowid_cursor;
      END IF;
      IF url_rowid_cursor%ISOPEN THEN
         CLOSE url_rowid_cursor;
      END IF;
  RETURN NULL;
END DELETE_HOURLY_DIST_DATA;

--

FUNCTION DELETE_DAILY_DIST_DATA(
              target_name_in  IN VARCHAR2,
              target_type_in  IN VARCHAR2,
              begin_time_in   IN DATE,
              end_time_in     IN DATE) RETURN VARCHAR2
IS
  TYPE RowIdList IS TABLE OF UROWID
    INDEX BY BINARY_INTEGER;
  CURSOR url_rowid_cursor(begin_time DATE, end_time DATE, limit NUMBER, l_target_guid RAW) IS
    SELECT rowid
    FROM MGMT_RT_URL_DIST_1DAY
    WHERE
      target_guid = l_target_guid
      AND ((begin_time IS NULL) OR rollup_timestamp > begin_time)
      AND ((end_time IS NULL) OR rollup_timestamp < end_time)
      AND ROWNUM <= limit;

  CURSOR domain_rowid_cursor(begin_time DATE, end_time DATE, limit NUMBER,l_target_guid RAW) IS
    SELECT rowid
    FROM MGMT_RT_DOMAIN_DIST_1DAY
    WHERE
      target_guid = l_target_guid
      AND ((begin_time IS NULL) OR rollup_timestamp > begin_time)
      AND ((end_time IS NULL) OR rollup_timestamp < end_time)
      AND ROWNUM <= limit;

  CURSOR ip_rowid_cursor(begin_time DATE, end_time DATE, limit NUMBER,l_target_guid RAW) IS
    SELECT rowid
    FROM MGMT_RT_IP_DIST_1DAY
    WHERE
      target_guid = l_target_guid
      AND ((begin_time IS NULL) OR rollup_timestamp > begin_time)
      AND ((end_time IS NULL) OR rollup_timestamp < end_time)
      AND ROWNUM <= limit;
  v_rowIds RowIdList;
  i BINARY_INTEGER;
  target_guid_out RAW(16);
BEGIN

  SELECT target_guid INTO target_guid_out FROM mgmt_targets
  WHERE target_name=target_name_in AND target_type=target_type_in;

  LOOP
    OPEN url_rowid_cursor(begin_time_in,end_time_in,g_PurgeBatch,target_guid_out);
    FETCH url_rowid_cursor BULK COLLECT INTO v_rowIds;
    EXIT WHEN v_rowIds.COUNT <= 0;  -- Exit when nothing to purge

    FORALL i IN v_rowIds.FIRST..v_rowIds.LAST -- Bulk delete all rows by id.
      DELETE FROM MGMT_RT_URL_DIST_1DAY WHERE rowid = v_rowIds(i);
    COMMIT;
    CLOSE url_rowid_cursor;
    v_rowIds.DELETE;  -- Clear the temporary row id list.
  END LOOP;

  IF url_rowid_cursor%ISOPEN THEN
    CLOSE url_rowid_cursor;
  END IF;


  LOOP
    OPEN ip_rowid_cursor(begin_time_in,end_time_in,g_PurgeBatch,target_guid_out);
    FETCH ip_rowid_cursor BULK COLLECT INTO v_rowIds;
    EXIT WHEN v_rowIds.COUNT <= 0;  -- Exit when nothing to purge

    FORALL i IN v_rowIds.FIRST..v_rowIds.LAST -- Bulk delete all rows by id.
      DELETE FROM MGMT_RT_IP_DIST_1DAY WHERE rowid = v_rowIds(i);
    COMMIT;
    CLOSE ip_rowid_cursor;
    v_rowIds.DELETE;  -- Clear the temporary row id list.
  END LOOP;

  IF ip_rowid_cursor%ISOPEN THEN
    CLOSE ip_rowid_cursor;
  END IF;


  LOOP
    OPEN domain_rowid_cursor(begin_time_in,end_time_in,g_PurgeBatch,target_guid_out);
    FETCH domain_rowid_cursor BULK COLLECT INTO v_rowIds;
    EXIT WHEN v_rowIds.COUNT <= 0;  -- Exit when nothing to purge

    FORALL i IN v_rowIds.FIRST..v_rowIds.LAST -- Bulk delete all rows by id.
      DELETE FROM MGMT_RT_DOMAIN_DIST_1DAY WHERE rowid = v_rowIds(i);
    COMMIT;
    CLOSE domain_rowid_cursor;
    v_rowIds.DELETE;  -- Clear the temporary row id list.
  END LOOP;

  IF domain_rowid_cursor%ISOPEN THEN
    CLOSE domain_rowid_cursor;
  END IF;
  RETURN 'SUCCESS';
  EXCEPTION
    WHEN OTHERS
    THEN
      dbms_output.put_line('Error in Daily Distribution Delete: ' || SQLERRM);
      IF domain_rowid_cursor%ISOPEN THEN
         CLOSE domain_rowid_cursor;
      END IF;
      IF ip_rowid_cursor%ISOPEN THEN
        CLOSE ip_rowid_cursor;
      END IF;
      IF url_rowid_cursor%ISOPEN THEN
         CLOSE url_rowid_cursor;
      END IF;
  RETURN NULL;
END DELETE_DAILY_DIST_DATA;

--

PROCEDURE CHRONOS_PURGE_TIME(
              target_name_in  IN VARCHAR2,
              target_type_in  IN VARCHAR2,
              end_period_in   IN DATE,
              axis_period_in  IN VARCHAR2 DEFAULT NULL)
IS
BEGIN
  CHRONOS_PURGE_TIME_DATA(target_name_in,target_type_in,
                               NULL,end_period_in,axis_period_in);
END CHRONOS_PURGE_TIME;

--

PROCEDURE CHRONOS_PURGE_INTERVAL(
              target_name_in  IN VARCHAR2,
              target_type_in  IN VARCHAR2,
              start_period_in IN DATE,
              end_period_in   IN DATE,
              axis_period_in  IN VARCHAR2 DEFAULT NULL)
IS
BEGIN

  CHRONOS_PURGE_TIME_DATA(target_name_in,target_type_in,
                       start_period_in,end_period_in,axis_period_in);
END CHRONOS_PURGE_INTERVAL;

--

PROCEDURE CHRONOS_PURGE_ALL(
              target_name_in  IN VARCHAR2,
              target_type_in  IN VARCHAR2)
IS
BEGIN

  CHRONOS_PURGE_TIME_DATA(target_name_in,target_type_in);

END CHRONOS_PURGE_ALL;

PROCEDURE CHRONOS_PURGE_RAW(
              target_name_in  IN VARCHAR2,
              target_type_in  IN VARCHAR2,
              end_period_in   IN DATE)
IS
l_ret_val VARCHAR2(64);
BEGIN

  l_ret_val:= DELETE_RAW_DATA(target_name_in,target_type_in,NULL,end_period_in);

END CHRONOS_PURGE_RAW;


-- 

-- Purges Run Metric Dates from String Metric History table 
PROCEDURE PURGE_RUN_METRIC_DATES(pcb_params IN OUT MGMT_PURGE_CALLBACK_PARAMS) IS
    l_purge_rowids 	p_rowid_list_type;
    l_rows_purged 	NUMBER := 0;
    p_purge_batchsize	PLS_INTEGER := 2000; -- rows/bulk delete
    p_ErrMsg           	VARCHAR(2048);
    p_Module       	VARCHAR(128);
    p_Action      	VARCHAR(128);

    CURSOR purge_string_hist (c_purge_till_time DATE) IS
    SELECT smh2.ROWID
      FROM (SELECT smh.target_guid, smh.metric_guid, smh.key_value, 
                   max(smh.collection_timestamp) max_coll_ts
              FROM MGMT_STRING_METRIC_HISTORY smh,
                   MGMT_METRICS mm
             WHERE mm.metric_name = 'chronos_run'
               AND mm.metric_column IN ('START_LOG_TIME', 'END_LOG_TIME')
               AND mm.metric_guid = smh.metric_guid
               AND mm.target_type IN ('oracle_webcache', 'oracle_apache', 'generic_apache')
               GROUP BY smh.target_guid, smh.metric_guid, smh.key_value) max_col,
            MGMT_STRING_METRIC_HISTORY smh2
           WHERE max_col.metric_guid = smh2.metric_guid
             AND max_col.target_guid = smh2.target_guid
             AND max_col.key_value = smh2.key_value
             AND smh2.collection_timestamp < least(c_purge_till_time, max_col.max_coll_ts);

  BEGIN

    DBMS_APPLICATION_INFO.SET_MODULE(
        module_name => 'Oracle Enterprise Manager.purge run metric dates',
        action_name => 'start');

    OPEN purge_string_hist(pcb_params.purge_upto_time);
    LOOP
      -- Bulk collect row ids
      FETCH purge_string_hist BULK COLLECT INTO l_purge_rowids LIMIT p_purge_batchsize;

      -- Exit when no more row ids fetched
      EXIT WHEN l_purge_rowids.COUNT <= 0;

      -- BULK delete
      FORALL i IN l_purge_rowids.FIRST..l_purge_rowids.LAST
        DELETE FROM MGMT_STRING_METRIC_HISTORY WHERE ROWID = l_purge_rowids(i);

      COMMIT;

      l_rows_purged := l_rows_purged + l_purge_rowids.COUNT;

      l_purge_rowids.DELETE;

    END LOOP;

    -- Close the cursor if open
    IF (purge_string_hist%ISOPEN) THEN
      CLOSE purge_string_hist;
    END IF;

    pcb_params.callback_result := 0;
    pcb_params.rows_processed  := l_rows_purged;
    pcb_params.error_code      := 0;
    pcb_params.error_msg       := NULL;

    DBMS_APPLICATION_INFO.SET_MODULE(null,null); 

  EXCEPTION
    WHEN OTHERS THEN
      p_ErrMsg := SUBSTR(SQLERRM, 1, 2048);
      DBMS_APPLICATION_INFO.READ_MODULE(p_Module,p_Action);
      MGMT_LOG.LOG_ERROR('PURGE',null,
          'Error during '||p_Module||' : '||p_ErrMsg,p_Action);
      -- Close the cursor if open
      IF (purge_string_hist%ISOPEN) THEN
        CLOSE purge_string_hist;
      END IF; 

END PURGE_RUN_METRIC_DATES;

--

END EMD_CHRONOS_ADMIN;
/
show errors;