Expert Oracle APEX

I'm a bit of a fan of APEX; haven't used it consistently in a while but I think it's a great tool. I've annoyed many managers/DBAs about getting it installed and configured for use...most of that to no avail.

Many years ago, almost 6 to be exact, I decided to port my business' J2EE app over to APEX and I needed hosting. Still relatively new, the market was small. Through the forums, I found John Scott and ShellPrompt. It took a few months to write the J2EE application from scratch, it took less than 2 weeks of off-hours work to port. I hosted my site there for a year, maybe a little longer. During that time, John was pure awesome. I blogged about John's customer service once, but that was another life and the blog no longer exists. We have met IRL, but have yet to have a beer together, I'm pretty sure he owes me at least 4 now.

Anyway, John has put together a new book, Expert Oracle Application Express which is a joint effort by some 14 different APEX authors.



While the content must be outstanding, the best part is that all funds will be donated to the families of 2 men, Carl Backstrom and Scott Spadafore. Both men worked for Oracle on the APEX team. Both men were incredibly involved in the community. Both were highly regarded in that community.


John blog's about it here.

If you use APEX, new to APEX, or just want to know WTF it is, go out and (pre)order this book now. Not only do you get a great resource, but you get to help out the families of Carl and Scott.

The Nerd Defense

Really couldn't pass this one up. Friend and co-worker, Erica Baer [@skibaer] who has a Ph.D. in some sort of crazy thing (and she's not even 30!) sent me a link to Eyeglasses and Mock Juror Decisions...I'm pretty sure her Ph.D. is in something related to psychology (which is good for me, free counseling) and the selection of jurors. Wait, it's Forensic Psychology, LinkedIn told me so. Sadly, much of my charm is wasted on her, she sees right through it. Oh well.

So the link...had a great picture:



There are like 4 elements that apply to me there:
1. Nerd
2. Justice
3. Bald
4. Rollie Fingers-esque mustache
5. Glasses

Ok, 5, I can count, but you knew that.

OEL 6 + VirtualBox Guest Additions

I'm taking my first second spin at Oracle Enterprise Linux 6. The first time, I did not install a Desktop, so I was completely lost. I suppose I could do it now...but, I'm not going there just yet.

One of the very first things I do when creating a VM is install the Guest Additions. The main reason is screen size. Out-of-the-box, it's 800x600 or some such non-sense. I'm sure I could cope, if I had to, I'd rather not, it's just annoying. I guess a little perspective is in order:



That's against the backdrop of a 23" monitor. So yeah, annoying.

After having done this about 20 times, I know you need to install some extra packages. Usually the kernel* and make packages. I'll just skip straight to installing the Guest Additions though, let's see what happens.

Nothing to see here.

Well, Guest Additions includes the bi-directional clipboard support, and I'm not in the mood to type up the entire thing. Here's the specific error I received however (typed by hand, you are welcome):

Building the VirtualBox Guest Additions kernel modules
The headers for the current running kernel were not found. If the following
module compilation fails then this could be the reason.
The missing package can be probably [sic] installed with
yum install kernel-devel-2.6.32-100.28.5.el6.x86_64

The output of the log file shows this:

[root@oel6test VBOXADDITIONS_4.0.4_70112]# tail /var/log/vboxadd-install.log -n 100
Makefile:23: *** Error: unable to find the sources of your current Linux kernel. 
Specify KERN_DIR= and run Make again.. Stop.

OK, let's try that. The Google Machine told me the kernel lives in /usr/src/kernels/

[root@oel6test VBOXADDITIONS_4.0.4_70112]# export KERN_DIR=/usr/src/kernels/

Run the command again and it tells me that "KERN_DIR does not point to a directory. Stop." No you stop. Whatever.

I then go into the "additional" packages provided by the OEL6 DVD. Funny, now that I look at the kernel* packages, I wonder if they are outdated? uname -r shows me 2.6.32-100.28.5.el6.x86_64 which I believe is correct, but the kernel* packages on the disk look to be in the 2.6.32-71 range. Weird, since I seem to have the latest...

Now, it's time for some public yum, Oracle's public yum server to be exact. Awesome, instructions on the front page on how to set it up, makes it somewhat foolproof for someone like me:

Download and copy the appropriate yum configuration file in place, by running the following commands as root:

Oracle Linux 4, Update 6 or Newer

# cd /etc/yum.repos.d
# mv Oracle-Base.repo Oracle-Base.repo.disabled
# wget http://public-yum.oracle.com/public-yum-el4.repo
Oracle Linux 5

