Wednesday, 28 November 2018

Using ANT to investigate JCA adapters

My current customer has a SOA Suite implementation dating from the 10g era. They use many queues (JMS serves by AQ) to decouple services, which is in essence a good idea.

However, there are quite a load of them. Many composites have several adapter specifications that use the same queue, but with different message selectors. But also over composites queues are shared.

There are a few composites with ship loads of .jca files. You would like to replace those with a generic adapter specification, but you might risk eating messages from other composites. This screendump is an anonymised of one of those, that actually still does not show every adapter. They're all jms adapter specs actually.

So, how can we figure out which queues are used by which composites and if they read or write?
I wanted to create a script that reads every .jca file in our repository and write a line to a CSV file for each JCA file, containing:
  • Name of place
  • Name of the jca file
  • Type of the adapter
  • Is it an activation (consume) or interaction (type)
  • What is the location (eis JNDI )
  • Destination
  • Payload
  • Message selector (when consumption)
Amongst some other properties.

Using ANT to scan jca Files

I found that ANT is more than capable for the job. I put my project on GitHub, so you can all the files there.

First let's talk the first parts of scanJCAFiles.xml.

Since I want to know the project that the .jca file belongs to, I first select all the .jpr files in the repository. Because the project folders are spread over the repository, although structured they're not neatly in a linear row of folders, finding the .jpr files gives me a list of all the projects. 
  <!-- Initialisatie -->
  <target name="clean" description="Clean the temp folder">
    <delete dir="${jcaTempDir}"/>
    <mkdir dir="${jcaTempDir}"/>
  </target>
  <!-- Perform all -->
  <target name="all" description="Scan All SOA applications" depends="clean">
    <echo>FMW_HOME=${fmw.home}.</echo>
    <echo file="${outputFile}" append="false"
          message="project name,jcaFile,adapter-config-name,adapter-type,connection factory location,endpoint type,class,DestinationName,QueueName,DeliveryMode,TimeToLive,UseMessageListener,MessageSelector,PayloadType,ObjectFieldName,PayloadHeaderRequired,RecipientList,Consumer${line.separator}"></echo>
    <foreach param="project.file" target="handleProject" delimiter=';' inheritall="true">
      <path>
        <fileset id="dist.contents" dir="${svnRoot}" includes="**/*.jpr"/>
      </path>
    </foreach>
  </target>
Side note, as can be seen in the snippet, I re-create a folder for transformed jca files (as described later) and I create a new output file, in which I write a header row with all the column names, using echo to a file with the append properties to false.

So, I do a foreach over a fileset, using the svnRoot property in the build.properties, that includes ever .jpr file anywhere in the structure. For each file the handleProject target is called with the file in the project.file property. Foreach is an antcontrib addition to ANT. So you need to add that as a task definition (one thing I do as a first thing).

  <taskdef resource="net/sf/antcontrib/antlib.xml">
    <classpath>
      <pathelement location="${ant-contrib.jar}"/>
    </classpath>
  </taskdef>

With the name of the .jpr file I have the name of the project and the location:
  <target name="handleProject">
    <echo message="projectFile: ${project.file}"></echo>
    <dirname property="project.dir" file="${project.file}"/>
    <echo message="project dir: ${project.dir}"></echo>
    <basename property="project.name" file="${project.file}" suffix=".jpr"/>
    <foreach param="jca.file" target="handleJca" delimiter=";" inheritall="true">
      <path>
        <fileset id="dist.contents" dir="${project.dir}" includes="**/*.jca"/>
      </path>
    </foreach>
  </target>

In this snippet the dirname ANT task trims the filename from the project.file property, to provide me the project folder into the project.dir property. The project.name can be determined from the project.file using the basename task. Nice touch is that it allows you to trim the suffix (.jpr) from it. Within the project location I can find all the .jca file, and in the same way as the .jpr files I can use a foreach on the project.dir and call the handleJca target for each .jca file.

Using XSL to pre-process the jca files

Fortunately, jca files are simply XML files and ANT turns out to be able to read XML as a property file, using the xmlproperty task, which came in handy. Those properties can be appended to an output file using echo, very easily.

