Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\emdrep\sql\core\latest\purge\purge

Edit D:\app\Administrator\product\11.2.0\dbhome_1\sysman\admin\emdrep\sql\core\latest\purge\purge_pkgbodys.sql

Rem drv: <create type="pkgbodys" pos="preferences/preferences_pkgbodys.sql+"/>
Rem
Rem $Header: purge_pkgbodys.sql 09-dec-2005.02:20:39 jsadras Exp $
Rem
Rem purge_pkgbodys.sql
Rem
Rem Copyright (c) 2003, 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      purge_pkgbodys.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    jsadras     12/09/05 - Bug:4691227, Capture ORA-155 and log error 
Rem    scgrover    07/07/05 - add extended sql trace 
Rem    gsbhatia    07/01/05 - New repmgr header impl 
Rem    gsbhatia    06/26/05 - New repmgr header impl 
Rem    jsadras     03/28/05 - security fix 
Rem    jsadras     03/16/05 - check purge callback 
Rem    jsadras     03/04/05 - fix purge_upto_time 
Rem    rpinnama    02/18/05 - 
Rem    gsbhatia    02/13/05 - updating repmgr header 
Rem    gsbhatia    02/09/05 - updating repmgr header 
Rem    rpinnama    02/14/05 - Record the last_apply details
Rem    gsbhatia    02/07/05 - updating repmgr header 
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    rzazueta    10/29/03 - Compute total rows processed in apply_purge_policies
Rem    rzazueta    10/29/03 - Use mgmt_global.elapsed_time_msec function 
Rem    rpinnama    09/08/03 - rpinnama_purge_policies 
Rem    rpinnama    09/08/03 - Log statistics 
Rem    rpinnama    09/07/03 - 
Rem    rpinnama    08/28/03 - Created
Rem

CREATE OR REPLACE PACKAGE BODY EM_PURGE AS

-- Enables debugging
  PROCEDURE enable_debugging IS
  BEGIN
    debug_enabled := TRUE;
  END enable_debugging;

-- Disables debugging
  PROCEDURE disable_debugging IS
  BEGIN
    debug_enabled := FALSE;
  END disable_debugging;

--
  -- PURPOSE 
  --   Procedure(s) to enable/disable/verify detailed logging
  -- 
  PROCEDURE enable_detailed_logging IS
  BEGIN
    BEGIN
      INSERT INTO MGMT_PARAMETERS
        (parameter_name, parameter_value, parameter_comment,
         internal_flag)
       VALUES
        (DETAILED_PURGE_LOGGING, 'TRUE', DETAILED_PURGE_LOGGING_CMT, 1);
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        UPDATE MGMT_PARAMETERS
           SET parameter_value = 'TRUE'
         WHERE parameter_name = DETAILED_PURGE_LOGGING;
    END;
  END enable_detailed_logging;

PROCEDURE disable_detailed_logging IS
  BEGIN
    BEGIN
      INSERT INTO MGMT_PARAMETERS
        (parameter_name, parameter_value, parameter_comment,
         internal_flag)
       VALUES
        (DETAILED_PURGE_LOGGING, 'FALSE', DETAILED_PURGE_LOGGING_CMT, 1);
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        UPDATE MGMT_PARAMETERS
           SET parameter_value = 'FALSE'
         WHERE parameter_name = DETAILED_PURGE_LOGGING;
    END;
  END disable_detailed_logging;

FUNCTION  is_detailed_logging_enabled 
    RETURN INTEGER IS
    l_retVal INTEGER := 0;
    l_cnt    INTEGER := 0;
  BEGIN
    SELECT COUNT(*) into l_cnt
      FROM MGMT_PARAMETERS
     WHERE parameter_name=DETAILED_PURGE_LOGGING
       AND upper(parameter_value)='TRUE';

IF (l_cnt > 0) THEN
      -- detailed_purge_logging property is TRUE
      l_retVal := 1;
    END IF;

RETURN l_retVal;
  END is_detailed_logging_enabled;