# cd /etc/yum.repos.d
# wget http://public-yum.oracle.com/public-yum-el5.repo
Oracle Linux 6

# cd /etc/yum.repos.d
# wget http://public-yum.oracle.com/public-yum-ol6.repo
Oracle VM 2

# cd /etc/yum.repos.d
# wget http://public-yum.oracle.com/public-yum-ovm2.repo
Enable the appropriate repository by editing the yum configuration file

Open the yum configuration file in a text editor
Locate the section in the file for the repository you plan to update from, e.g. [el4_u6_base]
Change enabled=0 to enabled=1
Begin using yum, for example:

yum list

yum install firefox

And here is where I cheat, sort of. Instead of just using yum, I go to the Add/Remove Software GUI.



Wait just a second...those "%100%" packages have "uek" in them, I thought that was for the Unbreakable Enterprise Kernel. Did I install that? I thought I opted out of that...

So back to the DVD and there are those packages sitting there, of the "uek*100*" variety. Let's install those.

kernel-uek-2.6.32-100.28.5.el6.x86_64.rpm
kernel-uek-debug-2.6.32-100.28.5.el6.x86_64.rpm
kernel-uek-debug-devel-2.6.32-100.28.5.el6.x86_64.rpm
kernel-uek-devel-2.6.32-100.28.5.el6.x86_64.rpm
kernel-uek-doc-2.6.32-100.28.5.el6.noarch.rpm
kernel-uek-firmware-2.6.32-100.28.5.el6.noarch.rpm
kernel-uek-headers-2.6.32-100.28.5.el6.x86_64.rpm

I install the debug, the debug-devel, devel and headers. Reboot.

No joy. But I can see that it can't find the gcc command, so I just have to load that package. Of course it isn't that easy, it has dependencies.

[root@oel6test Packages]# rpm -ivh gcc-4.4.4-13.el6.x86_64.rpm 
warning: gcc-4.4.4-13.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
error: Failed dependencies:
 cloog-ppl >= 0.15 is needed by gcc-4.4.4-13.el6.x86_64
 cpp = 4.4.4-13.el6 is needed by gcc-4.4.4-13.el6.x86_64

First up:

[root@oel6test Packages]# rpm -ivh glibc-headers-2.12-1.7.el6.x86_64.rpm 
warning: glibc-headers-2.12-1.7.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Preparing...                ########################################### [100%]
   1:glibc-headers          ########################################### [100%]

Now I can load the devel files for glibc.

[root@oel6test Packages]# rpm -ivh glibc-devel-2.12-1.7.el6.x86_64.rpm 
warning: glibc-devel-2.12-1.7.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Preparing...                ########################################### [100%]
   1:glibc-devel            ########################################### [100%]

What else?

[root@oel6test Packages]# rpm -ivh cpp-4.4.4-13.el6.x86_64.rpm 
warning: cpp-4.4.4-13.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
error: Failed dependencies:
 libmpfr.so.1()(64bit) is needed by cpp-4.4.4-13.el6.x86_64

Seriously. Since this isn't the first time today, I know what package that so file is from.

[root@oel6test Packages]# rpm -ivh mpfr-2.4.1-6.el6.x86_64.rpm 
warning: mpfr-2.4.1-6.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Preparing...                ########################################### [100%]
   1:mpfr                   ########################################### [100%]

Now cloog-ppl.

[root@oel6test Packages]# rpm -ivh cloog-ppl-0.15.7-1.2.el6.x86_64.rpm 
warning: cloog-ppl-0.15.7-1.2.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
error: Failed dependencies:
 libppl.so.7()(64bit) is needed by cloog-ppl-0.15.7-1.2.el6.x86_64
 libppl_c.so.2()(64bit) is needed by cloog-ppl-0.15.7-1.2.el6.x86_64

What about a ppl* package?

[root@oel6test Packages]# rpm -ivh ppl-0.10.2-11.el6.x86_64.rpm 
warning: ppl-0.10.2-11.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Preparing...                ########################################### [100%]
   1:ppl                    ########################################### [100%]

Almost there. Back to cloog.

[root@oel6test Packages]# rpm -ivh cloog-ppl-0.15.7-1.2.el6.x86_64.rpm 
warning: cloog-ppl-0.15.7-1.2.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Preparing...                ########################################### [100%]
   1:cloog-ppl              ########################################### [100%]

cpp

