Rem drv: <create type="types"/>
Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/core/latest/collections/collections_types.sql rbali_oneoff_updated/1 2025/03/14 06:41:14 rbali Exp $
Rem
Rem collections_types.sql
Rem
Rem Copyright (c) 2002, 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/latest/collections/collections_types.sql
Rem EMRU_UNIX_DELIVERABLES:
Rem emcore/source/oracle/sysman/emdrep/sql/core/latest/collections/collections_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      collections_types.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       gbafna   02/03/25 - 
Rem       gbafna   01/07/25 - make synonyms noneditionable
Rem       amikhare 07/10/15 - The tables are renamed for EBRification of EM
Rem                           Repository in 13.1. The original table name is
Rem                           taken by an Editioning view. All your business
Rem                           logic would access the table via Editioning view.
Rem       rnadidar 02/11/15 - cut over to EBR
Rem       debasmoh 04/14/12 - bug# 13933347
Rem       bram     09/22/10 - Remove MGMT_METRIC_VALUE_ARRAY MGMT_METRIC_VALUE_OBJ
Rem    jsadras     04/28/10 - move mgmt_namevalue to sdk
Rem    jsadras     07/17/06 - Backport jsadras_bug-5002887 from main 
Rem    jsadras     02/26/06 - add metric result objects for BAM metric loading 
Rem    pmodi       09/10/05 - Bug:4593645 -Add index on mgmt_task_qtable 
Rem    gsbhatia    06/26/05 - New repmgr header impl 
Rem    jsadras     05/02/05 - add task class  and remove task type
Rem    jsadras     02/21/05 - prefix schema name for AQ (Bug:4199342)
Rem    gsbhatia    02/13/05 - updating repmgr header 
Rem    gsbhatia    02/09/05 - updating repmgr header 
Rem    gsbhatia    02/07/05 - updating repmgr header 
Rem    ktlaw       01/11/05 - add repmgr header 
Rem    jsadras     11/03/04 - remove mgmt_coll_metric_def 
Rem    jsadras     10/27/04 - on-demand
Rem    jsadras     10/20/04 - mgmt_coll_metric
Rem    jsadras     09/30/04 - max_retries
Rem    jsadras     09/01/04 - message_code
Rem    jsadras     08/27/04 - remove queue 
Rem    jsadras     08/14/04 - 
Rem    jsadras     06/28/04 - 
Rem    rpinnama    06/17/04 - Create types for new repos collection framework 
Rem    streddy     07/20/03 - Added message and static new method
Rem                           to mgmt_threshold
Rem    skini       07/12/02 - target_name column size change
Rem    rpinnama    05/15/02 - rpinnama_reorg_rep_scripts
Rem    rpinnama    05/15/02 - Created
Rem

CREATE OR REPLACE TYPE MGMT_METRIC_RESULT IS OBJECT 
( metric_column    VARCHAR2(64), 
  key_column_value VARCHAR2(256), 
  metric_value     VARCHAR2(4000) 
); 
/ 

CREATE OR REPLACE TYPE MGMT_METRIC_RESULTS IS TABLE OF MGMT_METRIC_RESULT; 
/ 

-- Use the NEW() function to create instances of MGMT_THRESHOLD object. This
-- is important to support type evolution.
CREATE OR REPLACE TYPE MGMT_THRESHOLD IS OBJECT 
( key_column_value    VARCHAR2(64), 
  metric_column       VARCHAR2(64), 
  warning_operator    NUMBER, 
  warning_threshold   VARCHAR2(256), 
  critical_operator   NUMBER, 
  critical_threshold  VARCHAR2(256), 
  num_occurrences     NUMBER,
  message             VARCHAR2(4000),
  message_nlsid       VARCHAR2(64),
  STATIC FUNCTION NEW(key_column_value    VARCHAR2,
                      metric_column       VARCHAR2, 
                      warning_operator    NUMBER, 
                      warning_threshold   VARCHAR2, 
                      critical_operator   NUMBER, 
                      critical_threshold  VARCHAR2, 
                      num_occurrences     NUMBER,
                      message             VARCHAR2 DEFAULT NULL,
                      message_nlsid       VARCHAR2 DEFAULT NULL)
   RETURN MGMT_THRESHOLD
); 
/ 

