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

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

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

Multiple times I encountered that the Transformation tool of de BPEL designer has difficulties to cope with xpath and xslt functions in the stylesheet that it does not 'know'.
We have for example some custom xslt functions and I use some xpath 2.0 functions. And if I use them and deploy the process, they'll work. But for the mapper -tool the transform is invalid and it will not show the transformation map.

This is especially true for BPEL 10.1.2 that is still used at my current customer (there is a upgrade to 11g project on going).

Very anoying, because we have some large xsl's that use a large number of custom xslt-functions to do a cached dvm lookup.

But today I found a very nice workaround. If you hide those functions in a custom user template then the transformation map does not have difficulties with it.


I now have the following user-defined template (put at the bottom of the xsl stylesheet):

<xsl:template name="TransformLandCode">
<xsl:param name="landCode"/>
<xsl:comment>Transform Landcode gebruikmakend van cache:lookup
</xsl:comment>
<xsl:variable name="result" select="cache:lookup($landCode,&quot;LandCodeDomain_<FromSystem>To<ToSystem>&quot;)"/>
<xsl:value-of select="$result"/>
</xsl:template> 

So where I had something like:

<xsl:value-of select="cache:lookup(/ns1:rootElement/ns1:subElement/ns1:LandCode,&quot;LandCodeDomain_&lt;FromSystem&gt;To&lt;ToSystem&gt;&quot;)"/>

I now call the template:

<xsl:call-template name="TransformLandCode">
<xsl:with-param name="landCode" select="/ns1:rootElement/ns1:subElement/ns1:LandCode"/>
</xsl:call-template>

Nice is by the way that the mapper also allows for adding the call statement in to the map using drag and drop. Also the parameters can be filled using dragging the lines:




And of course you can add code-snippets for it.

Earlier I wrote a post on how postfix could be used in a mail integration solution. This was a result on a project that I'm doing using postfix to catch mail and transfer it via a bash-shell script, through mq to BPEL PM using the mq-adapter.

I added a logging construction in the script that give me a nice mechanism to do some logging:

...
TRUE=1
FALSE=0
...
#Logging variables
LOG_ENABLED=$TRUE
#LOG_ENABLED=$FALSE
LOG_DIR=/tmp/log
LOG_FILENAME=$LOG_DIR/routescript.log
#Check log dir and create it if it does not exist.
check_logdir(){
if [ "$LOG_ENABLED" -eq $TRUE ]; then
  if [ -d $LOG_DIR ]; then
    #log a seperation line
    log
  else
    mkdir $LOG_DIR
  fi
fi
}
#Function to display usage
usage(){
  SCRIPT_PARAMETERS=" -q queuename -s sender -r receiver";
  USAGE="Usage: `basename $0` $SCRIPT_PARAMETERS";
  echo $USAGE;
}
#Log
log(){
  if [ "$LOG_ENABLED" -eq $TRUE ]; then
    TEXT="$1 ""$2"
    echo $TEXT >>$LOG_FILENAME;
  fi
}
# First check logdir
check_logdir

#Do the rest of the script logic

It works nicely, but the solution described in the earlier post works only for mails with one receipient. This was caused by the flag 'D' in the transport-line of the master.cf:
The 'D' flag prepends a message with a property line with the receipient.

# 2010-02-10, M. van den Akker: Setup transport routescript for passing message to a bash-script
routescript   unix  -       n       n       -       -       pipe
flags=FDq. user=smtpuser argv=/bin/bash -c /home/smtpuser/routescript.sh -s $sender -r $recipient -q $nexthop

Besides removing this flag, also the argument parsing of the script should be adapted, to support for multiple receipients:

ARG_NR=0;
until [ -z "$1" ]
do
  ARG_NR=$(($ARG_NR+1));
  log "Argument $ARG_NR: " $1
  case $1 in
    "-s") PARAM=$1;;
    "-r") PARAM=$1;;
    "-q") PARAM=$1;;
    * )  case $PARAM in
            "-s") SENDER=$1;
                  log "Sender: " $SENDER;;
            "-r") RECEIVER=$1;
                  log "Receiver: " $RECEIVER;
                  if  [ -z "$RECEIVER_LIST" ]
                  then
                    RECEIVER_LIST=$RECEIVER;
                  else
                    RECEIVER_LIST="$RECEIVER_LIST,$RECEIVER";
                  fi
                  log "Receiverlist: " $RECEIVER_LIST;;
            "-q") FQN_QUEUE_NAME=$1;
                  log "Queue: " $FQN_QUEUE_NAME;;
            * ) usage;
            exit $E_WRONG_ARGS;;
         esac;;
  esac;