However, there are two main problems with the structure of the jca files:
  1. The jca files for the interaction type (the write kind) are different from the activation type (the read kind), So I would need to distinguish those.
  2. The properties like DestinationName, payload and message selector are name value pair properties in the .jca file. The  interprets the names of the properties as separate property values with the name. I can't select specifically the Destination Name for instance.
So I decided to create an xml stylesheet to transform the JCA files to a specific schema, that merges the endpoint interaction and activation elements and has the properties I'm interested in as separate elements. To do so, I created an xsd from both types of jca files. JDeveloper can help me with that:
Just follow the wizard, but emtpy the target namespace. As said I did this for both kinds of jca files (the interaction and activation kinds) and merge them into jcaAdapter.xsd with a xsd:choice:

Out of that I created jcaAdapterProps.xsd where the xsd:choice elements are merged into spec element. I changed the targetnamespace and created specific property elements:
That allowed me to create the XSL Map jcaAdapter.xsl easily:

For the xmlproperty task it is important that the resulting xml is in a default namespace and that the elements depend on the default namespaces, they should not reference a specific namespace (not even the default one).

With that I can finish off with the handleJca target of my script:
  <target name="handleJca">
    <basename property="jca.file.name" file="${jca.file}"/>
    <property name="jca.file.props" value="${jcaTempDir}/${jca.file.name}.props"/>
    <echo message="Jca File: ${jca.file.name}"></echo>
    <xslt style="${jcaPropsXsl}" in="${jca.file}" out="${jca.file.props}"/>
    <xmlproperty file="${jca.file.props}" collapseattributes="true"/>
    <!-- see https://ant.apache.org/manual/Tasks/xmlproperty.html -->
    <property name="cf.location" value="${adapter-config.connection-factory.location}"/>
    <property name="ep.class" value="${adapter-config.endpoint.spec.className}"/>
    <property name="ep.type" value="${adapter-config.endpoint.spec.type}"/>
    <property name="ep.DestinationName" value="${adapter-config.endpoint.spec.DestinationName}"/>
    <property name="ep.DeliveryMode" value="${adapter-config.endpoint.spec.DeliveryMode}"/>
    <property name="ep.TimeToLive" value="${adapter-config.endpoint.spec.TimeToLive}"/>
    <property name="ep.UseMessageListener" value="${adapter-config.endpoint.spec.UseMessageListener}"/>
    <property name="ep.MessageSelector" value="${adapter-config.endpoint.spec.MessageSelector}"/>
    <property name="ep.PayloadType" value="${adapter-config.endpoint.spec.PayloadType}"/>
    <property name="ep.QueueName" value="${adapter-config.endpoint.spec.QueueName}"/>
    <property name="ep.ObjectFieldName" value="${adapter-config.endpoint.spec.ObjectFieldName}"/>
    <property name="ep.PayloadHeaderRequired" value="${adapter-config.endpoint.spec.PayloadHeaderRequired}"/>
    <property name="ep.RecipientList" value="${adapter-config.endpoint.spec.RecipientList}"/>
    <property name="ep.Consumer" value="${adapter-config.endpoint.spec.Consumer}"/>
    <echo file="${outputFile}" append="true"
          message="${project.name},${jca.file.name},${adapter-config.name},${adapter-config.adapter},${cf.location},${ep.type},${ep.class},${ep.DestinationName},${ep.QueueName},${ep.DeliveryMode},${ep.TimeToLive},${ep.UseMessageListener},${ep.MessageSelector},${ep.PayloadType},${ep.ObjectFieldName},${ep.PayloadHeaderRequired},${ep.RecipientList},${ep.Consumer}${line.separator}"></echo>
  </target>
With the xslt task the jca file is transformed to the jcaTempDir folder. And using the xmlproperty task the transformed .jca is read as an xml property file. Because the property references are quite long, I copy them in a shorter named property and then echo them as a comma separated line into the outputFile using the append attribute to true.

Note that I used collapseattributes attribute set to true.

