I started today with setting up an OSB training and figuring out how to run XQuery scripts outside of OSB. I looked into the (ok a little dated) book: Oracle Database 10g XML&SQL, that I have in my library. In that book there's also a chapter about XQuery. And it shows that the Oracle XDK has a XQuery processer, but also a commandline tool like SQLPLus: XQLPlus. It is in the xquery jar, but how to run it? Well you need at least the xquery.jar, but the xmlparserv2.jar is handy for more specialized functions. For localization functionality the orai18n-collation.jar is recommended. In this blog I found some of the basic jar files to put in the classpath. To put things together in a convenient start script I created the following bash script:

export ORACLE_BASE=/u01/app/oracle
export FMW_HOME=$ORACLE_BASE/Middleware/11.1.1
export ORACLE_COMMOD=$FMW_HOME/oracle_common/modules
export XDK_HOME=$ORACLE_COMMOD/oracle.xdk_11.1.0
export NLSRTL_HOME=$ORACLE_COMMOD/oracle.nlsrtl_11.1.0
export CLASS_PATH=$XDK_HOME/xquery.jar:$XDK_HOME/xmlparserv2.jar:$NLSRTL_HOME/orai18n-collation.jar
java -cp $CLASS_PATH oracle.xquery.XQLPlus $*

Maybe it can come handy for others as well.

This week in the BPM Adaptive Case Management workshop at the Oracle Fusion Middleware Community Forum I ran into a strange bug.

The background: earlier this week my own laptop broke down. So I had to borrow a laptop, what fortunately I could borrow from one of the presenters (thanks Guido). The thing is, though, that it runs on Ubuntu 12, so I ran my VM in VirtualBox 4.3 on Ubunty. Now actually that should not be of any difference, but it is really the only difference between my configuration and all the others (who all probably run on Windows).

When I create a bpmn process in Jdeveloper, I get this:
 And when I add an activity I get this.
 The generated events and activities apparently get invalid. The errors are:

So apparently jDeveloper does not like the names it generates itself. Actually it turns out not to be the names in error, but the ids of the activities and events.

I struggled half a day with this, importing a new VM, since the suggestion was that I got a corrupt one and try it there. In the end I compared the bpmn file (found in the processes sub folder of the project) with the given lab-solution. And I found out that jdeveloper in my configuration generates Id's with a dash and a number that is one digit longer that those of the bpmn files in the lab-solutions.

The simple solution for this, be it very inconvenient, is to create your process, add all the necessary activities, but don't bother the implementations or even the names. Then close the application in JDeveloper. It's not enough to just close the file, since JDeveloper apparently keeps the file as XML DOM tree in memory. Then open the bpmn file in an ascii editor like notepad++ (windows) or gedit (linux).

You'll see enties like this: 

<scriptTask isForCompensation="false" name="ScriptTask" id="ACT-10244072768044">

Search and replease all occurrences of 'ACT-' to 'ACT' and 'EVT-' to 'EVT'.

Then when you re-open the application and bpmn process, the activities are valid.

You see that the Script task gets a name according to it's Id. This is because there is another file in the root of the project, named after the process but suffixed with 'Documentation.xml'. Like 'ActIdTestDocumentation.xml'. In this file the actual names, documentation in different locales are placed. But you don't have to bother about that. When you change the name of the activity in jDeveloper the old name with id is removed of that file and the new name is added.

The weirdest thing occurred to me yesterday. I have this OSB-project in Eclipse that is working fine and can be published to the dev-server perfectly.

However, I use a release/deployment framework that does a scripted export of the OSB-configuration-jar. It copies the osb-projects to a temp-folder and creates an configuration-jar out of it. It is based on the scripts of Edwin Biemond, that I adapted in way that it can be included in my release and deployment framework for SOA, BPM and now also OSB projects.

The thing is that when I create a release of the project using my release script, I get a vague error in the exportFromWorkspace part where a java call is done to the com.bea.alsb.core.ConfigExport application of ${eclipse.home}/plugins/org.eclipse.equinox.launcher_1.2.0.v20110502.jar.

The error is:

...
     [java] Importing project located in "Aap" directory of the workspace...
     [java] Importing project located in "Noot" directory of the workspace...
     [java] Importing project located in "Mies" directory of the workspace...
     [java] com.sun.org.apache.bcel.internal.generic.ClassGenException: Branch target offset too large for short
     [java]  at com.sun.org.apache.bcel.internal.generic.BranchInstruction.dump(BranchInstruction.java:99)
     [java]  at com.sun.org.apache.bcel.internal.generic.InstructionList.getByteCode(InstructionList.java:980)
     [java]  at com.sun.org.apache.bcel.internal.generic.MethodGen.getMethod(MethodGen.java:616)
     [java]  at com.sun.org.apache.xalan.internal.xsltc.compiler.Mode.compileNamedTemplate(Mode.java:556)
     [java]  at com.sun.org.apache.xalan.internal.xsltc.compiler.Mode.compileTemplates(Mode.java:566)
     [java]  at com.sun.org.apache.xalan.internal.xsltc.compiler.Mode.compileApplyTemplates(Mode.java:818)
     [java]  at com.sun.org.apache.xalan.internal.xsltc.compiler.Stylesheet.compileModes(Stylesheet.java:615)
     [java]  at com.sun.org.apache.xalan.internal.xsltc.compiler.Stylesheet.translate(Stylesheet.java:730)
     [java]  at com.sun.org.apache.xalan.internal.xsltc.compiler.XSLTC.compile(XSLTC.java:354)
     [java]  at com.sun.org.apache.xalan.internal.xsltc.compiler.XSLTC.compile(XSLTC.java:429)
     [java]  at com.sun.org.apache.xalan.internal.xsltc.trax.TransformerFactoryImpl.newTemplates(TransformerFactoryImpl.java:795)
     [java]  at com.bea.wli.sb.resources.xslt.XsltEntryHelper.getTemplates(XsltEntryHelper.java:818)
     [java]  at com.bea.wli.sb.resources.xslt.XsltEntryHelper.validateTree(XsltEntryHelper.java:479)
     [java]  at com.bea.wli.sb.resources.xslt.XsltEntryHelper.validate(XsltEntryHelper.java:191)
     [java]  at com.bea.wli.sb.resources.xslt.XsltTypeDef.validate(XsltTypeDef.java:65)
     [java]  at com.bea.wli.config.validation.ResourceValidator.validateContents(ResourceValidator.java:115)
     [java]  at com.bea.wli.config.validation.ResourceValidator.call(ResourceValidator.java:66)
     [java]  at com.bea.wli.config.validation.SequentialValidator.validate(SequentialValidator.java:34)
     [java]  at com.bea.wli.config.validation.ValidationService.validate(ValidationService.java:177)
     [java]  at com.bea.wli.config.impl.CoreToSessionPropagator.doValidation(CoreToSessionPropagator.java:239)
     [java]  at com.bea.wli.config.impl.CoreToSessionPropagator.handleBeforePrepare(CoreToSessionPropagator.java:118)
     [java]  at com.bea.wli.config.impl.CoreToSessionPropagator.beforePrepare(CoreToSessionPropagator.java:77)
     [java]  at com.bea.wli.config.transaction.TransactionListenerWrapper.beforePrepare(TransactionListenerWrapper.java:64)
     [java]  at com.bea.wli.config.transaction.TransactionManager.notifyBeforePrepare(TransactionManager.java:1094)
     [java]  at com.bea.wli.config.transaction.TransactionManager._prepareForCommit(TransactionManager.java:654)
     [java]  at com.bea.wli.config.transaction.TransactionManager.endTransaction(TransactionManager.java:782)
...

I found this error on serveral places related to the Xalan parser, for instance: https://issues.apache.org/jira/i#browse/XALANJ-2064. I saw that SAP's Netweaver suffers from this issue as well.

Apparently the XSL is compiled into java methods, wich in byte codes have a branch limitation of 32K. So the solution is to break-down the XSL into multiple smaller sub-templates. This makes the XSLT more modular, but unfortunately the XSLT mapper of JDeveloper can't do mappings in those sub-templates. It would be nice if Oracle Development think of something smart to do mappings using sub-templates. I have some thoughts for that...