shift 1;
done;
#Log Parameters.
log "SENDER: " $SENDER
log "RECEIVER_LIST: " $RECEIVER_LIST
log "FQN_QUEUE_NAME: " $FQN_QUEUE_NAME

But implementing this I got the strange behaviour that posfix did call my script but the script did not get any arguments! Logging like the following gave me 0 arguments:

# Check Arguments
log "Number of arguments:  $#"
log "Arguments:  $*"

Yesterday and this morning I tried and figured and thought until my brains got nearly overheated. But since all my configs seemed alright and even the version I knew they worked, it finally stroke me that it had to with the commandline to call the script.

And I finally had it. It was due to the nasty '-c' option after '/bin/bash'!

It probably got there through an example I used. And it was removed in the test environments, but apparently not in my examples and documentation. So it also came into my earlier post. The option toggles the spawn of a new bash-session for the script. The arguments got into the 'parent' session but not into the 'child'-session that runs the script. Removing the '-c'option would do the trick.

Furthermore I moved the $receiver argument to the end since it can expand to more arguments. Although the script would not have problems with it, I found it saver.

So the resulting (correct) line in the master.cf should be:

routemq   unix  -       n       n       -       -       pipe
flags=Fq. user=smtpbridge argv=/bin/bash  /home/smtpbridge/routemq.sh -q $nexthop -s $sender -r $recipient

Lately I had to change a few BPEL Processes in a way that I had to pass multiple parameters in one field. Instead of a complete base64 encoded data-object I had to pass a key to that object in a way that I could determine that the object was in fact a key-value pair. The field contains in that case in fact two key-value pairs. I thought that if I would code the key-value pair with something like $KEY="AABBCCDDEEFF" I could search for $KEY=" and then the value after the double-quote and before the next would then be the value.

The problem with XSLT is that you have functions like substring-before(), substring-after() and positional substring, where the from-position and length can be passed as numeric values. But for the latter function you need to determine the start and end position of the sub-string to extract from the input string. But apparently XSLT does not provide something like the Pl/Sql instr or Java index-of(). Also XSLT lacks a replace function in which you can replace a string within a string with a replacement string. The xslt-replace() function does a character-by-character replace.

Fortunately you can build these functions quite easilily yourself as xslt-templates using the substring-before(), substring-after(), and string-length() functions. I found the examples somewhere and adapted them a little for my purpose. Mainly to get them case-insensitive.

Index-of-ci
Here is my Case insensitive version of the Index-of template:

<!-- index-of: find position of search within string
2010-08-31, by Martien van den Akker -->
<xsl:template name="index-of-ci">
<xsl:param name="string"/>
<xsl:param name="search"/>
<xsl:param name="startPos"/>
<xsl:variable name="searchLwr" select="xp20:lower-case($search)"/>
<xsl:variable name="work">
<xsl:choose>
<xsl:when test="string-length($startPos)&gt;0">
<xsl:value-of select="substring($string,$startPos)"/>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$string"/>
</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<xsl:variable name="stringLwr" select="xp20:lower-case($work)"/>
<xsl:variable name="result">
<xsl:choose>
<xsl:when test="contains($stringLwr,$searchLwr)">
<xsl:variable name="stringBefore">
<xsl:value-of select="substring-before($stringLwr,$searchLwr)"/>
</xsl:variable>
<xsl:choose>
<xsl:when test="string-length($startPos)&gt;0">
<xsl:value-of select="$startPos +string-length($stringBefore)"/>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="1 + string-length($stringBefore)"/>
</xsl:otherwise>
</xsl:choose>
</xsl:when>
<xsl:otherwise>-1</xsl:otherwise>
</xsl:choose>
</xsl:variable>
<xsl:copy-of select="$result"/>
</xsl:template>

The template expects 3 parameters:

• string: the string in which is searched
• search: the substring that has to be searched
• startPos: position in string where the search is started

The first thing the template does is declaring a work-variable. If startPos is not given, the variable work will contain the complete input string. But if startPos is given work will contain the substring of string from startPos to the end.

Then the input and the search strings are converted to lower case into new variables: andinputLwr and searchLwr. These variables are to test case-insensitively if the search string is in the input. If that is the case then the part of the input string before the search string is determined with the substring-before() function. The string-length() of the result denotes in fact the numeric position of the search string within the input string. This is incremented by 1 or by startPos depending on startPos being filled.

The template can be called without the startPos parameter:

<xsl:call-template name="index-of-ci">
<xsl:with-param name="string" select="$input"/>
<xsl:with-param name="search" select="$keyStr"/>
</xsl:call-template>

Or with the parameter:

<xsl:call-template name="index-of-ci">
<xsl:with-param name="string" select="$input"/>
<xsl:with-param name="search" select="string('&quot;')"/>
<xsl:with-param name="startPos" select="$startIdx"/>
</xsl:call-template>

