MacLochlainns Weblog

I saw a post on the forum and fielded a question from my students on how you can write a database trigger that uses Java for the programming logic. I provided two approaches in this blog page. One lets Java raise the exception, which becomes an unhandled exception in SQL. The other implements the library as a function, and uses an IF statement to raise an exception – with RAISE_APPLICATION_ERROR.

I’m partial to the second approach but think the Fusion middleware may yet present a better option in the future. You should take a peak at the oracle.dss.util.TypeNotSupportedException.

I was doing some cross product testing between Excel 2007 (PC) and Excel 2008 (Mac) and got a big surprise! The number representing a date differs by 1,462. Excel 2007 starts using integers at 1 equal to 1-Jan-1900, but Excel 2008 starts with 0 equal to 1-Jan-1904. It clearly means that dates should be uploaded as formatted strings rather than numbers when loading them into a database. That also appears to mean any shared computations linked to dates across a collection of PC and Mac computers fails unless you account for the difference. I’ve updated that earlier post on what’s true with the details.

As I occasionally teach an overload freshman class on Microsoft Excel, it has become very interesting to observe how students perceive the truth of a statement. They generally get the idea that an expression evaluates the equality or inequality of expressions. The idea that value x equals value y (x=y), value x is less than value y (x>y) makes sense but when cell coordinates are substituted it can be a bumpy ride to complete understanding.

The idea of relative cell coordinates (or references) versus absolute cell coordinates takes a bit for some. Raising the ante with partially relative, partially absolute, or as most Microsoft Excel books label them mixed references becomes a challenge that is best overcome by experimentation. I label that type of learning as the Mario Brother’s paradigm, success at level 5 is generally preceded by success at level 4.

A nasty twist though is that Microsoft Excel doesn’t limit truth to the results of expressions. A number or date (a date is only a number with a format mask) is always true provided it isn’t equal to zero. Also, the strings TRUE and FALSE are respectively true or false, as if they were written as the =TRUE() or =FALSE() functions. Any other string value returns a #VALUE! expression. Microsoft Excel does this because it applies a weakly typed programming language rule (similar to Perl or PHP), which assumes any positive or negative number is true, while a zero value is false. This really opens up the use of the =IF(conditional_expression, true_outcome, false_outcome) function to advanced users but can be a stunner to new ones. By the way, Microsoft Excel labels the conditional expression as a logical test but the problem is that the rules governing logical tests generally don’t make sense to non-programmers.

Dates formatting is a bit quirky in Microsoft Excel. It would be wonderful if they’d published rules, noted exceptions, and such. My favorite quirk is that you can’t apply a format mask to any number in the range of (((2^16)*2)-70) to (((2^16)*2)+51) in Microsoft Excel 2007. You can format that range in Microsoft Excel 2008 on the Mac. Beyond that oddity, date numbering starts with 0 being equal to 1-Jan-04 in Excel 2008, while 0 formats to 0-Jan-1900 in Excel 2007. Negative numbers also format in Excel 2008 with a negative sign pre-pending the date.

The mystery question is: Where’s the next gap in Excel 2007? As you can see in the image, negative numbers can’t be date formatted in that version. However, you can evaluate whether they’re true or false notwithstanding the formatting error – those #’s. If you use the =DATE(2258,9,1) you create a date in the missing range – amazing.

Obviously, the Microsoft Engineers have a hack of some kind in place in Excel 2007 because a typical unsigned short is ((2^16)*2), while the =DATE(2258,9,1) function works in the offending range. Perhaps the DATE() function works because it uses an integer. I’d bet there’s another gap too. Gaps are shown in the next image.

As noted by Laurent in his comment, 0-Jan-1900 is false on Excel 2007, which makes sense because it’s an invalid date. Excel 2008 also treats a zero as false, but zero is a valid 1-Jan-1904 date. This means the numeric value of a date differs by 1,462 between the two versions of Excel. That makes dates non-portable between Windows and Mac version of the same product – Wowie!

