If you want to use JDeveloper 11g for Soa and/or BPM Development you need to download the SOA JDev Extension for the SOA Composite Designer and the BPM JDev extension for BPM Studio.

In all the documentation you'll find, you see an explanation on how to download them using the Help/Update utilities of JDeveloper. This might be neat and user-friendly for updating one JDeveloper instance where you have a fast internet access. But it is not so handy if you have to provide these extensions to multiple JDeveloper instances for a course for example. Then it is handy when you're able to download them and provide them on a stick.

I found these files on this JDeveloper extension page (just using Google).
So I downloaded the latest versions on my harddisk at home, where I have a good internet access. I hope this page is on-line indefinately.

Just read on Sue Harpers blog that SQL Developer Datamodeller 3.0 went production. It's downloadable from its OTN-page. I'm happy to know it's a free download. End 2008 I used the beta version to model a datamodel for a project I did then. I found it a very nice tool, a good replacement for the ERD/Datamodelling capabilities of Designer. There were a few "misses, and the main ones I had were:

• Generation of pl/sql packages and functions was not possible
• Subversion support was poor: when you save a project it recreates directories to save the xml-artifacts in. Recreating directory means removal of the .svn folders, which breaks your svn working copy.

In the new features list I saw promissing statements that these should be solved now. You should be able to generate packages and functions and there is even Subversion Integration (not only support): you can see pending changes, compare models and it shoud "recognize versioned designs". Well, isn't that great?

Since it's free and since it's a really good modelling tool, I think it is a must-use on every project that uses a datamodel. Haven't used it yet? Re-engineer your ERD by importing your datamodel from the database. It gives a quick insight in the datamodel for newcomers in your project.

See also for an early adopter of Sql Developer 3.0 the SqlDeveloper OTN-page. It includes the Datamodeller.

For my current project I had to install tomcat as a j2ee server in Eclipse. It would not start since the Oracle XE database has port 8080 in use.

I found the method to change the ports in XE here. From that example I created my own little script:

set serveroutput on

select dbms_xdb.gethttpport as "HTTP-Port"
,      dbms_xdb.getftpport as "FTP-Port" 
from dual;

declare
  new_port varchar2(4):='8085';
begin

 dbms_output.put_line('Change HTTP Port to '||new_port);
 dbms_xdb.sethttpport(new_port);
end;
/

select dbms_xdb.gethttpport as "HTTP-Port"
,      dbms_xdb.getftpport as "FTP-Port" 
from dual; 

In good old Dutch: "Mucho Plezieros" with it...

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.

Lately I was asked to help with routing messages from a object-type based AQ-queue to JMS.
The thing is that JMS works with text. So you have to transform the objecttype to text.
When the Object type is one of your own, you could extend it with a method "toXML" to give a XML message based on the attributes of the object.

In this case it was about a Oracle Integratin B2B AQ Queue, which is based on the B2B object "IP_MESSAGE_TYPE".

It turns out not too hard to translate the object type to JMS. I created a package for it which I provided for download here.

You can test it with the following code:

declare
  -- Non-scalar parameters require additional processing
  result     sys.aq$_jms_text_message;
  ip_message ip_message_type;
  payload    clob;
begin
  payload         := def_b2b.varchar_to_clob('Jet, Teun, Vuur, Schapen');
  ip_message := ip_message_type(MSG_ID           => 'Aap'
                                    ,INREPLYTO_MSG_ID => 'noot'
                                    ,FROM_PARTY       => 'Mies'
                                    ,TO_PARTY         => 'Zus'
                                    ,ACTION_NAME      => 'Lezen'
                                    ,DOCTYPE_NAME     => 'Leesplankje'
                                    ,DOCTYPE_REVISION => '1.0'
                                    ,MSG_TYPE         => 1
                                    ,PAYLOAD          => payload
                                    ,ATTACHMENT       => null);
  -- Call the function
  result := def_b2b.ip_message2jms_msg(ip_message => ip_message);
   result.get_text( :msg);
end;

As you can see a jms-accesible AQ-queue has a special system-Object-type: 'sys.aq$_jms_text_message'. There are several others, for different kinds of jms queues or topics. Also markt that the object types differ between Oracle 10g or 11g Enterprise Edition or equivalent and Oracle XE. In XE you wouldn't find a 'construct' method. You could try the solution of Peter Ebell for this.

Another thing is that the guys that asked me for help, had to do the enqueue of the message on the JMS-queue based on the enqueue on the source B2B-queue.
From Oracle they got permission to use a trigger on the Queue-table. To begin with they used a Before Row Insert trigger. Besides triggers on queue-tables are not support and certainly not the way to go, they encountered a problem with it. And that lays in the fact that the payload attribute is a CLOB. I allways found the way Oracle handled CLOBs at least "a little remarkable". On insert you create a row with an empty CLOB and then query it for update. In the queried row you upload the content to the CLOB-column. Since an AQ queue is based on a table it works essentially the same way. So on Before Row Insert the payload attribute is still empty. They solved it to use an After Delete trigger (when the message is consumed by the subscribing-process).

The way to go is actually to register a notification service on the queue using code like:

declare
  lc_reg_info      SYS.AQ$_REG_INFO;
  lc_reg_info_list SYS.AQ$_REG_INFO_LIST;
begin
  lc_reg_info := SYS.AQ$_REG_INFO('B2B.IP_IN_QUEUE:'
                                 ,DBMS_AQ.NAMESPACE_AQ
                                 ,'plsql://b2b.def_b2b.handle_inbound_notification?PR=1'
                                 ,HEXTORAW('FF'));

  lc_reg_info_list := SYS.AQ$_REG_INFO_LIST(lc_reg_info);
  dbms_aq.register(lc_reg_info_list, 1);
end;

Such a plsql notification function is a function that is required to have a particular "authograph":

PROCEDURE handle_inbound_notification(context  RAW
                                       ,reginfo  sys.aq$_reg_info
                                       ,descr    sys.aq$_descriptor
                                       ,payload  RAW
                                       ,payloadl NUMBER)

These parameters provide you with the data to fetch/dequeue the message this procedure is called for. You won't get the message itself, you have to dequeue-it explicitly. See the package for an example to implement this procedure.

You should perform the register for every queue-consumer that you want to reroute the messages for. But it is not too hard to put this in a parameterized-procedure and call it based on a query that fetches the consumer from either the dictionary or (better) the B2B repository. In fact this code is extracted from such a construct. But it was a little too much (I already put a reasonable amount of time in the package) to anonymize it and make it more generic.
If you need more help with it, I could of course provide some consultancy.

Hello
The EA version of sql data modeler is available.
See http://forums.oracle.com/forums/ann.jspa?annID=1446

Features:
http://www.oracle.com/technetwork/developer-tools/datamodeler/ea1-newfeatures-176686.html

Finally it should be possible to generate packages.....