-- Forward declarations;;
  PROCEDURE apply_policies_to_target(p_target_guid IN RAW,
                                     p_last_load_time IN DATE,
                                     p_detailed_logging IN NUMBER,
                                     p_rows_processed_out OUT NUMBER);

PROCEDURE apply_policy_to_target(p_target_guid IN RAW,
                                   p_last_load_time IN DATE,
                                   p_policy_name IN VARCHAR2,
                                   p_policy_type IN NUMBER,
                                   p_rollup_proc_name IN VARCHAR2,
                                   p_purge_proc_name IN VARCHAR2,
                                   p_policy_retent_hrs IN NUMBER,
                                   p_retent_group_name IN VARCHAR2,
                                   p_can_rollup_upto_time IN DATE,
                                   p_rolledup_upto_time IN OUT DATE,
                                   p_target_retent_hrs IN NUMBER,
                                   p_detailed_logging IN NUMBER,
                                   p_rows_processed_out OUT NUMBER);

PROCEDURE add_purge_policy_group(p_policy_group_name IN VARCHAR2,
                                   p_policy_group_desc IN VARCHAR2,
                                   p_retention_hours   IN NUMBER) IS
  BEGIN
    -- TODO : Check for null p_group_name and throw exception
    -- or dont do anything and the constraint error is raised
    INSERT INTO MGMT_PURGE_POLICY_GROUP
      (group_name, group_description, group_retention_hours)
     VALUES
      (p_policy_group_name, p_policy_group_desc, p_retention_hours);

END add_purge_policy_group;

PROCEDURE modify_purge_policy_group(p_policy_group_name IN VARCHAR2,
                                      p_policy_group_desc IN VARCHAR2,
                                      p_retention_hours   IN NUMBER) IS
  BEGIN

IF (p_policy_group_desc IS NOT NULL) THEN
      UPDATE MGMT_PURGE_POLICY_GROUP
         SET group_description = p_policy_group_desc
       WHERE group_name = p_policy_group_name;
    END IF;

IF (p_retention_hours IS NOT NULL) THEN
      UPDATE MGMT_PURGE_POLICY_GROUP
         SET group_retention_hours = p_retention_hours
       WHERE group_name = p_policy_group_name;
    END IF;

END modify_purge_policy_group;

PROCEDURE drop_purge_policy_group(p_policy_group_name IN VARCHAR2) IS
  BEGIN
    -- TODO: Check for NULL p_policy_group_name.
    DELETE FROM MGMT_PURGE_POLICY_GROUP
     WHERE group_name = p_policy_group_name;

END drop_purge_policy_group;

FUNCTION get_group_retention_hours(p_policy_group_name IN VARCHAR2)
      RETURN NUMBER IS
    l_retention_hours NUMBER := 0;
  BEGIN

BEGIN
      SELECT group_retention_hours INTO l_retention_hours
        FROM MGMT_PURGE_POLICY_GROUP
       WHERE group_name = p_policy_group_name;
    EXCEPTION 
      WHEN NO_DATA_FOUND THEN
        l_retention_hours := 0;
    END;

return l_retention_hours;
  END get_group_retention_hours;

PROCEDURE add_purge_policy(p_policy_name       IN VARCHAR2,
                             p_policy_type       IN NUMBER,
                             p_purge_proc_name   IN VARCHAR2,
                             p_retention_hours   IN NUMBER   DEFAULT 0,
                             p_rollup_proc_name  IN VARCHAR2 DEFAULT NULL,
                             p_policy_desc       IN VARCHAR2 DEFAULT NULL,
                             p_retent_group_name IN VARCHAR2 DEFAULT NULL,
                             p_exec_group_name   IN VARCHAR2 DEFAULT G_POLICY_EXEC_GROUP_DEFAULT,
                             p_user_configurable IN NUMBER   DEFAULT G_POLICY_CONFIGURABLE,
                             p_is_enabled        IN NUMBER   DEFAULT G_POLICY_ENABLED) IS
  l_error_message VARCHAR2(2000) ;
  BEGIN
    IF p_rollup_proc_name IS NOT NULL AND
       NOT EM_CHECK.is_valid_signature(
              p_purge_proc_name,
              mgmt_short_string_array('MGMT_ROLLUP_CALLBACK_PARAMS'),
              l_error_message) 
    THEN
      raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,l_error_message);
    END IF ;

