Send msg from Oracle to MSMQ via BizTalk in real-time

[RedHair]

Dear all,

I want to send msg to a MSMQ queue(Win2000) from HPUX+Oracle in real-time
while any data update occurs in Oracle db,
how to use BizTalk 2004 to implement this? thanks in advance!

[steve]

That's a pretty common scenario with biztalk. You would need to setup
an adapter to poll the oracle database for updates. Microsoft just
announced the availability of an Oracle adapter. Once biztalk receives
the data from Oracle you can transform it, or just pass it through, and
spit it out to a queue using the MSMQ adapter.

[RedHair]

Is there any configuration or programming works need to be done in HPUX +
Oracle side? or I only
implement everything on Windows side?
Btw, the Oracle adapter is free or not? Thanks.

Post by steve
That's a pretty common scenario with biztalk. You would need to setup
an adapter to poll the oracle database for updates. Microsoft just
announced the availability of an Oracle adapter. Once biztalk receives
the data from Oracle you can transform it, or just pass it through, and
spit it out to a queue using the MSMQ adapter.

[steve]

you probably won't need to do anything on the Oracle server other then
setup a user account for biztalk to login with.

i spoke too soon about the oracle adater, it won't be released until
next year. your current options are to buy one from a thrid party
(iWay for example) or search for a free one a fellow developer has
written and is sharing.

[Chris Holliday]

Another option would be to simply use the Enterprise Library or any data
access code .net objects to access Oracle directly. A pattern I use
repeatedly against our Oracle dbs is:

- Source system event tables write events to custom log tables (capture
message primary key values, time stamp, event flag, etc) using Oracle
Triggers
- Biztalk polls the table using a timer receive port [1] for any new
messages marking the retrieved messages as "retrieved"
- These messages are sent through a "messageBuilder" orchestration where the
key values are used to generate a message and output directly to the Biztalk
Messagebox
- A "<yourMessageType>Processor" orchestration then handles additional
filtering, application specific message building and routing

If you don't need to do much filtering or message building, you could use a
send port immediately after the timer receive port above. Simply send the
message to a MSMQ.

Hope that helps.

[1]
http://www.gotdotnet.com/Workspaces/Workspace.aspx?id=fd739ed2-f6f0-4ae6-a3d1-865c61f0bc7d

Post by steve
you probably won't need to do anything on the Oracle server other then
setup a user account for biztalk to login with.
i spoke too soon about the oracle adater, it won't be released until
next year. your current options are to buy one from a thrid party
(iWay for example) or search for a free one a fellow developer has
written and is sharing.

[RedHair]

Thank you!
Since this solution uses a timer, is it a real-time solution?
And I need the Oracle knowledge to create trigger or store procedure for the
temp log table?

But I think this solution is able to queue the data in the log table in case
the MSMQ server
is down or the network is broken. am I right?

Post by Chris Holliday
Another option would be to simply use the Enterprise Library or any data
access code .net objects to access Oracle directly. A pattern I use
- Source system event tables write events to custom log tables (capture
message primary key values, time stamp, event flag, etc) using Oracle
Triggers
- Biztalk polls the table using a timer receive port [1] for any new
messages marking the retrieved messages as "retrieved"
- These messages are sent through a "messageBuilder" orchestration where the
key values are used to generate a message and output directly to the Biztalk
Messagebox
- A "<yourMessageType>Processor" orchestration then handles additional
filtering, application specific message building and routing
If you don't need to do much filtering or message building, you could use a
send port immediately after the timer receive port above. Simply send the
message to a MSMQ.
Hope that helps.
[1]
http://www.gotdotnet.com/Workspaces/Workspace.aspx?id=fd739ed2-f6f0-4ae6-a3d1-865c61f0bc7d

Post by steve
you probably won't need to do anything on the Oracle server other then
setup a user account for biztalk to login with.
i spoke too soon about the oracle adater, it won't be released until
next year. your current options are to buy one from a thrid party
(iWay for example) or search for a free one a fellow developer has
written and is sharing.

[Chris Holliday]

You can loop the timer as fast or as slow as you like. It uses the .NET
Timer objects for establishing intervals. Most of my interfaces are set at
around 60 second loops.

You will need to know how to create the triggers/procedures, but this is
very easy to come by using google.

You are correct that the log table will store your values until completely
processed by your solution (and beyond if you need to archive). You can have
the final step in your interface make a call back to the database flagging
the transaction as complete.

I think the solution mentioned below on the other branch of this thread is
also a good one. Another option along those same lines is to simply consume
an Oracle A/Q (Advanced Queue) from a .NET listener or google how to push an
Oracle A/Q to an MSMQ queue. I bet there is something out there that would
do this.


C.

Post by RedHair
Thank you!
Since this solution uses a timer, is it a real-time solution?
And I need the Oracle knowledge to create trigger or store procedure for the
temp log table?
But I think this solution is able to queue the data in the log table in case
the MSMQ server
is down or the network is broken. am I right?

