Tuesday 29 September 2020

Logging in SOA Suite BPEL

This article feels like I should have written years ago. As it is, I haven't, but let's do it anyway.

A few weeks ago a (somewhat older) question on community.oracle.com caught my attention. It was about how to do logging in Oracle SOA Suite. 

This very much possible, and it can be simply done using an Embedded Java activity. However, if you want to have multiple loggings in a larger BPEL process, or have multiple BPEL components in a composite with multiple loggings scattered all over them, then Embedded Java activities aren't that practical.

So, I have developed a bit more sophisticated solution. For just one logging, it is a bit overdone, but I find it more practical with multiple loggings.

It starts with a quite simple Log wrapper class, that I added to GitHub. It is a wrapper around Java Util Logging, and helps with instantiating with a Logger instance. One of the constructors takes in a compositeName and componentName:

public class Log {
    private static final String BASE_PACKAGE="oracle.soa.bpel";
    private static Logger log;
    private String className;
...
    public Log(Class loggingClass) {
        super();
        setClassName(loggingClass.getName());
        log = Logger.getLogger(getClassName());
    }
    
    public Log(String loggingClass) {
        super();
        setClassName(loggingClass);
        log = Logger.getLogger(getClassName());
    }

    public Log(String compositeName, String componentName) {
        super();
        String loggingClass = BASE_PACKAGE+"."+compositeName+"."+componentName;             
        setClassName(loggingClass);
        log = Logger.getLogger(getClassName());
    }

An important aspect here is the static variable BASE_PACKAGE which is set to "oracle.soa.bpel". Which I'll get back to in a minute. The constructor uses this, and the compositeName and compentName to build up a sort of className, that it prefixes with the BASE_PACKAGE.

It also has some logging methods, that requires a methodName, that it uses as an extra identifier for the logging, added to the full class Name.

This class I 'deployed' to a jar file. This makes it reusable in multiple composites, while the source is versioned only once.

 Add it to the SCA_INF/lib folder of your composite:

But you could probably also add it to the oracle.soa.ext_11.1.1 folder in your $MW_HOME/soa/soa/modules folder and run the Ant script there: 

After running Ant in that folder, you should restart the server. The Ant script will add all the jar files in that folder, including yours, to the manifest file of the oracle.soa.ext.jar file in that folder. Doing so, it will be appended to the Classpath of SOA Suite.

To use this in your BPEL, it is important to add the following line at the beginning:

<import location="nl.darwinit.soautils.logging.Log" importType="http://schemas.oracle.com/bpel/extension/java"/>

Like this:

Having done that you can use the Log class in the Embbeded Java activity. To begin with, I find it usefull to add an Embedded Java to a scope which contains simple xsd:string based variables. Using an Assign you can easily assing proper values to the local variables:

The compositeName and componentName variables can be filled with ora:getCompositeName() and ora:getComponentName() respectively. Doing so makes it easier to access these values in an Embedded Java activity. The Java snippet Embedded Java in my example project is:

String compositeName = (String) getVariableData("compositeName");      
String componentName = (String) getVariableData("componentName");      
String text = (String) getVariableData("text");      
String methodName= (String) getVariableData("methodName");      
Log log = new Log(compositeName,componentName);     
  
String message="**** BPEL "+methodName +" " + text +" ****";    
log.info(methodName, message);    
addAuditTrailEntry(message);

The addAuditTrailEntry() shown in this snippet is an API that adds the message to the flowtrace also:


So not necessary for logging and also not specifically in scope of this article, but good to mention.
The message build up in this snippet is a concatenation of: "**** BPEL "+methodName +" " + text +" ****". This maybe handy in the AuditTrail, but in the log you may want to show just the text, like: log.info(methodName, text).

Earlier I mentioned the BASE_PACKAGE variable in the Log class. This refers to the oracle.soa.bpel log-appender. This can be configured in the soa-infra Log Configuration:


And then:

You could add a custom Logger, but it is easier to use an existing one. And to me it makes sense to use the oracle.soa.bpel Logger. If you would choose to use another logger, you would need to change the BASE_PACKAGE variable in the class.

Make sure it has a severity or Log Level low enough to cater for your logging. Set it to the Runtime Logger, but for persistence purpose you probably would need to add it to the "Loggers with Persistent Log Level State". For changing the Runtime logger, you would not need to restart the server. You do need to make sure that the "minimum severity to log" on the server in the WebLogic console.

Before you test, it can be handy to "tail" the diagnostic log as follows:

[oracle@ol7vm logs]$ tail -f DefaultServer-diagnostic.log |grep oracle.soa.bpel

I added my Demo BPEL process also to GitHub. If you test it, the output will be as follows:


So that works! Easy, right?

Now, this works for one simple Log in a BPEL process. But what about if you want to trace the flow using multiple Logs. And maybe even, in fault handlers log particular errors. The scope I introduced can be converted to a subProcess:


 I renamed the SubProcess to Log and then you can remove the Assign:


The scope is replaced with a call activity, that can be renamed. The scope variables now function as call arguments:

You can copy&paste this and rename it to reflect for instance a LogEnd activity:


Testing this, gives the following output:

As can be seen, now a simple log is done using a simple call activity. In this example the Log subprocess is within the same BPEL process, so you could move the setting of the componentName and compositeName variables in an assign in the subProcess, re-enstating the original assign.

However, you could of course move the Embedded Subprocess to a Reusable Subprocess. And then it might be usefull to be able to provide at least the componentName as an argument.

I think it is not so useful to put it in a separate BPEL process that can be called from external composites. In that case you would need to do an invoke with an accompanying assign and variable declarations for each Logging. So I would prefer to define either an embedded or a reusable subprocess for each composite/bpel that you want to do logging in.

Although I experience this article a few years late, I hope it does help.

Friday 25 September 2020

My boxes in the Vagrant Cloud

Last year I wrote about how I created a seemless desktop using Vagrant, VirtualBox en MobaXterm.

This week I was busy creating a new box with Oracle Linux, later switching to CentOS and installing several IDE's in it. And Docker.

Next week a big change is due for me. And for that I'll be going to switch laptops. Also others will going to use my vagrant project. Up till now I used local file based boxes. So if you wanted to use my projects that I posted on GitHub, you had not only to have the install binaries in a certain folder structure, but also the particular box downloaded in the particular boxes folder. 

This morning I decided to figure out how to publish them on the Vagrant cloud. And it is surprisingly easy, of course! Why I didn't do that before? Well, actually, I started with this by preparing a workshop for colleagues. And to simultaneously download the same box by every participant, did not seem a good idea. So I distributed the vagrant project with all the installers including the box on a stick.

But now, preparing for my laptop switch and distributing it for my colleagues, it seems a good idea.

I found this step by step article that guided me through the process. But let me go through the process my self.

First you'll need an account on the Vagrant Cloud. You can get there from the main page of vagrantup.com. And then click on the Find Boxes button:

 

Create a new account or login, if not already done that.

You'll land on the Search page:


There you can search for existing boxes. But to create and upload your own one, click on the Dashboard tab:

There click the "New Vagrant Box" button:


Here give the box a name and a short description. My first boxes had a version number in the name. But I found that a bit overdone, because later on you get to define a box versions. Click on the Create box button. I would urge you to provide a description that give some base, identifiable information on the box.


Provide a version, it's smart to start with 1 (it will check it), and possibly a description. Although I find a good base description important, I'm not sure what to write on a first version as a description. For subsequent versions, it seems a good to fill in as well. Like with GitHub/Subversion commit messages.