Conclusion

And that is actually about it. ANT is very handy to find and process files in a controlled way. Also the combination with XSL makes it powerfull. In this project I concentrated on JMS and AQ adapters, as far as the properties are concerned. But you can extend this for DB Adapters and File Adapters, etc. quite easily. Maybe even create an output file per type.

I can't share the output with you, due to company policy contraints. Just try it out.




Thursday, 22 November 2018

How to query your JMS over AQ Queues

At my current customer we use queues a lot. They're JMS queues, but in stead of Weblogic JMS, they're served by the Oracle database.

This is not new, in fact the Oracle database supports this since 8i through Advanced Queueing. Advanced Queueing is Oracle's Queueing implementation based on tables and views. That means you can query the queue table to get to the content of the queue. But you might know this already.

What I find few people know is that you shouldn't query the queue table directly but the accompanying AQ$ view instead. So, if your queue table is called MY_QUEUE_TAB, then you should query AQ$MY_QUEUE_TAB. So simply prefix the table name with  AQ$. Why? The AQ$ view is created automatically for you and joins the queue table with accompanying IOT tables to give you a proper and convenient representation of the state, subscriptions and other info of the messages. It is actually the supported wat of query the queue tables.

A JMS queue in AQ is implemented by creating them in queue tables based on the Oracle type
sys.aq$_jms_text_message type.

That is in fact a quite complex type definition that implements common JMS Text Message based queues. There are a few other types to support other JMS message types. But let's leave that.

Although the payload of the queue table is a complex type, you can get to its attributes in the query using the dot notation. But for that it is mandatory to have a table shortname and prefix the view columns with the table shortname.

The sys.aq$_jms_text_message has a few main attributes, such as text_lob for the content and header for the JMS header attributes. The header is based on the type sys.aq$_jms_header. You'll find the JMS type there. But also the properties attribute based on sys.aq$_jms_userproparray. That in its turn  is a varray based on aq$_jms_userproperty. Now, that makes it a bit complex, because we would like to know the values of the JMS properties, right?

We use those queues using the JMS adapter of SOA Suite and that adds properties containing the composite instance ID, ECID, etcetera. And if I happen to have a message that isn't picked up, it would be nice to know which Composite Instance enqueued this message, wouldn't it?

Luckily, a Varray can be considered as a collection of Oracle types. And do you know you  can query those? Simply provide it to the table() function and Oracle threats it as a table. When you know which properties you may expect, and their types, you can select them in the select clause of your query.  I found the properties that are set by SOA Suite and added them to my query. But you could find others as well.

Putting all this knowledge together, I came up with the following  query:

select qtb.queue
, qtb.msg_id
, qtb.msg_state
,qtb.enq_timestamp
--,qtb.user_data.header.replyto
,qtb.user_data.header.type type
,qtb.user_data.header.userid userid
,qtb.user_data.header.appid appid
,qtb.user_data.header.groupid groupid
,qtb.user_data.header.groupseq groupseq
--, qtb.user_data.header.properties properties
, (select str_value from table (qtb.user_data.header.properties) prp where prp.name = 'tracking_compositeInstanceId') tracking_compositeInstanceId
, (select str_value from table (qtb.user_data.header.properties) prp where prp.name = 'JMS_OracleDeliveryMode') JMS_OracleDeliveryMode
, (select str_value from table (qtb.user_data.header.properties) prp where prp.name = 'tracking_ecid') tracking_ecid
, (select num_value from table (qtb.user_data.header.properties) prp where prp.name = 'JMS_OracleTimestamp') JMS_OracleTimestamp
, (select str_value from table (qtb.user_data.header.properties) prp where prp.name = 'tracking_parentComponentInstanceId') tracking_prtCptInstanceId
, (select str_value from table (qtb.user_data.header.properties) prp where prp.name = 'tracking_conversationId') tracking_conversationId
,qtb.user_data.header
,qtb.user_data.text_lob text
from AQ$MY_QUEUE_TAB qtb
where qtb.queue = 'MY_QUEUE'
order by enq_timestamp desc;