So I split up my template into smaller bits and now my release works perfectly.

Now you could ask "OSB and XSL: why not XQuery?". Well, maybe a bit to my embarrasment: I'm not too familiar yet with XQuery, where through my SOASuite background, I'm quite loose on XSLT. And I found that the XQuery mapper in Eclipse has some limitations in the use of some simpletypes. I have xsd's with definitions that are based on simple type, with length restrictions. But the XQuery mapper did not let me map those on a simple xsd:string target-element...
And to be honest: I find XSL "cleaner" then XQuery, since it is completely XML based. But that is more a  purist-finding and maybe due to a lack of XQuery-experience...

Triggered by a comment on an older article I wrote a little article yesterday on determining the instance of an object. As far as known by me at least and seemingly by Google there is no java instanceOf counterpart in Oracle Pl/Sql. In SQL Where clauses you can use the 'is of' expression. But that is not available in pl/sql. I tried some suggestions with embedded sql but I couldn't get it to work. The suggestion from a forum I found yesterday was to use function overloading. I played around a little with it and it seems I stumbled on a fairly simple method. Let's say I've a type like the following:

CREATE OR REPLACE TYPE "DWN_PERSON" AS OBJECT 
( 
name varchar2(40)
, member function instance_of return varchar2
) not final;
/
CREATE OR REPLACE TYPE BODY "DWN_PERSON" AS
  member function instance_of return varchar2 AS
  BEGIN
    return 'DWN_PERSON';
  END instance_of;
END;
/

I just created a simple Instance_of method that does nothing but returning the name of the object. It was my first step in making things spiffier later on. Keeping in mind that it probably should become an overloading function with a parameter. Then I created a Natural Person type:

  CREATE OR REPLACE TYPE "DWN_NATURAL_PERSON" under DWN_PERSON 
( surname varchar2(100)
, overriding member  function instance_Of return varchar2 
) ;
/
CREATE OR REPLACE TYPE BODY "DWN_NATURAL_PERSON" AS
  overriding member  function instance_Of return varchar2 AS
  BEGIN
    RETURN 'DWN_NATURAL_PERSON';
  END instance_Of;
END;
/

And because I was going so well, I created a Not Natural Person:

CREATE OR REPLACE TYPE "DWN_NOT_NATURAL_PERSON" under DWN_PERSON 
( companyname varchar2(100)
, overriding member  function instance_Of return varchar2 
) ;
/
CREATE OR REPLACE TYPE BODY "DWN_NOT_NATURAL_PERSON" AS
  overriding member  function instance_Of return varchar2 AS
  BEGIN
    RETURN 'DWN_NOT_NATURAL_PERSON';
  END instance_Of;
END;
/

Then I created the following little script:

declare
l_person1 dwn_person;
l_person2 dwn_person;
l_person3 dwn_person;

function create_person(p_name varchar2, p_surname varchar2, p_comany_name varchar2) return dwn_person
as
l_person dwn_person;
begin
  if p_surname is not null then
  l_person := dwn_natural_person(p_name, p_surname);
  elsif p_comany_name is not null then
    l_person := dwn_not_natural_person(p_name, p_comany_name);
  else
  l_person := dwn_person(p_name);
  end if;
 return l_person;
end;

begin
  l_person1 := create_person('Flip', 'Fluitketel', null);
  dbms_output.put_line('l_person1 is a '||l_person1.instance_of);
  l_person2 := create_person('Hatseflats', null, null);
  dbms_output.put_line('l_person2 is a '||l_person2.instance_of);
  l_person3 := create_person('Hatseflats', null, 'Hatseflats Inc.');
  dbms_output.put_line('l_person3 is a '||l_person3.instance_of);
end;

All three persons are declared as a 'DWN_PERSON'. So when I call the instance_of method of the particular object I expected that the method of the particular declared type is called. But, a little to my surprise, it turns out that Oracle uses the method of the Instantiated type:

l_person1 is a DWN_NATURAL_PERSON
l_person2 is a DWN_PERSON
l_person3 is a DWN_NOT_NATURAL_PERSON