CREATE OR REPLACE TYPE MGMT_THRESHOLDS IS TABLE OF MGMT_THRESHOLD; 
/ 

CREATE OR REPLACE TYPE MGMT_COLLECTION_ID IS OBJECT
( target_name     VARCHAR2(256), 
  target_type     VARCHAR2(64),
  metric_name     VARCHAR2(64),
  collection_name VARCHAR2(64)
);
/

CREATE OR REPLACE TYPE MGMT_COLLECTION_IDS IS TABLE OF MGMT_COLLECTION_ID;
/

Rem PURPOSE
Rem mgmt_metric_value is used to hold the results of metric evaluation
Rem This will eventually replace mgmt_metric_results type
Rem Target_guid has been introduced into the object to support bulk collections
Rem COLUMNS
Rem TARGET_GUID   : Target Guid for which the metric result has been obtained
Rem STATUS        : Status of the message
Rem                 0=Success, 1=Skip 2=Error
Rem STATUS_MESSAGE: Status message
Rem METRIC_VALUES : List of (metric_column,value) pairs
Rem The function metric_value returns the value of a particular metric column
Rem The function index_position returns the index in the array of the column

--
-- PL/SQL object which has the same structure as mgmt_metrics_raw table
-- Used for bulk loading data into mgmt_metrics_raw
--
CREATE OR REPLACE TYPE mgmt_metric_raw_obj AS object
(
 target_guid          RAW(16),
 metric_guid          RAW(16),
 key_values           mgmt_medium_string_array,
 collection_timestamp DATE,
 value                NUMBER,
 string_value         VARCHAR2(4000)
) ;
/
--
-- PL/SQL object which has same structure as mgmt_metrics_raw_table
--
CREATE OR REPLACE TYPE mgmt_metric_raw_table AS TABLE OF mgmt_metric_raw_obj
/

CREATE OR REPLACE TYPE mgmt_metric_results_obj AS OBJECT
(
  key_value        mgmt_medium_string_array,
  metric_values    mgmt_namevalue_array
) ;
/                   

CREATE OR REPLACE TYPE mgmt_metric_results_table AS TABLE OF mgmt_metric_results_obj ;
/

Rem
Rem PURPOSE
Rem
Rem Used to hold Collection Tasks information in AQ
Rem
Rem COLUMNS
Rem
Rem TASK_ID       : Task_ID of the collection
Rem MESSAGE_CODE  : Message_code=0 Means Data
Rem                 Message_Code=N Where N>0 Means stop worker N
Rem SCHEDULED_TIME: Time when this task is to be run
Rem                 Time is in Repository Timezone

ALTER SESSION SET CURRENT_SCHEMA=SYSMAN_TYPES;

CREATE OR REPLACE TYPE mgmt_coll_queue_obj IS OBJECT
(  
   task_id        NUMBER,
   task_class     NUMBER,
   message_code   NUMBER,
   scheduled_time DATE, 
   STATIC FUNCTION NEW( p_task_id        IN NUMBER,
                        p_task_class     IN NUMBER,
                        p_message_code   IN NUMBER,
                        p_scheduled_time IN DATE
                       )
  RETURN mgmt_coll_queue_obj
)
/

grant execute on mgmt_coll_queue_obj to sysman;

BEGIN
    SYSMAN.CUTOVER_SYNONYM('MGMT_COLL_QUEUE_OBJ');
END;
/

ALTER SESSION SET CURRENT_SCHEMA=SYSMAN;

BEGIN
 DBMS_AQADM.CREATE_QUEUE_TABLE ( 
           queue_table=> '&EM_REPOS_USER'||'.MGMT_TASK_QTABLE', 
           sort_list=> 'PRIORITY,ENQ_TIME', 
           queue_payload_type=> '&EM_REPOS_USER'||'_TYPES.MGMT_COLL_QUEUE_OBJ') ;

 DBMS_AQADM.CREATE_QUEUE ( queue_name=> '&EM_REPOS_USER'||'.MGMT_TASK_Q', 
                           queue_table=> '&EM_REPOS_USER'||'.MGMT_TASK_QTABLE',
                           max_retries=>10,
                           retry_delay=>0,
                           retention_time=>0) ;

  DBMS_AQADM.START_QUEUE( queue_name=> '&EM_REPOS_USER'||'.MGMT_TASK_Q');
  COMMIT ;

