/// <summary> /// Creates the necessary table /// </summary> public void EnsureQueueIsCreated(OracleAQInitializationOptions options) { try { CreateSchema(options); } catch (Exception exception) { throw new RebusApplicationException(exception, $"Error attempting to initialize SQL transport schema with mesages table [dbo].[{options.TableName}]"); } }
void CreateSchema(OracleAQInitializationOptions options) { using (var connection = _connectionHelper.GetConnection()) { var tableNames = connection.GetTableNames(); if (tableNames.Contains(options.TableName, StringComparer.OrdinalIgnoreCase)) { _log.Info("Database already contains a table named {tableName} - will not create anything", options.TableName); return; } _log.Info("Table {tableName} does not exist - it will be created with the queue now", options.TableName); ExecuteCommands(connection, $@" DECLARE v_count NUMBER(10); BEGIN SELECT COUNT(*) INTO v_count FROM user_objects WHERE object_type = 'TYPE' AND UPPER(object_name) = 'REBUS_MESSAGE_V2'; IF v_count = 0 THEN EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE REBUS_MSG_HEADER AS OBJECT ( KEY VARCHAR2(4000), VALUE VARCHAR2(4000) );'; EXECUTE IMMEDIATE 'CREATE OR REPLACE TYPE REBUS_MSG_HEADERS IS VARRAY(100) OF REBUS_MSG_HEADER NOT NULL;'; EXECUTE IMMEDIATE 'create or replace type REBUS_MESSAGE_V2 as object ( HEADERS REBUS_MSG_HEADERS, BODY_RAW RAW(2000), BODY_BLOB BLOB, MEMBER FUNCTION ContainsHeader(p_header_key VARCHAR2) RETURN NUMBER, MEMBER FUNCTION GetHeaderValue(p_header_key VARCHAR2)RETURN VARCHAR2 );'; EXECUTE IMMEDIATE 'create or replace type body REBUS_MESSAGE_V2 is -- Member procedures and functions MEMBER FUNCTION ContainsHeader(p_header_key VARCHAR2) RETURN NUMBER IS BEGIN FOR i IN 1..HEADERS.Count LOOP BEGIN IF HEADERS(i).KEY = p_header_key THEN RETURN 1; END IF; END; END LOOP; RETURN 0; END; MEMBER FUNCTION GetHeaderValue(p_header_key VARCHAR2)RETURN VARCHAR2 IS BEGIN FOR i IN 1..HEADERS.Count LOOP BEGIN IF HEADERS(i).KEY = p_header_key THEN RETURN HEADERS(i).VALUE; END IF; END; END LOOP; RETURN NULL; END; end;'; EXECUTE IMMEDIATE 'create or replace package PKG_REBUS is TYPE VARCHAR2_ARRAY IS TABLE OF VARCHAR2(4000) -- Associative array type INDEX BY PLS_INTEGER; PROCEDURE P_Enqueue_Rebus_Msg ( p_Queue_Name IN VARCHAR2, p_EO_Visibility IN BINARY_INTEGER, p_EO_Transformation IN VARCHAR2, p_EO_Delivery_Mode IN PLS_INTEGER, p_MP_Priority IN BINARY_INTEGER, p_MP_Delay IN BINARY_INTEGER, p_MP_Expiration IN BINARY_INTEGER, p_Headers_Keys IN VARCHAR2_ARRAY, p_Headers_Values IN VARCHAR2_ARRAY, p_Body_Raw IN RAW, p_Body_Blob IN BLOB ); PROCEDURE P_Dequeue_Rebus_Msg ( p_Queue_Name IN VARCHAR2, p_DO_Consumers_Names IN VARCHAR2_ARRAY, p_DO_Visibility IN BINARY_INTEGER, p_DO_Wait IN BINARY_INTEGER, p_DO_Transformation IN VARCHAR2, p_DO_Delivery_Mode IN PLS_INTEGER, p_Headers_Keys OUT VARCHAR2_ARRAY, p_Headers_Values OUT VARCHAR2_ARRAY, p_Body_Raw OUT RAW, p_Body_Blob OUT BLOB ); end PKG_REBUS; '; EXECUTE IMMEDIATE 'create or replace package body PKG_REBUS is PROCEDURE P_Enqueue_Rebus_Msg ( p_Queue_Name IN VARCHAR2, p_EO_Visibility IN BINARY_INTEGER, p_EO_Transformation IN VARCHAR2, p_EO_Delivery_Mode IN PLS_INTEGER, p_MP_Priority IN BINARY_INTEGER, p_MP_Delay IN BINARY_INTEGER, p_MP_Expiration IN BINARY_INTEGER, p_Headers_Keys IN VARCHAR2_ARRAY, p_Headers_Values IN VARCHAR2_ARRAY, p_Body_Raw IN RAW, p_Body_Blob IN BLOB ) IS v_Enqueue_Options dbms_aq.enqueue_options_t; v_Message_Properties dbms_aq.message_properties_t; v_message_handle RAW(16); v_headers rebus_msg_headers; BEGIN v_Enqueue_Options.visibility := p_EO_Visibility; v_Enqueue_Options.delivery_mode := p_EO_Delivery_Mode; v_Enqueue_Options.transformation := p_EO_Transformation; v_Message_Properties.priority := p_MP_Priority; v_Message_Properties.delay := p_MP_Delay; v_Message_Properties.expiration := p_MP_Expiration; v_headers := rebus_msg_headers(); v_headers.extend(p_Headers_Keys.count); FOR i IN 1..p_Headers_Keys.count LOOP v_headers(i) := rebus_msg_header(p_Headers_Keys(i), p_Headers_Values(i)); END LOOP; DBMS_AQ.ENQUEUE( QUEUE_NAME => p_Queue_Name, ENQUEUE_OPTIONS => v_Enqueue_Options, MESSAGE_PROPERTIES => v_Message_Properties, PAYLOAD => new REBUS_MESSAGE_V2(v_headers, p_Body_Raw, p_Body_Blob), MSGID => v_message_handle); END P_Enqueue_Rebus_Msg; PROCEDURE P_Dequeue_Rebus_Msg ( p_Queue_Name IN VARCHAR2, p_DO_Consumers_Names IN VARCHAR2_ARRAY, p_DO_Visibility IN BINARY_INTEGER, p_DO_Wait IN BINARY_INTEGER, p_DO_Transformation IN VARCHAR2, p_DO_Delivery_Mode IN PLS_INTEGER, p_Headers_Keys OUT VARCHAR2_ARRAY, p_Headers_Values OUT VARCHAR2_ARRAY, p_Body_Raw OUT RAW, p_Body_Blob OUT BLOB ) IS v_Dequeue_Options dbms_aq.DEQUEUE_OPTIONS_T; v_Message_Properties dbms_aq.message_properties_t; v_Message_Handle RAW(16); v_Payload REBUS_MESSAGE_V2; v_Agent_List DBMS_AQ.AQ$_AGENT_LIST_T; v_Agent SYS.AQ$_AGENT; BEGIN v_Dequeue_Options.visibility := p_DO_Visibility; v_Dequeue_Options.delivery_mode := p_DO_Delivery_Mode; v_Dequeue_Options.transformation := p_DO_Transformation; v_Dequeue_Options.WAIT := p_DO_Wait; v_Dequeue_Options.NAVIGATION := DBMS_AQ.FIRST_MESSAGE; IF p_DO_Consumers_Names IS NULL OR p_DO_Consumers_Names.count = 0 OR p_DO_Consumers_Names(1) IS NULL THEN DBMS_AQ.DEQUEUE( QUEUE_NAME => p_Queue_Name, DEQUEUE_OPTIONS => v_Dequeue_Options, MESSAGE_PROPERTIES => v_Message_Properties, PAYLOAD => v_Payload, MSGID => v_Message_Handle); FOR i IN 1..v_Payload.HEADERS.count LOOP p_Headers_Keys(i) := v_Payload.HEADERS(i).KEY; p_Headers_Values(i) := v_Payload.HEADERS(i).VALUE; END LOOP; p_Body_Raw := v_Payload.BODY_RAW; p_Body_Blob := v_Payload.BODY_BLOB; ELSE FOR i IN 1..p_DO_Consumers_Names.count LOOP v_Agent_List(i) := SYS.AQ$_AGENT(p_DO_Consumers_Names(i), p_Queue_Name, NULL); END LOOP; DBMS_AQ.LISTEN ( agent_list => v_Agent_List, wait => p_DO_Wait, agent => v_Agent); IF v_Agent IS NOT NULL THEN v_Dequeue_Options.CONSUMER_NAME := v_Agent.name; DBMS_AQ.DEQUEUE( QUEUE_NAME => p_Queue_Name, DEQUEUE_OPTIONS => v_Dequeue_Options, MESSAGE_PROPERTIES => v_Message_Properties, PAYLOAD => v_Payload, MSGID => v_Message_Handle); FOR i IN 1..v_Payload.HEADERS.count LOOP p_Headers_Keys(i) := v_Payload.HEADERS(i).KEY; p_Headers_Values(i) := v_Payload.HEADERS(i).VALUE; END LOOP; p_Headers_Keys(v_Payload.HEADERS.count + 1) := ''rbs2-consumer-name''; p_Headers_Values(v_Payload.HEADERS.count + 1) := v_Agent.name; p_Body_Raw := v_Payload.BODY_RAW; p_Body_Blob := v_Payload.BODY_BLOB; END IF; END IF; EXCEPTION WHEN OTHERS THEN IF SQLCODE = -25228 --ORA-25228 Timeout or End-of-Fetch Error When Dequeuing Messages OR SQLCODE = -25254 --ORA-25254: time-out in LISTEN while waiting for a message THEN p_Body_Raw := NULL; p_Body_Blob := NULL; ELSE RAISE; END IF; END P_Dequeue_Rebus_Msg; end PKG_REBUS;'; END IF; END; ---- BEGIN -- Call the procedure sys.dbms_aqadm.create_queue_table(queue_table => '{options.TableName}', queue_payload_type => 'REBUS_MESSAGE_V2', primary_instance => 1); sys.dbms_aqadm.create_queue(queue_name => '{options.InputQueueName}', queue_table => '{options.TableName}', max_retries => 10, retry_delay => 3); sys.dbms_aqadm.start_queue(queue_name => '{options.InputQueueName}'); END; "); connection.Complete(); } }