Darwin-IT: March 2014

Friday, 28 March 2014

JDeveloper BPMN Bug: Activity Name conflict - Follow Up

A few weeks ago I reported about my experiences with JDeveloper 11g PS6 in Malta. I had to borrow a laptop to do the Adaptive Case Management workshop. It was a HP laptop with Ubuntu 12. Somehow this combination with VirtualBox 4.3.6 lead into a bug in JDeveloper. Creating a new project would introduce a dash ('-') in the ID of each created activity.

In the mean time I have a new laptop, a very nice Asus N56. This morning I was in the opportunity to startup the VM that I exported and backed up from the borrowed laptop. And what a surprise: creating a new project just works! No naming conflicts. Also, of course, creating a new activity is ok.

A strange case indeed.

Thursday, 27 March 2014

Hierarchical XML from SQL

Years ago I wrote an article (in Dutch) on the XML functions in Oracle SQL. It can be found here.
It describes how to create an xml document as an XMLType with an Oracle SQL Query.

The query that is described is based on a pretty simple table, with no relationships. I'm creating a new course based on a datamodel we created years ago, that contains data. I wanted to abstract some of that data as xml, but then: how about the foreign key relations?

It turns out pretty straightforward, that you can probably figure out yourself. But hey, I'm not a bad guy, so how about sharing it to you?

The query selects employees with their addresses and goes as follows:

select xmlelement("emp:employees"
  , xmlattributes( 'http://xmlnls.darwin-it.nl/doe/xsd/v1/employee' as "xmlns"
                 , 'http://xmlnls.darwin-it.nl/doe/xsd/v1/employee' as "xmlns:emp"
                 , 'http://xmlnls.darwin-it.nl/doe/xsd/v1/address' as "xmlns:ads")
  , xmlagg
  ( xmlelement
    ( "emp:employee"
    , xmlforest
      ( emp.title as "emp:title"
      , emp.firstname as "emp:firstName"
      , emp.last_name as "emp:lastName"
      , emp.gender as "emp:gender"
      , emp.birth_date as "emp:birthDate"
      )
    , ( select xmlelement( "emp:addresses"
        , xmlagg(
             xmlelement("ads:address"
             , xmlattributes( ate.code as "type"
                            , ate.description as "description")
             , xmlforest
               ( ads.adress_line1 as "ads:addressLine1"
               , ads.adress_line2 as "ads:addressLine2"
               , ads.adress_line3 as "ads:addressLine3"
               , ads.postal_code as "ads:postalCode"
               , ads.city as "ads:city"
               , ads.country as "ads:country"
               )
             )
          )
        )
        from doe_party_addresses pae
        join doe_addresses ads on ads.id = pae.ads_id
        join doe_address_types ate on ate.id= pae.ate_id
        where pae.emp_id = emp.id
        )
      )
    )
  )  xml
from doe_employees emp;

The doe_addresses table is joined with the doe_employees table via a couple-table named doe_party_addresses. This is because an employee can have multiple addresses, but with different types. An employee can have a Business address and a Home address. Like a customer can have a shipping, billing and visiting addresses. Also an address can be used by multiple parties. You see here that the addresses are selected as a subselect. The output of the sub-select is an XMLType that can be embedded in the xmlelement (and other xml-) functions. What you also see is that I added namespace declarations as xmlattributes on the top-level element. The element and attribute names are prefixed with the corresponding 'emp:' or 'ads:' namespace-prefixes. Oh, and the output of the query is something like:

<?xml version="1.0" encoding="UTF-8" ?>
<emp:employees xmlns="http://xmlnls.darwin-it.nl/doe/xsd/v1/employee"
               xmlns:emp="http://xmlnls.darwin-it.nl/doe/xsd/v1/employee"
               xmlns:ads="http://xmlnls.darwin-it.nl/doe/xsd/v1/address">
  <emp:employee>
    <emp:title>Mr.</emp:title>
    <emp:firstName>Ed</emp:firstName>
    <emp:lastName>Bushes</emp:lastName>
    <emp:gender>M</emp:gender>
    <emp:birthDate>1968-01-20</emp:birthDate>
    <emp:addresses>
      <ads:address type="HOME" description="Home address">
        <ads:addressLine1>Maasstraat 19</ads:addressLine1>
        <ads:postalCode>3812HS</ads:postalCode>
        <ads:city>Amersfoort</ads:city>
        <ads:country>NETHERLANDS</ads:country>
      </ads:address>
    </emp:addresses>
  </emp:employee>
  <emp:employee>
    <emp:title>Mr.</emp:title>
    <emp:firstName>M.</emp:firstName>
    <emp:lastName>Outback</emp:lastName>
    <emp:gender>M</emp:gender>
    <emp:birthDate>1961-10-14</emp:birthDate>
    <emp:addresses>
      <ads:address type="HOME" description="Home address">
        <ads:addressLine1>Rocky Road 2</ads:addressLine1>
        <ads:postalCode>20001</ads:postalCode>
        <ads:city>StoneHench</ads:city>
        <ads:country>UNITED KINGDOM</ads:country>
      </ads:address>
    </emp:addresses>
  </emp:employee>
  <emp:employee>
    <emp:title>Mr.</emp:title>
    <emp:firstName>M.</emp:firstName>
    <emp:lastName>Outback</emp:lastName>
    <emp:gender>M</emp:gender>
    <emp:birthDate>1961-10-14</emp:birthDate>
    <emp:addresses>
      <ads:address type="HOME" description="Home address">
        <ads:addressLine1>Hofvijver 12</ads:addressLine1>
        <ads:postalCode>2000XX</ads:postalCode>
        <ads:city>Den Haag</ads:city>
        <ads:country>NETHERLANDS</ads:country>
      </ads:address>
    </emp:addresses>
  </emp:employee>
  <emp:employee>
    <emp:title>Ms.</emp:title>
    <emp:firstName>Pat</emp:firstName>
    <emp:lastName>Darwin</emp:lastName>
    <emp:gender>F</emp:gender>
    <emp:birthDate>1980-03-14</emp:birthDate>
    <emp:addresses>
      <ads:address type="HOME" description="Home address">
        <ads:addressLine1>Rijnzathe 6</ads:addressLine1>
        <ads:postalCode>3140ZP</ads:postalCode>
        <ads:city>De Meern</ads:city>
        <ads:country>NETHERLANDS</ads:country>
      </ads:address>
    </emp:addresses>
  </emp:employee>
  <emp:employee>
    <emp:title>Mr.</emp:title>
    <emp:firstName>T.</emp:firstName>
    <emp:lastName>Barakus</emp:lastName>
    <emp:gender>M</emp:gender>
    <emp:birthDate>1970-02-11</emp:birthDate>
    <emp:addresses>
      <ads:address type="HOME" description="Home address">
        <ads:addressLine1>Erasmusstraat 312</ads:addressLine1>
        <ads:postalCode>1234GK</ads:postalCode>
        <ads:city>Rotterdam</ads:city>
        <ads:country>NETHERLANDS</ads:country>
      </ads:address>
    </emp:addresses>
  </emp:employee>
  <emp:employee>
    <emp:title>Ms.</emp:title>
    <emp:firstName>Debby</emp:firstName>
    <emp:lastName>Waters</emp:lastName>
    <emp:gender>F</emp:gender>
    <emp:birthDate>1982-01-20</emp:birthDate>
    <emp:addresses>
      <ads:address type="WORK" description="Work address">
        <ads:addressLine1>Darwinplein 11</ads:addressLine1>
        <ads:postalCode>4321PS</ads:postalCode>
        <ads:city>Amsterdam</ads:city>
        <ads:country>NETHERLANDS</ads:country>
      </ads:address>
      <ads:address type="HOME" description="Home address">
        <ads:addressLine1>Newtonweg 41</ads:addressLine1>
        <ads:postalCode>6543AB</ads:postalCode>
        <ads:city>Maasland</ads:city>
        <ads:country>NETHERLANDS</ads:country>
      </ads:address>
    </emp:addresses>
  </emp:employee>
  <emp:employee>
    <emp:title>Mr.</emp:title>
    <emp:firstName>Wally</emp:firstName>
    <emp:lastName>Waters</emp:lastName>
    <emp:gender>M</emp:gender>
    <emp:birthDate>1963-06-13</emp:birthDate>
    <emp:addresses></emp:addresses>
  </emp:employee>
</emp:employees>

2014-04-25, update: I changed the namespaces and I found a curiosity in my database content: all birthdates were in the future...

Tuesday, 25 March 2014

