PL/SQL Access Provider 

The PL/SQL Access Provider is the function within the Oracle Database by which the framework actively connects and sends messages to the Application Server (IFS Middleware Server). The PL/SQL Access Provider needs a couple of configuration settings to function properly, these settings are:

Note: The above settings are used for synchronous invocations using the HTTP protocol. As of patch 145612 (available within Update 5) the algorithm has been re-written and, by default, doesn't use HTTP any more, see Invoking Messages. It is, however, possible (but not recommended) to switch to use the HTTP protocol even with the new algorithm.

Contents

Cleaning Job

The new way of invoking messages based on Oracle AQ Queues may create messages in AQ Exception Queues. This is because there is timeout defined in call to AQ queues. In a situation when the Invoke method will not get response within the predefined period of time it will just abandon the queue and continue. It can happen for example if the Middleware Server is down or the third-party service doesn't respond within the requested time period. But the AQ message will be anyway created sooner or later. AQ messages not consumed within the time period defined by the timeout parameter will be automatically moved to the AQ Exception Queues. Those messages cannot be reused and can be safely removed.

There is a predefined Database Task "Cleanup AQ Exception Queues". By default this task is scheduled to be executed every day at 00:30 and is supposed to remove all AQ messages from the AQ Exception Queues that are older than 24 hours. The parameters can be changed. Read more about Task Scheduling.

The Database Task "Cleanup AQ Exception Queues" calls the stored procedure App_Message_Processing_API.Cleanup_Aq_Exception_Queues, which, in turn, deletes the AQ messages from the AQ Exception Queues and fires an Event with ID CLEANUP_AQ_EXCEPTION_QUEUES. By default the event is disabled and there are no actions on it.

The example below shows an Event Action sending a mail with statistics about the removed messages:

The procedure App_Message_Processing_API.Cleanup_Aq_Exception_Queues deletes messages from the following AQ Exception Queues:

HTTP Protocol

The settings described in this section are only used when the HTTP protocol is used for synchronous method/BizAPI invocation.

All these settings are stored in the PLSQLAP_ENVIRONMENT_TAB database table. This table only have two columns, NAME and VALUE (thus containing name - value pairs). Since especially the password for the IFSPLSQLAP user is very sensitive information, there is no client GUI used to modify the values in this table, neither is there any Logical Unit or database view exposing this database table to any end users. If you want to manually modify the values in the table you will have to use SQL*Plus or a similar tool, logged on as application owner.

These parameters are normally maintained automatically. If the password needs to be changed or if the oracle password is accidentally changed on the IFSPLSQLAP users, it can be reset in Solution Manager - Oracle User form. Changing the password of IFSPLSQLAP in this form, will automatically update the PLSQLAP_ENVIRONMENT_TAB.      

Setup

The PL/SQL Access Provider is automatically configured correctly by the Installer tool during installation/reconfiguration. The connection string is set to point to the Integration Server access point. Also during installation/configuration the URL to the Integration Server web server is entered into the ORACLE ACL (Access Control List).

When the Oracle AQ based implementation is used for invoking of synchronous messages it is possible to trim the involved Message Driven Beans using Work Managers.

Verifying the PL/SQL Access Provider Settings

There is an entry in the Application Monitoring Console feature in IFS Solution Manager that can be used to verify the settings. One important thing to note, though - the monitoring entry only verifies that the PL/SQL Access Provider can access the configured Middleware Server instance, not that it is the correct server instance!
Please make sure the CONN_STR value (URL) mentioned above actually refers to the Middleware Server instance used together with your database instance.