PLSQL Access Provider 

The PLSQL Access Provider allows invocations of server operations from PL/SQL business logic code executing within the Oracle database.

Contents

General

The PLSQL Access Provider is used to send messages, call outbound BizAPIs, and to invoke any operation implemented within IFS Middleware Server. 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_Bizapi. All those method simply create a new Application Message putting the incoming data as Message Body. In most cases it is also possible to specify the outbound BizAPI that is supposed to be executed by Message Processor later on, once the message is being processed.

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_Request_Bizapi 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.

With the Invoke procedures it is possible to specify BizAPI to be executed by Message Processorr, but some versions allow also usage of operations not being defined as BizAPIs.

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 Oracle AQ, is available as of patch 145612 (available within Update 5). See also description of a typical Message flow for an Outbound Request BizAPI.

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 AQ 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 Middleware Server is not up and running, the request will timeout after 1 min.

Note that AQ messages not consumed with the timeout period will be moved to AQ Exception Queue. There is a predefined cleaning job that removes such messages.

Note that in the old implementation Application Messages were saved only in some circumstances, while the new implementation will always save a new Application Message for each invocation.

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.