IF p_purge_proc_name IS NOT NULL AND
       NOT EM_CHECK.is_valid_signature(
              p_purge_proc_name,
              mgmt_short_string_array('MGMT_PURGE_CALLBACK_PARAMS'),
              l_error_message) 
    THEN
      raise_application_error(MGMT_GLOBAL.INVALID_PARAMS_ERR,l_error_message);
    END IF ;

INSERT INTO MGMT_PURGE_POLICY
      (policy_name, policy_description, policy_type,
       rollup_proc_name, purge_proc_name, execution_group_name,
       policy_retention_hours, retention_group_name, user_configurable,
       is_enabled)
     VALUES
      (p_policy_name, p_policy_desc, p_policy_type,
       p_rollup_proc_name, p_purge_proc_name, p_exec_group_name,
       p_retention_hours, p_retent_group_name, p_user_configurable,
       p_is_enabled);

-- TODO: Register a logging module for tracking perfomance details

IF (p_policy_type = G_POLICY_TYPE_TARGET) THEN
      FOR tgt_rec IN (SELECT target_guid 
                        FROM MGMT_TARGETS)
      LOOP
        add_target_policy_details(tgt_rec.target_guid, p_policy_name);
      END LOOP;
    ELSE
      add_target_policy_details(G_ALLZERO_GUID, p_policy_name);
    END IF;

END add_purge_policy;

PROCEDURE modify_purge_policy IS
  BEGIN
    NULL;
  END modify_purge_policy;

PROCEDURE drop_purge_policy(p_policy_name IN VARCHAR2) IS
  BEGIN
    -- Delete from target_policy_details
    DELETE FROM MGMT_PURGE_POLICY_TARGET_STATE
      WHERE policy_name = p_policy_name;

-- Delete from mgmt_purge_policy table.
    DELETE FROM MGMT_PURGE_POLICY
      WHERE policy_name = p_policy_name;

END drop_purge_policy;

PROCEDURE enable_purge_policy(p_policy_name IN VARCHAR2) IS
  BEGIN

UPDATE MGMT_PURGE_POLICY
       SET is_enabled = G_POLICY_ENABLED
     WHERE policy_name = p_policy_name;

END enable_purge_policy;

PROCEDURE disable_purge_policy(p_policy_name IN VARCHAR2) IS
  BEGIN

UPDATE MGMT_PURGE_POLICY
       SET is_enabled = G_POLICY_DISABLED
     WHERE policy_name = p_policy_name;

END disable_purge_policy;

PROCEDURE configure_purge_policy IS
  BEGIN
    NULL;
  END configure_purge_policy;

PROCEDURE add_target_policy_details(p_target_guid IN RAW,
                                      p_purge_policy IN VARCHAR2,
                                      p_can_rollup_upto_time IN DATE DEFAULT G_DATE_01_01_1970,
                                      p_rolledup_upto_time IN DATE DEFAULT G_DATE_01_01_1970,
                                      p_retention_hours NUMBER DEFAULT 0) IS
  BEGIN
    BEGIN
      INSERT INTO MGMT_PURGE_POLICY_TARGET_STATE
        (target_guid, policy_name, can_rollup_upto_time,
         rolledup_upto_time, target_retention_hours)
       VALUES
        (p_target_guid, p_purge_policy, p_can_rollup_upto_time,
         p_rolledup_upto_time, p_retention_hours);
    EXCEPTION
      WHEN DUP_VAL_ON_INDEX THEN
        -- Ignore duplicates
        NULL;

END;
  END add_target_policy_details;