It is possible to fix Excel 2007 to work with Excel 2008 files. All you need to do is click on the Options button, and then choose the Advanced selection. Inside the Advanced selectoin, you’ll find a When calculating this workbook section. Simply click the Use 1904 date system checkbox to enable your Excel 2007 to work like Excel 2008. Unfortunately, I can’t find an equivalent to set Excel 2008 to work like Excel 2007.

You enter formulas by typing them in the cell or clicking the insert formula button. The button launches a wizard that lets you walk through the basics of entering a formula. An =IF() function looks like this in the formula bar:

Cases for entering a logical expression vary. There are some simple rules. They are:

• Numbers can be compared directly.
• Text must be enclosed by double quotes inside logical expressions.
• Formulas are entered without prefacing equal symbols (=).

The following show you how to create some basic logical expressions inside the function wizard for an =IF() function.

1. The equality of numbers, which returns the true statement:

2. The non-equality of numbers, which returns the false statement:

3. The relation between text. Text is compared based on the ASCII values of the characters in the strings. Capital A maps to 65, capital B maps to 66, et cetera, and lowercase a maps to 91, lowercase b maps to 92, et cetera. Notice that the text “one” is not greater than “only” because the ASCII value of an “e” is lower than the ASCII value of an “l”. The comparison stops with the first letter that differs.

The next teaching point is the idea or when two things must be true, or when at least one thing of two things must be true for an expression to be true. It introduces the students to basic truth statements from Philosophy 101 (which they may not have taken). These are implemented as the =AND() function and =OR() functions, which are illustrated in the truth tables below.

When they understand what’s true and when it is true, they can master any combination of truth or non-truth. Until the concepts are clear it seems impossible, or at least very difficult, for end-users to understand or use the pre-built functions in Excel 2007 or Excel 2008.

I’d love to see cool learning vehicles, Flash widgets and such that I could leverage in the classroom. If you know of any, I’d appreciate a comment directing me and other readers there.

It seemed like a good day to test VMWare Fusion 2.0.1 on my Mac, but while it works well with Microsoft Windows XP VM, it doesn’t work as well with Ubuntu 8.04.1 or 8.10 VM. It wasn’t too surprising to see that VMWare Tools (VMwareTools-7.9.3-128865.tar.gz) don’t work with Ubuntu 8.04.1. There’s a mismatch between the gcc compiler and the kernel. You need gcc 4.2.3 to compile the kernel but gcc 4.2.4 to compile the modules for VMWare Tools.

You see it right away when the VMWare Tools script prompts you to compile the vmmemctl modules, like this:

None of the pre-built vmmemctl modules for VMware Tools is suitable for your
running kernel.  Do you want this program to try to build the vmmemctl module
for your system (you need to have a C compiler installed on your system)?
[yes] 
 
Using compiler "/usr/bin/gcc". Use environment variable CC to override.
 
Your kernel was built with "gcc" version "4.2.3", while you are trying to use
"/usr/bin/gcc" version "4.2.4". This configuration is not recommended and
VMware Tools may crash if you'll continue. Please try to use exactly same
compiler as one used for building your kernel. Do you want to go with compiler
"/usr/bin/gcc" version "4.2.4" anyway? [no]

At this point, upgrading Ubuntu appears ideal. Upgrading was tedious, and resulted in two failures. The first failure requires you shut down the instance by using the VMWare Fusion menu – Virtual Machine, Shut Down Guest. Don’t expect it to work as fast as it did in 2.0.0, at least with Ubuntu.

The second failure is that vsock.o can’t be made due to missing header files. That’s really as good as it gets because a fresh install produces the same error. The failure shown is:

Using 2.6.x kernel build system.
make: Entering directory `/tmp/vmware-config0/vsock-only'
make -C /lib/modules/2.6.27-7-generic/build/include/.. SUBDIRS=$PWD SRCROOT=$PWD/. modules
make[1]: Entering directory `/usr/src/linux-headers-2.6.27-7-generic'
  CC [M]  /tmp/vmware-config0/vsock-only/linuxaf_vsock.o
  CC [M]  /tmp/vmware-config0/vsock-only/driverLog.o
  CC [M]  /tmp/vmware-config0/vsock-only/util.o