Replace
The next template replaces the fromStr in input to toStr.

<!-- replace-ci: case insensitive replace based on strings 
2010-08-31, by Martien van den Akker -->
<xsl:template name="replace-ci">
<xsl:param name="input"/>
<xsl:param name="fromStr"/>
<xsl:param name="toStr"/>
<xsl:param name="startStr"/>
<xsl:if test="string-length( $input ) &gt; 0">
<xsl:variable name="posStartStr">
<xsl:call-template name="index-of-ci">
<xsl:with-param name="string"
select="$input"/>
<xsl:with-param name="search"
select="$startStr" />
</xsl:call-template>
</xsl:variable>
<xsl:variable name="startPos">
<xsl:call-template name="index-of-ci">
<xsl:with-param name="string"
select="$input"/>
<xsl:with-param name="search"
select="$fromStr" />
<xsl:with-param name="startPos"
select="$posStartStr" />
</xsl:call-template>
</xsl:variable>
<xsl:variable name="inputLwr" select="xp20:lower-case($input)"/>
<xsl:variable name="startStrLwr" select="xp20:lower-case($startStr)"/>
<xsl:choose>
<xsl:when test="contains( $input, $startStrLwr ) and contains( $inputLwr, $fromStr )">
<xsl:variable name="stringBefore" select="substring($input,1,$startPos - 1)"/>   
<xsl:variable name="stringAfter" select="substring($input,$startPos + string-length($fromStr))"/>   
<xsl:value-of select="concat($stringBefore,$toStr)"/>         
<xsl:call-template name="replace-ci">
<xsl:with-param name="input"
select="$stringAfter"/>
<xsl:with-param name="fromStr"
select="$fromStr" />
<xsl:with-param name="toStr"
select="$toStr" />
<xsl:with-param name="startStr"
select="$startStr" />
</xsl:call-template>
</xsl:when>
<xsl:otherwise>
<xsl:value-of select="$input"/>
</xsl:otherwise>
</xsl:choose>
</xsl:if>
</xsl:template>

Here the pos of the from string is determined using the index-of-ci template described earlier.
But this is done from the position of the startStr that marks the start of the replacement search. For example, if you want to replace domain names in email-adresses, you want to start the search of the domain after the at-sign ( '@' ).
Having the start position of the 'from'-string the part of the input before and after the 'from'-string is taken using the string-before() and strina-after() functions.
The 'to'-string is concatenated to the result of the string-before(). The result of the string-after() is used to call the template recursively to search the remainder of the input-string.


Parsing keys
The following template parses a key value like $KEY="AABBCCDDEEFF"

<!-- Parse a KeyValue
2010-08-31, By Martien van den Akker -->
<xsl:template name="getKeyValue">
<xsl:param name="input"/>
<xsl:param name="key"/>
<xsl:param name="default"/>
<!-- Init variables -->
<xsl:variable name="keyStr" select="concat('$',$key,'=&quot;')"/>
<xsl:if test="string-length( $input ) &gt; 0">
<xsl:variable name="startIdxKey">
<xsl:call-template name="index-of-ci">
<xsl:with-param name="string" select="$input"/>
<xsl:with-param name="search" select="$keyStr"/>
</xsl:call-template>
</xsl:variable>
<xsl:variable name="keyLength" select="string-length($keyStr)"/>
<xsl:variable name="startIdx" select="$startIdxKey+$keyLength"/>
<xsl:variable name="endIdx">
<xsl:call-template name="index-of-ci">
<xsl:with-param name="string" select="$input"/>
<xsl:with-param name="search" select="string('&quot;')"/>
<xsl:with-param name="startPos" select="$startIdx"/>
</xsl:call-template>
</xsl:variable>
<!-- Determine value -->
<xsl:choose>
<xsl:when test="$startIdxKey&gt;=0 and $endIdx&gt;=0">
<xsl:value-of select="substring($input,$startIdx, $endIdx - $startIdx)"/>
</xsl:when>
<xsl:when test="$startIdxKey>0 and $endIdx&lt;0">
<xsl:value-of select="substring($input,$startIdx)"/>
</xsl:when>
<xsl:otherwise>
<xsl:if test="$default='Y'">
<xsl:value-of select="$input"/>
</xsl:if>
</xsl:otherwise>
</xsl:choose>
</xsl:if>
</xsl:template>

The working is quite similar to the templates above. If you understand the replace-template, you wouldn't have trouble with this one. Basically the value is search using the index-of-ci template with a concatenation of '$', the key and '="'. The string after that is the value, with a double quote as the end delimiter.
Having this template you can search for the key any where in the string, even if there are multiple key-value pairs.
The template can be called like:

<ns1:Id>
<xsl:call-template name="getKeyValue">
<xsl:with-param name="input" select="/ns2:aap/ns2:noot"/>
<xsl:with-param name="key" select="'KEY'"/>
<xsl:with-param name="default" select="'Y'"/>
</xsl:call-template>
</ns1:Id>

The parameter 'default' is optional: if it is set to 'Y' and the KEY is not found in the input string, then the value of the input is returned as result. Otherwise nothing is returned.

Conclusion
I found these templates very helpful. Using them you can do almost any string replacements in XSLT. ALso to me they function as example to cope with more advanced XSLT-challenges.

Virtualization is great. It is very handy to have your complex installations of server software in a Virtual Machine. I do it for years and have only some client tools that do need a complex installation on my host. JDeveloper, SQLDeveloper or Pl/Sql Developer for instance. It enables you to reinstall your laptop and be up and running after having installed your virtualization product. Provided that you backup your VM's of course. Also I have a Windows based VM with some tools for administration purposes.

I started years ago with VMware Workstation 3 to 5.x. But then VMware Server came out. And back then workstation did not provide much extra above Server. And although I had a demo license for Workstation (which ended), Server was for free. Actually the only missing feature in Server I encountered was 'Shared Folders'. But that I solved using (S)Ftp. Under windows it is very convenient to have a FileZilla Server. You could solve it by defining a share in Windows. But I found that that gives some problems when you have multiple copies of a VM running on the same network. Then you have to change your host name, etc. And it is problematic if you have multiple shared open in multiple VM's while your windows account have a regularly changed password. So shared folders as an alternative to FTP is not necessary but very convenient.

Shortly after VMware Server VMware Player came out. But until today it was not possible to create new VM's in Player. And that is a 'must have' to me.

VMware 2.0 was a large step for me since I found it astonishing that the footprint from 1.0 grew by a factor 5! But since VMware dit not keep 1.0 up with the Linux Kernel changes, I did the upgrade. Now I was a pretty satisfied VMware Server user.

But there are two problems now with VMware Server 2.x.:

• VMware Server 2.x does not keep up with the Linux kernel changes either. I upgraded to OpenSuse 11.2, which worked very well. But stepped back to 11.1 since VMware Server did not install with that kernel.
• Since FireFox 3.6 the VMware Console does not work anymore. This is solvable by installing a seperate FF 3.5.9 for VMware usage. But this is very inconvenient.

Lately I tried Virtual Box again because of the Firefox problems and I reported to be very enthousiastic about it. And indeed it is a very good product. But unfortunately most of my VM's are VMware based. It is possible to run a VM in virtual box based on VMware files. But you have to create a new Virtual Box VM for that purpose that refer to the VMware files. And than you have to de-install the VMware Tools (which probably have installed in that VM) and install VirtualBox GuestAdditions. It's all doable and no rocket-science. But it's not a simple import-and-run.

But since 25th of may VMware Player 3.1 was released. And apparently Vmware released it as an answer to the Windows XP mode on Windows 7. To run older Windows XP compatible apps in Windows 7. But it occurs to me that they've looked closely to VirtualBox. The VMware Unity mode is quite comparable or actually the same as the Seamless Windows mode of VirtualBox. This is a very attractive feature that makes VMware Player as well as VirtualBox suitable to beat the Windows XP mode of Windows 7. I have not be able to try the XP mode of Windows 7. So I don't know about load times of the XP mode. But for VirtualBox and VMware Player you need to start the guest Windows to be able to use it. And you have to have an activated version of Windows in your VM.

And since VMware Player 3.1 I'm able to create VM's in Player. Actually, with being able to create VM's in Player and with the 'Shared Folder' feature I think you might say that Player is on the 'must-have'-feature-level of VMware Workstation 5.5. And since Player has a smaller foot print (100MB, about the same as Server 1.0.x) and does not need to have an Apache Tomcat running, no need for browser access of the console: VM's running in a seperate application window, Player is a very much better suitable for running VM's on a laptop/desktop. And a feature like VMware Unity/VirtualBox Seamless Windows mode is really nice. It is funny to have windows applications running in a window side by side with your Linux apps. Even copy and paste works. And having shared folders makes it possible to have your Guest-OS apps work with the same files as your host OS apps.

And what made me most lyric? I had my OpenSuse 11.1 connected to a beamer. In the NVidia conrols I had the CRT (as NVidia/X called it) placed right of my LCD desktop. Then I placed my PowerPoint in Unity on the Beamer area of the desktop (so I was able to see other apps on my lcd screen). And then it played the slideshow fullscreen on the beamer...
I was very happy about it, because I had PowerPoint 2007 working under Wine. But editing a file under PowerPoint2007/wine was not doable. Whey drag and drop or move an object on the canvas the canvas becomes black until you release the object. Only then it draws again. But in the VMware Unity mode, PowerPoint 2007 is usable under Linux!

