rem SYNOPSYS
rem
rem   @purge_audit_template.sql workspace {DEPLOYMENT | EXECUTION | SCHEDULER_JOB | SCHEDULER_EXECUTION}
rem                                       {sql_predicate | ALL | DATE_RANGE} [start_date end_date]
rem
rem NAME
rem
rem   purge_audit_template.sql - SQLPlus Purge Audit Data Template
rem
rem USAGE
rem
rem   workspace      := e.g. MY_WORKSPACE                   - Workspace in which the job is to run
rem
rem   audit_type     :- DEPLOYMENT                          - Deployment Job Audit Data
rem                  |  EXECUTION                           - Execution Job Audit Data
rem                  |  SCHEDULER_JOB                       - Scheduler Job Audit Data
rem                  |  SCHEDULER_EXECUTION                 - Scheduler Execution Job Audit Data
rem
rem   sql_predicate  :- e.g. "number_script_run_errors > 0" - Selection Criteria e.g. deployment errors
rem                  |  ALL                                 - Purge all deployment or execution audit data
rem                  |  DATE_RANGE                          - Purge audit data within given Date Range
rem
rem   start_date     :- e.g. TO_DATE('01-Jan-2008')         - Start of Date Range
rem
rem   end_date       :- e.g. SYSDATE                        - End of Date  Range
rem
rem RETURNS
rem
rem   none
rem
rem DESCRIPTION
rem
rem   This SQL*Plus script can be called from the SQL*Plus shell.
rem
rem   This script is designed to be run from a WorkspaceOwner or WorkspaceUser with Admin privilege.
rem
rem   The Workspace is nominated in the parameters, and should be declared as workspaceOwner.workspaceName
rem   (if only workspaceName is given, workspaceOwner will be defaulted to user).
rem
rem   It is designed to purge either Deployment Audit Data or Execution Audit Data.
rem   Scheduler and non-Scheduler Execution Audit Data are treated separately.
rem
rem   A selection mode parameter is provided to enable the purge to be selective
rem   using a predicate or a date range.
rem
rem   The SQL predicate parameter, if supplied, must conform to the SQL syntax rules for "condition".
rem   Columns available for Deployment predicate are given by ALL_RT_AUDIT_DEPLOYMENTS view.
rem   Columns available for Execution and Scheduler predicate are given by ALL_RT_AUDIT_EXECUTIONS view.
rem
rem   The start date and end date parameters, if supplied, must conform to the SQL syntax rules for "date".
rem
rem EXAMPLE
rem
rem   sqlplus user/password@tns_name @purge_audit_template.sql MY_WORKSPACE DEPLOYMENT ALL null null
rem   sqlplus user/password@tns_name @purge_audit_template.sql MY_WORKSPACE EXECUTION "number_task_errors > 0" null null
rem   sqlplus user/password@tns_name @purge_audit_template.sql MY_WORKSPACE EXECUTION DATE_RANGE TO_DATE('01-Jan-2004') SYSDATE
rem
--

set serveroutput on
set verify off

--define WORKSPACE=&1
--define AUDIT_TYPE=&2
--define SQL_PREDICATE=&3
--define START_DATE=&4
--define END_DATE=&5
define DATE_MASK = "DD-Mon-YYYY HH12-MI-SSAM" (CHAR) -- Date format used in output message

set role OWB_USER;
call owbsys.wb_rt_script_util.set_workspace('&1.');

declare
  l_audit_type         varchar2(30);              -- DEPLOYMENT or EXECUTION or SCHEDULER_...
  l_criteria           varchar2(2000);            -- Selection criteria
  l_start              date;                      -- Start of date range
  l_end                date;                      -- End of date range
  l_message            varchar2(2100);            -- Summary of actions

begin
  l_audit_type := '&2';
  l_criteria := '&3';
  l_start := &4;
  l_end := &5;

  l_message := owbsys.wb_rt_script_util.purge_audit_data(l_audit_type, l_criteria,
                                                         l_start, l_end, '&DATE_MASK');
  dbms_output.put_line(l_message);

end;
/