Rem drv: <migrate type="schema_upgrade" version="10.2.0.2" />
Rem
Rem $Header: jobs_schema_upgrade.sql 20-dec-2005.17:16:27 dsahrawa Exp $
Rem
Rem jobs_schema_upgrade.sql
Rem
Rem Copyright (c) 2005, Oracle. All rights reserved.  
Rem
Rem    NAME
Rem      jobs_schema_upgrade.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    dsahrawa    12/20/05 - recover from non existence of index 
Rem    dsahrawa    12/06/05 - jobs scheme upgrade to 10.2.0.2 
Rem    dsahrawa    12/06/05 - Created
Rem

REM first drop the erroneous index on target_guid if it exists
BEGIN
    EXECUTE IMMEDIATE 'DROP INDEX MGMT_NESTED_JOB_CRED_INFO_IDX1';
EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE = -01418 THEN
            NULL;
        ELSE
            RAISE;
        END IF;
END;
/

REM then create the correct one on (target_name, target_type)
REM using IDX1 instead of IDX01 due to name length constraint
CREATE INDEX MGMT_NESTED_JOB_CRED_INFO_IDX1 ON
    MGMT_NESTED_JOB_CRED_INFO(target_name, target_type);