OSB: Remove artefacts with wlst

In my current project I needed to restructure my projects. Where I had all projects in one configuration, I wanted multiple configurations to do partial deployments. So I created configurations per highlevel business service, and merged canonical and application specific services into one osb project. But at deployment time, for OSB moved services are actually new services that conflict with the existing services at the current location, because now there are two versions of the same service with the same endpoint-uri exist. That is, it gets OSB in a conflicting state, that it can't activate. So I created a series of scripts to remove artefacts. My starting point was this Oracle forum entry.

  
import wlstModule

from java.util import Collections
from com.bea.wli.sb.util import Refs
from com.bea.wli.config import Ref
from com.bea.wli.sb.management.configuration import SessionManagementMBean
from com.bea.wli.sb.management.configuration import ALSBConfigurationMBean
#from com.bea.wli.config.component import AlreadyExistsException

import sys


#=======================================================================================
# Entry function to delete a  project from the alsbConfiguration 
#=======================================================================================
def deleteProject(alsbConfigurationMBean, projectName):
     try:
          print "Trying to remove " + projectName
          projectRef = Ref(Ref.PROJECT_REF, Ref.DOMAIN, projectName)                  
          if alsbConfigurationMBean.exists(projectRef):
               print "#### removing OSB project: " + projectName
               alsbConfigurationMBean.delete(Collections.singleton(projectRef))
               print "#### removed project: " + projectName
          else:
               failed = "OSB project <" + projectName + "> does not exist"
               print failed
          print
     except:
          print "Error whilst removing project:", sys.exc_info()[0]
          raise
  
#=======================================================================================
# Entry function to undeploy a project from an OSB Configuration
#=======================================================================================
def undeployProjectFromOSBDomain(projectName):
     try:
          domainRuntime()      
          sessionName  = "UndeployProjectStateSession_" + str(System.currentTimeMillis())         
          sessionManagementMBean = findService(SessionManagementMBean.NAME,SessionManagementMBean.TYPE)
          print "SessionMBean started session"
          sessionManagementMBean.createSession(sessionName)
          print 'Created session <', sessionName, '>'
          alsbConfigurationMBean = findService(ALSBConfigurationMBean.NAME + "." + sessionName, ALSBConfigurationMBean.TYPE)
          deleteProject(alsbConfigurationMBean, projectName)   
          sessionManagementMBean.activateSession(sessionName, "Complete project removal with customization using wlst")
     except:
          print "Error whilst removing project:", sys.exc_info()[0]
          discardSession(sessionManagementMBean, sessionName)
          raise

#=======================================================================================
# Entry function to undeploy multiple projects from an OSB Configuration
#=======================================================================================
def undeployProjectsFromOSBDomain(projectNames):
     try:
          domainRuntime()      
          sessionName  = "UndeployProjectStateSession_" + str(System.currentTimeMillis())         
          sessionManagementMBean = findService(SessionManagementMBean.NAME,SessionManagementMBean.TYPE)
          print "SessionMBean started session"
          sessionManagementMBean.createSession(sessionName)
          print 'Created session <', sessionName, '>'
          alsbConfigurationMBean = findService(ALSBConfigurationMBean.NAME + "." + sessionName, ALSBConfigurationMBean.TYPE)
          for projectName in projectNames:
            print '\nDelete project '+projectName
            deleteProject(alsbConfigurationMBean, projectName)   
          sessionManagementMBean.activateSession(sessionName, "Complete project removal with customization using wlst")
     except:
          print "Error whilst removing project:", sys.exc_info()[0]
          discardSession(sessionManagementMBean, sessionName)
          raise

def discardSession(sessionManagementMBean, sessionName):
     if sessionManagementMBean != None:
          if sessionManagementMBean.sessionExists(sessionName):
               sessionManagementMBean.discardSession(sessionName)
               print "Session discarded"

I did a little restructure so that I can remove multiple projects in one session. This is needed since some projects are inter-related. So must be removed together to get a consistent state. To remove multiple projects I have the following main function:

def main():
  projectNames=["Project1", "Project2", "Project3", "Project4", "Project5", "Project6", "Project7", "Etc." ]
  undeployProjectsFromOSBDomain(projectNames)
# Call the main function
main()

I call this script from an ANT based patch-framework. To succesfully call this wlst script you need the following jar files in your classpath:

${osb.home}/modules/com.bea.common.configfwk_1.7.0.0.jar
${osb.home}/lib/sb-kernel-api.jar
${osb.home}/sb-kernel-impl.jar
${osb.home}/osb-coherence-client.jar (I don't understand why, but this is needed for the Ref-class that is also available in the com.bea.common.configfwk_1.7.0.0.jar)

But you should also be ok with:

${osb.home}/modules/com.bea.common.configfwk_1.7.0.0.jar
${osb.home}/lib/alsb.jar (this jar contains references to the other alsb-jar files in the lib folder in it's manifest file)

Here '${osb.home}' relates to the 'Oracle_OSB1' folder in your Fusion Middleware Home.
Based on the scripts above I also created a function to remove a single Proxy service:

#=======================================================================================
# Entry function to find a proxy service
#=======================================================================================
def findProxyService(folder, serviceName, sessionName):
  print "Find proxy service: " + folder + "/" + serviceName
  pxyConf = "ProxyServiceConfiguration." + sessionName
  mbean = findService(pxyConf, 'com.bea.wli.sb.management.configuration.ProxyServiceConfigurationMBean')
  folderRef = Refs.makeParentRef(folder + '/')
  serviceRef = Refs.makeProxyRef(folderRef, serviceName)
  return serviceRef, mbean
#=======================================================================================
# Entry function to remove a proxyservice from OSB configuration 
#=======================================================================================
def undeployProxyFromOSBDomain(relativePath, proxyServiceName):
     try:
          domainRuntime()
          
          sessionName  = "UndeployProxySession_" + str(System.currentTimeMillis())
          print "Trying to remove " + proxyServiceName
          
          sessionManagementMBean = findService(SessionManagementMBean.NAME,SessionManagementMBean.TYPE)
          print "SessionMBean started session"
          sessionManagementMBean.createSession(sessionName)
          print 'Created session <', sessionName, '>'
          serviceRef, sessionBean = findProxyService(relativePath, proxyServiceName, sessionName)
          alsbConfigurationMBean = findService(ALSBConfigurationMBean.NAME + "." + sessionName, ALSBConfigurationMBean.TYPE)
          if alsbConfigurationMBean.exists(serviceRef):
            print "#### removing OSB proxy service: " + proxyServiceName
            alsbConfigurationMBean.delete(Collections.singleton(serviceRef))
            sessionManagementMBean.activateSession(sessionName, "Complete service removal with customization using wlst")
          else:
            failed = "OSB project <" + proxyServiceName + "> does not exist"
            print failed
            discardSession(sessionManagementMBean, sessionName)
            #raise Failure(failed)
          print
     except:
          print "Error whilst removing project:", sys.exc_info()[0]
          discardSession(sessionManagementMBean, sessionName)
          raise

It should not be too hard to create one for business services out of this one. Removing folders is slightly easier:

#=======================================================================================
# Entry function to remove a folder from OSB configuration 
#=======================================================================================
def removeFolderFromOSBDomain(folder):
     try:
          domainRuntime()
         
          sessionName  = "RemoveFolderSession_" + str(System.currentTimeMillis())
          print "Trying to remove " + folder
          
          sessionManagementMBean = findService(SessionManagementMBean.NAME,SessionManagementMBean.TYPE)
          print "SessionMBean started session"
          sessionManagementMBean.createSession(sessionName)
          print 'Created session <', sessionName, '>'
          folderRef = Refs.makeParentRef(folder)
          alsbConfigurationMBean = findService(ALSBConfigurationMBean.NAME + "." + sessionName, ALSBConfigurationMBean.TYPE)
          if alsbConfigurationMBean.exists(folderRef):
            print "#### removing OSB folder: " + folder
            alsbConfigurationMBean.delete(Collections.singleton(folderRef))
            sessionManagementMBean.activateSession(sessionName, "Complete service removal with customization using wlst")
          else:
            failed = "OSB folder <" + folder + "> does not exist"
            print failed
            discardSession(sessionManagementMBean, sessionName)
            #raise Failure(failed)
          print
     except:
          print "Error whilst removing project:", sys.exc_info()[0]
          discardSession(sessionManagementMBean, sessionName)
          #raise

Don't forget the import statements from the first script for these latter ones.

Thursday, 20 March 2014

OSB 11g For-each: is index variable an integer?

In OSB you can add a for-each construct to loop over a set of elements:

Today I encountered something strange with the index variable of the for-each. At one stage I wanted to select an element based on the index-variable, in the screendump above it is 'functieIndex'.
The xpath expression I created was something like:

$body/maat:requestDoc/maat:dataElement/maat:functies/maat:functie[$functieIndex]/text()

But although I checked the value of the variable functieIndex using alerts, I always got the complete set of 'functie'-elements. But when I explicitly filled in '1' as a index I got the denoted element, like this:

$body/maat:requestDoc/maat:dataElement/maat:functies/maat:functie[1]/text()

I checked with a co-worker, and he confirmed that what I did was just how it should work. Now, thinking my brains almost to cooking temperature, I came up with the idea: 'what if it does not know it is a number?'. So I explicitly casted the $functieIndex variable to int:

$body/maat:requestDoc/maat:dataElement/maat:functies/maat:functie[xs:int($functieIndex)]/text()

And this works! Apparently somehow OSB does not know that the variable it declares for the sake of indexing a for-each is an integer by nature.

By the way, in this example I get the value also by the for-each variable. But sometimes You need the index-variable to index from another element-set.

Tuesday, 18 March 2014

JDeveloper 11gR1 XSL Mapper annoyances

I've been working with the JDeveloper 11gR1 XSL Mapper for years. And I think I became quite handy with it. I find it a great, impressing tool, since you can actually drag and drop pretty much everything together. When you draw a line, you can drop a function on it. When you think a little about the sequence of drag&drops you can tie up a pretty complex logical expression together. However, there are a few annoyances that I keep running into. I think they're bugs, or at least very unwanted behaviors. Since I keep running into them, I'll write them down, so maybe it will be picked up by the development team, or at least you as a reader can take advantage of them.

Call-templates within for-each

The first one is a very tricky one.
If you add a for-each construct in your xsl-map, all the expressions below them should be relative to the select attribute in the for-each construct. And the XSL-mapper does a pretty good job in keeping track of these relative xpath-expressions. Except in parameters of within the call-template constructs!!!
This is very risky business, since you need to edit each occurence of a call to a user-defined template by hand. And you need to check this pretty much every time you did a change of the xsl-map within the graphical designer. Because it messes it up every time.
Here's an example of a not null-check of an element from the database adapter. You see that the xpath of the if-test should be the same as the path in the select of the with-param. Except for the not(..@xsi:nil) construct in the if-test of course.

                  <xsl:if test="not(eigmat:personen/dbfuncmat:geslachtsaanduiding/@xsi:nil)">
                    <psn:geslachtsaanduiding>
                      <xsl:call-template name="lookupGeslachtCDD">
                        <xsl:with-param name="sourceValue"
                                        select="/eigmat:rootElement/eigmat:subElement/eigmat:personen/dbfuncmat:geslachtsaanduiding"/>
                      </xsl:call-template>

Formatting when global variables or user-defined templates

The second one is annoying, but not so functional problematic. When you declare variables at the beginning of your xsl-map, before the main template, then after an edit in the graphical map-designer, the formatting is a little messy. It will put the first variable-declaration behind the opening tag of the xsl-map. Then it places the second attribute on the next line, but indents it and starts the second variable on the same line, and so on:

                exclude-result-prefixes="xsi xsl eigmat dbloc dbfuncmat xsd loc ns5 psn version ns1 mat dom jdz bas rel xp20 bpws bpel bpm ora socket mhdr oraext dvm hwf med ids xdk xref bpmn ldap"><!-- globale variabele declaraties --><xsl:variable name="targetApplCode"
                                                                                                                                                                                                                                                          select='"NUT"'/><xsl:variable name="sourceApplCode"
                                                                                                                                                                                                                                                                                        select='"APE"'/>

About the same occurs with the user defined templates after the main template:

  </xsl:template>  <xsl:template name="getCurrentTime">
    <xsl:value-of select='sdf:format(sdf:new("hh:mm:ss"),date:new())'/>
  </xsl:template> <xsl:template name="

It can be solved quite easy by giving an enter after the opening tag of the xsl-map or, for the templates, after the closing tag of the main template. Then do a reformat of the xsl-map and the formatting is neat again.

Friday, 14 March 2014

Run XQuery with XqlPlus

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.