-- Add target callback
  PROCEDURE add_target(p_target_guid IN RAW) IS
  BEGIN
    FOR policy_rec IN (SELECT policy_name 
                         FROM MGMT_PURGE_POLICY
                        WHERE policy_type = G_POLICY_TYPE_TARGET)
    LOOP
      add_target_policy_details(p_target_guid, policy_rec.policy_name);
    END LOOP;

END add_target;

-- job control
  PROCEDURE remove_purge_policy_jobs IS
  BEGIN
    NULL;
  END remove_purge_policy_jobs;

PROCEDURE submit_purge_policy_jobs IS
  BEGIN
    NULL;
  END submit_purge_policy_jobs;

PROCEDURE DBMSJOB_EXTENDED_SQL_TRACE_ON(p_value IN BOOLEAN) IS
  BEGIN  
    MGMT_SQLTRACE.EXTENDED_SQL_TRACE_ON(EST_PURGE_NAME, p_value);
  END DBMSJOB_EXTENDED_SQL_TRACE_ON;

PROCEDURE apply_purge_policies IS

l_target_guid       t_guid_list;
    l_last_load_time    t_date_list;

l_start_timestmp    TIMESTAMP := SYSTIMESTAMP;
    l_rows_processed     NUMBER   := 0;
    l_tot_rows_processed NUMBER   := 0;
    l_detailed_logging   NUMBER   := 0;
    l_proc_name          CONSTANT VARCHAR2(30) := 'apply_purge_policies' ;

CURSOR target_cursor IS
      SELECT target_guid, last_load_time
        FROM MGMT_TARGETS;
  BEGIN

MGMT_SQLTRACE.EXTENDED_SQL_TRACE(EST_PURGE_NAME);

l_detailed_logging := is_detailed_logging_enabled;

OPEN target_cursor;
    LOOP

FETCH target_cursor BULK COLLECT INTO l_target_guid,
                                            l_last_load_time ;

EXIT WHEN NOT l_target_guid.EXISTS(1);

-- Loop through the targets returned from the target_cursor.
      FOR i IN l_target_guid.FIRST..l_target_guid.LAST
      LOOP

IF (EMDW_LOG.P_IS_DEBUG_SET) THEN
	  EMDW_LOG.DEBUG(l_proc_name||' Applying purge policies for tguid - ' || 
	      l_target_guid(i), G_MODULE_NAME);
        END IF;

apply_policies_to_target(l_target_guid(i), l_last_load_time(i), 
                                 l_detailed_logging, l_rows_processed);
        l_tot_rows_processed := l_tot_rows_processed + l_rows_processed;

END LOOP;

END LOOP; -- target_cursor

l_target_guid.DELETE;
    l_last_load_time.DELETE;
    
    IF target_cursor%ISOPEN THEN
      CLOSE target_cursor;
    END IF;

-- Apply system purge policies
    apply_policies_to_target(G_ALLZERO_GUID, SYSDATE, 
                             l_detailed_logging, l_rows_processed);
    l_tot_rows_processed := l_tot_rows_processed + l_rows_processed;

-- Record the time taken
    MGMT_LOG.LOG_PERFORMANCE(PURGE_POLICY_MODULE,
             MGMT_GLOBAL.ELAPSED_TIME_MSEC(l_start_timestmp, SYSTIMESTAMP),
             l_start_timestmp, 'Y', 'RECORDS', l_tot_rows_processed);

IF (EMDW_LOG.P_IS_DEBUG_SET) THEN
      EMDW_LOG.DEBUG(l_proc_name||' Time taken = ' || 
          MGMT_GLOBAL.ELAPSED_TIME_MSEC(l_start_timestmp, SYSTIMESTAMP),
	  G_MODULE_NAME);
    END IF;
  EXCEPTION
  WHEN OTHERS THEN
    mgmt_log.log_error(PURGE_POLICY_MODULE, 0,
                     'Error caught processing purge policy ' ||
                     SUBSTR(sqlerrm, 0, 400));
  END apply_purge_policies;

PROCEDURE apply_policies_to_target(p_target_guid IN RAW,
                                     p_last_load_time IN DATE,
                                     p_detailed_logging IN NUMBER,
                                     p_rows_processed_out OUT NUMBER) IS