Simple, but apparently very effective.

Already four and a half year ago I wrote an article on Oracle Type inheritance and type casting.  See here. But although Object types are supported by Oracle since Oracle 8, it seems to me that there is still little familiarity on the subject.
I'm not really a Pl/Sql programmer on daily basis anymore, the language is still close to me. And working with object types is a favorite subject to me ever since I discovered the endless use in Oracle 8i. Know Object types and you can do everything in Oracle.

Today I got a comment on the article, asking on if there is a solution on "downcasting" types. The subject in the mentioned article was that from an API (in the example from an EBusiness Suite12  API) you get an object type to which you want to add functionality without re-implementing/changing the delivered object type. The only thing to do it is to sub-type the delivered type. But how to come from an instance of a supertype to the use it as the subtype. Well, the answer is two fold:

1. If the instance of the super type is actually instantiated as the supertype then you actually can't. As explained in the mentioned article, the only way is to instantiate the subtype with the attributes of the supertype. For what I think I found a smart generic solution.
2. If you get an instance of the supertype, that was actually instantiated as the subtype then you can copy it again to a variable based on the subtypehttp://blog.darwin-it.nl/ and your good to go.

Lets elaborate on the second option. I'll do a dry swim, so the code presented is not tested in a database, so might not be free from syntax errors.

In Java there is a  statement called "InstanceOf". In Oracle SQL you have the "Is Of"construct. I found a forum post with the question about an "InstanceOf" analogy in Pl/Sql. As in the forum is stated the "Is Of" clause is in fact a possible where clause condition in a query. See also the documentation.

However in the post I saw a smart comment: create an overloaded procedure:

create procedure p_x(p_obj super_type);

do supertype code here



create procedure p_x(p_obj sub_type);

do subtype code here 

Now let's say we have a type named 't_car' and a subtype say 't_opel_zafira under t_car':

create type t_car as object
(license_plate varchar2(10) )
/

create type t_opel_zafira under t_car 
(model varchar2(10))
/

Then you could create an overloaded function like

create function instance_of(p_obj t_car ) returns varchar2
as
begin
  return 't_car';
end;

create function instance_of(p_obj t_opel_zafira ) returns varchar2
as
begin
  return 't_opel_zafira';
end;

Then you could indeed do something like:

declare
  l_car t_car;
  l_opel_zafira t_opel_zafira;
begin
  l_car := instance_opel_zafira_as_car(...); -- This functions returns a value of type t_car but is in fact an Opel Zafira
  if instance_of(l_car) = 't_opel_zafira' then
    dbms_output.put_line('Enjoy your Opel Zafira');
  end if;
end;

I should try it, but you should even be able to add a such an instance_of member function to each object type. Going to try that tomorrow.

Regularly I create VM's for courses as a 'Virtual Course Environment'. Earlier I did an install of Oracle Enterprise Manager 12c. But now there was a request for an integrated BI Publisher install.

Having an OEM12c install, the integration with BI Publisher with OEM12c is pretty straightforward. The install guide for this integration can be found here. This guide is part of the OEM12c 12.1.0.1 version, and for that version Oracle Business Intelligence Enterprise Edition 11g version 11.1.1.5. is required.

My OEM12c install is of version 12.1.0.2.0, so one patch set newer. Apparently the current OEM12c release is 12.1.0.3 (as of november '13).

The 11.1.1.6 release of BI-Publisher can be downloaded here. I initially installed the current 11.1.1.7 release that I downloaded here. However, when I ran the configureBIP script, from {OEM12c Home}/oms/bin, I got the following error:

[oracle@darlin-vce-db bin]$ ./configureBIP
Error: This script requires that BI EE Version "11.1.1.6.0" be installed, but "11.1.1.7.0" has been installed

So it's important to note that OEM12c install is of version 12.1.0.2.0 expects Oracle Business Intelligence Enterprise Edition 11g version 11.1.1.6.

By the way, the BI Publisher integration guide of OEM12c release 12.1.0.2.0  is found here. And 'surprisingly' it indeed states that it expects BI-Publisher 11.1.1.6. So, I could have known...