This delivered me an actual message that was not picked up by my process. And I could use  the property tracking_compositeInstanceId to find my soa composite instance in EM.

Very helpful if you are able to pause the consumption of your messages.

This also shows you how to query tables with complex nested tables.

Monday, 19 November 2018

URL Resolving in an Enterprise Deployment

A few blogs ago I wrote about issues we encountered with persistence of settings in an Enterprise Deployment with seperate Admin and Managed Server domains.

For one of the problems, the  mdm-url-resolver.xml, used to store the Global Tokens, we had a Service Request with support. After over a year, we got an answer from development, that as per design SOA updates will only update the mdm-url-resolver.xml in the soa managed server.

Besides the workaround in my previous article, there is a Java custom system property that refers to the mdm-url-resolver.xml you want to use:
-Doracle.soa.url.resolver.properties.file=/path-to-the/mdm-url-resolver.xml 

With this property set, SOA Suite will use this file, and does not have it affacted by the domain config.
I did not try it myself yet, but I think it is advisable to put this file on a shared disk. Otherwise you would need to create a copy of it for each managed server and update every one.
Unfortunately I did not find this Java system property in the documentation. I did find a blog that mentions it, but not where can be found the documentation.

So, for global tokens this seems a workable approach. But the same behavior we saw with the UMS Driver property files. I don't have a property like this for those property files. As soon as I find it, I will update this blog post.

Monday, 5 November 2018

List the server group memberships of your domain

Last few years I posted on installation of Fusion Middleware. One of the features of FMW12c is the concept of Server Groups. As can be read here: 'Server groups target Fusion Middleware applications and services to one or more servers by mapping defined application service groups to each defined server group.'

For Fusion Middleware Topology I found this article on SOASuite 12c topology from the A-team very helpful.

Server groups are set at creation of the domain. Relating to SOA and OSB it is important to determin where the WebServices Manager Policy Manager is targetted. By default SOA and OSB servers have the SOA-MGD-SVRS or OSB-MGD-SVRS-COMBINED respectively, which means that those servers/clusters have the Policy Manager targetted automatically. If OSB or SOA is the only component in the domain, then this is sufficient. But if you have a domain that combine those components (and/or BAM or MFT), then there should be a separate WSM cluster that target the PM, since you want it targetted to only one cluster. In that case SOA and OSB should have the server groups SOA-MGD-SVRS-ONLY and  OSB-MGD-SVRS-ONLY.

But how to know if you have targetted the proper server groups to your servers? You can use the getServerGroups([serverName]) wlst command for that.

I created a simple wlst script for it. Save the following script as listServerGroups.py. Change the domainHome variable at the top of the scripts to the location of your domain (I did not bothered to parameterize this...):


#############################################################################
# List ServerGroups for a domain
#
# @author Martien van den Akker, Darwin-IT Professionals
# @version 1.0, 2018-11-05
#
# Usage:
#   wlst listServerGroups.py
#
# When       Who                      What
# 20181105   Martien van den Akker    Create
#
#############################################################################
#
import sys, traceback
scriptName = sys.argv[0]
#
domainHome='/data/oracle/config/domains/soa_domain'
#
#
def main():
  readDomain(domainHome)
  cd('/')
  allServers=cmo.getServers()
  if (len(allServers) > 0):
    for wlserver in allServers:
      wlserverName = wlserver.getName()
      print('Groups of server: '+wlserverName)
      serverGroups=getServerGroups(wlserverName)
      for serverGroup in serverGroups:
        print('..'+serverGroup)
#
# Main
main() 

The getServerGroups() command is an wlst offline commmand, so you need to read the domain for it.
You can remove groups and or set new groups using the setServerGroups() command. If you do so, you need to update the domain (updateDomain()) and then close the domain (closeDomain()). And of course you need to restart your domain. This means by the way, also the AdminServer, since it needs to re-read the domain. It is even recommended to stop the domain before updating it in offline mode.

By the way, my scripts to create a FMW Domain also make use of the setServerGroups() command, as can be read in this article, but if you reuse them, make sure you have the correct ServerGroups set (Maybe I should parameterize those too).