l_purge_policy t_purge_policy_rec;
    l_rows_processed NUMBER := 0;

l_error_code     NUMBER := 0;
    l_error_msg      VARCHAR2(2048);
    l_proc_name          CONSTANT VARCHAR2(30) := 'apply_purge_policies_to_target' ;

CURSOR purge_policy_cursor (c_target_guid RAW) IS
      SELECT pp.policy_name, pp.policy_type, pp.rollup_proc_name, pp.purge_proc_name, 
             pp.policy_retention_hours, pp.retention_group_name, 
             tpd.can_rollup_upto_time, tpd.rolledup_upto_time, 
             tpd.target_retention_hours
        FROM MGMT_PURGE_POLICY pp, MGMT_PURGE_POLICY_TARGET_STATE tpd
       WHERE pp.policy_name = tpd.policy_name
         AND tpd.target_guid = c_target_guid
         AND pp.is_enabled = G_POLICY_ENABLED
         AND (pp.rollup_proc_name IS NULL OR 
              tpd.can_rollup_upto_time > tpd.rolledup_upto_time) 
         ;

BEGIN

p_rows_processed_out := 0;

OPEN purge_policy_cursor(p_target_guid);
    LOOP

FETCH purge_policy_cursor BULK COLLECT INTO l_purge_policy.policy_name,
                                                  l_purge_policy.policy_type,
                                                  l_purge_policy.rollup_proc_name,
                                                  l_purge_policy.purge_proc_name,
                                                  l_purge_policy.policy_retent_hrs,
                                                  l_purge_policy.retent_group_name,
                                                  l_purge_policy.can_rollup_upto_time,
                                                  l_purge_policy.rolledup_upto_time,
                                                  l_purge_policy.target_retent_hrs
                                                  LIMIT 100;

-- Exit if no more purge policy have to be done
      EXIT WHEN NOT l_purge_policy.policy_name.EXISTS(1);

-- Execute rollup proc..
      FOR i IN l_purge_policy.policy_name.FIRST..l_purge_policy.policy_name.LAST
      LOOP
        BEGIN
	  l_error_code := 0;
          apply_policy_to_target(p_target_guid, 
	                         p_last_load_time,
                                 l_purge_policy.policy_name(i),
                                 l_purge_policy.policy_type(i),
                                 l_purge_policy.rollup_proc_name(i),
                                 l_purge_policy.purge_proc_name(i),
                                 l_purge_policy.policy_retent_hrs(i),
                                 l_purge_policy.retent_group_name(i),
                                 l_purge_policy.can_rollup_upto_time(i),
                                 l_purge_policy.rolledup_upto_time(i),
                                 l_purge_policy.target_retent_hrs(i),
                                 p_detailed_logging,
                                 l_rows_processed);

-- Commit/Rollback after every purge policy..
          COMMIT;
          p_rows_processed_out := p_rows_processed_out + l_rows_processed;
        EXCEPTION
          WHEN OTHERS THEN
	    l_error_code := SQLCODE;
            l_error_msg := SQLERRM;
            ROLLBACK;

IF (EMDW_LOG.P_IS_DEBUG_SET) THEN
	      EMDW_LOG.DEBUG(l_proc_name||' Error caught processing purge policy ' ||
                  l_purge_policy.policy_name(i), G_MODULE_NAME);
	      EMDW_LOG.DEBUG(l_proc_name||' ERROR = ' || SUBSTR(l_error_msg, 0, 200),
                  G_MODULE_NAME);
	      EMDW_LOG.DEBUG(l_proc_name||' ERROR = ' || SUBSTR(l_error_msg, 200, 400),
                  G_MODULE_NAME);
            END IF;

mgmt_log.log_error(PURGE_POLICY_MODULE, 0, 
                     'Error caught processing purge policy ' ||  
		     l_purge_policy.policy_name(i) || ' ' || 
                     SUBSTR(l_error_msg, 0, 400));
        END;