/tmp/vmware-config0/vsock-only/linux/util.c: In function 'VSockVmciLogPkt':
/tmp/vmware-config0/vsock-only/linux/util.c:157: warning: format not a string literal and no format arguments
  CC [M]  /tmp/vmware-config0/vsock-only/linuxaf_vsock.o
  LD [M]  /tmp/vmware-config0/vsock-only/vsock.o
  MODPOST 1 modules
WARNING: "VMCIDatagram_CreateHnd" [/tmp/vmware-config0/vsock-only/vsock.ko] undefinied!
WARNING: "VMCIDatagram_DestroyHnd" [/tmp/vmware-config0/vsock-only/vsock.ko] undefinied!
WARNING: "VMCIEvent_Subscribe" [/tmp/vmware-config0/vsock-only/vsock.ko] undefinied!
WARNING: "VMCI_DeviceGet" [/tmp/vmware-config0/vsock-only/vsock.ko] undefinied!
WARNING: "VMCIEvent_Subscribe" [/tmp/vmware-config0/vsock-only/vsock.ko] undefinied!
WARNING: "VMCIDevice_Get" [/tmp/vmware-config0/vsock-only/vsock.ko] undefinied!
WARNING: "VMCIMemcpyFromQueueV" [/tmp/vmware-config0/vsock-only/vsock.ko] undefinied!
WARNING: "VMCIQueuePair_Detach" [/tmp/vmware-config0/vsock-only/vsock.ko] undefinied!
WARNING: "VMCI_GetConextID" [/tmp/vmware-config0/vsock-only/vsock.ko] undefinied!
WARNING: "VMCIDatagram_Send" [/tmp/vmware-config0/vsock-only/vsock.ko] undefinied!
WARNING: "VMCIQueuePair_Alloc" [/tmp/vmware-config0/vsock-only/vsock.ko] undefinied!
WARNING: "VMCIEvent_Unsubscribe" [/tmp/vmware-config0/vsock-only/vsock.ko] undefinied!
WARNING: "VMCIMemcpyToQueueV" [/tmp/vmware-config0/vsock-only/vsock.ko] undefinied!
  CC      /tmp/vmware-config0/vsock-only/vsock.mod.o
  LD [M]  /tmp/vmware-config0/vsock-only/vsock.ko
make[1]: Leaving directory `/usr/src/linux-headers-2.6.27-7-generic'
cp -f vsock.ko ./../vsock.o
make: Leaving directory `/tmp/vmware-config0/vsock-only'
Unable to make a vsock module that can be loaded in the running kernel:
insmod: error inserting '/tmp/vmware-config0/vsock.o': -1 Unknown symbol in module
 
There is probably a slight difference in the kernel configuration beetween the
set of C header files you specified and your running kernel. You may want to
rebuild a kernel based on that directory, or specify another directory.
 
The VM communication interface socket family is used in conjunction with the VM
communication interface to provide a new communication path among guests and
host. The rest of this software provided by VMWare Tools is designed to work
independently of this feature. If you with to have the VSOCK feature you can
install the driver by running the vmware-config-tools.pl again after making sure
that gcc, binutils, make and the kernel sources for your running kernel are
installed on your machine. These packages are available on your distribution's
installation CD.
[ Press the Enter key to continue.]

Wouldn’t it be nice if they pointed to a specific file. It didn’t take much effort to find them, after all it’s Linux. I found that they’re defined in the vmci_queue_pair.h and vmciGuestKernelAPI.h files. Those files are found inside the vsock-only.tar file. You can find the vsock-only.tar file in the vmware-tools-distrib/lib/modules/source directory. You can read more about the Virtual Machine Communication Interface on VMWare’s web site.

The only pre-built VMWare Fusion 2.0.1 pre-built tool modules for Ubuntu are compatible with the listed kernels. Unfortunately, as noted above they don’t work because of a gcc difference.