Friday, 2 November 2018

MobaXterm 11.0

Recently I wrote about MobaXterm as a welcome replacement of Putty. Looking for a 64-bit version of MobaXterm, I found that they released version 11.0 only yesterday.

Nice: one of the improvements says:
  • Improvement: updated PuTTY-based SSH engine to the latest version
Another welcome improvement:
  • Improvement: improved SFTP / FTP / S3 sessions performances, especially when remote folder contains many files/folders
 However, did not found a specific 64 bit version.

Friday, 26 October 2018

Recursion in XSLT

Last week I helped someone on the Oracle community forums with transforming a comma separated string to a list of elements. He needed this to process each element in BPM Suite, but it is a use case that can come around in SOA Suite or even in Oracle Integration Cloud.

You would think that you could do something like a for-each and trimming the element from the variable.

Recursion

One typical thing with XSLT is that variables are immutable. That means that you can declare a variable and assign a value to it, but you cannot change it. So it is not possible to assign a new value to a variable based on a substring of that same variable.

To circumvent this, you should implement a template that conditionally calls itself until an end-condition is met. This is a typical algorithm called recursion. Recursion is a way of implementing a function that calls itself, for example to calculate the faculty of a number. Recursion can help circumventing the immutability of variables, because with every call to the function you can pass (a) calculated and thus different value(s) through the parameter(s).

I wrote about this earlier, but last week a co-worker asked a similar question, but just the other way around: transforming a list into a comma separated string.

So, apparently it's time to write an article about it.

Transforming CSV to a List

I refactored the xsd's from the question as follows. First the source xsd:
<?xml version="1.0" encoding="windows-1252" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:tns="http://www.example.org/Approvals/Source"
            targetNamespace="http://www.example.org/Approvals/Source" elementFormDefault="qualified">
  <xsd:element name="ApprovalRoute" type="tns:approvalRouteByInvoiceNatureResponse"/>
  <xsd:complexType name="approvalRouteByInvoiceNatureResponse">
    <xsd:sequence>
      <xsd:element type="xsd:string" name="approvalRoute" minOccurs="0"/>
      <xsd:element type="xsd:boolean" name="autoApprove" minOccurs="0"/>
    </xsd:sequence>
  </xsd:complexType>
</xsd:schema>

And the target schema is:
<?xml version="1.0" encoding="windows-1252" ?>
<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:tns="http://www.example.org/Approvals/Target"
            targetNamespace="http://www.example.org/Approvals/Target" elementFormDefault="qualified">
  <xsd:element name="ApprovalRoute" type="tns:ApprovalRouteType"/>
  <xsd:complexType name="ApprovalRouteType">
    <xsd:sequence>
      <xsd:element name="Approver" type="xsd:string" minOccurs="0" maxOccurs="unbounded"/>
    </xsd:sequence>
  </xsd:complexType>
</xsd:schema>

To start with, we have an ApprovalRoute element based on a complex type with the approvalRoute sub-element being the comma-separated list of approvers. Then as a target we have an ApprovalRoute, based on a list of Approver elements.

I generated the following source xml to transform:
<?xml version="1.0" encoding="UTF-8" ?>
<ApprovalRoute xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
               xsi:schemaLocation="http://www.example.org/Approvals/Source SOA/Schemas/Approvals-Source.xsd"
               xmlns="http://www.example.org/Approvals/Source">
  <approvalRoute>Approver1,Approver2,Approver3,Approver4,Approver5</approvalRoute>
  <autoApprove>true</autoApprove>
</ApprovalRoute>

Now, we need to split the approvalRoute value in a part before the first comma, and after the first comma. The value before the first comma can be put in an element. But the remainder has to be fed into the same template again. Then, at the end there is no comma in the remainder, so the part before the comma will be empty. There is no comma anymore, so we should not call the template with the remainder, but simply put the remainder in an element. Therefor, the non-existence of the comma can be the end-condition.