-- Log the last times and records processed.
	-- Note : Log the rolledup_upto_time also 
        UPDATE MGMT_PURGE_POLICY_TARGET_STATE
           SET rolledup_upto_time = NVL(l_purge_policy.rolledup_upto_time(i), rolledup_upto_time),
	       last_apply_time    = SYSDATE,
	       last_apply_status  = l_error_code,
	       last_apply_msg     = SUBSTR(l_error_msg, 0, 200),
	       last_apply_rows    = l_rows_processed
         WHERE target_guid = p_target_guid
           AND policy_name = l_purge_policy.policy_name(i);

-- Commit after every purge policy..
        COMMIT;

END LOOP;

-- Release temporary tables..
      l_purge_policy.policy_name.DELETE;
      l_purge_policy.policy_type.DELETE;
      l_purge_policy.rollup_proc_name.DELETE;
      l_purge_policy.purge_proc_name.DELETE;
      l_purge_policy.policy_retent_hrs.DELETE;
      l_purge_policy.retent_group_name.DELETE;
      l_purge_policy.can_rollup_upto_time.DELETE;
      l_purge_policy.rolledup_upto_time.DELETE;
      l_purge_policy.target_retent_hrs.DELETE;

END LOOP;
    
  END apply_policies_to_target;

l_rollup_cb_params     MGMT_ROLLUP_CALLBACK_PARAMS;
    l_purge_cb_params      MGMT_PURGE_CALLBACK_PARAMS;
    l_can_rollup_upto_time DATE;
    l_rolledup_upto_time   DATE;
    l_retention_hours      NUMBER := 0;
    l_purge_upto_time      DATE;
    l_start_timestmp       TIMESTAMP := SYSTIMESTAMP;
    l_action_start_ts      TIMESTAMP := SYSTIMESTAMP;
    l_proc_name            CONSTANT VARCHAR2(30) := 'apply_purge_policy_to_target' ;

BEGIN

p_rows_processed_out := 0;

IF (p_rollup_proc_name IS NOT NULL) THEN
  
      l_action_start_ts := SYSTIMESTAMP;

l_can_rollup_upto_time := p_can_rollup_upto_time;
      l_rolledup_upto_time   := p_rolledup_upto_time;

IF (EMDW_LOG.P_IS_DEBUG_SET) THEN
        EMDW_LOG.DEBUG(l_proc_name||' Calling rollup proc -' || p_rollup_proc_name ||
                ' target_guid ' || p_target_guid || 
                ' policy name ' || p_policy_name ||
                ' Can rollup time - ' || l_can_rollup_upto_time ||
                ' Rolledup upto time - ' || l_rolledup_upto_time,
	  G_MODULE_NAME);
      END IF;
  
      l_rollup_cb_params := MGMT_ROLLUP_CALLBACK_PARAMS(p_policy_name,
                                                        p_policy_type,
                                                        p_target_guid,
                                                        p_can_rollup_upto_time,
                                                        p_rolledup_upto_time,
                                                        0, 0, 0, NULL);
      BEGIN

EXECUTE IMMEDIATE
          'BEGIN ' ||EM_CHECK.qualified_sql_name(p_rollup_proc_name)|| '(:1); END; '
          USING IN OUT l_rollup_cb_params;

EXCEPTION
        WHEN OTHERS THEN
          raise_application_error(-20000, 'Error executing rollup proc ' ||
                p_rollup_proc_name || ' for purge policy ' || p_policy_name || 
                ' for target ' || p_target_guid || ' ERROR = ' || SQLERRM);
      END;

IF (l_rollup_cb_params.callback_result <> 0) THEN

raise_application_error(-20000, 'Error executing rollup proc ' ||
          p_rollup_proc_name || ' for purge policy ' || p_policy_name || 
          ' for target ' || p_target_guid || 
          ' ERROR CODE = ' || l_rollup_cb_params.error_code ||
          ' ERROR MSG = ' || l_rollup_cb_params.error_msg);

END IF;
  
      -- Record rolledup_upto_time to MGMT_TARGET_PURGE_DETAILS
      p_rolledup_upto_time := l_rollup_cb_params.rolledup_upto_time;