bld-2.6.24-16-i386generic-Ubuntu8.04
bld-2.6.24-16-i386server-Ubuntu8.04
bld-2.6.24-16-i386virtual-Ubuntu8.04
bld-2.6.24-16-x86_64generic-Ubuntu8.04
bld-2.6.24-16-x86_64server-Ubuntu8.04
bld-2.6.24-19-i386generic-Ubuntu8.04.1
bld-2.6.24-19-i386server-Ubuntu8.04.1
bld-2.6.24-19-i386virtual-Ubuntu8.04.1
bld-2.6.24-19-x86_64generic-Ubuntu8.04.1
bld-2.6.24-19-x86_64server-Ubuntu8.04.1

The only question I’m left with is do I troubleshoot this or downgrade VMWare back to 2.0.0? I’m inclined to the latter given the lack of energy in the VMWare forum.

Ultimately, this was fixed with the next release of VMWare. They simply lag a bit in getting the libraries straight. In fact, I ran into a similar problem with Ubuntu 9.04 and the page.c file. I hacked it and got everything working but really, you should probably just use the last release of Ubuntu a little longer because VMWare looks to lag release by about 4 months.

Now that I’ve organized the blog a bit, I can start posting new information. VMWare Fusion is a great tool but I fat fingered a few installs before i mastered it. You need a customized set of settings to create an effective virtual machine to run Oracle 11g. You need to allocate enough memory and pre-allocate disk space before you do the install or it takes much longer. You may also fragment a base operating system unless you setup a separate mount point (true also for VMWare Workstation for Linux).

You’ll find the steps to create a customized virtual machine for a Red Hat AS 4 installation in the Configure Custom VM blog page. It’s more or less the same thing for Oracle 10g or the Oracle eBusiness suite, except you’ll need to pre-allocate more disk space.

After migrating the blog from WordPress.com, I reflected on how you can organize and access blogs. Internet searches land you on a list of sites. Site selection places you on a page. Once on a page, the page can include a blog search feature, but it is limiting. Blog searches simply return all potentially related blog posts and pages in a ranked order. They don’t organize data into information, and they don’t cross reference data to create information.

Blog searches by themself didn’t seem to address good design patterns. Popular web site design patterns (1) limit the depth of navigational steps and (2) consolidate information into singular locations. At first blush, it seems blogs are ill suited to anything like that. They seem more like rambling brain dumps of what we’re working on at the moment. Things that you perceive have high value get placed in blog pages, while low value information is tossed out as blog posts.

Blog pages are not natively organized and can proliferate too quickly, making your site overwhelming to visitors. Overwhelming because they don’t know where to start. You can organize blog pages into topical trees, like the illustration from my updated blog (shown on the left). Unfortunately, this only addresses blog pages, and excludes blog posts.

Any cross reference between the information is limited to current other blog entries. After all, future blog entries are unknown. This can lead to disconnected pieces of data in a blog. I decided to stop this insanity in my blog because it was taking me too long to find something already written. It was becoming like some of the forums, rich with data but time consuming to navigate. My solution is to create subject summary pages, like Oracle Configuration and Oracle PL/SQL Programming. My summary pages include organized references to blog pages and posts. The second change is to consolidate information by updating older blogs, which makes them more complete. I was doing that to blog pages but now I’m including blog posts.

As Confucius did or didn’t say, a picture is worth many words …

While helping out in the conversion of some MySQL SQL, I tried the WITH clause inside a subquery for a multiple row INSERT statement. I got a nasty surprise, it’s not supported. I got the following error:

  FROM     dual )
                *
ERROR at line 16:
ORA-32034: unsupported USE OF WITH clause

Consistent with how I’m updating old blog pages and posts, you can find the full explanation in the updated blog post on the WITH clause. As Dominic commented, I got the syntax wrong and he’s got it for a single row subquery in the comment too. The blog page is updated with both a single and multiple row subquery.

I thought it would be nice to walk through the Excel 2008 configuration steps to query Oracle. I was quite surprised when navigating the path, this error dialog was thrown:

When you navigate to the Microsoft web site, you’ll find that you have a choice of an ODBC driver from Open Link or Actual Technologies. You might think that Oracle would have their own ODBC driver that you can use without paying for a 3rd party solution. Unfortunately, there isn’t one. The most recent kits are missing the libsqora.so shared library. The only ones that I could find are for the Mac OS X Tiger edition.

I may have missed something but you’ll find the Oracle documentation here. Feel free to comment with a solution. My solution is to use Code Weaver’s CrossOver Mac, and Microsoft Office 2007. How I regret the money wasted on Microsoft Office 2008.

A quick note, addendum, it looks like Actual Technologies is the best. Unfortunately, they charge for one copy for Oracle and another for MySQL and Postgre. What a discouraging note, but I may bite the bullet on the $60 bucks for both. I’ll defer the MySQL and Postgre until they release their 2.9 version. Don’t forget to also download Microsoft’s Query tool.

I caught a post on the OTN forum asking how to do this, and it happened to be something I’m working on for a new course that I’ll be teaching on data analytics. Ultimately, Microsoft Excel is the de facto tool of many accounts and financial analysts, protests notwithstanding.

This shows you how to query an Oracle 11g database from Excel 2007. Actually, it should work on any current version of the Oracle database. The key to making this work is having the Oracle 10g Client software or an Oracle 11g database on the same machine. The Oracle client software allows you to resolve an Oracle Network Alias (found in the %ORACLE_HOME%\network\admin\tnsnames.ora file).

The steps are provided in this blog page …

I was doing yet another install of Red Hat AS 4 in VMWare Fusion and ran into a new error. It’s the following: The virtual machine is unable to reserve memory.

The only way I found to fix this involves opening a terminal and killing the process manually. Killing without prejudice (cleanly shutdown the process and dependents) didn’t work. I had to kill it with prejudice (shutdown the process notwithstanding anything), then click the Abort button. The next error message says the peer process is missing and allows you to exit VMWare Fusion. Then, you reboot the Mac OS X.

The steps for find the process and killing it are:

1. Open a Terminal and run the following command:

# ps -ef | grep vmware

It returns something like this:

    0    90     1   0   0:00.00 ??         0:00.00 /Library/Application Support/VMware Fusion/vmnet-dhcpd -cf /Library/Application Support/VMware Fusion/vmnet8/dhcpd.conf -lf /var/db/vmware/vmnet-dhcpd-vmnet8.leases -pf /var/run/vmnet-dhcpd-vmnet8.pid vmnet8
    0    98     1   0   0:00.00 ??         0:00.00 /Library/Application Support/VMware Fusion/vmnet-dhcpd -cf /Library/Application Support/VMware Fusion/vmnet1/dhcpd.conf -lf /var/db/vmware/vmnet-dhcpd-vmnet1.leases -pf /var/run/vmnet-dhcpd-vmnet1.pid vmnet1
  501   160   115   0   1:44.12 ??         3:15.87 /Applications/VMware Fusion.app/Contents/MacOS/vmware -psn_0_77843
    0   322     1   0   0:00.07 ??         0:00.29 /Library/Application Support/VMware Fusion/vmware-vmx -E en -D ZjTtGrJgANADRAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA= -# product=1;name=VMware Fusion;version=2.0;buildnumber=116369;licensename=VMware Fusion for Mac OS;licenseversion=6.0 build-116369; -@ pipe=/var/folders/fi/fiepDOKbFJeE42RxGcDBgU+++TI/-Tmp-//vmware-mclaughlinm/vmxd9641b5487a98f78;readyEvent=24 /Volumes/Disk2/Red Hat Enterprise Linux 4 Oracle.vmwarevm/Red Hat Enterprise Linux 4 Oracle.vmx
  501   397   303   0   0:00.00 ttys000    0:00.00 grep vmware

2. Kill the VMWare Fusion process by using the following syntax:

# kill -9 322

3. Reboot the operating system.