Oracle Advanced Queuing with .Net

Question

Does anyone know how to implement Oracle Advance Queue from C # using PL / SSQL and ODP.NET? I can not find a single example or resource with specific examples in C # or VB.NET. Ideally, I would like some examples of how enqueue and dequeue messages have simple types (XMl / string).

Answer 1

I cannot help you with the best practices, but I can help you with the UDT lineup. Before accessing the queue, you need to create custom types from the database in your C # project. Assuming you have Visual Studio and ODP.NET installed, you just need to connect to the database through Server Explorer, find your UDTs, right-click and select "Create a custom class ...". These classes are mapped directly to your UDTs and are used to store allocation information.

Here is an example of the code you would use to post 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; } 

This is a similar process for 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; } 

This is a simple example. I pulled most of this from Pro ODP.NET for Oracle Database 11g from Ed Zehoo. This is a great book, and I highly recommend it to help you better understand all aspects of OPD.NET. You can buy the e-book here: http://apress.com/book/view/9781430228202 . If you enter the MACWORLDOC coupon code, you can get the book for $ 21.00. This offer is only suitable for a book that comes in a password protected PDF format. Hope this helps!

Answer 2

I do not know the exact answer to this problem, but here is what we did:

• First, each .net application that needs to listen on the ESB (ESB is built on AQ) must use its own local Oracle database and discard messages from it. Messages are distributed to local queues. This solves the potential scalability problem with having a DB connection open to receive messages.
• Secondly, we created our own AQ library, which basically encapsulates stored procedures. - this is no longer necessary, since Oracle finally released ODAC 11.1.0.7.20 (with ODP.NET supporting AQ). We use Oracle types as a kind of DTO to define message contracts.

Answer 3

Here is a good example that I found from Oracle.

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

Answer 4

I had a requirement when I had to enter / deactivate UDT messages in a queue. This post was really helpful. He has almost everything, but the creation of the "Oracle Custom Type" is missing. I thought it was worth adding this code here so that the solution is complete.

In EnQueue / DeQueue in Oracle:

A user with the role "AQ_ADMINISTRATOR_ROLE" must be created. The example below creates "AQUSER" with this role.

 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(); } } 

Answer 5

AQ has a plsql interface through DBMS_AQ [adm]. All you need to do is run packages from your environment and general AQ examples and settings. I don’t think there is anything special when you call these packages from C #.