Remember, using recursion, you should always have a finalizing condition. To be honest, in my first piece of code in the answer of the question, I forgot about that. But, to my defence: I just put it together by heart and haven't been able to test.

The explanation above results in the following template:
<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0"
                xmlns:xp20="http://www.oracle.com/XSL/Transform/java/oracle.tip.pc.services.functions.Xpath20"
                xmlns:tns="http://www.example.org/Approvals/Target"
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                xmlns:ns0="http://www.example.org/Approvals/Source" xmlns:xsl="
                http://www.w3.org/1999/XSL/Transform">
  <!-- https://community.oracle.com/thread/4178385 -->
  <xsl:template match="/">
    <tns:ApprovalRoute>
      <xsl:call-template name="parseDelimitedString">
        <xsl:with-param name="delimitedStr" select="/ns0:ApprovalRoute/ns0:approvalRoute"/>
      </xsl:call-template>
    </tns:ApprovalRoute>
  </xsl:template>
  <xsl:template name="parseDelimitedString">
    <xsl:param name="delimitedStr"/>
    <!-- https://www.w3schools.com/xml/xsl_functions.asp -->
    <xsl:variable name="firstItem" select="substring-before($delimitedStr, ',')"/>
    <xsl:variable name="restDelimitedStr" select="substring-after($delimitedStr, ',')"/>
    <tns:Approver>
      <xsl:value-of select="$firstItem"/>
    </tns:Approver>
    <xsl:choose>
      <xsl:when test="contains($restDelimitedStr, ',')">
        <xsl:call-template name="parseDelimitedString">
          <xsl:with-param name="delimitedStr" select="$restDelimitedStr"/>
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <tns:Approver>
          <xsl:value-of select="$restDelimitedStr"/>
        </tns:Approver>
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>
</xsl:stylesheet>