 Within the version, I was looking for an upload button, but you first get to define a provider. So click on the provider button:


In the following page you get to define a provider. Provide virtualbox as a provider name. Vagrant need to be able to recognize and use that. But there is no poplist, so just a free text field.

I want to upload to the Vagrant Cloud, so the default will suffice. Click on the Continue to upload button:

Using the Browse button, browse to your Vagrant box and have it upload it.

Now, to be able to use the box, and others to discover your box, you'll need to release it. So go to the versions sub tab, and click on the Release button for the v1 version:


In the following page, click on the release button:

Now my boxes are searchable:


To use a box, you can create a Vagrant file with the following reference to the box:


Or create a new box in a new folder using a command like vagrant init makker/CO78SwGUI --box-version 1, continued by vagrant up:

d:\Projects\vagrant\co78>vagrant init makker/CO78SwGUI --box-version 1
A `Vagrantfile` has been placed in this directory. You are now
ready to `vagrant up` your first virtual environment! Please read
the comments in the Vagrantfile as well as documentation on
`vagrantup.com` for more information on using Vagrant.

d:\Projects\vagrant\co78>vagrant up
Bringing machine 'default' up with 'virtualbox' provider...
==> default: Box 'makker/CO78SwGUI' could not be found. Attempting to find and install...
    default: Box Provider: virtualbox
    default: Box Version: 1
==> default: Loading metadata for box 'makker/CO78SwGUI'
    default: URL: https://vagrantcloud.com/makker/CO78SwGUI
==> default: Adding box 'makker/CO78SwGUI' (v1) for provider: virtualbox
    default: Downloading: https://vagrantcloud.com/makker/boxes/CO78SwGUI/versions/1/providers/virtualbox.box

==> default: Waiting for cleanup before exiting...
Download redirected to host: vagrantcloud-files-production.s3.amazonaws.com

You can list boxes with the (sub)command vagrant box list:

d:\Projects\vagrant\co78>vagrant box list
CO77GUIv1.1          (virtualbox, 0)
makker/ol77SwGUIv1.1 (virtualbox, 1)

Remove a box with vagrant box remove CO77GUIv1.1:

d:\Projects\vagrant\co78>vagrant box remove CO77GUIv1.1
Box 'CO77GUIv1.1' (v0) with provider 'virtualbox' appears
to still be in use by at least one Vagrant environment. Removing
the box could corrupt the environment. We recommend destroying
these environments first:

rhfuse (ID: ca219fa1fe0b4984bf77aa7807c0feb2)

Are you sure you want to remove this box? [y/N] y
Removing box 'CO77GUIv1.1' (v0) with provider 'virtualbox'...

But you can add the freshly created box also using the vagrant box add command:

d:\Projects\vagrant\co78>vagrant box add makker/CO78SwGUI --box-version 1
==> box: Loading metadata for box 'makker/CO78SwGUI'
    box: URL: https://vagrantcloud.com/makker/CO78SwGUI
==> box: Adding box 'makker/CO78SwGUI' (v1) for provider: virtualbox
    box: Downloading: https://vagrantcloud.com/makker/boxes/CO78SwGUI/versions/1/providers/virtualbox.box
==> box: Box download is resuming from prior download progress
Download redirected to host: vagrantcloud-files-production.s3.amazonaws.com
Progress: 3% (Rate: 10.5M/s, Estimated time remaining: 0:03:29)

As can be seen it mentions that it started the download earlier, but I broke it off. It apparently resumes the download.

My current Vagrantfiles have the following declaration of the vagrant box:

...
BOX_NAME="CO78GUIv1.1"
BOX_URL="file://../boxes/CO78SwGUIv1.0.box"
VM_MEMORY = 12288 # 12*1024 MB
...
Vagrant.configure("2") do |config|
  # The most common configuration options are documented and commented below.
  # For a complete reference, please see the online documentation at
  # https://docs.vagrantup.com.

  # Every Vagrant development environment requires a box. You can search for
  # boxes at https://vagrantcloud.com/search.
  config.vm.box=BOX_NAME
  config.vm.box_url=BOX_URL
  config.vm.hostname=VM_HOST_NAME
  config.vm.define VM_MACHINE
  config.vm.provider :virtualbox do |vb|
    vb.name=VM_NAME
    vb.gui=VM_GUI
    vb.memory=VM_MEMORY
    vb.cpus=VM_CPUS
...

Based on the suggestion of the Vagrant Cloud:


I adapted this as follows:

...
BOX_NAME="makker/CO78SwGUI"
BOX_VERSION = "1"
#BOX_URL="file://../boxes/CO78SwGUIv1.0.box"
VM_MEMORY = 12288 # 12*1024 MB
...
Vagrant.configure("2") do |config|
  # The most common configuration options are documented and commented below.
  # For a complete reference, please see the online documentation at
  # https://docs.vagrantup.com.

