Rem
Rem $Header: purge_audit_tables.sql 21-jan-2008.02:52:30 tzrose   Exp $
Rem
Rem purge_audit_tables.sql
Rem
Rem Copyright (c) 2008, Oracle.  All rights reserved.  
Rem
Rem    NAME
Rem      purge_audit_tables.sql - Purge Runtime Audit Tables
Rem
Rem    DESCRIPTION
Rem      This script is used to purge execution audit data from the runtime audit tables
Rem      for a heterogeneous environment in an Oracle database
Rem
Rem    NOTES
Rem      This SQL*Plus script can be called from the SQL*Plus shell:
Rem      sqlplus user/password@tns_name @purge_audit_tables.sql
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    tzrose      01/21/08 - Created
Rem

SET ECHO ON
SET FEEDBACK 1
SET NUMWIDTH 10
SET LINESIZE 80
SET TRIMSPOOL ON
SET TAB OFF
SET PAGESIZE 100

set serveroutput on
set verify off

whenever sqlerror exit failure;

declare
  l_mode           varchar2(10);       -- ALL or DATE_RANGE
  l_start          date;               -- Start of date range
  l_end            date;               -- End of date range

begin
  l_mode := '&1';
  l_start := &2;
  l_end := &3;

  if UPPER(l_mode) = 'ALL'
  then
    delete from wb_rt_ht_audit_job;
      
    dbms_output.put_line('ALL EXECUTION AUDIT DETAILS PURGED');
  end if;  
        
  if UPPER(l_mode) = 'DATE_RANGE'
  then
    delete from wb_rt_ht_audit_job where creation_date > l_start and creation_date < l_end;

    dbms_output.put_line('ALL EXECUTION AUDIT DETAILS PURGED BETWEEN ' ||
                         to_char(l_start) || ' AND ' || to_char(l_end));
  end if;  

  commit;

end;