Rem
Rem $Header: emcore/source/oracle/sysman/emdrep/sql/connector/11.2.0.1/connector_types.sql rbali_oneoff_updated/1 2025/03/14 06:41:12 rbali Exp $
Rem
Rem connector_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/connector/11.2.0.1/connector_types.sql
Rem EMRU_UNIX_DELIVERABLES:
Rem emcore/source/oracle/sysman/emdrep/sql/connector/11.2.0.1/connector_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      connector_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      01/07/25 - make synonyms noneditionable
Rem    rnadidar    02/11/15 - cut over to EBR
Rem    yangwang    04/12/10 - Created
Rem

Rem ==========================
Rem Create connector queue
Rem ==========================


ALTER SESSION SET CURRENT_SCHEMA=SYSMAN_TYPES;

-- Create message payload object for the AQ
CREATE OR REPLACE TYPE EM_CNTR_REQUEST_MSG AS OBJECT
(
    instance_id VARCHAR(64),
    request_type NUMBER(2),         -- TC_OUTBOUND, EC_OUTBOUND, CCC_OUTBOUND, CCC_INBOUND
    connector_guid RAW(16),
    request_occur_ts DATE,
    request_seq INT,
    second_id VARCHAR(64),
    message CLOB, 
    error_message VARCHAR(256),
    isannotated NUMBER(1),
    CONSTRUCTOR FUNCTION EM_CNTR_REQUEST_MSG
    (
        p_instance_id IN VARCHAR,
        p_request_type IN NUMBER,         -- TC_OUTBOUND, EC_OUTBOUND, CCC_OUTBOUND, CCC_INBOUND
        p_connector_guid IN RAW,
        p_request_occur_ts IN DATE,
        p_request_seq INT,
        p_second_id VARCHAR,
        p_message IN CLOB,
        p_error_message IN VARCHAR,
        p_isannotated IN VARCHAR
    )
    RETURN  SELF AS RESULT,
    MEMBER FUNCTION GET_STRING RETURN VARCHAR2
);
/

grant execute on EM_CNTR_REQUEST_MSG to sysman;

create or replace noneditionable synonym SYSMAN.EM_CNTR_REQUEST_MSG for EM_CNTR_REQUEST_MSG;



ALTER SESSION SET CURRENT_SCHEMA=SYSMAN;

BEGIN
    DBMS_AQADM.CREATE_QUEUE_TABLE (queue_table => '&EM_REPOS_USER'||'.EM_CNTR_QTABLE',
                              queue_payload_type => '&EM_REPOS_USER'||'_TYPES.EM_CNTR_REQUEST_MSG',
                              multiple_consumers => TRUE);
    DBMS_AQADM.CREATE_QUEUE (queue_name => '&EM_REPOS_USER'||'.EM_CNTR_QUEUE',
                           queue_table => '&EM_REPOS_USER'||'.EM_CNTR_QTABLE');
    DBMS_AQADM.START_QUEUE (queue_name => '&EM_REPOS_USER'||'.EM_CNTR_QUEUE');

END;
/
/**
BEGIN
    DBMS_AQADM.STOP_QUEUE (queue_name => 'SYSMAN'||'.EM_CNTR_QUEUE');
    DBMS_AQADM.DROP_QUEUE (queue_name => 'SYSMAN'||'.EM_CNTR_QUEUE');
    DBMS_AQADM.DROP_QUEUE_TABLE (queue_table => 'SYSMAN'||'.EM_CNTR_QTABLE');
END;
/
**/