END ;
/
--
--This is to speed up collections which queries on 
--collection time
-- Index is created here because the table is created 
-- when queue is created which is after type is created
--
CREATE INDEX MGMT_TASK_QTABLE_IDX01
ON mgmt_task_qtable(user_data.scheduled_time,user_data.task_class) ;

CREATE INDEX MGMT_TASK_QTABLE_IDX02
ON mgmt_task_qtable(user_data.task_id) ;

Rem PURPOSE
Rem Used to hold Collection scheduling information
Rem Column Values mean the same as in mgmt_blackout_schedule_record
Rem
Rem Use the NEW() function to create instances of MGMT_COLL_SCHEDULE object. This
Rem is important to support type evolution.

CREATE OR REPLACE TYPE mgmt_coll_schedule_obj IS OBJECT
(   
    frequency_code     NUMBER,
    start_time         DATE,
    end_time           DATE,
    execution_hours    NUMBER,
    execution_minutes  NUMBER,
    interval           NUMBER,  
    months             MGMT_INTEGER_ARRAY,
    days               MGMT_INTEGER_ARRAY,
   STATIC FUNCTION NEW(p_frequency_code     IN NUMBER,
                       p_execution_hours    IN NUMBER,
                       p_execution_minutes  IN NUMBER,
                       p_interval           IN NUMBER,
                       p_months             IN mgmt_integer_array,
                       p_days               IN mgmt_integer_array,
                       p_start_time         IN DATE,
                       p_end_time           IN DATE
                      )
  RETURN mgmt_coll_schedule_obj,
  STATIC FUNCTION one_time(p_start_time IN DATE)
  RETURN mgmt_coll_schedule_obj,
  STATIC FUNCTION interval_schedule(p_interval   IN NUMBER,
                                    p_start_time IN DATE,
                                    p_end_time   IN DATE)
  RETURN mgmt_coll_schedule_obj,
  STATIC FUNCTION daily_schedule(p_execution_hours   IN NUMBER,
                                 p_execution_minutes IN NUMBER,
                                 p_start_time        IN DATE,
                                 p_end_time          IN DATE)
  RETURN mgmt_coll_schedule_obj,
  STATIC FUNCTION days_of_week(p_execution_hours   IN NUMBER,
                               p_execution_minutes IN NUMBER,
                               p_days              IN  mgmt_integer_array,
                               p_start_time        IN DATE,
                               p_end_time          IN DATE)
  RETURN mgmt_coll_schedule_obj,
  STATIC FUNCTION days_of_month(p_execution_hours  IN NUMBER,
                               p_execution_minutes IN NUMBER,
                               p_days              IN mgmt_integer_array,
                               p_start_time        IN DATE,
                               p_end_time          IN DATE)
  RETURN mgmt_coll_schedule_obj,
  STATIC FUNCTION days_of_year(p_execution_hours   IN NUMBER,
                               p_execution_minutes IN NUMBER,
                               p_days              IN mgmt_integer_array,
                               p_months            IN mgmt_integer_array,
                               p_start_time        IN DATE,
                               p_end_time          IN DATE)
  RETURN mgmt_coll_schedule_obj,
  STATIC FUNCTION on_demand_schedule
  RETURN mgmt_coll_schedule_obj
);
/

Rem PURPOSE
Rem Used to hold a name array pair for a metric item properties
Rem COLUMNS
Rem NAME     : Name of the metric column
Rem nv_array : ARRAY OF TYPE mgmt_namevalue_array

CREATE OR REPLACE TYPE mgmt_namearray_obj IS OBJECT
(
   name       VARCHAR2(64),
   nv_array   mgmt_namevalue_array, 
   STATIC FUNCTION NEW(p_name  IN VARCHAR2,
                       p_nv_array IN mgmt_namevalue_array 
                      )
  RETURN mgmt_namearray_obj
);
/

CREATE OR REPLACE TYPE mgmt_namearray_array as TABLE of mgmt_namearray_obj;
/

@&EM_SQL_ROOT/core/11.2.0.1/collections/collections_types.sql
@&EM_SQL_ROOT/core/12.1.0.2/collections/collections_types.sql