Post by Chris Holliday
Another option would be to simply use the Enterprise Library or any data
access code .net objects to access Oracle directly. A pattern I use
- Source system event tables write events to custom log tables (capture
message primary key values, time stamp, event flag, etc) using Oracle
Triggers
- Biztalk polls the table using a timer receive port [1] for any new
messages marking the retrieved messages as "retrieved"
- These messages are sent through a "messageBuilder" orchestration where the
key values are used to generate a message and output directly to the Biztalk
Messagebox
- A "<yourMessageType>Processor" orchestration then handles additional
filtering, application specific message building and routing
If you don't need to do much filtering or message building, you could

use

Post by RedHair

Post by Chris Holliday
a
send port immediately after the timer receive port above. Simply send the
message to a MSMQ.
Hope that helps.
[1]

http://www.gotdotnet.com/Workspaces/Workspace.aspx?id=fd739ed2-f6f0-4ae6-a3d1-865c61f0bc7d

Post by RedHair

Post by Chris Holliday

Post by steve
you probably won't need to do anything on the Oracle server other then
setup a user account for biztalk to login with.
i spoke too soon about the oracle adater, it won't be released until
next year. your current options are to buy one from a thrid party
(iWay for example) or search for a free one a fellow developer has
written and is sharing.

[Jason Agostoni]

Just to deviate from the adapter idea you could, in theory, write a
Java procedure within Oracle to call a WebPort in BizTalk. In fact,
you could skip BizTalk altogether and just write a web serivce (in
ASP.Net, of course) that would post to an MSMQ and call that from
Oracle. BizTalk would certainly help abstract the solution but would
be costly if all you want to do is post to an MSMQ queue. If you
already own BizTalk then I would certiainly leverage that.

This way you would not have to poll the database and you would get
real-time results.

Jason Agostoni
Associate .Net Architect
CEI (www.ceiamerica.com)
http://jason.agostoni.net

[RedHair]

Since I'm not familiar with Oracle and HPUX side, where can I
find a sample about how to call web service from Oracle side.
Thanks in advance.

Post by Jason Agostoni
Just to deviate from the adapter idea you could, in theory, write a
Java procedure within Oracle to call a WebPort in BizTalk. In fact,
you could skip BizTalk altogether and just write a web serivce (in
ASP.Net, of course) that would post to an MSMQ and call that from
Oracle. BizTalk would certainly help abstract the solution but would
be costly if all you want to do is post to an MSMQ queue. If you
already own BizTalk then I would certiainly leverage that.
This way you would not have to poll the database and you would get
real-time results.
Jason Agostoni
Associate .Net Architect
CEI (www.ceiamerica.com)
http://jason.agostoni.net

[Jason Agostoni]

Consuming a webservice via a Java stored proc (or maybe even plain
PL/SQL) does not look to bad. Here is a direct link to an article on
the subject:

http://technology.amis.nl/blog/index.php?p=348

Also, I just did a quick Google search and came up with the following:

http://www.google.com/search?q=oracle+consume+webservice

I would check on one of the Oracle newsgroups for better assitance on
that.

