/// <summary>
/// Gets the metadata XML for the given type name.
/// </summary>
/// <param name="connection"></param>
/// <param name="typeName"></param>
/// <param name="log"></param>
/// <returns></returns>
static async Task <XDocument> GetTypeMetadataXmlAsync(
OracleConnection connection,
string typeName,
OracleLogger log)
{
if (connection == null)
{
throw new ArgumentNullException(nameof(connection));
}
if (string.IsNullOrWhiteSpace(typeName))
{
throw new ArgumentException(nameof(typeName));
}
if (log == null)
{
throw new ArgumentNullException(nameof(log));
}
using (var cmd = connection.CreateCommand())
{
cmd.CommandType = CommandType.Text;
cmd.CommandText = $@"
SELECT XMLELEMENT(TYPE,
XMLELEMENT(TYPE_OWNER, ALL_TYPES.OWNER),
XMLELEMENT(TYPE_NAME, ALL_TYPES.TYPE_NAME),
XMLELEMENT(TYPE_CODE, ALL_TYPES.TYPECODE),
XMLELEMENT(ATTRIBUTES, (
SELECT XMLELEMENT(ATTRIBUTE,
XMLFOREST(
ALL_TYPE_ATTRS.ATTR_NAME AS NAME,
ALL_TYPE_ATTRS.ATTR_TYPE_OWNER AS TYPE_OWNER,
ALL_TYPE_ATTRS.ATTR_TYPE_NAME AS TYPE_NAME,
ALL_TYPE_ATTRS.ATTR_TYPE_MOD AS TYPE_MOD,
ALL_TYPE_ATTRS.LENGTH AS LENGTH,
ALL_TYPE_ATTRS.PRECISION AS PRECISION,
ALL_TYPE_ATTRS.SCALE AS SCALE,
ALL_TYPE_ATTRS.CHARACTER_SET_NAME AS CHARACTER_SET_NAME))
FROM ALL_TYPE_ATTRS
WHERE ALL_TYPE_ATTRS.OWNER = ALL_TYPES.OWNER
AND ALL_TYPE_ATTRS.TYPE_NAME = ALL_TYPES.TYPE_NAME)),
XMLELEMENT(COLL_TYPE, ALL_COLL_TYPES.COLL_TYPE),
XMLELEMENT(COLL_CAPACITY, ALL_COLL_TYPES.UPPER_BOUND),
XMLELEMENT(ELEM_TYPE_OWNER, ALL_COLL_TYPES.ELEM_TYPE_OWNER),
XMLELEMENT(ELEM_TYPE_NAME, ALL_COLL_TYPES.ELEM_TYPE_NAME),
XMLELEMENT(ELEM_TYPE_MOD, ALL_COLL_TYPES.ELEM_TYPE_MOD),
XMLELEMENT(ELEM_LENGTH, ALL_COLL_TYPES.LENGTH),
XMLELEMENT(ELEM_PRECISION, ALL_COLL_TYPES.PRECISION),
XMLELEMENT(ELEM_SCALE, ALL_COLL_TYPES.SCALE),
XMLELEMENT(ELEM_CHARACTER_SET_NAME, ALL_COLL_TYPES.CHARACTER_SET_NAME))
FROM ALL_TYPES
LEFT JOIN ALL_COLL_TYPES
ON ALL_COLL_TYPES.OWNER = ALL_TYPES.OWNER
AND ALL_COLL_TYPES.TYPE_NAME = ALL_TYPES.TYPE_NAME
WHERE ALL_TYPES.TYPE_NAME = :type_name
OR ALL_TYPES.OWNER || '.' || ALL_TYPES.TYPE_NAME = :type_name";
// collect type metadata
var typeNameParameter = cmd.CreateParameter();
typeNameParameter.ParameterName = ":type_name";
typeNameParameter.OracleDbType = OracleDbType.Varchar2;
typeNameParameter.Direction = ParameterDirection.Input;
typeNameParameter.Value = typeName;
cmd.Parameters.Add(typeNameParameter);
log.Debug(cmd.CommandText);
var typeXmlValue = (string)await cmd.ExecuteScalarAsync();
if (typeXmlValue == null)
{
return(null);
}
// deserialize XML into metadata
return(XDocument.Parse(typeXmlValue));
}
}
/// <summary>
/// Queues the given message and returns the new message ID.
/// </summary>
/// <param name="queue"></param>
/// <param name="options"></param>
/// <param name="message"></param>
/// <param name="log"></param>
/// <returns></returns>
public static async Task <byte[]> EnqueueUdtAsync(
OracleAQQueue queue,
OracleAQEnqueueOptions options,
OracleAQMessage message,
OracleLogger log,
CancellationToken cancellationToken)
{
if (queue == null)
{
throw new ArgumentNullException(nameof(queue));
}
if (options == null)
{
throw new ArgumentNullException(nameof(options));
}
if (message == null)
{
throw new ArgumentNullException(nameof(message));
}
if (log == null)
{
throw new ArgumentNullException(nameof(log));
}
// fetch UDT type information
var payloadType = await queue.GetPayloadTypeAsync();
if (payloadType == null)
{
throw new NullReferenceException("Unable to fetch message payload type.");
}
using (var cmd = queue.Connection.CreateCommand())
{
cmd.BindByName = true;
cmd.CommandType = CommandType.Text;
cmd.CommandText = $@"
DECLARE
enqueue_options DBMS_AQ.enqueue_options_t;
message_properties DBMS_AQ.message_properties_t;
message_payload {payloadType};
message_id RAW(16);
message_properties_xml XMLTYPE;
message_payload_xml XMLTYPE;
no_messages exception;
PRAGMA EXCEPTION_INIT (no_messages, -25228);
BEGIN
enqueue_options.delivery_mode := {FormatDeliveryMode(options.DeliveryMode)};
enqueue_options.visibility := {FormatVisibility(options.Visibility)};
message_properties_xml := XMLTYPE(:message_properties);
message_properties.priority := message_properties_xml.extract('/MESSAGE/PRIORITY/text()').getNumberVal();
-- message_properties.delay := message_properties_xml.extract('/MESSAGE/DELAY/text()').getNumberVal();
-- message_properties.expiration := message_properties_xml.extract('/MESSAGE/EXPIRATION/text()').getNumberVal();
message_properties.correlation := message_properties_xml.extract('/MESSAGE/CORRELATION/text()').getStringVal();
-- message_properties.exception_queue := message_properties_xml('/MESSAGE/EXCEPTION_QUEUE/text()').getStringVal();
-- message_properties.sender_id := message_properties_xml.extract('/MESSAGE/SENDER_ID/text()').getStringVal();
-- message_properties.user_property := message_properties_xml.extract('/MESSAGE/USER_PROPERTY/text()').getStringVal();
message_payload_xml := XMLTYPE(:message_payload);
message_payload_xml.toObject(message_payload);
DBMS_AQ.ENQUEUE(
queue_name => :queue_name,
enqueue_options => enqueue_options,
message_properties => message_properties,
payload => message_payload,
msgid => message_id);
:message_id := message_id;
END;";
var queueNameParameter = cmd.CreateParameter();
queueNameParameter.ParameterName = ":queue_name";
queueNameParameter.OracleDbType = OracleDbType.Varchar2;
queueNameParameter.Direction = ParameterDirection.Input;
queueNameParameter.Value = queue.Name;
cmd.Parameters.Add(queueNameParameter);
var messagePropertiesXmlParameter = cmd.CreateParameter();
messagePropertiesXmlParameter.ParameterName = ":message_properties";
messagePropertiesXmlParameter.OracleDbType = OracleDbType.Varchar2;
messagePropertiesXmlParameter.Direction = ParameterDirection.Input;
messagePropertiesXmlParameter.Value = SerializeMessageProperties(message.Properties).ToString();
cmd.Parameters.Add(messagePropertiesXmlParameter);
var messagePayloadXmlParameter = cmd.CreateParameter();
messagePayloadXmlParameter.ParameterName = ":message_payload";
messagePayloadXmlParameter.OracleDbType = OracleDbType.Varchar2;
messagePayloadXmlParameter.Direction = ParameterDirection.Input;
messagePayloadXmlParameter.Value = OracleObjectXmlTransferSerializer.Serialize((OracleObjectValue)message.Payload ?? payloadType.CreateNullValue()).ToString();
cmd.Parameters.Add(messagePayloadXmlParameter);
var messageIdParameter = cmd.CreateParameter();
messageIdParameter.ParameterName = ":message_id";
messageIdParameter.Direction = ParameterDirection.Output;
messageIdParameter.OracleDbType = OracleDbType.Raw;
messageIdParameter.Size = 16;
cmd.Parameters.Add(messageIdParameter);
log.Debug(cmd.CommandText);
await cmd.ExecuteNonQueryAsync(cancellationToken);
// return new message id
return(((OracleBinary)messageIdParameter.Value).Value);
}
}
/// <summary>
/// Dequeues the next UDT message from the specified queue.
/// </summary>
/// <param name="connection"></param>
/// <param name="queue"></param>
/// <param name="options"></param>
/// <param name="log"></param>
/// <returns></returns>
public static async Task <OracleAQMessage> DequeueUdtAsync(
OracleAQQueue queue,
OracleAQDequeueOptions options,
OracleLogger log,
CancellationToken cancellationToken)
{
if (queue == null)
{
throw new ArgumentNullException(nameof(queue));
}
if (options == null)
{
throw new ArgumentNullException(nameof(options));
}
if (log == null)
{
throw new ArgumentNullException(nameof(log));
}
// fetch UDT type information
var payloadType = await queue.GetPayloadTypeAsync();
if (payloadType == null)
{
throw new NullReferenceException("Unable to fetch message payload type.");
}
using (var cmd = queue.Connection.CreateCommand())
{
cmd.BindByName = true;
cmd.CommandType = CommandType.Text;
cmd.CommandText = $@"
DECLARE
dequeue_options DBMS_AQ.dequeue_options_t;
message_id RAW(16);
message_properties DBMS_AQ.message_properties_t;
message_payload {payloadType};
no_messages exception;
PRAGMA EXCEPTION_INIT (no_messages, -25228);
BEGIN
dequeue_options.consumer_name := :dequeue_options__consumer_name;
dequeue_options.dequeue_mode := {FormatDequeueMode(options.DequeueMode)};
dequeue_options.navigation := {FormatNavigationMode(options.NavigationMode)};
dequeue_options.visibility := {FormatVisibility(options.Visibility)};
dequeue_options.wait := {FormatWait(options.Wait)};
dequeue_options.correlation := :dequeue_options__correlation;
DBMS_AQ.DEQUEUE(
queue_name => :queue_name,
dequeue_options => dequeue_options,
message_properties => message_properties,
payload => message_payload,
msgid => message_id);
:message_count := 1;
:message_id := message_id;
SELECT XMLELEMENT(MESSAGE,
XMLELEMENT(PRIORITY, message_properties.priority),
XMLELEMENT(DELAY, message_properties.delay),
XMLELEMENT(EXPIRATION, message_properties.expiration),
XMLELEMENT(CORRELATION, message_properties.correlation),
XMLELEMENT(ATTEMPTS, message_properties.attempts),
-- XMLELEMENT(RECIPIENT_LIST, message_properties.recipient_list),
XMLELEMENT(EXCEPTION_QUEUE, message_properties.exception_queue),
XMLELEMENT(ENQUEUE_TIME, message_properties.enqueue_time),
XMLELEMENT(STATE, message_properties.state),
XMLELEMENT(SENDER_ID, message_properties.sender_id),
XMLELEMENT(ORIGINAL_MSGID, message_properties.original_msgid),
XMLELEMENT(SIGNATURE, message_properties.signature),
XMLELEMENT(TRANSACTION_GROUP, message_properties.transaction_group),
XMLELEMENT(USER_PROPERTY, message_properties.user_property),
XMLELEMENT(DELIVERY_MODE, message_properties.delivery_mode))
INTO :message_properties
FROM DUAL;
IF message_payload IS NOT NULL THEN
:message_payload := XMLTYPE(message_payload);
ELSE
:message_payload := NULL;
END IF;
EXCEPTION
WHEN no_messages
THEN :message_count := 0;
END;";
var dequeueOptionsConsumerNameParameter = cmd.CreateParameter();
dequeueOptionsConsumerNameParameter.ParameterName = ":dequeue_options__consumer_name";
dequeueOptionsConsumerNameParameter.OracleDbType = OracleDbType.Varchar2;
dequeueOptionsConsumerNameParameter.Direction = ParameterDirection.Input;
dequeueOptionsConsumerNameParameter.Value = options.ConsumerName;
cmd.Parameters.Add(dequeueOptionsConsumerNameParameter);
var dequeueOptionsCorrelationParameter = cmd.CreateParameter();
dequeueOptionsCorrelationParameter.ParameterName = ":dequeue_options__correlation";
dequeueOptionsCorrelationParameter.OracleDbType = OracleDbType.Varchar2;
dequeueOptionsCorrelationParameter.Direction = ParameterDirection.Input;
dequeueOptionsCorrelationParameter.Value = options.Correlation;
cmd.Parameters.Add(dequeueOptionsCorrelationParameter);
var queueNameParameter = cmd.CreateParameter();
queueNameParameter.ParameterName = ":queue_name";
queueNameParameter.OracleDbType = OracleDbType.Varchar2;
queueNameParameter.Direction = ParameterDirection.Input;
queueNameParameter.Value = queue.Name;
cmd.Parameters.Add(queueNameParameter);
var messageIdParameter = cmd.CreateParameter();
messageIdParameter.ParameterName = ":message_id";
messageIdParameter.Direction = ParameterDirection.Output;
messageIdParameter.Size = 16;
messageIdParameter.OracleDbType = OracleDbType.Raw;
cmd.Parameters.Add(messageIdParameter);
var messagePropertiesParameter = cmd.CreateParameter();
messagePropertiesParameter.ParameterName = ":message_properties";
messagePropertiesParameter.Direction = ParameterDirection.Output;
messagePropertiesParameter.OracleDbType = OracleDbType.XmlType;
cmd.Parameters.Add(messagePropertiesParameter);
var messagePayloadParameter = cmd.CreateParameter();
messagePayloadParameter.ParameterName = ":message_payload";
messagePayloadParameter.Direction = ParameterDirection.Output;
messagePayloadParameter.OracleDbType = OracleDbType.XmlType;
cmd.Parameters.Add(messagePayloadParameter);
var messageCountParameter = cmd.CreateParameter();
messageCountParameter.ParameterName = ":message_count";
messageCountParameter.Direction = ParameterDirection.Output;
messageCountParameter.OracleDbType = OracleDbType.Decimal;
cmd.Parameters.Add(messageCountParameter);
log.Debug(cmd.CommandText);
await cmd.ExecuteNonQueryAsync(cancellationToken);
// parses the result into the set of messages
return(((OracleDecimal)messageCountParameter.Value).ToInt32() == 1 ? ReadUdtMessage(
payloadType,
((OracleBinary)messageIdParameter.Value).Value,
((OracleXmlType)messagePropertiesParameter.Value).Value,
((OracleXmlType)messagePayloadParameter.Value).Value) : null);
}
}
/// <summary>
/// Dequeues the given number of UDT messages.
/// </summary>
/// <param name="connection"></param>
/// <param name="queue"></param>
/// <param name="options"></param>
/// <param name="log"></param>
/// <returns></returns>
public static async Task <OracleAQMessage[]> DequeueUdtArrayAsync(
OracleAQQueue queue,
OracleAQDequeueOptions options,
int count,
OracleLogger log,
CancellationToken cancellationToken)
{
if (queue == null)
{
throw new ArgumentNullException(nameof(queue));
}
if (options == null)
{
throw new ArgumentNullException(nameof(options));
}
if (log == null)
{
throw new ArgumentNullException(nameof(log));
}
// fetch UDT type information
var payloadType = await queue.GetPayloadTypeAsync();
if (payloadType == null)
{
throw new NullReferenceException("Unable to fetch message payload type.");
}
var payloadArrayType = await queue.GetPayloadArrayTypeAsync();
if (payloadArrayType == null)
{
throw new OracleAQException("Unable to find message payload array type. Required for batch sizes of more than one.");
}
using (var cmd = queue.Connection.CreateCommand())
{
cmd.BindByName = true;
cmd.CommandType = CommandType.Text;
cmd.CommandText = $@"
DECLARE
dequeue_options DBMS_AQ.DEQUEUE_OPTIONS_T;
message_id_array DBMS_AQ.MSGID_ARRAY_T;
message_properties_array DBMS_AQ.MESSAGE_PROPERTIES_ARRAY_T;
message_payload_array {payloadArrayType};
message_count PLS_INTEGER;
message_id RAW(16);
message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
message_payload {payloadType};
no_messages exception;
PRAGMA EXCEPTION_INIT (no_messages, -25228);
BEGIN
message_id_array := DBMS_AQ.MSGID_ARRAY_T();
message_properties_array := DBMS_AQ.MESSAGE_PROPERTIES_ARRAY_T();
message_payload_array := {payloadArrayType}();
dequeue_options.consumer_name := :dequeue_options__consumer_name;
dequeue_options.dequeue_mode := {FormatDequeueMode(options.DequeueMode)};
dequeue_options.navigation := {FormatNavigationMode(options.NavigationMode)};
dequeue_options.visibility := {FormatVisibility(options.Visibility)};
dequeue_options.wait := {FormatWait(options.Wait)};
dequeue_options.correlation := :dequeue_options__correlation;
message_count := DBMS_AQ.DEQUEUE_ARRAY(
queue_name => :queue_name,
dequeue_options => dequeue_options,
array_size => :count,
message_properties_array => message_properties_array,
payload_array => message_payload_array,
msgid_array => message_id_array);
-- return number of retrieved messages
:message_count := message_count;
-- copy data to output structures
FOR idx IN 1..message_count LOOP
message_id := message_id_array(idx);
message_properties := message_properties_array(idx);
message_payload := message_payload_array(idx);
:message_id(idx) := message_id;
SELECT XMLELEMENT(MESSAGE,
XMLELEMENT(PRIORITY, message_properties.priority),
XMLELEMENT(DELAY, message_properties.delay),
XMLELEMENT(EXPIRATION, message_properties.expiration),
XMLELEMENT(CORRELATION, message_properties.correlation),
XMLELEMENT(ATTEMPTS, message_properties.attempts),
-- XMLELEMENT(RECIPIENT_LIST, message_properties.recipient_list),
XMLELEMENT(EXCEPTION_QUEUE, message_properties.exception_queue),
XMLELEMENT(ENQUEUE_TIME, message_properties.enqueue_time),
XMLELEMENT(STATE, message_properties.state),
XMLELEMENT(SENDER_ID, message_properties.sender_id),
XMLELEMENT(ORIGINAL_MSGID, message_properties.original_msgid),
XMLELEMENT(SIGNATURE, message_properties.signature),
XMLELEMENT(TRANSACTION_GROUP, message_properties.transaction_group),
XMLELEMENT(USER_PROPERTY, message_properties.user_property),
XMLELEMENT(DELIVERY_MODE, message_properties.delivery_mode)).getClobVal()
INTO :message_properties(idx)
FROM DUAL;
IF message_payload IS NOT NULL THEN
:message_payload(idx) := XMLTYPE(message_payload).getClobVal();
ELSE
:message_payload(idx) := NULL;
END IF;
END LOOP;
EXCEPTION
WHEN no_messages
THEN :message_count := 0;
END;";
var dequeueOptionsConsumerNameParameter = cmd.CreateParameter();
dequeueOptionsConsumerNameParameter.ParameterName = ":dequeue_options__consumer_name";
dequeueOptionsConsumerNameParameter.OracleDbType = OracleDbType.Varchar2;
dequeueOptionsConsumerNameParameter.Direction = ParameterDirection.Input;
dequeueOptionsConsumerNameParameter.Value = options.ConsumerName;
cmd.Parameters.Add(dequeueOptionsConsumerNameParameter);
var dequeueOptionsCorrelationParameter = cmd.CreateParameter();
dequeueOptionsCorrelationParameter.ParameterName = ":dequeue_options__correlation";
dequeueOptionsCorrelationParameter.OracleDbType = OracleDbType.Varchar2;
dequeueOptionsCorrelationParameter.Direction = ParameterDirection.Input;
dequeueOptionsCorrelationParameter.Value = options.Correlation;
cmd.Parameters.Add(dequeueOptionsCorrelationParameter);
var queueNameParameter = cmd.CreateParameter();
queueNameParameter.ParameterName = ":queue_name";
queueNameParameter.OracleDbType = OracleDbType.Varchar2;
queueNameParameter.Direction = ParameterDirection.Input;
queueNameParameter.Value = queue.Name;
cmd.Parameters.Add(queueNameParameter);
var countParameter = cmd.CreateParameter();
countParameter.ParameterName = ":count";
countParameter.OracleDbType = OracleDbType.Int32;
countParameter.Direction = ParameterDirection.Input;
countParameter.Value = count;
cmd.Parameters.Add(countParameter);
var messageIdParameter = cmd.CreateParameter();
messageIdParameter.ParameterName = ":message_id";
messageIdParameter.Direction = ParameterDirection.Output;
messageIdParameter.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
messageIdParameter.Size = count;
messageIdParameter.OracleDbType = OracleDbType.Raw;
messageIdParameter.Value = new OracleBinary[count];
messageIdParameter.ArrayBindSize = Enumerable.Range(0, count).Select(i => 16).ToArray();
cmd.Parameters.Add(messageIdParameter);
var messagePropertiesParameter = cmd.CreateParameter();
messagePropertiesParameter.ParameterName = ":message_properties";
messagePropertiesParameter.Direction = ParameterDirection.Output;
messagePropertiesParameter.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
messagePropertiesParameter.Size = count;
messagePropertiesParameter.OracleDbType = OracleDbType.Varchar2;
messagePropertiesParameter.Value = new OracleString[count];
messagePropertiesParameter.ArrayBindSize = Enumerable.Range(0, count).Select(i => 1024 * 64).ToArray();
cmd.Parameters.Add(messagePropertiesParameter);
var messagePayloadParameter = cmd.CreateParameter();
messagePayloadParameter.ParameterName = ":message_payload";
messagePayloadParameter.Direction = ParameterDirection.Output;
messagePayloadParameter.CollectionType = OracleCollectionType.PLSQLAssociativeArray;
messagePayloadParameter.Size = count;
messagePayloadParameter.OracleDbType = OracleDbType.Varchar2;
messagePayloadParameter.Value = new OracleString[count];
messagePayloadParameter.ArrayBindSize = Enumerable.Range(0, count).Select(i => 1024 * 64).ToArray();
cmd.Parameters.Add(messagePayloadParameter);
var messageCountParameter = cmd.CreateParameter();
messageCountParameter.ParameterName = ":message_count";
messageCountParameter.Direction = ParameterDirection.Output;
messageCountParameter.OracleDbType = OracleDbType.Decimal;
cmd.Parameters.Add(messageCountParameter);
log.Debug(cmd.CommandText);
await cmd.ExecuteNonQueryAsync(cancellationToken);
// parses the result set into the set of messages
return(ReadUdtMessages(
payloadType,
((OracleDecimal)messageCountParameter.Value).ToInt32(),
((OracleBinary[])messageIdParameter.Value)?.Select(i => i.Value)?.ToArray() ?? Array.Empty <byte[]>(),
((OracleString[])messagePropertiesParameter.Value)?.Select(i => i.Value)?.ToArray() ?? Array.Empty <string>(),
((OracleString[])messagePayloadParameter.Value)?.Select(i => !i.IsNull ? i.Value : null)?.ToArray() ?? Array.Empty <string>())
.ToArray());
}
}