[root@oel6test Packages]# rpm -ivh cpp-4.4.4-13.el6.x86_64.rpm 
warning: cpp-4.4.4-13.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Preparing...                ########################################### [100%]
   1:cpp                    ########################################### [100%]

gcc?

[root@oel6test Packages]# rpm -ivh gcc-4.4.4-13.el6.x86_64.rpm 
warning: gcc-4.4.4-13.el6.x86_64.rpm: Header V3 RSA/SHA256 Signature, key ID ec551f03: NOKEY
Preparing...                ########################################### [100%]
   1:gcc                    ########################################### [100%]

Yes!

It's working...

Sweet!

[root@oel6test VBOXADDITIONS_4.0.4_70112]# /bin/bash ./VBoxLinuxAdditions.run install
Verifying archive integrity... All good.
Uncompressing VirtualBox 4.0.4 Guest Additions for Linux.........
VirtualBox Guest Additions installer
Removing installed version 4.0.4 of VirtualBox Guest Additions...
Removing existing VirtualBox DKMS kernel modules           [  OK  ]
Removing existing VirtualBox non-DKMS kernel modules       [  OK  ]
Building the VirtualBox Guest Additions kernel modules
Your guest system does not seem to have sufficient OpenGL support to enable
accelerated 3D effects (this requires Linux 2.6.27 or later in the guest
system).  This Guest Additions feature will be disabled.


Building the main Guest Additions module                   [  OK  ]
Building the shared folder support module                  [  OK  ]
Doing non-kernel setup of the Guest Additions              [  OK  ]
Starting the VirtualBox Guest Additions                    [  OK  ]
Installing the Window System drivers
Installing X.Org Server 1.7 modules                        [  OK  ]
Setting up the Window System to use the Guest Additions    [  OK  ]
You may need to restart the hal service and the Window System (or just restart
the guest system) to enable the Guest Additions.

Installing graphics libraries and desktop services componen[  OK  ]

Reboot (or just restart your X-windows.



Voila!

Update: 03/30/2011 20:50:00 EST
Thanks to Robin Moffat [blog|@rnm1978] for providing a much simpler solution:

yum install kernel* dkms gcc

Reboot after that and install your Guest Additions. No more having to figure out the dependencies on your own (I'll leave it to you to decide whether that is a good exercise or not).

Update: 02/14/2013 12:00:00 EST
One more addition from Mr. Closson (comments, below).

# yum install kernel-uek-devel 

Decoding DECODE

I've never been a fan of DECODE. I find it difficult to read, especially past the (EXPRESSION, MATCH_TO_EXPRESSION, RESULT_IF_MATCHED, ELSE_RETURN_VALUE), i.e. 4 spots there. A friend of mine was trying to...decode a long DECODE expression, so I put on my Cary Millsap hat and tried to break it down.

Despite never having seen Cary speak, I love that he always includes Teacher in his bio. Those that present are in fact teachers, but with that inclusion, I think Cary takes it a step further.



(I plan on changing that streak this year, see Tools, Tools, Tools!)

So here's what I said, maybe it can help some newbie out there get started. Me, I encourage people to avoid the statement suggesting the CASE statement instead...mostly for it's readability (naturally I have seen that abused as well, oh well).

Here's a mock statement:

DECODE(my_expression,0,0,NULL,NULL,(another_expresssion/my_expression))

First, format the dang thing. Put some white space in there.

DECODE( my_expression, 0, 0, NULL, NULL, ( another_expresssion / my_expression ) )

We're not exactly tuning, but readability goes a long way.

Now, let's look at the picture defined in the docs:



Just for simplicity's sake, I'm going to spell it out:

DECODE( POSITION_1, POSITION_2, POSITION_3, POSITION_4, POSITION_5, POSITION_6 )

At least to me, that helps. Up to 4 places, this probably isn't necessary, but as it gets long (and I've seen some whoppers), it's good to be able to draw it out.

What does that mean? If POSITION_1 = POSITION_2, then use the value from POSITION_3. If POSITION_1 = POSITION_4, use the value from POSITION_5. The default will be POSITION_6.

Written out in CASE format, it would look like this:

CASE
  WHEN my_expression /* POSITION_1 */ = 0 /* POSITION_2 */
  THEN 0 /* POSITION_3 */
  WHEN my_expression /* POSITION_1 */ IS NULL /* POSITION_4 */
  THEN NULL
  ELSE my_expression / another_expression /* POSITION_6 */
END

I write this up because I got the "You should be a teacher" comment at the end, which I took as a great compliment.