使用 .Net 的 Oracle 高级排队
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1265750/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Oracle Advanced Queueing with .Net
提问by Geo
Somebody knows how to implement Oracle Advance Queue from C# using PL/SSQL and ODP.NET? I can't find a single example or resource with concrete examples in C# or VB.NET. Ideally I would like some examples on how the enqueue and dequeue messages with simple types (XMl/string).
有人知道如何使用 PL/SSQL 和 ODP.NET 从 C# 实现 Oracle Advance Queue？我在 C# 或 VB.NET 中找不到包含具体示例的单个示例或资源。理想情况下，我想要一些关于如何使用简单类型（XMl/string）使消息入队和出队的示例。
回答by Michael
I can't help you with the best practices, but I can help you with a UDT Queue. Before you deal with the queue, you need to generate custom types from the database into your C# project. Assuming you have Visual Studio and ODP.NET installed, you simply need to connect to the database through the Server Explorer, locate your UDTs, right click and choose "Generate Custom Class..." These classes map directly to your UDTs and are used to store the Dequeued information.
我无法在最佳实践方面为您提供帮助，但我可以在 UDT 队列方面为您提供帮助。在处理队列之前，您需要从数据库生成自定义类型到您的 C# 项目中。假设您安装了 Visual Studio 和 ODP.NET，您只需通过服务器资源管理器连接到数据库，找到您的 UDT，右键单击并选择“生成自定义类...”这些类直接映射到您的 UDT 并使用存储出队信息。
Here is an example of the code you would use to enqueue a message:
以下是用于将消息入队的代码示例：
private void main(string[] args)
{
string _connstring = "Data Source=host/DB;User
Id=USER;Password=PASSWORD1;";
OracleConnection _connObj = new OracleConnection(_connstring);
// Create a new queue object
OracleAQQueue _queueObj = new OracleAQQueue("UDT_NAME", _connObj);
_connObj.Open();
OracleTransaction _txn = _connObj.BeginTransaction();
// Set the payload type to your UDT
_queueObj.MessageType = OracleAQMessageType.Udt;
_queueObj.UdtTypeName = "UDT_NAME";
// Create a new message object
OracleAQMessage _msg = new OracleAQMessage();
// Create an instance of JobClass and pass it in as the payload for the
// message
UDT_CUSTOM_CLASS _custClass = new UDT_CUSTOM_CLASS();
// Load up all of the properties of custClass
custClass.CustString = "Custom String";
custClass.CustInt = 5;
_msg.Payload = custClass;
// Enqueue the message
_queueObj.EnqueueOptions.Visibility = OracleAQVisibilityMode.OnCommit;
_queueObj.Enqueue(_msg);
_txn.Commit();
_queueObj.Dispose();
_connObj.Close();
_connObj.Dispose();
_connObj = null;
}
It's a similar process to dequeue:
出队的过程类似：
private void main(string[] args)
{
string _connstring = "Data Source=host/DB;User
Id=USER;Password=PASSWORD1;";
OracleConnection _connObj = new OracleConnection(_connstring);
// Create a new queue object
OracleAQQueue _queueObj = new OracleAQQueue("UDT_NAME", _connObj);
// Set the payload type to your UDT
_queueObj.MessageType = OracleAQMessageType.Udt;
_queueObj.UdtTypeName = "UDT_NAME";
_connObj.Open();
OracleTransaction _txn = _connObj.BeginTransaction();
// Dequeue the message.
_queueObj.DequeueOptions.Visibility = OracleAQVisibilityMode.OnCommit;
_queueObj.DequeueOptions.Wait = 10;
OracleAQMessage _deqMsg = _queueObj.Dequeue();
UDT_CUSTOM_CLASS data = (UDT_CUSTOM_CLASS)_deqMsg.Payload;
// At this point, you have the data and can do whatever you need to do with it
_txn.Commit();
_queueObj.Dispose();
_connObj.Close();
_connObj.Dispose();
_connObj = null;
}
That's a "simple" example. I pulled most of that out of Pro ODP.NET for Oracle Database 11g by Ed Zehoo. It's an excellent book and I strongly recommend it to help you gain a better understanding of the ins and outs of all things OPD.NET. You can buy the eBook here: http://apress.com/book/view/9781430228202. If you enter the coupon code MACWORLDOC, you can get the eBook for $21.00. That offer is only good for the eBook which comes in a password protected PDF format. I hope this helps!
这是一个“简单”的例子。我从 Ed Zehoo 的 Pro ODP.NET for Oracle Database 11g 中提取了大部分内容。这是一本优秀的书，我强烈推荐它来帮助您更好地了解 OPD.NET 的来龙去脉。您可以在这里购买电子书：http: //apress.com/book/view/9781430228202。如果您输入优惠券代码 MACWORLDOC，您可以获得 21.00 美元的电子书。该优惠仅适用于受密码保护的 PDF 格式的电子书。我希望这有帮助！
回答by Geo
I don't know the exact answer to this problem but here is what we did:
我不知道这个问题的确切答案，但这是我们所做的：
- First every .net application that need to listen on the ESB (ESB is build on AQ) has to use his own local Oracle DB and dequeue messages from there. The messages are propagated to the local queues. This solves the potential scalability problem linked to keeping a DB connection open to recieve messages.
- Second we built our own AQ library that basicly encapsulate stored procedures. - this is not needed any more as Oracle has finaly released an the ODAC 11.1.0.7.20(with an ODP.NET that supports AQ). We use Oracle types as sort of DTO to define the message contracts.
- 首先，每个需要侦听 ESB（ESB 构建在 AQ 上）的 .net 应用程序都必须使用他自己的本地 Oracle DB 并从那里出列消息。消息被传播到本地队列。这解决了与保持数据库连接打开以接收消息相关的潜在可扩展性问题。
- 其次，我们构建了自己的 AQ 库，该库基本上封装了存储过程。- 这不再需要了，因为 Oracle 最终发布了 ODAC 11.1.0.7.20（带有支持 AQ 的 ODP.NET）。我们使用 Oracle 类型作为一种 DTO 来定义消息协定。
回答by Vishal A
I had a requirement where I had to enqueue/dequeue UDT messages to a queue. This post was really helpful. It has almost everything but creation of a "Oracle Custom Type" is missing. I thought its worth adding that code here so that the solution is complete.
我有一个要求，我必须将 UDT 消息入队/出队到队列中。这篇文章真的很有帮助。它几乎拥有所有东西，但缺少“Oracle 自定义类型”的创建。我认为值得在此处添加该代码，以便解决方案完整。
To EnQueue/DeQueue in Oracle:
在 Oracle 中入队/出队：
User with role "AQ_ADMINISTRATOR_ROLE" has to be created. In the example below, the "AQUSER" is created with that role.
必须创建具有“AQ_ADMINISTRATOR_ROLE”角色的用户。在下面的示例中，“AQUSER”是用该角色创建的。
PL Sql to EnQueue:
DECLARE
queue_options DBMS_AQ.ENQUEUE_OPTIONS_T;
message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
message_id RAW(16);
my_message AQUSER.USER_DEFINED_TYPE;
BEGIN
my_message := AQUSER.USER_DEFINED_TYPE('XXX','YYY','ZZZ');
DBMS_AQ.ENQUEUE(
queue_name => 'AQUSER.QUEUE_NAME',
enqueue_options => queue_options,
message_properties => message_properties,
payload => my_message,
msgid => message_id);
COMMIT;
END;
/
PL SQL to DeQueue
DECLARE
queue_options DBMS_AQ.DEQUEUE_OPTIONS_T;
message_properties DBMS_AQ.MESSAGE_PROPERTIES_T;
message_id RAW(2000);
my_message AQUSER.USER_DEFINED_TYPE;
BEGIN
DBMS_AQ.DEQUEUE(
queue_name => 'AQUSER.QUEUE_NAME',
dequeue_options => queue_options,
message_properties => message_properties,
payload => my_message,
msgid => message_id );
COMMIT;
END;
/
-------------------------------------------------------------------------------------------
To create a Oracle Custom Type, you can use the following code:
public class CustomMessageType : IOracleCustomType, INullable
{
[OracleObjectMappingAttribute("XXXXX")]
public string XXXXX { get; set; }
[OracleObjectMappingAttribute("YYYYY")]
public string YYYYY { get; set; }
[OracleObjectMappingAttribute("ZZZZZ")]
public string ZZZZZ { get; set; }
public void FromCustomObject(Oracle.DataAccess.Client.OracleConnection con, IntPtr pUdt)
{
if (!string.IsNullOrEmpty(XXXXX))
{
OracleUdt.SetValue(con, pUdt, "XXXXX", XXXXX);
}
if (!string.IsNullOrEmpty(YYYYY))
{
OracleUdt.SetValue(con, pUdt, "YYYYY", YYYYY);
}
if (!string.IsNullOrEmpty(ZZZZZ))
{
OracleUdt.SetValue(con, pUdt, "ZZZZZ", ZZZZZ);
}
}
public void ToCustomObject(Oracle.DataAccess.Client.OracleConnection con, IntPtr pUdt)
{
XXXXX = (string)OracleUdt.GetValue(con, pUdt, "XXXXX");
YYYYY = (string)OracleUdt.GetValue(con, pUdt, "YYYYY");
ZZZZZ = (string)OracleUdt.GetValue(con, pUdt, "ZZZZZ");
}
public bool IsNull { get; set; }
}
[OracleCustomTypeMappingAttribute("SCHEMA.CUSTOM_TYPE")]
public class QueueMessageTypeFactory : IOracleCustomTypeFactory
{
public IOracleCustomType CreateObject()
{
return new CustomMessageType();
}
}
回答by jbrook10
Here is a nice example I found from Oracle.
这是我从 Oracle 找到的一个很好的例子。
http://www.oracle.com/technetwork/issue-archive/2009/09-nov/o69net-099734.html
http://www.oracle.com/technetwork/issue-archive/2009/09-nov/o69net-099734.html
Code: http://www.oracle.com/technetwork/issue-archive/2009/09-nov/o69odt-131369.zip
代码：http: //www.oracle.com/technetwork/issue-archive/2009/09-nov/o69odt-131369.zip
回答by Michal Pravda
AQ has plsql interface via DBMS_AQ[adm]. All you need is run that packages from your environment and common AQ examples and setup. I don't think that there is anything special when you call those packages from c#.
AQ 通过 DBMS_AQ[adm] 具有 plsql 接口。您所需要的只是从您的环境和常见的 AQ 示例和设置中运行这些包。当您从 c# 调用这些包时，我认为没有什么特别之处。
