/// <summary>
/// Gets the metadata for the given type name.
/// </summary>
/// <param name="connection"></param>
/// <param name="owner"></param>
/// <param name="name"></param>
/// <param name="log"></param>
/// <returns></returns>
public static async Task <OracleObjectType> GetObjectMetadataAsync(
OracleConnection connection,
string owner,
string name,
OracleLogger log)
{
if (connection == null)
{
throw new ArgumentNullException(nameof(connection));
}
if (string.IsNullOrWhiteSpace(owner))
{
throw new ArgumentNullException(nameof(owner));
}
if (string.IsNullOrWhiteSpace(owner))
{
throw new ArgumentNullException(nameof(name));
}
if (log == null)
{
throw new ArgumentNullException(nameof(log));
}
return(await GetObjectMetadataAsync(connection, owner + "." + name, log));
}
/// <summary>
/// Gets the metadata for the given type name.
/// </summary>
/// <param name="connection"></param>
/// <param name="typeName"></param>
/// <param name="log"></param>
/// <returns></returns>
public static async Task <OracleObjectType> GetObjectMetadataAsync(
OracleConnection connection,
string typeName,
OracleLogger log)
{
if (connection == null)
{
throw new ArgumentNullException(nameof(connection));
}
if (typeName == null)
{
throw new ArgumentNullException(nameof(typeName));
}
if (string.IsNullOrEmpty(typeName))
{
throw new ArgumentOutOfRangeException(nameof(typeName));
}
if (log == null)
{
throw new ArgumentNullException(nameof(log));
}
var metadataXml = await GetTypeMetadataXmlAsync(connection, typeName, log);
if (metadataXml == null)
{
throw new OracleExtensionsException($"Unable to locate type metadata for '{typeName}'.");
}
return(await DeserializeTypeMetadata(connection, metadataXml, log));
}
/// <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 Task <byte[]> EnqueueXmlAsync(
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));
}
throw new NotImplementedException();
}
/// <summary>
/// Dequeues the next RAW message from the specified queue.
/// </summary>
/// <param name="queue"></param>
/// <param name="options"></param>
/// <param name="logger"></param>
/// <returns></returns>
public static async Task <OracleAQMessage> DequeueRawAsync(
OracleAQQueue queue,
OracleAQDequeueOptions options,
OracleLogger logger,
CancellationToken cancellationToken)
{
throw new NotImplementedException();
}
/// <summary>
/// Dequeues the given number of XML messages.
/// </summary>
/// <param name="queue"></param>
/// <param name="options"></param>
/// <param name="count"></param>
/// <param name="logger"></param>
/// <returns></returns>
public static async Task <OracleAQMessage[]> DequeueXmlArrayAsync(
OracleAQQueue queue,
OracleAQDequeueOptions options,
int count,
OracleLogger logger,
CancellationToken cancellationToken)
{
throw new NotImplementedException();
}
/// <summary>
/// This constructor takes a queue name to initialize a queue object.
/// </summary>
/// <param name="connection"></param>
/// <param name="name"></param>
public OracleAQQueue(string name)
{
log = new OracleLogger(a => LogEvent?.Invoke(this, new OracleLogEventArgs(a)));
Name = name;
}
/// <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>
/// Deserializes type metadata into a <see cref="OracleObjectType"/>.
/// </summary>
/// <param name="connection"></param>
/// <param name="xml"></param>
/// <param name="log"></param>
/// <returns></returns>
static async Task <OracleObjectType> DeserializeTypeMetadata(
OracleConnection connection,
XDocument xml,
OracleLogger log)
{
if (connection == null)
{
throw new ArgumentNullException(nameof(connection));
}
if (xml == null)
{
throw new ArgumentNullException(nameof(xml));
}
if (log == null)
{
throw new ArgumentNullException(nameof(log));
}
// remove all empty nodes
foreach (var x in xml.Descendants().ToList())
{
if (!x.HasElements && string.IsNullOrWhiteSpace(x.Value))
{
x.Remove();
}
}
var TYPE_CODE = (string)xml.Root.Element("TYPE_CODE");
var TYPE_OWNER = (string)xml.Root.Element("TYPE_OWNER");
var TYPE_NAME = (string)xml.Root.Element("TYPE_NAME");
// plain object type
if (TYPE_CODE == "OBJECT")
{
return(new OracleObjectType(
TYPE_OWNER,
TYPE_NAME,
xml.Root.Element("ATTRIBUTES").Elements("ATTRIBUTE")
.Select(i => new OracleObjectTypeAttribute(
(string)i.Element("NAME"),
(string)i.Element("TYPE_OWNER"),
(string)i.Element("TYPE_NAME"),
OracleDbTypeNameParser.ParseDbTypeName((string)i.Element("TYPE_NAME")),
(int?)i.Element("LENGTH"),
(int?)i.Element("PRECISION"),
(int?)i.Element("SCALE")))));
}
// collection type
if (TYPE_CODE == "COLLECTION")
{
var COLL_TYPE = (string)xml.Root.Element("COLL_TYPE");
var ELEM_TYPE_OWNER = (string)xml.Root.Element("ELEM_TYPE_OWNER");
var ELEM_TYPE_NAME = (string)xml.Root.Element("ELEM_TYPE_NAME");
var ELEM_LENGTH = (int?)xml.Root.Element("ELEM_LENGTH");
var ELEM_PRECISION = (int?)xml.Root.Element("ELEM_PRECISION");
var ELEM_SCALE = (int?)xml.Root.Element("ELEM_SCALE");
var ELEM_CHARACTER_SET_NAME = (string)xml.Root.Element("ELEM_CHARACTER_SET_NAME");
if (COLL_TYPE == "VARYING ARRAY")
{
var COLL_CAPACITY = (int)xml.Root.Element("COLL_CAPACITY");
// with DbType body
if (ELEM_TYPE_OWNER == null &&
OracleDbTypeNameParser.TryParseDbTypeName(ELEM_TYPE_NAME, out OracleDbType itemDbType))
{
return(new OracleObjectType(
TYPE_OWNER,
TYPE_NAME,
OracleObjectDbType.Array,
new OracleDbTypeDef(itemDbType, ELEM_LENGTH, ELEM_PRECISION, ELEM_SCALE, ELEM_CHARACTER_SET_NAME),
null,
COLL_CAPACITY));
}
// with Object body
if (ELEM_TYPE_OWNER != null)
{
var itemObjectType = await GetObjectMetadataAsync(
connection,
ELEM_TYPE_OWNER,
ELEM_TYPE_NAME,
log);
if (itemObjectType == null)
{
throw new NullReferenceException($"Could not locate element type {ELEM_TYPE_OWNER}.{ELEM_TYPE_NAME}");
}
return(new OracleObjectType(
TYPE_OWNER,
TYPE_NAME,
itemObjectType.ObjectDbType,
null,
itemObjectType,
COLL_CAPACITY));
}
}
if (COLL_TYPE == "TABLE")
{
// with DbType body
if (ELEM_TYPE_OWNER == null &&
OracleDbTypeNameParser.TryParseDbTypeName(ELEM_TYPE_NAME, out OracleDbType itemDbType))
{
return(new OracleObjectType(
TYPE_OWNER,
TYPE_NAME,
OracleObjectDbType.Table,
new OracleDbTypeDef(itemDbType, ELEM_LENGTH, ELEM_PRECISION, ELEM_SCALE, ELEM_CHARACTER_SET_NAME),
null));
}
// with Object body
if (ELEM_TYPE_OWNER != null)
{
var itemObjectType = await GetObjectMetadataAsync(
connection,
ELEM_TYPE_OWNER,
ELEM_TYPE_NAME,
log);
if (itemObjectType == null)
{
throw new NullReferenceException($"Could not locate element type {ELEM_TYPE_OWNER}.{ELEM_TYPE_NAME}");
}
return(new OracleObjectType(
TYPE_OWNER,
TYPE_NAME,
itemObjectType.ObjectDbType,
null,
itemObjectType));
}
}
}
throw new InvalidOperationException("Could not parse object type XML.");
}
/// <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>
/// 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 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());
}
}