To conclude, when to use what? A question I try to answer a lot lately.

• If you want to run VMware VM's on your own laptop along with other apps, use VMware Player
• If you want to run MS-Office 2007 or other Windows apps side by side with other applications on your Linux machine, use either VMware Player with Unity or Virtual Box with Seamless Windows mode (you may choose). But you should try if the app works properly enough under Wine, since that will give you much faster startup times and lower system-load, since you do not have to load Windows as a guest os.
• If you want to run VM's on a host that has to be reachable from different location, then use VMware Server. For example, if you have a spare desktop on the attick that you want to leverage for different purposes, then use Server. With server you can start, stop and use the VM-desktop using a browser. Unfortunatly it has to be a FireFox 3.5 at most, for the current Server version (2.x).
• If you look for an enterprise solution, look at Oracle VM or VMware ESX and their management products. These solutions are beyond my use. Since they run on 'bare-metal', and that is out of the question for me.

I don't have particular arguments for the choice between VirtualBox and VMware Player. There are other virtualization tools like KVM or Xen based. There are people that favor a particular solution over VMware because of performance. Especially the VMware would not be that fast in IO-operations. But I haven't seen any benchmarks that support this. I haven't done any measurements in particular but I did not experience any particular performance differences between VirtualBox and VMware. The only reason actually that I favor VMware is just that most of my VM's are based on VMware.

But I sincerely hope that Oracle works on improving both Oracle VM and VirtualBox in a way that get both products in line with eachother and eventually let both products work with the same VM-architecture. Then it would be able to use Oracle VM's on both Oracle VM and VirtualBox. Maybe that would be an extra stimulus to build VirtualBox/Oracle VM appliances.

It's been a while that I wrote an article. This week I struggled with creating insert scripts based on data from DB2 to be used in my local test database (Oracle XE) at my customer.

We use Siebel and have to integrate here and there by querying data from the Siebel DB2 database. It turns out that my local database adapter has trouble with connecting to the DB2 database. Could not find out what's wrong, so I decided that I would query the data from the Siebel tables and insert it into my local XE database. It's faster anyway (in my case) and it also allows me to manipulate the data for test-case purposes.
But querying db2 to generate insert statements isn't as obvious as I would do it in Oracle.

Here is an example script.

