Rem drv: <migrate type="schema_upgrade" version="10.2.0.4.2"/>
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/10.2.0.4.2/jobs/jobs_schema_upgrade.sql /st_emcore_10.2.0.4.2db11.2/1 2008/10/20 21:16:39 lsatyapr Exp $
Rem
Rem jobs_schema_upgrade.sql
Rem
Rem Copyright (c) 2008, Oracle and/or its affiliates. All rights reserved.
Rem
Rem    NAME
Rem      jobs_schema_upgrade.sql - Schema upgrade for DB11.2
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    lsatyapr    10/16/08 - Created
Rem    lsatyapr    10/12/08 - Alter purge tables
Rem


----------------------------------
-- Purge Policy related changes --
----------------------------------

-- Modify purge criteria
ALTER TABLE MGMT_JOB_PURGE_CRITERIA
ADD (
    criterion_type NUMBER(1),
    negated        NUMBER(1)
);

DECLARE
l_criterion_type NUMBER(1);
l_negated        NUMBER(1);
-- constants copied over from MGMT_JOB_ENGINE def
TARGET_CRITERION  constant NUMBER(1) := 1;
JOBTYPE_CRITERION constant NUMBER(1) := 2;
USER_CRITERION    constant NUMBER(1) := 3;
JOBNAME_CRITERION constant NUMBER(1) := 4;
BEGIN

    FOR c IN (SELECT ROWID, select_clause
              FROM   MGMT_JOB_PURGE_CRITERIA)
    LOOP
        IF INSTR(c.select_clause, 'AND e.job_id NOT IN') > 0 THEN
            l_negated := 1;
        ELSE
            l_negated := 0;
        END IF;

        IF INSTR(c.select_clause, 'MGMT_JOB_PURGE_TARGETS') > 0 THEN
            l_criterion_type := TARGET_CRITERION;
        ELSIF INSTR(c.select_clause, ' job_type IN') > 0 THEN
            l_criterion_type := JOBTYPE_CRITERION;
        ELSIF INSTR(c.select_clause, ' job_owner IN') > 0 THEN
            l_criterion_type := USER_CRITERION;
        ELSIF INSTR(c.select_clause, ' job_name LIKE') > 0 THEN
            l_criterion_type := JOBNAME_CRITERION;
        ELSE
            -- unknown
            l_criterion_type := -1;
        END IF;

        IF l_criterion_type = -1 THEN
            DELETE FROM MGMT_JOB_PURGE_CRITERIA
            WHERE  ROWID = c.ROWID;
        ELSE
            UPDATE MGMT_JOB_PURGE_CRITERIA
            SET    negated = l_negated,
                   criterion_type = l_criterion_type
            WHERE  ROWID = c.ROWID;
        END IF;
    END LOOP;

    COMMIT;
END;
/

-- Table would be very small, so DROP would be better than SET UNUSED
ALTER TABLE MGMT_JOB_PURGE_CRITERIA
      DROP ( select_clause );

--------------------------------------
-- END Purge Policy related changes --
--------------------------------------