Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/12.2.0.0/jobs/jobs_types.sql rbali_oneoff_updated/1 2025/03/14 06:41:13 rbali Exp $
Rem
Rem jobs_types.sql
Rem
Rem Copyright (c) 2012, 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/12.2.0.0/jobs/jobs_types.sql
Rem EMRU_UNIX_DELIVERABLES:
Rem emcore/source/oracle/sysman/emdrep/sql/core/12.2.0.0/jobs/jobs_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_types.sql - Jobs Types SQL file 
Rem
Rem    DESCRIPTION
Rem      New jobs types for EM13
Rem
Rem    NOTES
Rem      none at this time, see inline.
Rem
Rem    MODIFIED   (MM/DD/YY)
Rem    gbafna      01/07/25 - make synonyms noneditionable
Rem    rnadidar    02/11/15 - cut over to EBR
Rem    rdabbott    09/25/14 - remove MGMT_JOB_RUN_ID_LIST, use
Rem                           MGMT_JOB_GUID_ARRAY instead
Rem    orazoma     08/22/14 - Bug19483646 Increase
Rem                           MGMT_JOB_DNAME_TYPE_PROP_REC.TARGET_NAME size
Rem    bboggava    04/04/14 - EMJB-150 create custom pl/sql type for 
Rem                           job status change messages.
Rem    jnordby     11/01/12 - Review comments.
Rem    jnordby     10/09/12 - File for EM13 JS Types
Rem    jnordby     10/09/12 - Created
Rem

--type used in restart_job_run
CREATE OR REPLACE TYPE MGMT_JOB_STATUSQ_RECORD AS OBJECT
(
    job_run_id   NUMBER
);
/

-- type used for JOBS with recent status changes.
CREATE OR REPLACE TYPE MGMT_JOB_STATUSQ_OBJECTS AS TABLE OF MGMT_JOB_STATUSQ_RECORD;
/ 


alter session set current_schema = SYSMAN_TYPES;

/**
 * Custom pl/sql type for Job Status message queue payload
 *
 */
CREATE OR REPLACE TYPE EM_JOB_STATUS_UPD_RECORD AS OBJECT
(
    -- Type of status message (Execution Status, step status etc.)
    MESSAGE_TYPE       NUMBER(2), 
    -- Internal book-keeping fields
    STATUS_UPDATED_TIME DATE,
    -- Actual message entries.
    JOB_ID             &GUID_TYPE,
    EXECUTION_ID       &GUID_TYPE,
    STEP_ID            NUMBER(38), 
    STATUS             NUMBER(38)
);
/

grant execute on EM_JOB_STATUS_UPD_RECORD to sysman with grant option;

create or replace noneditionable synonym SYSMAN.EM_JOB_STATUS_UPD_RECORD for EM_JOB_STATUS_UPD_RECORD;


alter session set current_schema = SYSMAN;

CREATE OR REPLACE TYPE EM_JOB_STATUS_UPD_LIST AS TABLE OF EM_JOB_STATUS_UPD_RECORD;
/

-- Bug 19483646: MGMT_JOB_DNAME_TYPE_PROP_REC.TARGET_NAME should match entity_name length from EM_MANAGEABLE_ENTITIES
DECLARE
  l_length NUMBER;
  l_type_sql VARCHAR2(32767);
BEGIN
  SELECT LENGTH
    INTO l_length
    FROM ALL_TYPE_ATTRS
   WHERE TYPE_NAME = 'MGMT_JOB_DNAME_TYPE_PROP_REC'
     AND ATTR_NAME = 'TARGET_NAME';
     
  IF (l_length < 256) THEN
	EXECUTE IMMEDIATE 'DROP TYPE MGMT_JOB_DNAME_TYPE_PROP_REC FORCE';
    --EXECUTE IMMEDIATE 'ALTER TYPE MGMT_JOB_DNAME_TYPE_PROP_REC MODIFY ATTRIBUTE TARGET_NAME VARCHAR2(256) CASCADE';
	l_type_sql := q'[CREATE OR REPLACE TYPE MGMT_JOB_DNAME_TYPE_PROP_REC AS OBJECT
	(
	 target_display_name VARCHAR2(256),
	 target_name VARCHAR2(256),
	 target_type VARCHAR2(64),
	 propName VARCHAR2(64)
	)]';
	EXECUTE IMMEDIATE l_type_sql;
  END IF;
END;
/