(I created this as an XSL Map, but removed the comments that were included by JDeveloper.
I tested this with the following input:
<?xml version="1.0" encoding="UTF-8" ?>
<ApprovalRoute xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
               xsi:schemaLocation="http://www.example.org/Approvals/Source SOA/Schemas/Approvals-Source.xsd"
               xmlns="http://www.example.org/Approvals/Source">
  <approvalRoute>Approver1,Approver2,Approver3,Approver4,Approver5</approvalRoute>
  <autoApprove>true</autoApprove>
</ApprovalRoute>

And this resulted in the following output:
<?xml version = '1.0' encoding = 'UTF-8'?>
<tns:ApprovalRoute xmlns:tns="http://www.example.org/Approvals/Target" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://www.example.org/Approvals/Target file:/D:/Projects/2018-ODC/XSL-Demo/XSL-Demo/SOA/Schemas/Approvals-Target.xsd">
   <tns:Approver>Approver1</tns:Approver>
   <tns:Approver>Approver2</tns:Approver>
   <tns:Approver>Approver3</tns:Approver>
   <tns:Approver>Approver4</tns:Approver>
   <tns:Approver>Approver5</tns:Approver>
</tns:ApprovalRoute>

This I used for input for the following xslt.

The other way around: List to CSV

For didactional reasons I'll show the other way around too. Although, we'll see that this can be done easier.

In this case I mean to loop over a series of elements, starting with an index of 1, and adding the elements to a partial string. That means I have 3 parameters:
  • loopApprovers: the parent element, containing all the elements to loop over
  • index: the loop index, with a default of 1
  • partialApprovalRoute: the partial CSV list, defaulted to an empty string

The template loopApprovers can be called with only the approvalRoute. Then with an index of 1, the template is called recursively the first time, with a partialApprovalRoute assigned with the first Approver occurence and an index increased with 1.
For the other occurences where index > 1 and index <= count of elements, the template is called again recursively, but with an increased index and the indexed element added to the partialApprovalRoute separated with a comma.
Then the end situation is when the template is called where index exceeds the count of elements. Then just the partialApprovalRoute is 'returned'  (by the value-of instruction) where it is substringed to a 20000 characters:

<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0"
                xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                xmlns:xp20="http://www.oracle.com/XSL/Transform/java/oracle.tip.pc.services.functions.Xpath20"
                xmlns:ns0="http://www.example.org/Approvals/Target"
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                xmlns:tns="http://www.example.org/Approvals/Source" 
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <xsl:template match="/">
    <tns:ApprovalRoute>
      <tns:approvalRoute>
        <xsl:call-template name="loopApprovers">
          <xsl:with-param name="approvalRoute" select="/ns0:ApprovalRoute"/>
        </xsl:call-template>
      </tns:approvalRoute>
    </tns:ApprovalRoute>
  </xsl:template>
  <xsl:template name="loopApprovers">
    <xsl:param name="approvalRoute"/>
    <xsl:param name="index" select="1"/>
    <xsl:param name="partialApprovalRoute" select="''"/>
    <xsl:choose>
      <xsl:when test="number($index)=1">
        <xsl:call-template name="loopApprovers">
          <xsl:with-param name="approvalRoute" select="$approvalRoute"/>
          <xsl:with-param name="index" select="$index+1"/>
          <xsl:with-param name="partialApprovalRoute" select="$approvalRoute/ns0:Approver[1]"/>
        </xsl:call-template>
      </xsl:when>
      <xsl:when test="number($index)> 1 and number($index)&lt;=count($approvalRoute/ns0:Approver)">
        <xsl:call-template name="loopApprovers">
          <xsl:with-param name="approvalRoute" select="$approvalRoute"/>
          <xsl:with-param name="index" select="$index+1"/>
          <xsl:with-param name="partialApprovalRoute"
                          select="concat($partialApprovalRoute,',',$approvalRoute/ns0:Approver[number($index)])"/>
        </xsl:call-template>
      </xsl:when>
      <xsl:otherwise>
        <xsl:value-of select="substring($partialApprovalRoute,1,20000)"/>
      </xsl:otherwise>
    </xsl:choose>
  </xsl:template>
</xsl:stylesheet>

Simpler transformation from list to csv

As can be found here for instance, a for-each does not necessarily need to return an element. It can return just a value. So, it can be a bit simpeler:
<?xml version="1.0" encoding="UTF-8" ?>
<xsl:stylesheet version="1.0"
                xmlns:xsd="http://www.w3.org/2001/XMLSchema"
                xmlns:xp20="http://www.oracle.com/XSL/Transform/java/oracle.tip.pc.services.functions.Xpath20"
                xmlns:ns0="http://www.example.org/Approvals/Target"
                xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
                xmlns:tns="http://www.example.org/Approvals/Source" 
                xmlns:xsl="http://www.w3.org/1999/XSL/Transform">
  <!-- http://p2p.wrox.com/xslt/72164-xslt-need-concatenate-strings-loop-hold-them-later-use.html -->
  <xsl:template match="/">
    <tns:ApprovalRoute>
      <tns:approvalRoute>
        <xsl:call-template name="loopApprovers">
          <xsl:with-param name="approvalRoute" select="/ns0:ApprovalRoute"/>
        </xsl:call-template>
      </tns:approvalRoute>
    </tns:ApprovalRoute>
  </xsl:template>
  <xsl:template name="loopApprovers">
    <xsl:param name="approvalRoute"/>
    <xsl:variable name="approvalRouteCsv">
      <xsl:for-each select="$approvalRoute/ns0:Approver">
        <xsl:value-of select="concat(substring(.,1,20000),',')"/>
      </xsl:for-each>
    </xsl:variable>
    <xsl:value-of select="substring($approvalRouteCsv,1,string-length($approvalRouteCsv)-1)"/>
  </xsl:template>
</xsl:stylesheet>

Conclusion

Understanding Recursion with XSLT will help you with solving much complexer problems in transformations. The last example of transforming a list to a comma separated list is of course structural easier. But the recursive variant allows for more calculations or conditional processing.

Friday, 12 October 2018

Enable X11 on Oracle Cloud Infrastructure

Today my colleague was starting with the installation of Oracle Database on the Oracle Cloud Infrastrcture, for a customer. He phoned me for help on enabling VNC to have a graphical UI to install the database.

Install an ssh client with XServer emulator

Most of my co-workers of around my age, have grown up with Putty. And apparently we as computer consultants are quite hooked to our tools. I know, only yesterday I mentioned it to a co-worker, that everywhere I come the two first tools I install are:
  1. Firefox (unfortunately to be downloaded using IE or Edge, it's from then on immediately the last time I use those browsers, as far as I'm concerned)
  2. Total Commander (of course downloaded with Firefox): I really hate Windows Explorer, ever since it is introduced with Windows 95/NT. Windows 3.1 had FileManager (yes I"m that old...)

    I liked that much better than the successor Windows Explorer. But little filemanager beat the revival of Norton Commander: Total Commander. I use it almost all of my carreer, and so much that quite early on I bought a key for what we would call 'an apple and an egg'.

    But about the the third tool I install is:
  3. MobaXterm:this tool is so much richer than Putty. It includes a SCP/SFTP client that can follow your terminal. Which means, every time you cd to a certain folder, your SFTP window will cd to it as well. I like the fonts, the looks more, it's more comfortable. But it also includes a XServer, with no additional install! It's free, with a limited number of sessions though. However, the cost for a lifetime license is really low.
So, as soon as I see a co-worker work with Putty, I recommend MobaXterm (no, I don't have shares).
Quite because of the same reasons I would recommend Total Commander over Win Explorer.

When connecting to a server, MobaXterm, by default (although you can uncheck it) will do X11Forwarding.

However, in the case of my colleague, unfortunately X11-forwarding was disabled:

We installed xclock which additionally installed several X-libraries. We checked XForwarding in /etc/ssh/sshd_config. All with no luck. But, we were so close. In the end, the answer (thanks Radu) was in this whitepaper. We needed to set the X11UseLocalhost property to no in /etc/ssh/sshd_config.

The complete setup, following the whitepaper:
  1. Log into the instance (obviously)
  2. Configure SSHD to not use localhost for X11:
    1. Open /etc/ssh/sshd_config in your favorite editor.
    2. Search for the line that has X11UseLocalhost (it’scommented out).
    3. Uncomment  the line by removing the # from the beginning.
    4. Set the property to no.
    5. While you're at it, check if the property X11Forwarding is set to yes.
    6. Save the file.
    7. Restart the ssh daemon by executing: sudo systemctl restart sshd
  3. Install xauth by executing: sudo yum-y install xauth
  4. Install xterm (used to verify X configuration) by executing: sudo yum -y install xterm
  5. Also isntall xclock for simple testing of the X Forwarding: sudo yum -y install xclock
Now, reconnect using MobaXterm, and you should see that X11-forwarding is enabled:

When running xclock on the remote terminal will show a clock on your local desktop.
As can be seen in the screendump, you might run into the message 'Missing charsets in String to FontSet conversion'. This can be solved following this hint by RedHat. It is caused by improper locale environment variable. Run the following:
export LC_ALL=C

You might want to add this to your ~/.bash_profile. Then run xclock again.

But, but, but... I can't log on to oracle...

To make things slightly more complicated, in most situations, you can't logon as the oracle user. You get a user to connect, and then have to sudo to oracle. In those cases you ned to redirect your xauth.

So, perform the following:
  1. Connect as the user provided 
  2. List your xauth by executing: xauth list $DISPLAY (you might need to check the DISPLAY variable)
    This would provide a line like:
    darlin123/unix:11  MIT-MAGIC-COOKIE-1  1231a6f34cca12394d3233456230df26
  3. Sudo to oracle: sudo su - oracle
  4.  Then set the DISPLAY using the port from the xauth list above:
    export DISPLAY=darlin123:11
    (In some examples explaining this X forwarding across users, you might see export DISPLAY=localhost:11. But, remember: we disabled the use of localhost above).
  5. Then add the autorisation with:
    xauth add darlin123/unix:11  MIT-MAGIC-COOKIE-1  1231a6f34cca12394d3233456230df26

    Also take over the port in the dipslay, as well as the GUID (in green) from the xauth list.

That should work!