Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/11.2.0.1/jobs/jobs_schema_upgrade_types.sql rbali_oneoff_updated/1 2025/03/14 06:41:12 rbali Exp $
Rem
Rem jobs_schema_upgrade_types.sql
Rem
Rem Copyright (c) 2010, 2025, Oracle and/or its affiliates. 
Rem All rights reserved.

Rem BEGIN EMRU_FILE_METADATA
Rem EMRU_SOURCE_FILE:emcore/source/oracle/sysman/emdrep/sql/core/11.2.0.1/jobs/jobs_schema_upgrade_types.sql
Rem EMRU_UNIX_DELIVERABLES:
Rem emcore/source/oracle/sysman/emdrep/sql/core/11.2.0.1/jobs/jobs_schema_upgrade_types.sql

Rem EMRU_FILE_POSTINSTALL:<!!! enter the view based location of the executable file. If there is NONE leave as-is !!!>
Rem EMRU_GENERIC_FLAG:NONE
Rem END EMRU_FILE_METADATA

Rem
Rem    NAME
Rem      jobs_schema_upgrade_types.sql - <one-line expansion of the name>
Rem
Rem    DESCRIPTION
Rem      new types introduced in 11.2 for job system
Rem
Rem    NOTES
Rem      <other useful comments, qualifications, etc.>
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    gbafna      01/07/25 - make synonyms noneditionable
Rem    rnadidar    02/11/15 - cut over to EBR
Rem    emeijer     05/02/11 - bug 11890259 - support for more targets per job.
Rem    emeijer     03/14/11 - bug 11735165 - Add ready_time, priority and start_time
Rem    lsatyapr    08/23/10 - Fix size of jobtype in types
Rem    amathur     07/08/10 - add credentials as part of step details
Rem    lsatyapr    04/21/10 - Update job_step_record
Rem    amathur     03/17/10 - Created
Rem

-- introduce new types before changing existing types due to dependencies.
@&EM_SQL_ROOT/core/11.2.0.1/jobs/jobs_types.sql


-- update existing types

-- Drop the types in reverse dependency, that is, the least dependent type must
-- be dropped last.
DROP TYPE MGMT_JOB_STEP_LIST;
DROP TYPE MGMT_JOB_STEP_RECORD;
DROP TYPE MGMT_JOB_SIMPLE_STEP_RECORD;

-- add the step level credentials to this type
CREATE OR REPLACE TYPE MGMT_JOB_SIMPLE_STEP_RECORD AS OBJECT (
    command_name   VARCHAR2(&COMMAND_NAME_SIZE),
    command_type   NUMBER(1),
    command_class  VARCHAR2(512),
    step_params    MGMT_JOB_PARAM_LIST,
    agent_bound    INTEGER,
    trusted        NUMBER(1),
    step_creds     GC_JOB_CRED_ARRAY
);
/



-- MGMT_JOB_STEP_RECORD can be recreated, so just use create or replace
CREATE OR REPLACE TYPE MGMT_JOB_STEP_RECORD AS OBJECT (
    job_type     VARCHAR2(&JOB_TYPE_NAME_SIZE),
    job_name     VARCHAR2(&JOB_PARAM_NAME_SIZE),
    job_owner    VARCHAR2(&USERNAME_SIZE),
    run_identity VARCHAR2(&USERNAME_SIZE),
    job_id       &GUID_TYPE,
    execution_id &GUID_TYPE,
    step_name    VARCHAR2(&JOB_PARAM_NAME_SIZE),
    step_type    NUMBER(2),
    step_id      INTEGER,
    step_status  NUMBER(2),

    iterate_param VARCHAR2(&JOB_PARAM_NAME_SIZE),
    iterate_param_index NUMBER(6),

    first_step   NUMBER(1),

    job_params   MGMT_JOB_PARAM_LIST,

    -- Valid for steps only
    step_info    MGMT_JOB_SIMPLE_STEP_RECORD,

    plugin       VARCHAR2(&PLUGIN_ID_SIZE),
    sdk_level    INTEGER,

    -- Step-scheduler produced data.
    start_time   DATE,
    ready_time   DATE,
    priority     INTEGER,

    CONSTRUCTOR FUNCTION MGMT_JOB_STEP_RECORD(p_job_id       RAW,
                                              p_execution_id RAW,
                                              p_step_name    VARCHAR2,
                                              p_step_type    NUMBER,
                                              p_step_id      INTEGER,
                                              p_step_status  NUMBER,
                                              p_command_name VARCHAR2,
                                              p_command_type NUMBER)
        RETURN SELF AS RESULT,

    CONSTRUCTOR FUNCTION MGMT_JOB_STEP_RECORD(p_stmt         MGMT_JOB_STMT_EXEC_DETAILS,
                                              p_job_type     VARCHAR2,
                                              p_job_name     VARCHAR2,
                                              p_job_owner    VARCHAR2,
                                              p_run_identity VARCHAR2,
                                              p_first_step   NUMBER,
                                              p_iterate_param_index NUMBER,
                                              p_job_params   MGMT_JOB_PARAM_LIST,
                                              p_step_info    MGMT_JOB_SIMPLE_STEP_RECORD,
                                              p_plugin       VARCHAR2,
                                              p_sdk_level    INTEGER)
        RETURN SELF AS RESULT
);
/


