Posting and Commenting with Code

Ok, I’ve spend all morning trying to figure out how to allow WordPress to accept code in comments — nothing seems satisfactory to me.

I think my code comes out ok (I’m using Windows Live Write to post):

select ‘x’ from dual where 1 > 0 and 2<= 3;

But I know that comments aren’t…

So, I’ve added a link to an HTML formatter for code right above the comment box.  That should allow you to pop open the HTML formatter, drop your SQL into it, convert it and then post it.

Not seamless (I’m still looking), but it should avoid some of the problems from the past few people trying to answer the Prime quiz…

Swiss Army Oracle

So the other day I was trying to debug issues with an SMTP server — it seemed to allow me to send email when I connected via a WiFi connection, but not from my phone over EVDO.  That seemed odd to me — why would the transport or network connection make a difference?

So I started to dig into SMTP relaying permissions and learned a fair amount about how you can configure SMTP to allow relaying only from specific IP addresses (reference here).

I tested it by telnet-ing to the SMTP server using each network (and a copy of PocketPuTTY for my phone — I had to find a version that allowed specific ports) — and that seemed to validate that from certain IPs I could use the SMTP server to send mail, but not from EVDO.  All of this was done without validating myself to the SMTP server.

After my anonymous tests seemed to validate the selective relaying, I thought I’d try tests with authentication.  I did some research and learned that my SMTP server supports SMTP AUTH using the AUTH LOGIN method (here).

(I know, what does all of this have to do with Oracle?  Patience — I’m getting there).

On the page about using AUTH LOGIN, it mentions that SMTP server exchanges username and password information using BASE64 encoded strings.  So, when you say AUTH LOGIN, it says “VXNlcm5hbWU6″, which is Vogon for “Username” :-)

You respond with your username in Vogon, er, BASE64 and the server comes back with “UGFzc3dvcmQ6″ (“Password”).

(Why it just doesn’t use Username and Password instead of VXNlcm5hbWU6 and UGFzc3dvcmQ6 is beyond me — especially if they’re consistent anyway.  Why not speak French?).

Anyway, to test this I needed a quick way to convert my username and passwords into BASE64 so I could interact with the SMTP server via telnet.

I could have gone here to do this online, but since I’m an Oracle guy, I just fired up SQL*Plus and typed:

select utl_raw.cast_to_varchar2(
))) vogon_username
from dual;

And got back:


A long time ago I had a developer come to me and tell me that we needed to “encode” a data field (in a table with 30,000) rows.  He proposed writing a Java program to loop through the data, retrieve it, encode it, and store it back into the database.  Since he made out that the encoding was some super special encryption that only Java could do, I saw no problem with it.  However, in a spare cycle I asked him what the super-secret Java encoding was — he said “base64″.  I was pretty sure Oracle had uuencode / decode packages, so I figured that someone had either written a base64 encoder in PL/SQL or that Oracle shipped one.  Lo and behold Oracle shipped one.  No more looping and updating — one SQL command and we were done.

I’m current writing a hypervisor for x86 VMs in PL/SQL so that I can run my old copy of Microsoft Adventure.  :-)

“You are in a maze of twisty little passages, all alike”


I took my son to see the Transformers movie this weekend — one of the main characters is Optimus Prime, or “Prime” for short.

This post is tangentially related to a challenge by Howard Rogers on whether or not one can come up with a use case for the SCOTT.EMP table which cannot be solved using plain SQL but that requires PL/SQL.  You can read about it here.

Personally I’m not so sure there is such a use case, since any set transformation should be able to be specified by using any set of sets.  And today using the connect by level tricks, one can arbitrarily construct any set one likes and use that to apply any transformation.  Heck, it’s probably mathematically provable, but just thinking about it starts to give me flashbacks and headaches…

In any case, one of my suggestions to this problem involves calculating prime numbers — it’s not a solution to the problem, because it’s possible to use SQL to generate prime numbers, but it’s an interesting problem just the same.  A long time ago, I wrote a prime number generator in APL — it’s really easy there (single line of “code”, actually), but I bet it would be neat to see how to do it in SQL.

So, my “quiz” today is:

Using only SQL, generate a list of the first 100 prime numbers.

Help! I’m syncing!

