Wednesday, November 5, 2008

Allow Oracle Forms to receive messages from external sources

Combining Oracle Forms with other technologies and allowing messages and data to communicate between them is one of the challenges facing the enterprise today. If you have a large investment in Oracle Forms and wish to continue using them in partnership with other applications, then you will most likely be looking at some sort of SOA-governed solution. However, there are other solutions that simply require database connectivity to allow transactions to flow between differing technologies. This outlines a few of these 'other' approaches. Note that these are not intended as recommendations, but simply provide alternatives that already exist with the current Oracle database and Forms toolset.

Approach #1: Custom Messaging Database Table
  • Create a table that will hold messages.
  • Create a package to populate and retrieve messages from that table.
  • In Forms, create a timer that periodically polls the table for new messages, or allow the user to dictate when to check for new messages (button-click, menu-option, etc).
  • On successful retrieval, act upon message as appropriate.
Approach #2: Advanced Queues
The Oracle AQ implementation gives great flexibility in the way messages can be sent and received.
See http://www.oracle-base.com/articles/9i/AdvancedQueuing9i.php for a quick run-down on how to use this approach.

Approach #3: Forms as a Socket Server
A variation on the chat server as documented in
http://sheikyerbouti.developpez.com/forms-pjc-bean/pages/chat.htm
and
http://sheikyerbouti.developpez.com/forms-pjc-bean/pages/socketserver.htm
this approach opens a specific port that allows direct communication between different users of a Forms application. This can be extended to allow other technologies to also send telnet-style messages to Forms.

Approach #4: DBMS_PIPE
Ok, here is the technique I spent the most time on.
  • Create a package that implements DBMS_PIPE for sending and receiving messages.
  • Note that package owner must have execute permissions granted for DBMS_PIPE.
  • Call send routine from any technology that can access the database: SQLPlus, Java, Forms, etc...
  • DBMS_PIPE code based on http://www.oracle-base.com/articles/misc/dbms_pipe.php
  • Create Java class that can run asynchronously to call receive method and fire event when message is received.
  • See http://sheikyerbouti.developpez.com/forms-pjc-bean/pages/asyncjob.htm
  • Class must implement Runnable.
  • Note that the Class requires its own database connection to check pipe.
  • Package the class into a Jar, and place a signed version on the application server for subsequent distribution with Forms application.
  • Add Java Bean onto your Form to implement the Java asynchronous method.
  • Add When-Custom-Item-Event to bean item on Form that captures and acts upon message received.
  • Either alert user to message, or use message contents to automatically navigate toa different screen.
  • formsweb.cfg must be setup to also distribute the packaged and signed Jar containing the asyncjob class, and classes12.jar (DB Connection for JInitiator 1.3 - or use ojdbc14.jar for Sun Java 1.4) in the archive_jini or archive setting
If there is enough demand, I will post up some sample code for approach #4, meanwhile I might try and see if I can get #2 (AQ) working...