-- Log performance info
      IF (p_detailed_logging > 0) THEN
        MGMT_LOG.LOG_PERFORMANCE(PURGE_POLICY_MODULE,
             MGMT_GLOBAL.ELAPSED_TIME_MSEC(l_action_start_ts, SYSTIMESTAMP),
             l_action_start_ts, 'N', 'RECORDS', l_rollup_cb_params.rows_processed,
	     p_policy_name, 'ROLLUP', p_target_guid );
      END IF;

p_rows_processed_out := p_rows_processed_out + l_rollup_cb_params.rows_processed;

ELSE

-- If there is no rollup proc, assumed that they are rolled up to 
      -- last_load_time
      l_can_rollup_upto_time := p_last_load_time;
      p_rolledup_upto_time   := p_last_load_time;

END IF;

IF (p_purge_proc_name IS NOT NULL) THEN
  
      l_action_start_ts := SYSTIMESTAMP;

-- Get retentions hours..
      IF (p_target_retent_hrs > 0) THEN
        l_retention_hours := p_target_retent_hrs;
      ELSIF (p_policy_retent_hrs > 0) THEN
        l_retention_hours := p_policy_retent_hrs;
      ELSIF (p_retent_group_name IS NOT NULL) THEN
        l_retention_hours := get_group_retention_hours(p_retent_group_name);
      END IF;

IF (l_retention_hours > 0) THEN 
        l_purge_upto_time := LEAST(NVL(p_rolledup_upto_time, SYSDATE),
              (l_can_rollup_upto_time - (l_retention_hours/24)));

IF (debug_enabled) THEN

DBMS_OUTPUT.PUT_LINE('Calling purge proc -' || p_purge_proc_name ||
                  ' target_guid ' || p_target_guid || 
                  ' Retention hours ' || l_retention_hours || 
                  ' policy name ' || p_policy_name ||
                  ' Purge upto time - ' || l_purge_upto_time 
                  );
        END IF;

l_purge_cb_params := MGMT_PURGE_CALLBACK_PARAMS(
                                                    p_policy_name,
                                                    p_policy_type,
                                                    p_target_guid,
                                                    l_purge_upto_time,
                                                    0, 0, 0, NULL);

BEGIN
          EXECUTE IMMEDIATE
            'BEGIN ' ||EM_CHECK.qualified_sql_name(p_purge_proc_name)|| '(:1); END; '
            USING IN OUT l_purge_cb_params;
        EXCEPTION
          WHEN OTHERS THEN
            raise_application_error(-20000, 'Error executing purge proc ' ||
                p_purge_proc_name || ' for purge policy ' || p_policy_name || 
                ' for target ' || p_target_guid || ' ERROR = ' || SQLERRM);
        END;

IF (l_purge_cb_params.callback_result <> 0) THEN

raise_application_error(-20000, 'Error executing purge proc ' ||
            p_purge_proc_name || ' for purge policy ' || p_policy_name || 
            ' for target ' || p_target_guid || 
            ' ERROR CODE = ' || l_purge_cb_params.error_code ||
            ' ERROR MSG = ' || l_purge_cb_params.error_msg);

END IF;

IF (p_detailed_logging > 0) THEN
          MGMT_LOG.LOG_PERFORMANCE(PURGE_POLICY_MODULE,
             MGMT_GLOBAL.ELAPSED_TIME_MSEC(l_action_start_ts, SYSTIMESTAMP),
             l_action_start_ts, 'N', 'RECORDS', l_purge_cb_params.rows_processed,
             p_policy_name, 'PURGE', p_target_guid );
        END IF;

p_rows_processed_out := p_rows_processed_out + l_purge_cb_params.rows_processed;

END IF; -- (l_retention_hrs > 0)

END IF; -- (p_purge_proc IS NOT NULL)

END apply_policy_to_target;

END EM_PURGE;
/
show errors;

Ms-Dos/Windows Unix Write backup

jsp File Browser version 1.2 by www.vonloesch.de