Go read this excellent post by Kevin Closson on log file syncing.  Go ahead, take your time and absorb it.  A reminder that the Oracle wait events can contain a significant amount of “waiting to run” time without being called as such.  I don’t have a pointer to it, but Cary Millsap from Hotsos has done a lot of advanced work in describing wait time components including thinking about time on the runnable queues…

I wonder if there’s a decent methodology for determining a CPU scheduling / priority / affinity algorithm for Oracle background processes?  Sounds like a good whitepaper to me!

The rest of this post has to do with my preference to avoid data syncing (not log file syncing…).  I’m a gadget guy, so I’ve got 2-3 PCs and 2-3 mobile devices in my household.  Over the past 2-3 years I’ve gotten far away from trying to keep all of the machines “in sync” with software and data.  I’ve become a big fan of web-based / server-based applications and data because I just don’t have the time to deal with differences in my small environment.

It’s with this attitude that I’m extremely disappointed with Microsoft for eliminating Outlook Mobile Access in Exchange Server 2007 in favor of Exchange ActiveSync.  I understand that the OMA function in Exchange 2003 was problematic (although I never had a problem with it), but it was dirt simple.  Point your WAP mobile browser at http://your_exchange_server_here/oma and volia, lightweight access to your email, calendar, etc.  No syncing needed, no out-of-sync data.  As long as you had a cell signal, you could get your mail.  No security worries about data on the device, because it wasn’t there.

Contrast this with Exchange ActiveSync — I’ve read the documentation and I’m still not clear on how it works — other than I need to use a Windows Mobile 6.0 in order to take “full advantage” of it.  ActiveSync on the iPhone?  No.  On your generic phone running Opera Mini?  No.  More time spent by IT supporting and helping people configure Exchange ActiveSync?  Yes.  Who does this benefit again?  Why all this emphasis on “off-line” access (with its associated security concerns) when “on-line” access is getting more and more ubiquitous?

BTW, this is why I’m also not a big fan of data copying / syncing within databases either — more places for stuff to go wrong, more complexity.  By all means, use it if you need to, but spend time making sure you really need it.  I think you’d rather spend time adding value to the data instead of writing more code and procedures to make sure the data simply moves from one place to another…

Navel Gazing…

This is the term I use for a system that’s spending a lot of time doing things other than the business purpose for which it is ostensibly designed.    I was reminded of it last night when I was booting up my personal laptop — a boot sequence that had gotten intolerably slow after I “upgraded” to Norton Internet Security 2007.

Originally, I was looking forward to the upgrade.  The laptop in question is about 3 years old right now and was still running NIS 2004.  My virus subscription was running out and Symantec offered the upgrade to 2007 for the same price as the virus subscription renewal.  I thought that was a good idea — encourage people to move off old versions by simply giving them the latest version for the same price they were going to pay anyway for the subscription renewal.

However, NIS 2007 is so comprehensive that my machine was spending all of its time “protecting” me and making so many of my tasks ridiculously slow that I started to wonder.  I wondered if the way to protect me was to not allow me to do anything :-)  I ended up removing the s/w and my machine is now responding to what I need it to do.

Anyway, that episode reminded me of systems where the bulk of the processing has little or nothing to do with the business purpose for the system.  I’m thinking of systems that spend lots of cycles producing management summary reports, web status pages, and/or constantly analyzing themselves instead of processing orders, adding journal entries, registering new users, etc.  It’s no secret that I love to watch charts of system utilization over time — and I wonder why systems that are “idle” have a constant run rate of 30-40%.  I used to say that if you don’t use the CPU you’ve bought, it’s just going to waste, but nowadays with power concerns and virtualization options, why waste any CPU?

In the end, I like metrics — determine the number of CPU seconds and I/O bytes per business transaction (per $$$ if you can) and get it as small as possible.  Find ways to be able to run on smaller hardware (smaller footprint, smaller rack space, less power required, etc).

Lately I’ve been looking more closely at compression too — (11g looks cool here) — how can I not only reduce storage costs, but bandwidth required too?  Oh, and backup requirements — can I make stuff small, read-only and with relaxed access / performance requirements?

You know what would be cool?  Automatic LRU partitioning of tables into Hot and Not Hot (:-)) areas where the Not Hot stuff could be compressed, made read-only after defined time, etc.  I bet that could be done “automatically”.