select
'Insert into CONTACT (PARTYROWID,KLANTID,KLANTTYPE,AANGEMAAKTOP,BANKCODE,BANKLOCATIE,KLANTSTATUS,CORRESPONSDENTIETAAL,PRIMAIRTELEFOONNUMMER,'
|| 'PRIMAIRTELEFOONTYPE,PRIMAIREMAIL,PRIMAIREMAILFORMAAT,TELEFOONPRIVE,TELEFOONOVERIG,TELEFOONMOBIEL,TELEFOONWERK,FAX,EMAIL,EMAILFORMAAT,EMAILDATUM,'
|| 'EMAILBRON,INGEZETENEVAN,NATIONALITEIT,ACHTERNAAM,VOLLEDIGEACHTERNAAM,ROEPNAAM,GESLACHTSNAAM,VOORLETTERS,VOLLEDIGEVOORNAMEN,ACADEMISCHETITEL'
||',TUSSENTITEL,ACHTERVOEGSEL,ACHTERTITEL,VOORVOEGSEL,VOORVOEGSELGESLACHTSNAAM,GEBOORTDATUM,GESLACHT,GEBOORTELAND,GEBOORTEPLAATS,EIGENHUIS,FAILLIET,SAMENLEVINGSVORM'
||',BURGERLIJKSTAAT,HUW_VOORWAARDEN,PERSONEEL,TYPEKLANT,MAATSCHAPPELIJKESTATUS,LOKALEKLANTINDELING,CENTRALEKLANTINDELING,KLANTINDELING,LOKAALINGEDEELD,BEHOEFTEPROFIEL,'
||'TAXIDENTIFICATIONNR,WOONPLAATSVERKLARING,SOFINUMMER,REDENGEENSOFINUMER,DATUMOVERLIJDEN,OVERLEDEN,AARDIDENTIFICATIEDOC,DATUMLEGITIMATIE,NRIDENTIFICATIEDOC,'
|| 'DATUMUITGIFTE,LANDUITGIFTE,PLAASTUITGIFTE,PERTELEFOONBENADEREN,PEREMAILBENADEREN,PERPOSTBENADEREN,PERSMSBENADEREN,PERFAXBENADEREN,INSOLVENCYSTATUS,IKBNUMBER)'
||' values ('
|| ''''|| coalesce(ctt.PARTYROWID,'') ||''','
|| ''''|| coalesce(ctt.KLANTID,'') ||''','
|| ''''|| coalesce(ctt.KLANTTYPE,'') ||''','
|| case when ctt.AANGEMAAKTOP is not null then 'to_date('''||varchar_format( ctt.AANGEMAAKTOP,'YYYY-MM-DD HH24:MI:SS')||''',''YYYY-MM-DD HH24:MI:SS'')' else 'null' end ||','
|| ''''|| coalesce(ctt.BANKCODE,'') ||''','
|| ''''|| coalesce(ctt.BANKLOCATIE,'') ||''','
|| ''''|| coalesce(ctt.KLANTSTATUS,'') ||''','
|| ''''|| coalesce(ctt.CORRESPONSDENTIETAAL,'') ||''','
|| ''''|| coalesce(ctt.PRIMAIRTELEFOONNUMMER,'') ||''','
|| ''''|| coalesce(ctt.PRIMAIRTELEFOONTYPE,'') ||''','
|| ''''|| coalesce(ctt.PRIMAIREMAIL,'') ||''','
|| ''''|| coalesce(ctt.PRIMAIREMAILFORMAAT,'') ||''','
|| ''''|| coalesce(varchar(ctt.TELEFOONPRIVE),'') ||''','
|| ''''|| coalesce(varchar(ctt.TELEFOONOVERIG),'') ||''','
|| ''''|| coalesce(varchar(ctt.TELEFOONMOBIEL),'') ||''','
|| ''''|| coalesce(varchar(ctt.TELEFOONWERK),'') ||''','
|| ''''|| coalesce(varchar(ctt.FAX),'') ||''','
|| ''''|| coalesce(varchar(ctt.EMAIL),'') ||''','
|| ''''|| coalesce(varchar(ctt.EMAILFORMAAT),'') ||''','
|| case when ctt.EMAILDATUM is not null then 'to_date('''||varchar_format( ctt.EMAILDATUM,'YYYY-MM-DD HH24:MI:SS')||''',''YYYY-MM-DD HH24:MI:SS'')' else 'null' end ||','
|| ''''|| coalesce(EMAILBRON,'') ||''','
|| ''''|| coalesce(ctt.INGEZETENEVAN,'') ||''','
|| ''''|| coalesce(ctt.NATIONALITEIT,'') ||''','
|| ''''|| coalesce(ctt.ACHTERNAAM,'') ||''','
|| ''''|| coalesce(ctt.VOLLEDIGEACHTERNAAM,'') ||''','
|| ''''|| coalesce(ctt.ROEPNAAM,'') ||''','
|| ''''|| coalesce(ctt.GESLACHTSNAAM,'') ||''','
|| ''''|| coalesce(ctt.VOORLETTERS,'') ||''','
|| ''''|| coalesce(ctt.VOLLEDIGEVOORNAMEN,'') ||''','
|| ''''|| coalesce(ctt.ACADEMISCHETITEL,'') ||''','
|| ''''|| coalesce(ctt.TUSSENTITEL,'') ||''','
|| ''''|| coalesce(ctt.ACHTERVOEGSEL,'') ||''','
|| ''''|| coalesce(ctt.ACHTERTITEL,'') ||''','
|| ''''|| coalesce(ctt.VOORVOEGSEL,'') ||''','
|| ''''|| coalesce(ctt.VOORVOEGSELGESLACHTSNAAM,'') ||''','
|| case when ctt.GEBOORTDATUM is not null then 'to_date('''||varchar_format( ctt.GEBOORTDATUM,'YYYY-MM-DD HH24:MI:SS')||''',''YYYY-MM-DD HH24:MI:SS'')' else 'null' end ||','
|| ''''|| coalesce(ctt.GESLACHT,'') ||''','
|| ''''|| coalesce(ctt.GEBOORTELAND,'') ||''','
|| ''''|| coalesce(ctt.GEBOORTEPLAATS,'') ||''','
|| ''''|| coalesce(ctt.EIGENHUIS,'') ||''','
|| ''''|| coalesce(ctt.FAILLIET,'') ||''','
|| ''''|| coalesce(ctt.SAMENLEVINGSVORM,'') ||''','
|| ''''|| coalesce( ctt.BURGERLIJKSTAAT,'') ||''','
|| ''''|| coalesce( ctt.HUW_VOORWAARDEN,'') ||''','
|| ''''|| coalesce( ctt.PERSONEEL,'') ||''','
|| ''''|| coalesce( ctt.TYPEKLANT,'') ||''','
|| ''''|| coalesce( ctt.MAATSCHAPPELIJKESTATUS,'') ||''','
|| ''''|| coalesce( ctt.LOKALEKLANTINDELING,'') ||''','
|| ''''|| coalesce( ctt.CENTRALEKLANTINDELING,'') ||''','
|| ''''|| coalesce( ctt.KLANTINDELING,'') ||''','
|| ''''|| coalesce( ctt.LOKAALINGEDEELD,'') ||''','
|| ''''|| coalesce( ctt.BEHOEFTEPROFIEL,'') ||''','
|| ''''|| coalesce( ctt.TAXIDENTIFICATIONNR,'') ||''','
|| ''''|| coalesce( ctt.WOONPLAATSVERKLARING,'') ||''','
|| ''''|| coalesce( ctt.SOFINUMMER,'') ||''','
|| ''''|| coalesce( ctt.REDENGEENSOFINUMER,'') ||''','
|| case when ctt.DATUMOVERLIJDEN is not null then 'to_date('''||varchar_format( ctt.DATUMOVERLIJDEN,'YYYY-MM-DD HH24:MI:SS')||''',''YYYY-MM-DD HH24:MI:SS'')' else 'null' end ||','
|| ''''|| coalesce( ctt.OVERLEDEN,'') ||''','
|| ''''|| coalesce( ctt.AARDIDENTIFICATIEDOC,'') ||''','
|| case when ctt.DATUMLEGITIMATIE is not null then 'to_date('''||varchar_format( ctt.DATUMLEGITIMATIE,'YYYY-MM-DD HH24:MI:SS')||''',''YYYY-MM-DD HH24:MI:SS'')' else 'null' end ||','
|| ''''|| coalesce( ctt.NRIDENTIFICATIEDOC,'') ||''','
|| case when ctt.DATUMUITGIFTE is not null then 'to_date('''||varchar_format( ctt.DATUMUITGIFTE,'YYYY-MM-DD HH24:MI:SS')||''',''YYYY-MM-DD HH24:MI:SS'')' else 'null' end ||','
|| ''''|| coalesce( ctt.LANDUITGIFTE,'') ||''','
|| ''''|| coalesce( ctt.PLAASTUITGIFTE,'') ||''','
|| ''''|| coalesce( ctt.PERTELEFOONBENADEREN,'') ||''','
|| ''''|| coalesce( ctt.PEREMAILBENADEREN,'') ||''','
|| ''''|| coalesce( ctt.PERPOSTBENADEREN,'') ||''','
|| ''''|| coalesce( ctt.PERSMSBENADEREN,'') ||''','
|| ''''|| coalesce( ctt.PERFAXBENADEREN,'') ||''','
|| ''''|| coalesce( ctt.INSOLVENCYSTATUS,'') ||''','
|| ''''|| coalesce( ctt.IKBNUMBER,'') ||''');'
from siebel.contact ctt
where klantid='12345';