CREATE OR REPLACE TYPE MGMT_JOB_STEP_LIST AS TABLE 
       OF MGMT_JOB_STEP_RECORD;
/


-- Fix the job_type field size in all types
ALTER TYPE JobRunObjType
    MODIFY ATTRIBUTE job_type VARCHAR2(&JOB_TYPE_NAME_SIZE) CASCADE;

ALTER TYPE MGMT_JOB_PRMTER_URI_INF_RECORD
    MODIFY ATTRIBUTE job_type VARCHAR2(&JOB_TYPE_NAME_SIZE) CASCADE;

ALTER TYPE MGMT_JOB_TYPE_URI_INFO_RECORD
    MODIFY ATTRIBUTE job_type VARCHAR2(&JOB_TYPE_NAME_SIZE) CASCADE;

ALTER TYPE MGMT_TARGET_FIXCA_RECORD
    MODIFY ATTRIBUTE job_type VARCHAR2(&JOB_TYPE_NAME_SIZE) CASCADE;

ALTER TYPE MGMT_JOB_PARAMETER_RECORD
    MODIFY ATTRIBUTE job_type VARCHAR2(&JOB_TYPE_NAME_SIZE) CASCADE;

ALTER TYPE MGMT_PARAM_CRED_TGT_MET
    MODIFY ATTRIBUTE job_type VARCHAR2(&JOB_TYPE_NAME_SIZE) CASCADE;

-- Bug 11890259, We increased the size of the define VECTOR_ARRAY_SIZE in order
--               to support more targets per job, hence we need to "upgrade"
--               these type definitions. The CASCADE option ensures all 
--               dependent types, and their constituents (e.g. tables), are 
--               adjusted as well.


ALTER TYPE MGMT_JOB_BIG_VECTOR_PARAMS 
    MODIFY LIMIT &VECTOR_ARRAY_SIZE CASCADE;


ALTER SESSION SET CURRENT_SCHEMA=SYSMAN_TYPES;

ALTER TYPE MGMT_JOB_INT_ARRAY 
    MODIFY LIMIT &VECTOR_ARRAY_SIZE CASCADE;

create or replace noneditionable synonym SYSMAN.MGMT_JOB_INT_ARRAY for MGMT_JOB_INT_ARRAY;



ALTER SESSION SET CURRENT_SCHEMA=SYSMAN;

-- Note: On rdbms 10.2.0.4, in lrgemr0s5 and lrgemr0s1, the following 
--       ALTER TYPE statement for type MGMT_JOB_VECTOR_PARAMS gives 
--       the following error:
--
--       "ORA-00604: error occurred at recursive SQL level 1 ORA-22914:
--       DROP of nested tables not supported".
--
--       The rdbms bug 4746317 for database version 10.1 (fixed in 11.1) and 
--       bug 6664743 describe the same symptom and provide a workaround
--       to purge the recylebin to avoid the issue. However, even
--       that WA did not work, hence we decided for now to not apply
--       the change for 10.* environments. 
--

-- PURGE RECYCLEBIN;
--ALTER TYPE MGMT_JOB_VECTOR_PARAMS
--    MODIFY LIMIT &VECTOR_ARRAY_SIZE CASCADE;

ALTER SESSION SET CURRENT_SCHEMA=SYSMAN_TYPES;

DECLARE
    rdbms_version VARCHAR2(100);
BEGIN
    SELECT property_value     INTO rdbms_version
    FROM database_properties
    WHERE property_name = 'NLS_RDBMS_VERSION';

    -- Don't apply the change on rdbms versions 10.*
    IF INSTR(SUBSTR(rdbms_version,1,2),'10') != 1
    THEN
      EXECUTE IMMEDIATE 'ALTER TYPE MGMT_JOB_VECTOR_PARAMS
          MODIFY LIMIT &VECTOR_ARRAY_SIZE CASCADE';
    END IF;
END;
/

create or replace noneditionable synonym SYSMAN.MGMT_JOB_VECTOR_PARAMS for MGMT_JOB_VECTOR_PARAMS;


ALTER SESSION SET CURRENT_SCHEMA=SYSMAN;




