Skip to content

PLSQL Access Provider

The PLSQL Access Provider allows sending messages from PL/SQL business logic code executing within the Oracle database.

General

The PLSQL Access Provider is used to send messages, call outbound messages, from database to IFS Connect Integration broker. PLSQL Access Provider is implemented in the PL/SQL package PLSQL_Server_API.

Posting Messages

There are a number of overloaded procedures in the PLSQL_Server_API package that can be used for posting of messages. Names of all those procedure start with Post, e.g. Post_Outbound_Message. All those method simply create a new Application Message putting the incoming data as Message Body.

A database trigger will fire as soon as the transaction that is calling the actual Post procedure will be committed and the message will be picked up by the Batch Processor, which means that posting of messages is an asynchronous operation.

Note that creation of the Application Message is done within the same transaction as the one that has initiated the call to the actual Post method. If the transaction will be rolled back, the message will not be processed.

Invoking Messages

Names of procedures in PLSQL_Server_API package dedicated for invoking of messages are starting with Invoke, e.g. Invoke_Outbound_Message or Invoke_Record_Impersonate. The main difference between posting and invoking messages is that invoking of messages is done synchronously, i.e. the procedure will wait for the response and return it back to the caller once received.

Initially invoking of messages has been implemented using the HTTP protocol to access SOAP Gateway. But because of growing needs for stronger security it became hard to configure Oracle database to use the HTTP protocol. Therefore the logic has be re-written and the new algorithm, based on JMS messaging concepts.

Execution steps:

  1. The Invoke procedure creates an Application Message with incoming data in the Message Body and saves it. This is done in a separate (autonomous) transaction. The message is tagged so it can be distinguished from other messages by Message Processor.
  2. The Invoke method is then waiting for the Response AQ JMS Message.
  3. Database trigger sends an JMS Message causing Batch Processor to pick up the message and start processing it.
  4. Batch Processor's Forwarder MDB posts the JMS message to a dedicated JMS queue, InvokeQueue.
  5. An MDB listening on InvokeQueue retrieves the Application Message from the database and calls Message Processor.
  6. Message Processor is processing the message
  7. After saving the processed Application Message with Reply Message Body, Message Processor sends a Response AQ JMS Message.
  8. The Invoke procedure retrieves the Response AQ JMS Message and fetches the Reply Message Body from the database.
  9. The response is extracted from the Reply Message Body and returned to the caller.

By default the Invoke method will wait up to 20 min for response, but the timeout can be configured in Solution Manager, Setup IFS Connect feature. But if the Connect Container is not up and running, the request will timeout after 1 min.

Important! Synchronous invocations will execute in their own transactions, separate from any open transaction within Oracle. An Oracle rollback after a synchronous invocation using PLSQL Access Provider will not roll back the effects of the invocation. However asynchronous postings done with for example Post_Event_Message will be part of the Oracle transaction and included in rollbacks.