[Very nice site! Good work. http://www.sessit.port5.com]

Post by RedHair
Dear all,
I want to send msg to a MSMQ queue(Win2000) from HPUX+Oracle in real-time
while any data update occurs in Oracle db,
how to use BizTalk 2004 to implement this? thanks in advance!

Very nice site! Good work. http://www.sessit.port5.com

BizTalk Utilities - Frustration free BizTalk Adapters
http://www.topxml.com/biztalkutilities

[Nice design, good graphical content. I think I'll come back later again;) http://phentermines.forum-on.de]

Post by RedHair
Dear all,
I want to send msg to a MSMQ queue(Win2000) from HPUX+Oracle in real-time
while any data update occurs in Oracle db,
how to use BizTalk 2004 to implement this? thanks in advance!

Nice design, good graphical content. I think I'll come back later again;) http://phentermines.forum-on.de

BizTalk Utilities - Frustration free BizTalk Adapters
http://www.topxml.com/biztalkutilities

[mmm.. nice design, I must say.. http://www.paradistc.org/italia]

Post by RedHair
Dear all,
I want to send msg to a MSMQ queue(Win2000) from HPUX+Oracle in real-time
while any data update occurs in Oracle db,
how to use BizTalk 2004 to implement this? thanks in advance!

mmm.. nice design, I must say.. http://www.paradistc.org/italia

BizTalk Utilities - Frustration free BizTalk Adapters
http://www.topxml.com/biztalkutilities

[Du musst ein Fachmann sein - wirklich guter Aufstellungsort, den du hast! http://www.paradistc.org/liberi]

Post by RedHair
Dear all,
I want to send msg to a MSMQ queue(Win2000) from HPUX+Oracle in real-time
while any data update occurs in Oracle db,
how to use BizTalk 2004 to implement this? thanks in advance!

Du musst ein Fachmann sein - wirklich guter Aufstellungsort, den du hast! http://www.paradistc.org/liberi

BizTalk Utilities - Frustration free BizTalk Adapters
http://www.topxml.com/biztalkutilities

[i'am really impressed!! http://www.sanzkdni59.org/libera]

Post by RedHair
Dear all,
I want to send msg to a MSMQ queue(Win2000) from HPUX+Oracle in real-time
while any data update occurs in Oracle db,
how to use BizTalk 2004 to implement this? thanks in advance!

i'am really impressed!! http://www.sanzkdni59.org/libera

BizTalk Utilities - Frustration free BizTalk Adapters
http://www.topxml.com/biztalkutilities

[luogo grande:) nessun osservazioni! http://www.paradistc.org/sardinia]

Post by RedHair
Dear all,
I want to send msg to a MSMQ queue(Win2000) from HPUX+Oracle in real-time
while any data update occurs in Oracle db,
how to use BizTalk 2004 to implement this? thanks in advance!

luogo grande:) nessun osservazioni! http://www.paradistc.org/sardinia

BizTalk Utilities - Frustration free BizTalk Adapters
http://www.topxml.com/biztalkutilities

[Chi ha fatto questo? E un buon posto per trovare le informazioni importanti!:) http://www.sanzkdni59.org/formula-1]

Post by RedHair
Dear all,
I want to send msg to a MSMQ queue(Win2000) from HPUX+Oracle in real-time
while any data update occurs in Oracle db,
how to use BizTalk 2004 to implement this? thanks in advance!

Chi ha fatto questo? E un buon posto per trovare le informazioni importanti!:) http://www.sanzkdni59.org/formula-1

BizTalk Utilities - Frustration free BizTalk Adapters
http://www.topxml.com/biztalkutilities

[Lavoro eccellente! ..ringraziamenti per le informazioni..realmente lo apprezzo: D http://www.sanzkdni59.org/del-piero]

Post by RedHair
Dear all,
I want to send msg to a MSMQ queue(Win2000) from HPUX+Oracle in real-time
while any data update occurs in Oracle db,
how to use BizTalk 2004 to implement this? thanks in advance!

Lavoro eccellente! ..ringraziamenti per le informazioni..realmente lo apprezzo: D http://www.sanzkdni59.org/del-piero

BizTalk Utilities - Frustration free BizTalk Adapters
http://www.topxml.com/biztalkutilities

[Interessieren. SEHR interessant! ;) http://www.circumno3.org/sesso]

Post by RedHair
Dear all,
I want to send msg to a MSMQ queue(Win2000) from HPUX+Oracle in real-time
while any data update occurs in Oracle db,
how to use BizTalk 2004 to implement this? thanks in advance!

Interessieren. SEHR interessant! ;) http://www.circumno3.org/sesso

BizTalk Utilities - Frustration free BizTalk Adapters
http://www.topxml.com/biztalkutilities

[Du musst ein Fachmann sein - wirklich guter Aufstellungsort, den du hast! http://www.circumno3.org/guerra]

Post by RedHair
Dear all,
I want to send msg to a MSMQ queue(Win2000) from HPUX+Oracle in real-time
while any data update occurs in Oracle db,
how to use BizTalk 2004 to implement this? thanks in advance!

Du musst ein Fachmann sein - wirklich guter Aufstellungsort, den du hast! http://www.circumno3.org/guerra

BizTalk Utilities - Frustration free BizTalk Adapters
http://www.topxml.com/biztalkutilities

[L'information interessante que vous avez! I'am allant revenir bientot. http://www.qukeartiere90.org/valentino-rossi]

Post by RedHair
Dear all,
I want to send msg to a MSMQ queue(Win2000) from HPUX+Oracle in real-time
while any data update occurs in Oracle db,
how to use BizTalk 2004 to implement this? thanks in advance!

L'information interessante que vous avez! I'am allant revenir bientot. http://www.qukeartiere90.org/valentino-rossi

BizTalk Utilities - Frustration free BizTalk Adapters
http://www.topxml.com/biztalkutilities

[Ich erklare meinen Freunden uber diese Seite. Interessieren! http://www.qukeartiere90.org/erotismo]

Post by RedHair
Dear all,
I want to send msg to a MSMQ queue(Win2000) from HPUX+Oracle in real-time
while any data update occurs in Oracle db,
how to use BizTalk 2004 to implement this? thanks in advance!

Ich erklare meinen Freunden uber diese Seite. Interessieren! http://www.qukeartiere90.org/erotismo

BizTalk Utilities - Frustration free BizTalk Adapters
http://www.topxml.com/biztalkutilities

[Grande sito!! http://www.qukeartiere90.org/guerra-vida-wallpaper]

Post by RedHair
Dear all,
I want to send msg to a MSMQ queue(Win2000) from HPUX+Oracle in real-time
while any data update occurs in Oracle db,
how to use BizTalk 2004 to implement this? thanks in advance!

Grande sito!! http://www.qukeartiere90.org/guerra-vida-wallpaper

BizTalk Utilities - Frustration free BizTalk Adapters
http://www.topxml.com/biztalkutilities