I got a comment (in Dutch) on my article about the EBS Adapter.
Since giving short answers on simple questions is not my strengths, I'll answer with a blogpost.


In the article you can make up that I'm not too enthusiastic about the EBS Adapter. The reality is often more nuanced than stated, you should consider the EBS adapter specific for your own situation.

Two of the main reasons to use the EBS Adapter are:

• It sets the application context for you at connecting to the EBS instance
• The Adapter Wizard enables you to introspect the available interfaces.

If these reasons are not applicable for you since for instance you set the Application Context yourself for whatever reason and/or you use customer pl/sql procedures, then might be too little to ratify the use.

But what would I do if I need to expose a pl/sql as a webservice from EBS?

EBS 11.5.10 indeed just works with JServe. From 12 onwards OC4J is used, at least initially in the 10.1.2 version (don't know if later relases are on 10.1.3).
So to start with, I'd use at least a managed OC4J 10.1.3.x Application server. Either single node or clustered. So not a standalone oc4j.  

Then it depends mainly if you can use SoaSuite. If you can use SoaSuite I would create a BPEL Process, an Oracle ESB or (in case of SoaSuite11g) OSB service, based on the SoaSuite database adapter. Then arrange for setting the application context in the implementation block of the package where you have put the pl/sql procedure in.

If you can't use SoaSuite you could generate a webservice from JDeveloper based on the pl/sql procedure. And deploy that as a WAR or EAR file to the application server. The main disadvantage of having JDeveloper generate the webservice is that you can't influence the way the generated code calls the pl/sql procedure. So I think I would create a standalone java application that uses JNDI for getting the jdbc-connection. Then code the call of the pl/sql procedure in the java-application. Test it stand-alone. If that works then create webservice on that application. Doing so you have separated the technical code that does the job (calling the pl/sql procedure) and the actual webservice. For an example on how to use JNDI in standalone applications that also have to run on an AS see this article.

For creating the webservice it self you also have two choices:

• Let JDeveloper generated the code for you. But then JDeveloper generates the wsdl and you have very little (near to nothing) influence on how it looks like. Unless you generate the webservice based on the wsdl.
• Use a soap stack that supports annotations (like Sun Glassfish Metro). Using annotations you have very large influence on how the generated wsdl looks like. See for instance this article. Only in that case the wsdl will be generated at startup of the webservice application in the application server.

So my preferred way to go is either use SoaSuite/OSB or create a annotation based webservice on a standalone java-app that calls my procedure.

Back in 2008 I wrote an article on creating a BPEL Scheduler. The principle of that article I used later in a real implementation of a scheduler. In several blog-posts, amongst others of Clemens Utschig and Lucas Jellema, I read all kinds of arguments why you should or shouldn't use BPEL for scheduling.

I think however that BPEL is perfectly suitable to be used as to schedule jobs. Especially if you create a datamodel with an Apex front-end for instance (used JHeadstart myself for it) to register the schedule meta data to determine the next schedule dates.

There are a few considerations though. One of them is reinvoking the scheduler for a new scheduled-date, after performing a task. In my earlier article I used a plain invoke on the client-partnerlink. A big disadvantage of that is dat every task instance is created under the same root instance. After  a few iterations the process tree under the tree finder is exorbitant big.

This is solved quite easily by replacing the invoke by a piece of embedded java:

<bpelx:exec name="ReInvokeRH" language="Java" version="1.4"><![CDATA[//Get logger
                org.apache.commons.logging.Log log = org.apache.commons.logging.LogFactory.getLog("my.log");
                // Get singletonId
                log.info("ReInvoke-Get SingletonId.");
                String singletonId = (String)getVariableData("singletonId");;
                //Construct xml message
                log.info("ReInvoke - Construct XML message.");
                String xml = "<BPELProcessProcessRequest xmlns=\"http://xmlns.oracle.com/BPELProcessProcessRequest \">\n<singletonId>"
                           + singletonId 
                           + "</singletonId>\n</BPELProcessProcessRequest >";
                log.info("ReInvoke message: " + xml);
                //Get a locator and delivery service.
                log.info("ReInvoke - Get Locator.");
                try {
                  Locator locator = getLocator();
                  log.info("ReInvoke - Initiate IDeliveryService.");
                  IDeliveryService deliveryService = (IDeliveryService)locator.lookupService(IDeliveryService.SERVICE_NAME);
                  // Construct a normalized message and send to Oracle BPEL Process Manager
                  log.info("ReInvoke - Construct a normalized message and send to Oracle BPEL Process Manager.");
                  NormalizedMessage nm = new NormalizedMessage();
                  nm.addPart("payload", xml);
                  // Initiate the BPEL process
                  log.info("ReInvoke - Initiate the BPEL process.");
                  deliveryService.post("BPELProcess", "initiate", nm);
                } catch (RemoteException e) {
                  log.error(e);
                  setVariableData("invokeError", "true");
                  setVariableData("invokeErrorMessage", "ReInvokeRH-RemoteException: "+e.toString());  
                } catch (ServerException e) {
                  log.error(e);
                  setVariableData("invokeError", "true");
                  setVariableData("invokeErrorMessage", "ReInvokeRH-ServerException: "+e.toString());
                }
                log.info("ReInvoke - Return.");]]>
              </bpelx:exec>

This will build a String message, create NormalizedMesage of it, and post it to the "Initiate" operation of the "BPELProcess" using the post method of the DeliveryService that is fetched using the locator of the bpel instance.

Doing so the parent-child relation is broken, the new instance runs in a new process-instance-tree.

Another consideration is that you might want to "listen" to external messages to do an abort or a reschedule. This can be done in several ways. For example by doing a correlated receive (using a correlation set on a field in the request message like the "singletonId" in the reinvoke example) in a parallel flow.

Problem is that at a normal reinvoke this receive has to be released. If not, the parellel flow will not end and/or the correlation set will not be freed. In the new instance you might get a runtime message that there is already a receive on the same correlation-id. In another project I solved a similar problem by calling the process instance with an abort message from within the process instance, before doing a re-invoke. This is quite costly in terms of performance since it involves a message to the bpel-service with all the overhead (even if it's in fact a WSIF call). So recently I found myself a little smarter solution.

What I basically did was to wrap the parallel flow with the receive and task-execution logic with a scope.
Instead of normally ending the branches, I throw a "finish" or "abort" exception:

<throw name="Throw_Finish" faultName="client:Finish" faultVariable="ReceiveInput_initiate_InputVariable"/> 

Then this can be catched in the surrounding scope:

<catch faultName="client:Finish" faultVariable="ReceiveInput_initiate_InputVariable">

This might look strange at first sight, to end your normal (or abort) flow using a business-exception. Ending your flow is not an exception, is it?
But it will release your receive-activities and the correlation id they hold for a reinvoke on the same correlation-id.