The first 'not so obvious' is the NVL-function. This is a typical Oracle function. For most purposes this can be translated to the coalesce function above. In most cases when the column is empty I want to have an "empty value". In some cases just
giving "coalesce( column-reference,'')" does not suffice. I had to cast the column explicitly to char with the varchar() function:

coalesce(varchar(ctt.TELEFOONPRIVE),'')

Here TELEFOONPRIVE is apparently a number column. The function coalesce() assumes the number datatype and can't accept an empty string as default string.

For dates it is a little more complicated. If there is a date I want to transform it to an Oracle to_date function. But then I have to be sure that the format comming from DB2 is of a standard format. I choose "YYYY-MM-DD HH24:MI:SS". If the date is empty I just want to return an empty string again. I couldn't come up with a simple construct using coalesce(). So I used the CASE WHEN-construct:

case when ctt.DATUMOVERLIJDEN is not null then 'to_date('''||varchar_format( ctt.DATUMOVERLIJDEN,'YYYY-MM-DD HH24:MI:SS')||''',''YYYY-MM-DD HH24:MI:SS'')' else 'null' end

It took me a while to find out that where in Oracle you can provide a date-format to the to_char(<date-value>, <date-format>) function, in DB2 you need the varchar_format(<date-value>, <date-format>) function for that. Luckily the accepted date-formats are the same in my case. So here I transform the date-value from DB2 to the required date-format and concatenate it with the Oracle to_date() function with the same format.

The generated insert statement(s) will look like (enter at values clause added manually for readability):

Insert into CONTACT (PARTYROWID,KLANTID,KLANTTYPE,AANGEMAAKTOP,BANKCODE,BANKLOCATIE,KLANTSTATUS,CORRESPONSDENTIETAAL,PRIMAIRTELEFOONNUMMER,PRIMAIRTELEFOONTYPE,PRIMAIREMAIL,PRIMAIREMAILFORMAAT,TELEFOONPRIVE,TELEFOONOVERIG,TELEFOONMOBIEL,TELEFOONWERK,FAX,EMAIL,EMAILFORMAAT,EMAILDATUM,EMAILBRON,INGEZETENEVAN,NATIONALITEIT,ACHTERNAAM,VOLLEDIGEACHTERNAAM,ROEPNAAM,GESLACHTSNAAM,VOORLETTERS,VOLLEDIGEVOORNAMEN,ACADEMISCHETITEL,TUSSENTITEL,ACHTERVOEGSEL,ACHTERTITEL,VOORVOEGSEL,VOORVOEGSELGESLACHTSNAAM,GEBOORTDATUM,GESLACHT,GEBOORTELAND,GEBOORTEPLAATS,EIGENHUIS,FAILLIET,SAMENLEVINGSVORM,BURGERLIJKSTAAT,HUW_VOORWAARDEN,PERSONEEL,TYPEKLANT,MAATSCHAPPELIJKESTATUS,LOKALEKLANTINDELING,CENTRALEKLANTINDELING,KLANTINDELING,LOKAALINGEDEELD,BEHOEFTEPROFIEL,TAXIDENTIFICATIONNR,WOONPLAATSVERKLARING,SOFINUMMER,REDENGEENSOFINUMER,DATUMOVERLIJDEN,OVERLEDEN,AARDIDENTIFICATIEDOC,DATUMLEGITIMATIE,NRIDENTIFICATIEDOC,DATUMUITGIFTE,LANDUITGIFTE,PLAASTUITGIFTE,PERTELEFOONBENADEREN,PEREMAILBENADEREN,PERPOSTBENADEREN,PERSMSBENADEREN,PERFAXBENADEREN,INSOLVENCYSTATUS,IKBNUMBER) 
values ('1-100BM-100','000000105727750','Person',to_date('2006-05-09 19:59:21','YYYY-MM-DD HH24:MI:SS'),'3365','336515','C','NL','','','','','','','','','','','',to_date('2008-09-15 00:00:00','YYYY-MM-DD HH24:MI:SS'),'FULFILMENT','01','',to_date('2009-07-08 00:00:00','YYYY-MM-DD HH24:MI:SS'),'NL','NL','Name','Name','','Name','I.R.S.','Iris Ronald Simon','','','','','','',to_date('1966-11-11 00:00:00','YYYY-MM-DD HH24:MI:SS'),'M','NL','Tool Town','Y','N','3','1','9','03','01','08','','1','1','Y','','','X','123456789','',null,'N','03',to_date('2005-07-29 00:00:00','YYYY-MM-DD HH24:MI:SS'),'IC4631943',to_date('2004-07-29 00:00:00','YYYY-MM-DD HH24:MI:SS'),'NL','Tool Village','N','N','Y','Y','N','','1-22A-3344');

If you have installed the VirtualBox guest-additions then you can use the SharedFolders functionality as well. This might be a usefull differentiating feature over VMware Server. VMware Workstation has this feature as well, but it will cost you about 190 dollar.

Shared Folders are folders on your host OS that you denoted to the virtualization product (VirtualBox or VMware Workstation) as being available to the guest OS. This handy because you then do not have to setup Windows or Samba shares on your host to connect to from your guest. Also it prevents you from having to setup particular network settings for it.

An alternative to shared folders, besides Windows or Samba shares is to (S)Ftp the particular files to your guest. If it is about installation files (for example the Oracle 11gR2 database) then you need to have space available in the guest-virtual disks. The virtual disks will grow accordingly. If you go for that approach then it is wise to add a temporary virtual disk to hold the installations. Afterwards you can remove the disk without the need to defrag and shrink the remaining disks.

To use the shared folders in VirtualBox you need to define a folder to share in the SharedFolders screen. This is available through the Devices main menu option:



In a Windows guest you might find the shared folders in the Windows Explorer under the network places.
In Linux guest you need to mount the share explicitly.
You first need to make a directory under the /mnt folder :

[root@oel5soa11g mnt]# mkdir Zarchief
[root@oel5soa11g mnt]# chmod a+w Zarchief/
[root@oel5soa11g mnt]# chmod a+x Zarchief/
[root@oel5soa11g mnt]# ls -l
total 8
drwxrwxrwx 2 root root 4096 Apr 21 14:11 Zarchief

Then you can mount the shared folder with the following command:

# mount -t vboxsf [SharedFolderName] /mnt/[FolderName]

For example:

[root@oel5soa11g mnt]# mount -t vboxsf Zarchief /mnt/Zarchief