  # Every Vagrant development environment requires a box. You can search for
  # boxes at https://vagrantcloud.com/search.
  config.vm.box = BOX_NAME
  config.vm.box_version = BOX_VERSION
  # config.vm.box_url=BOX_URL
  config.vm.hostname=VM_HOST_NAME
  config.vm.define VM_MACHINE
  config.vm.provider :virtualbox do |vb|
    vb.name=VM_NAME
    vb.gui=VM_GUI
    vb.memory=VM_MEMORY
  vb.cpus=VM_CPUS
...

I uncommented the BOX_URL variable with the config.vm.box_url lines. And added the BOX_VERSION and config.vm.box_version lines. Most importantly I changed the BOX_NAME variable to makker/CO78SwGUI.

These suggestions will download my Cloud boxes without me needing to distributed them separately.

Happy Upping!


Tuesday 8 September 2020

Silent install of SQL Developer

Last week I provided a script to automatically install the SOA or BPM Quickstart.

Today, below I'll provide a script to install SQL Developer on Windows. I always use the "zip-with-no-jre" file. Therefor installing it is simply unzipping it.

For unzipping, I use the java jar tool This is convenient, because if you want use SQL Developer you need a JDK (unless you choose to use the installer with jre). And if you have a jdk, you have the jar tool. The script mentioned in the previous article, takes care of installing java. So, if you want to do that as well, you could add it to this script.

One disadvantage of the jar  tool is that it can't unzip to a certain folder other than the current folder. So you have to CD to the folder into which you want to unzip it. The script therefor saves the current folder, and CD's to the unzip folder. After installation it CD's back.

The script unzips into the a subfolder, under C:\Oracle\SQLDeveloper. I like to keep my Oracle IDE's together, but grouped. Within the zip file there is a sqldeveloper folder, which is renamed to the name of the zip.

With SQLDeveloper 20.2 I found that it required the msvcr100.dll in the $JDK\jre\bin folder. Apparently in the latest JDK 8 update (261), that I used when creating this script, it wasn't. I found it in c:\Windows\System32 on my system, so I copied it from there to the $JDK\jre\bin folder. But a colleague didn't find it.

Another step in the script is that it copies the a copy of the UserSnippets.xml file. At my customer I created several handy maintenance queries that I saved as snippets. When you do so, you find those saved into the UserSnippets.xml file in the %USERPROFILE%\AppData\Roaming\SQL Developer. Where the %USERPROFILE% usually points to the C:\users\%{your windows username} folder.

If you want to share a copy of that to the users installing the tool using this script, you can save it in the same folder as this script. We keep it in SVN.

@echo off
set CMD_LOC=%~dp0
set CURRENT_DIR=%CD%
SETLOCAL
set SOFTWARE_HOME=x:\SOFTWARE\Software
set SQLDEV_INSTALL_HOME=%SOFTWARE_HOME%\SQL Developer
set SQLDEV_NAME=sqldeveloper-20.2.0.175.1842-no-jre
set SQLDEV_ZIP=%SQLDEV_INSTALL_HOME%\%SQLDEV_NAME%.zip
set SQLDEV_BASE=c:\Oracle\SQLDeveloper
set SQLDEV_HOME=%SQLDEV_BASE%\%SQLDEV_NAME%
set SQLDEV_USERDIR=%USERPROFILE%\AppData\Roaming\SQL Developer
set CMD_LIB=%CMD_LOC%\ext
rem Install SqlDeveloper
if not exist "%SQLDEV_HOME%" (
  echo SqlDeveloper does not yet exist in "%SQLDEV_HOME%".
  if exist "%SQLDEV_ZIP%" (
    echo Install SqlDeveloper in %SQLDEV_HOME%.
    if not exist "%SQLDEV_BASE%" (
      echo Create folder %SQLDEV_BASE%
      mkdir %SQLDEV_BASE%
    )
    cd %SQLDEV_BASE%
    echo Unzip SqlDeveloper "%SQLDEV_ZIP%" into %SQLDEV_BASE%
    "%JAVA_HOME%"\bin\jar.exe -xf "%SQLDEV_ZIP%"
    echo Rename unzipped folder "sqldeveloper" to %SQLDEV_NAME%
    rename sqldeveloper %SQLDEV_NAME%
    rem Deze library wordt verwacht in de Java home, maar komt blijkbaar niet meer standaard mee. 
    if not exist "%JAVA_HOME%\jre\bin\msvcr100.dll" (
      echo Copy msvcr100.dll from c:\Windows\System32\ to "%JAVA_HOME%\jre\bin"
      copy c:\Windows\System32\msvcr100.dll "%JAVA_HOME%\jre\bin"
    ) else (
      echo Library "%JAVA_HOME%\jre\bin\msvcr100.dll" already exists.
    )
    if not exist "%SQLDEV_USERDIR%" (
      echo Create folder "%SQLDEV_USERDIR%"
      mkdir "%SQLDEV_USERDIR%"
    )
    if not exist "%SQLDEV_USERDIR%\UserSnippets.xml" (
      echo Copy "%CMD_LOC%\UserSnippets.xml" naar "%SQLDEV_USERDIR%"
      copy "%CMD_LOC%\UserSnippets.xml" "%SQLDEV_USERDIR%" /Y
    ) else (
      echo User Snippets "%SQLDEV_USERDIR%\UserSnippets.xml" already exists.
    )
    cd %CURRENT_DIR%
  ) else (
    echo SqlDeveloper zip  "%SQLDEV_ZIP%" does not exist!
  )
) else (
  echo SqlDeveloper already installed in %SQLDEV_HOME%.
)
echo Done.
ENDLOCAL

Update 2020-09-09: in the line with mkdir "%SQLDEV_USERDIR%", there should be quotes around the folder, since there is a space in it.
The folder structure "%USERPROFILE%\AppData\Roaming\SQL Developer" is taken from an existing installation. This is where SQLDeveloper expects the user data.

Monday 31 August 2020

Silently Install SOA QuickStart Revised


Earlier I wrote a script to silently install the SOA QuickStart installer and wrote about it here

Several customer projects further and iterations on the script further, I revised this script lately again. Because I'm leaving this customer in a week or three, and to help my successors to build up their development pc's in a comfortable and standard way.

You may have noticed that over the years I've grown fond of scripting stuff, especially building up environments. At my current customer every developer installed the several IDE's, test tooling and TortoiseSVN by hand. So every one has the tooling in another folder structure. Checked out the subversion repo's by hand and therefor in another structure. 

So, scripting things help in having the tooling in the same folder structure for every one. And that reduces the chances on problems and misconfigurations. Especially preventing the infamous phrase: 'It works with me...' when having problems.

One of the revisions is to have nested if-else structures in the script, which makes it more readable then the conditional goto's we were used to use in Windows .bat files.

Another important improvement was to have the install binaries in a separate fileserver-repository. This makes it possible to have the scripting and depending files in a Subversion repository.

The script improved installSoaQS.bat is as follows:

@echo off
rem Part 1: Settings
rem set JAVA_HOME=c:\Oracle\Java\jdk8
set JAVA_HOME=c:\Program Files\Java\jdk1.8.0_261
set SOFTWARE_HOME=Z:\Software
set JDK8_INSTALL_HOME=%SOFTWARE_HOME%\Java\JDK8
set JAVA_INSTALLER=%JDK8_INSTALL_HOME%\jdk-8u261-windows-x64.exe
rem set FMW_HOME=C:\oracle\JDeveloper\12213_SOAQS
set QS_INSTALL_HOME=%SOFTWARE_HOME%\Oracle\SOAQuickStart12.2.1.3
set QS_EXTRACT_HOME=%TEMP%\Oracle\SOAQuickStart12.2.1.3
set FMW_HOME=C:\oracle\JDeveloper\12213_SOAQS
set QS_RSP=soaqs1221_silentInstall.rsp
set QS_RSP_TPL=%QS_RSP%.tpl
set QS_JAR=fmw_12.2.1.3.0_soa_quickstart.jar
set QS_ZIP=%QS_INSTALL_HOME%\fmw_12.2.1.3.0_soaqs_Disk1_1of2.zip
set QS_JAR2=fmw_12.2.1.3.0_soa_quickstart2.jar
set QS_ZIP2=%QS_INSTALL_HOME%\fmw_12.2.1.3.0_soaqs_Disk1_2of2.zip
set QS_USER_DIR=c:\Data\JDeveloper\SOA
set CMD_LOC=%~dp0
set CURRENT_DIR=%CD%
rem Part 2: Install Java
rem Set JAVA_HOME
echo setx -m JAVA_HOME "%JAVA_HOME%"
setx -m JAVA_HOME "%JAVA_HOME%"
echo JAVA_HOME=%JAVA_HOME%
rem Check Java
if not exist "%JAVA_HOME%" (
  if exist "%JAVA_INSTALLER%" (
    echo Install %JAVA_HOME% 
    %JAVA_INSTALLER% /s INSTALLDIR="%JAVA_HOME%"
    if exist "%JAVA_HOME%" (
      echo Java Installer %JAVA_INSTALLER% succeeded.
    ) else (      
      echo Java Installer %JAVA_INSTALLER% apparently failed.
    )
  ) else (
    echo Java Installer %JAVA_INSTALLER% does not exist.
  )
) else (
  echo JAVA_HOME %JAVA_HOME% exists
)
rem Part 3: Check the QuickStart Installer Files
rem check SOA12.2 QS
if exist "%JAVA_HOME%" (
  if not exist "%FMW_HOME%" (
    echo Quickstart Installer %QS_JAR% not installed yet.
    echo Let's try to install it in %FMW_HOME%
    if not exist %QS_EXTRACT_HOME% (
      echo Temp folder %QS_EXTRACT_HOME% does not exist, create it.
      mkdir %QS_EXTRACT_HOME%
    ) else (
      echo Temp folder %QS_EXTRACT_HOME% already exists.
    )
    echo Change to %QS_EXTRACT_HOME% for installation.
    cd %QS_EXTRACT_HOME%
    rem Check Quickstart is unzipped
    echo Check if QuickStart Installer is unzipped.
    rem Check QS_JAR
    if not exist "%QS_JAR%" (
      echo QuickStart Jar part 1 %QS_JAR% does not exist yet.
      if exist "%QS_ZIP%" (
        echo Unzip QuickStart Part 1 %QS_ZIP%
        "%JAVA_HOME%"\bin\jar.exe -xf %QS_ZIP% 
        if exist "%QS_JAR%" (
          echo QuickStart Jar part 1 %QS_JAR% now exists.
        ) else (
          echo QuickStart Jar part 1 %QS_JAR% still not exists.
        )
      ) else (
        echo QuickStart ZIP part 1 %QS_ZIP% does not exist.
      )
    ) else ( 
      echo QuickStart Jar part 1 %QS_JAR% exists.
    )
    rem Check QS_JAR2
    if exist "%QS_JAR%" (
      if not exist "%QS_JAR2%" (
        echo QuickStart Jar part 2 %QS_JAR2% does not exist yet.
        if exist "%QS_ZIP2%" (
          echo Unzip QuickStart Part 2 %QS_ZIP2%
          "%JAVA_HOME%"\bin\jar.exe -xf %QS_ZIP2% 
          if exist "%QS_JAR2%" (
            echo QuickStart Jar part 2 %QS_JAR2% now exists.
          ) else (
            echo QuickStart Jar part 2 %QS_JAR2% still not exists.
          )
        ) else (
          echo QuickStart ZIP part 2 %QS_ZIP2% does not exist.
        )
      ) else ( 
        echo QuickStart Jar part 2 %QS_JAR2% exists.
      )
    ) 
    rem Part 4: Install the QuickStart
    echo Install %FMW_HOME% 
    echo Expand Response File Template %CMD_LOC%\%QS_RSP_TPL% to %CMD_LOC%\%QS_RSP%
    powershell -Command "(Get-Content %CMD_LOC%\%QS_RSP_TPL%) -replace '\$\{ORACLE_HOME\}', '%FMW_HOME%' | Out-File -encoding ASCII %CMD_LOC%\%QS_RSP%"
    echo Silent install SOA QuickStart, using response file: %CMD_LOC%\%QS_RSP%
    "%JAVA_HOME%\bin\java.exe" -jar %QS_JAR% -silent -responseFile %CMD_LOC%\%QS_RSP% -nowait
    echo Change back to %CURRENT_DIR%.
    cd %CURRENT_DIR%
    if exist "%FMW_HOME%" (
      echo FMW_HOME %FMW_HOME% exists
      rem Part 5: update the JDeveloper User Home location.
      echo "et the JDeveloper user home settings
      if not exist %QS_USER_DIR% mkdir %QS_USER_DIR%
      echo set  JDEV_USER_DIR_SOA and JDEV_USER_HOME_SOA as  %QS_USER_DIR%
      setx -m JDEV_USER_DIR_SOA %QS_USER_DIR%
      setx -m JDEV_USER_HOME_SOA %QS_USER_DIR%
      echo copy %CMD_LOC%\jdev.boot naar "%FMW_HOME%\jdeveloper\jdev\bin"
      copy "%FMW_HOME%\jdeveloper\jdev\bin\jdev.boot" "%FMW_HOME%\jdeveloper\jdev\bin\jdev.boot.org" /Y
      copy %CMD_LOC%\jdev.boot "%FMW_HOME%\jdeveloper\jdev\bin" /Y
      echo copy %CMD_LOC%\ide.conf naar "%FMW_HOME%\jdeveloper\ide\bin"
      copy "%FMW_HOME%\jdeveloper\ide\bin\ide.conf" "%FMW_HOME%\jdeveloper\ide\bin\ide.conf.org" /Y
      copy %CMD_LOC%\ide.conf "%FMW_HOME%\jdeveloper\ide\bin" /Y
    ) else (
      echo Quickstart Installer %QS_JAR% apparently failed.  
    )
  ) else (
    echo Quickstart Installer %QS_JAR% already installed in %FMW_HOME%.
  )
) else (
  echo %JAVA_HOME% doesn't exist so can't install SOA Quick Start.
)
echo Done
It first installs Oracle JDK 8 Update 261. Of course you can split this script to do only the Java install.
Then it checks the existance of the QuickStart install files as Zip files. It will create a Oracle\SOAQuickStart12.2.1.3 folder in the Windows %TEMP% Folder. After saving the current folder, it will do a change directory to it, to unzip the Installer Zip files into that temp folder. After the installation of the Quickstart it will change back to the saved folder. 

Mind that the %TEMP%\Oracle\SOAQuickStart12.2.1.3 is not removed afterwards.

The script expects the following files:

File
Location
jdk-8u261-windows-x64.exeZ:\Software\Java\JDK8    
fmw_12.2.1.3.0_soaqs_Disk1_1of2.zipZ:\Software\Oracle\SOAQuickStart12.2.1.3
fmw_12.2.1.3.0_soaqs_Disk1_2of2.zipZ:\Software\Oracle\SOAQuickStart12.2.1.3
fmw_12.2.1.3.0_soa_quickstart.jarExtracted into %TEMP%\Oracle\SOAQuickStart12.2.1.3
fmw_12.2.1.3.0_soa_quickstart2.jarExtracted into %TEMP%\Oracle\SOAQuickStart12.2.1.3
soaqs1221_silentInstall.rsp.tplSame folder as the script
jdev.bootSame folder as the script
ide.confSame folder as the script

These files are set in the variables at the top of the script. As you can see it will install the 12.2.1.3 version of the SOA QuickStart. This is because, that is the version we currently use. But, if you want to use 12.2.1.4, as I would recommend, then just change the relevant variables at the top. Same counts if you would want to use the BPM QuickStart: just change the relevant variables accordingly.
It will install the QuickStart  into the folder C:\oracle\JDeveloper\12213_SOAQS. I do like to have an Oracle Home folder that not only shows the version but also the type of the product. I dislike the default of Oracle: C:\Oracle\Middleware.

The install script expects a file soaqs1221_silentInstall.rsp.tpl which is the template file of the response file:
[ENGINE]

#DO NOT CHANGE THIS.
Response File Version=1.0.0.0.0

[GENERIC]

#Set this to true if you wish to skip software updates
DECLINE_AUTO_UPDATES=true

#My Oracle Support User Name
MOS_USERNAME=

#My Oracle Support Password
MOS_PASSWORD=<SECURE VALUE>

#If the Software updates are already downloaded and available on your local system, then specify the path to the directory where these patches are available and set SPECIFY_DOWNLOAD_LOCATION to true
AUTO_UPDATES_LOCATION=

#Proxy Server Name to connect to My Oracle Support
SOFTWARE_UPDATES_PROXY_SERVER=

#Proxy Server Port
SOFTWARE_UPDATES_PROXY_PORT=

#Proxy Server Username
SOFTWARE_UPDATES_PROXY_USER=

#Proxy Server Password
SOFTWARE_UPDATES_PROXY_PASSWORD=<SECURE VALUE>

#The oracle home location. This can be an existing Oracle Home or a new Oracle Home
ORACLE_HOME=${ORACLE_HOME}

When the install was succesfull it will also copy the file ide.conf to the corresponding folder in the Jdeveloper home, to set proper heapsizes, since the default heapsize of Jdeveloper is quite sparingly. Also it copies the jdev.conf to the proper folder, to have a the Jdeveloper User dirs set to C:\Data\Jdeveloper\SOA. As can be set at the top as well. The rationale for this is to have the Jdeveloper User Dir out side the Windows User Profile, and thus more accessible. Also it allows for having also another Jdeveloper installation that is of the same base version, but does not have the SOA/BPM quickstart add-ons. For instance for plain Java-ADF development.

The used ide.conf is as follows:

#-----------------------------------------------------------------------------
#
# ide.conf - IDE configuration file for Oracle FCP IDE.
#
# Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
#
#-----------------------------------------------------------------------------
#
# Relative paths are resolved against the parent directory of this file.
#
# The format of this file is:
#
#    "Directive      Value" (with one or more spaces and/or tab characters
#    between the directive and the value)  This file can be in either UNIX
#    or DOS format for end of line terminators.  Any path seperators must be
#    UNIX style forward slashes '/', even on Windows.
#
# This configuration file is not intended to be modified by the user.  Doing so
# may cause the product to become unstable or unusable.  If options need to be
# modified or added, the user may do so by modifying the custom configuration files
# located in the user's home directory.  The location of these files is dependent
# on the product name and host platform, but may be found according to the
# following guidelines:
#
# Windows Platforms:
#   The location of user/product files are often configured during installation,
#   but may be found in:
#     %APPDATA%\<product-name>\<product-version>\product.conf
#     %APPDATA%\<product-name>\<product-version>\jdev.conf
#
# Unix/Linux/Mac/Solaris:
#   $HOME/.<product-name>/<product-version>/product.conf
#   $HOME/.<product-name>/<product-version>/jdev.conf
#
# In particular, the directives to set the initial and maximum Java memory
# and the SetJavaHome directive to specify the JDK location can be overridden
# in that file instead of modifying this file.
#
#-----------------------------------------------------------------------------

IncludeConfFile ../../ide/bin/jdk.conf

AddJavaLibFile ../../ide/lib/ide-boot.jar

# All required Netbeans jars for running Netbinox
AddJavaLibFile  ../../netbeans/platform/lib/boot.jar
AddJavaLibFile  ../../netbeans/platform/lib/org-openide-util-ui.jar
AddJavaLibFile  ../../netbeans/platform/lib/org-openide-util.jar
AddJavaLibFile  ../../netbeans/platform/lib/org-openide-util-lookup.jar
AddJavaLibFile  ../../netbeans/platform/lib/org-openide-modules.jar

# Oracle IDE boot jar
AddJavaLibFile ../../ide/lib/fcpboot.jar
SetMainClass oracle.ide.osgi.boot.OracleIdeLauncher

# System properties expected by the Netbinox-Oracle IDE bridge
AddVMOption  -Dnetbeans.home=../../netbeans/platform/
AddVMOption  -Dnetbeans.logger.console=true
AddVMOption  -Dexcluded.modules=org.eclipse.osgi
AddVMOption  -Dide.cluster.dirs=../../netbeans/fcpbridge/:../../netbeans/ide/:../../netbeans/../

# Turn off verifications since the included classes are already verified
# by the compiler.  This will reduce startup time significantly.  On
# some Linux Systems, using -Xverify:none will cause a SIGABRT, if you
# get this, try removing this option.
#
AddVMOption  -Xverify:none

# With OSGI, the LAZY (ondemand) extension loading mode is the default,
# to turn it off, use any other words, ie EAGER
#
AddVMOption  -Doracle.ide.extension.HooksProcessingMode=LAZY

#
# Other OSGi configuration options for locating bundles and boot delegation.
#
AddVMOption  -Dorg.eclipse.equinox.simpleconfigurator.configUrl=file:bundles.info
AddVMOption  -Dosgi.bundles.defaultStartLevel=1
AddVMOption  -Dosgi.configuration.cascaded=false
AddVMOption  -Dosgi.noShutdown=true
AddVMOption  -Dorg.osgi.framework.bootdelegation=*
AddVMOption  -Dosgi.parentClassloader=app
AddVMOption  -Dosgi.locking=none
AddVMOption  -Dosgi.contextClassLoaderParent=app

# Needed for PL/SQL debugging
#
# To be disabled when we allow running on JDK9
AddVMOption  -Xbootclasspath/p:../../rdbms/jlib/ojdi.jar

# To be enabled when we allow running on JDK9
#AddVM8Option  -Xbootclasspath/p:../../rdbms/jlib/ojdi.jar
#AddJava9OrHigherLibFile ../../rdbms/jlib/ojdi.jar

# Needed to avoid possible deadlocks due to Eclipse bug 121737, which in turn is tied to Sun bug 4670071
AddVMOption   -Dosgi.classloader.type=parallel

# Needed for performance as the default bundle file limit is 100
AddVMOption   -Dosgi.bundlefile.limit=500

# Controls the allowed number of IDE processes. Default is 10, so if a higher limit is needed, uncomment this
# and set to the new limit. The limit can be any positive integer; setting it to 0 or a negative integer will
# result in setting the limit back to 10.
# AddVMOption -Doracle.ide.maxNumberOfProcesses=10

# Configure location of feedback server (Oracle internal use only)
AddVMOption -Dide.feedback-server=ide.us.oracle.com

# For the transformation factory we take a slightly different tack as we need to be able to
# switch the transformation factory in certain cases
#
AddJavaLibFile ../../ide/lib/xml-factory.jar
AddVMOption -Djavax.xml.transform.TransformerFactory=oracle.ide.xml.switchable.SwitchableTransformerFactory

# Override the JDK or XDK XML Transformer used by the SwitchableTransformerFactory
# AddVMOption -Doracle.ide.xml.SwitchableTransformer.jdk=...


# Pull parser configurations
AddJavaLibFile  ../../ide/lib/woodstox-core-asl-4.2.0.jar
AddJavaLibFile  ../../ide/lib/stax2-api-3.1.1.jar
AddVMOption -Djavax.xml.stream.XMLInputFactory=com.ctc.wstx.stax.WstxInputFactory
AddVMOption -Djavax.xml.stream.util.XMLEventAllocator=oracle.ideimpl.xml.stream.XMLEventAllocatorImpl

# Enable logging of violations of Swings single threaded rule. Valid arguments: bug,console
# Exceptions to the rule (not common) can be added to the exceptions file
AddVMOption -Doracle.ide.reportEDTViolations=bug
AddVMOption -Doracle.ide.reportEDTViolations.exceptionsfile=./swing-thread-violations.conf

# Set the default memory options for the Java VM which apply to both 32 and 64-bit VM's.
# These values can be overridden in the user .conf file, see the comment at the top of this file.
#AddVMOption  -Xms128M
#AddVMOption  -Xmx800M
AddVMOption  -Xms2048M
AddVMOption  -Xmx2048M
AddVMOption  -XX:+UseG1GC 
AddVMOption  -XX:MaxGCPauseMillis=200
# Shows heap memory indicator in the status bar.
AddVMOption -DMainWindow.MemoryMonitorOn=true 

#
# This option controls the log level at which we must halt execution on
# start-up. It can be set to either a string, like 'SEVERE' or 'WARNING',
# or an integer equivalent of the desired log level.
#
# AddVMOption   -Doracle.ide.extension.InterruptibleExecutionLogHandler.interruptLogLevel=OFF

#
# This define keeps track of command line options that are handled by the IDE itself.
# For options that take arguments (-option:<arguments>), add the fixed prefix of
# the the option, e.g. -role:.
#
AddVMOption -Doracle.ide.IdeFrameworkCommandLineOptions=-clean,-console,-debugmode,-migrate,-migrate:,-nomigrate,-nonag,-nondebugmode,-noreopen,-nosplash,-role:,-su

The used jdev.conf is as follows:

#--------------------------------------------------------------------------
#
#  Oracle JDeveloper Boot Configuration File
#  Copyright 2000-2012 Oracle Corporation. 
#  All Rights Reserved.
#
#--------------------------------------------------------------------------
include ../../ide/bin/ide.boot

#
# The extension ID of the extension that has the <product-hook>
# with the IDE product's branding information. Users of JDeveloper
# should not change this property.
#
ide.product = oracle.jdeveloper

#
# Fallback list of extension IDs that represent the different
# product editions. Users of JDeveloper should not change this
# property.
#
ide.editions = oracle.studio, oracle.j2ee, oracle.jdeveloper

#
# The image file for the splash screen. This should generally not
# be changed by end users.
#
ide.splash.screen = splash.png

#
# The image file for the initial hidden frame icon. This should generally not
# be changed by end users.
#
hidden.frame.icon=jdev_icon.gif

#
# Copyright start is the first copyright displayed. Users of JDeveloper
# should not change this property.
#
copyright.year.start = 1997

#
# Copyright end is the second copyright displayed. Users of JDeveloper
# should not change this property.
#
copyright.year.end = 2014

#
# The ide.user.dir.var specifies the name of the environment variable
# that points to the root directory for user files.  The system and
# mywork directories will be created there.  If not defined, the IDE
# product will use its base directory as the user directory.
#
#ide.user.dir.var = JDEV_USER_HOME,JDEV_USER_DIR
ide.user.dir.var = JDEV_USER_HOME_SOA,JDEV_USER_DIR_SOA

#
# This will enable a "virtual" file system feature within JDeveloper.
# This can help performance for projects with a lot of files,
# particularly under source control.  For non-Windows platforms however,
# any file changes made outside of JDeveloper, or by deployment for
# example, may not be picked by the "virtual" file system feature.  Do
# not enable this for example, on a Linux OS if you use an external editor.
#
#VFS_ENABLE = true

#
# If set to true, prevent laucher from checking/setting the shell
# integration mechanism. Shell integration on Windows associates 
# files with JDeveloper.
#
# The shell integration feature is enabled by default
#
#no.shell.integration = true

#
# Text buffer deadlock detection setting (OFF by default.)  Uncomment
# out the following option if encountering deadlocks that you suspect
# buffer deadlocks that may be due to locks not being released properly.
#
#buffer.deadlock.detection = true

#
# This option controls the parser delay (i.e., for Java error underlining)
# for "small" Java files (<20k).  The delay is in milliseconds.  Files 
# between the "small" (<20k) and "large" (>100k) range will scale the
# parser delay accordingly between the two delay numbers.
#
# The minimum value of this delay is 100 (ms), the default is 300 (ms).
#
ceditor.java.parse.small = 300

#
# This option controls the parser delay (i.e., for Java error underlining)
# for "large" Java files (>100k).  The delay is in milliseconds.
#
# The minimum value for this delay is 500 (ms), the default is 1500 (ms).
#
ceditor.java.parse.large = 1500

#
# This option is to pass additional vm arguments to the out-of-process
# java compiler used to build the project(s).  The arguments
# are used for both Ojc & Javac.
#
compiler.vmargs = -Xmx512m

#
# Additional (product specific) places to look for extension jars.
#
ide.extension.search.path=jdev/extensions:sqldeveloper/extensions

#
# Additional (product specific) places to look for roles.
#
ide.extension.role.search.path=jdev/roles

#
# Tell code insight to suppress @hidden elements 
#
insight.suppresshidden=true

#
# Disable Feedback Manager. The feedback manager is for internal use
# only.
#
feedbackmanager.disable=false

#
# Prevents the product from showing translations for languages other
# than english (en) and japanese (ja). The IDE core is translated into
# other languages, but other parts of JDeveloper are not. To avoid
# partial translations, we throttle all locales other than en and ja.
#
ide.throttleLocale=true

#
# Specifies the locales that we support translations for when 
# ide.throttleLocale is true. This is a comma separated list of 
# languages. The default value is en,ja.
#
ide.supportedLocales=en,ja

#
# Specifies the maximum number of JAR file handles that will be kept
# open by the IDE class loader.  A lower number keeps JDeveloper from
# opening too many file handles, but can reduce performance.
#
ide.max.jar.handles=500

#
# Specifies the classloading layer as OSGi. In the transition period
# to OSGi this flag can be used to check if JDev is running in OSGi
# mode.
#
oracle.ide.classload.layer=osgi




Thursday 27 August 2020

Finally created an Oracle Linux 8.2 myself


I'm certainly not the first one to do a fresh Oracle Linux 8 installation. For instance the great Tim Hall already wrote about it. My setup is quite similar, apart from:

  • I use 8.2 which is the latest-greatest at the moment.
  • For my Vagrant projects I want a base box with the Server with GUI topology. So I used that, which was actually the default in the wizard.
  • I use a NAT network adapter, for my Vagrant projects, so I skipped the network setting Tim Hall mentions.

Now, I use this as a base box for my Vagrant projects, and therefor I don't do this installation on a dayly basis. I have a Oracle Linux 7.7 box, and haven't had much problems with it.

However, I did had troubles with installing the Guest Additions this time. It didn't have the kernel-devel and kernel-header packages installed. Which is quite normal, so I did it using yum. However I kept getting the anoying mesasge that it couldn't get the 5.4.17-2011.5.3.el8uek.x86_64 version of the kernel headers. And the Guest Additions still wouldn't install. 

It kept me busy for some time, until I realized that by default it starts with the 5.4.x UEK kernel, while I it could install the kernel packages and headers for the 4.18.0.x  version.

So I found out how to startup with the correct kernel (correct in the sense that it is the kernel that allows me to use the GuestAdditions...). This can be done as follows:

sudo grubby --info=ALL

This lists the currently installed kernels. However, I found out that it is more convenient to check out the /boot folder:
sudo ls /boot//vmlinuz-*
/boot//vmlinuz-0-rescue-fddb3eeab19e4a928d6bfa04e0f91830
/boot//vmlinuz-4.18.0-193.14.3.el8_2.x86_64
/boot//vmlinuz-4.18.0-193.el8.x86_64
/boot//vmlinuz-5.4.17-2011.5.3.el8uek.x86_64

This merely because for setting the default kernel I need to provide the link to the image, also with a grubby command:
sudo grubby --set-default /boot/vmlinuz-4.18.0-193.14.3.el8_2.x86_64

Now, I can nicely install the necessary packages for the Guest Additions:
sudo dnf install kernel-devel kernel-headers gcc make perl

Next stop: boxing it into a Vagrant box.

Requeue expired JMS-AQ Messages

At my current customer we use JMS queues that are implemented with AQ queues based on sys.aq$_jms_text_message. In Weblogic you can create a so-called Foreign server that is able to interact with these queues over a datasource. For a Weblogic application, like SOA Suite or OSB, it is as if it is a regular Weblogic JMS queue. Pretty smart, because unlike a JDBC based Weblogic JMS Server, you can not only use the sys.aq$_jms_text_message type to query the aq table, as I described earlier. Not only that, you can also use the AQ PL/Sql api's to enqueue and dequeue these messages.

This can come in handy when you need to purge the tables, to remove the expired messages. But this morning there was a hickup in OSB, so that it couldn't process these messages succesfully. Because of the persisting rollbacks the messages are moved to the exception queue by AQ with the reason 'MAX_RETRY_EXCEEDED'. After I investigated the issue and some interaction with our admins the OSB was restarted which solved the problem.

But the earlier expired messages were still in the exception queue and processes were waiting for the response. So I thought it would be fun to have my own script to re-enqueue the expired messages. 

Although the admins turned out to have scripts for this, I would like to have my own. Theirs maybe smarter or at least they had more time to develop.

This script is at least publishable and might be a good starting point if you have to do something with AQ.

declare
  l_except_queue varchar2(30) := 'AQ$_DWN_OUTBOUND_TABLE_E';
  l_dest_queue varchar2(30) := 'DWN_OUTBOUND';
  l_message_type varchar2(30) := 'registersomethingmessage';
  cursor c_qtb 
    is select  qtb.queue_table 
      , qtb.queue 
      , qtb.msg_id
      , qtb.corr_id correlation_id
      , qtb.msg_state
      , qtb.enq_timestamp
      , qtb.user_data
      , 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 = 'JMSCorrelationID') JMSCorrelationID
      , (select str_value from table (qtb.user_data.header.properties) prp where prp.name = 'JMSMessageID') JMSMsgID
      , (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
      , (select str_value from table (qtb.user_data.header.properties) prp where prp.name = 'BPEL_SENSOR_NAME') bpel_sensor_name
      , (select str_value from table (qtb.user_data.header.properties) prp where prp.name = 'BPEL_PROCESS_NAME') bpel_process_name
      , (select str_value from table (qtb.user_data.header.properties) prp where prp.name = 'BPEL_PROCESS_REVISION') bpel_process_rev
      , (select str_value from table (qtb.user_data.header.properties) prp where prp.name = 'BPEL_DOMAIN') bpel_domain
      , (select str_value from table (qtb.user_data.header.properties) prp where prp.name = 'SBLCorrelationID') SBLCorrelationID
      , qtb.user_data.header
      , qtb.user_data.text_lob text_lob
      , qtb.user_data.text_vc text_vc
      , qtb.expiration_reason
      --, qtb.*
      from (
        select 'DWN_OUTBOUND_TABLE' queue_table
        , qtb.* 
        from AQ$DWN_OUTBOUND_TABLE qtb
      ) qtb
      where qtb.user_data.text_vc  like '<'||l_message_type||'%'
      and qtb.msg_state = 'EXPIRED'
      and qtb.expiration_reason = 'MAX_RETRY_EXCEEDED'
      order by queue_table, enq_timestamp asc;
  l_payload SYS.AQ$_JMS_TEXT_MESSAGE;
  l_sbl_correlation_id varchar2(100);
  l_parentComponentInstanceId varchar2(100);
  l_jms_type varchar2(100);
  --
  function get_jms_property(p_payload in SYS.AQ$_JMS_TEXT_MESSAGE, p_property_name in varchar2)
  return varchar2
  as
    l_property varchar2(32767);
  begin
    select str_value into l_property from table (l_payload.header.properties) prp where prp.name = p_property_name;
    return l_property;
  exception
    when no_data_found then
      return null;
  end get_jms_property;
  --
  procedure dequeue_msg(p_queue in varchar2, p_msg_id in raw)
  is
    l_dequeue_options dbms_aq.DEQUEUE_OPTIONS_T ;
    l_payload SYS.AQ$_JMS_TEXT_MESSAGE;
    l_message_properties dbms_aq.message_properties_t ;
    l_msg_id raw(32);
  begin
    --l_dequeue_options.visibility := dbms_aq.immediate;
    l_dequeue_options.visibility := dbms_aq.on_commit;
    l_dequeue_options.msgid := p_msg_id;    
    DBMS_AQ.DEQUEUE (
     queue_name          => p_queue,
     dequeue_options     => l_dequeue_options,
     message_properties  => l_message_properties,
     payload             => l_payload,
     msgid               => l_msg_id);
  end dequeue_msg;
  --
  procedure enqueue_msg(p_queue in varchar2, p_payload SYS.AQ$_JMS_TEXT_MESSAGE)
  is
    l_enqueue_options dbms_aq.ENQUEUE_OPTIONS_T ;
    l_message_properties dbms_aq.message_properties_t ;
    l_msg_id raw(32);
  begin
    --l_enqueue_options.visibility := dbms_aq.immediate;
    l_enqueue_options.visibility := dbms_aq.on_commit;
    DBMS_AQ.ENQUEUE (
     queue_name          => p_queue,
     enqueue_options     => l_enqueue_options,
     message_properties  => l_message_properties,
     payload             => p_payload,
     msgid               => l_msg_id);
  end enqueue_msg;
  --
begin
  for r_qtb in c_qtb loop
    l_payload := r_qtb.user_data;
    l_jms_type := r_qtb.user_data.header.type;
    l_sbl_correlation_id := get_jms_property(l_payload, 'SBLCorrelationID');
    l_parentComponentInstanceId := get_jms_property(l_payload, 'tracking_parentComponentInstanceId');
    dbms_output.put_line(r_qtb.queue||' - '||' - '||l_jms_type||' - '||r_qtb.msg_id||' - '||l_sbl_correlation_id||' - '||l_parentComponentInstanceId);
    enqueue_msg(l_dest_queue , l_payload);
    dequeue_msg(l_except_queue , r_qtb.msg_id);
  end loop;
end;

This script starts with a cursor that is based on the query described in the post mentioned above. It selects only the Expired messages, where the root-tag starts with a concatenation of '<' and the message type declared in the top. If there was a JMS type you could also select on the userdata.header.type attribute.

It logs a few attributes, merely for me to check if the base of the script worked, without the dequeue and the enqueue. The selecting of the particular JMS properties are taken from the earlier script and are an example on properties that you could use to more granularly determine if a message is eligable to be re-enqueued.

The found message is enqueued and then dequeued, both with visibility set to on_commit. This ensures that the enqueue and dequeue is done within the same transaction. You should hit the commit button in SQL Developer (or your other favorite Database IDE).

The from clause construct:

      from (
        select 'DWN_OUTBOUND_TABLE' queue_table
        , qtb.* 
        from AQ$DWN_OUTBOUND_TABLE qtb
      ) qtb

is from a script I created at the customer to query over all the available queue tables, by doing a union-all over all the queue-tables. That's why the first column names the queue table that is source for the record. 

This script can be made more dynamic by putting it in a package and make a pipelined function for the query, so that you can provide the queuetable to query from as a parameter. You could even loop over all the user_queue_tables to dynamically select all the message from all the tables without having to do union alls over the familiar queue tables. See my Object Oriented Pl/Sql article for more info and inspiration.

You might even have fun with Polymorphic Table Functions, the Patrick-ACE-Director-Bar-solutions is expert on that.


Tuesday 11 August 2020

The magic of CorrelationSets

CorrelationSets in BPEL are as old as the road to Rome. I wrote about it before: 

Although it was in the BPEL project from the very beginning, when Oracle acquired it in 2004, you might not have dealt with it before. But maybe not even realized that you can use it in Oracle Integration Cloud, with structured processes.

In the first week of june I got to do a presentation about this subject, in a series of Virtual Meetups.

If you weren't able to attend, but would like to watch it then you're in luck, it got recorded by Phil Wilkins:



In my presentation I start with a simple demo based on a BPEL process. I have put the resulting code on GitHub: https://github.com/makker-nl/blog/tree/master/CorrelationDemo

Then I move on to a more complicated situation in OIC. I created an export for that project and placed it on GitHub too: https://github.com/makker-nl/blog/tree/master/CorrelationDemoOIC

This allows you to inspect it and try to recreate it yourself.

My sincere appologies for this late sharing.