Oracle-free Oracle access

I got an interesting question from Guido d'Amico... he wants to use Python scripts to access Oracle databases from machines with no Oracle software installed. Between us, we came up with these options.

• cx_Oracle and DCOracle2: These "classic" DB-API2 modules both rely on the OCI (Oracle Call Interface), a piece of software distributed by Oracle. (I believe all comparable means for accessing Oracle from other languages rely on the OCI, too.) There's just no way around that - you need some sort of Oracle client installed on the machine you're using them from.
  You don't have to bulk up your machine with a full-blown standard Oracle client, though.
  • Oracle Instant Client is lightweight (85 MB on my Windows box), free, and redistributable. For better and worse, it comes as a simple zipped set of files - if you want any environment variables set (ORACLE_HOME, PATH), you need to do that yourself.
  • OracleXEClient is likewise lightweight (72 MB) and free, and very easy to install.
  Neither of these options comes with a /network/admin/ folder, which might be a little confusing - unless you want to make your connections with Easy Connect, you'll need to set up $ORACLE_HOME/network/admin/tnsnames.ora by yourself.
• You can use ODBC. mxODBC has been around for a while, but is not free for commercial use. pyODBC is free, and I hadn't actually heard of it until I researched this question - maybe I'll review it (or at least find a review) sometime soon.
• You can use JDBC from Jython. Andy Todd and Przemek Piotrowski have blog posts detailing this.
• You can go to IronPython and... um... OK, I've never yet done database access from IronPython, but I assume that using ODT.NET from IronPython is easy enough.
  
  EDIT: Przemek Piotrowski has not just made it work, he's posted a tutorial on ODP under IronPython. Thanks, Przemek!

Ohio LinuxFest

Ohio LinuxFest is coming Sep. 30!

I absolutely loved it last year. It looks like I won't be able to make this year... *weeps bitter tears* - unless, that is, one of you would like to babysit a ten-year-old? No? Bleah, Saturday events are not parent-friendly. (Actually, I did meet one attendee last year who brought his ten-year old. I can't imagine pulling that off with ours, though.)

But anyway, you can give me some comfort by going yourself and having a fantastic time. They've promised extra Linux-newbie stuff this year, too. It will be better than Cats; you will want to see it again and again.

Qnxo price weirdness

Let me say, first off, that I deeply appreciate everything Steven Feuerstein does for the Oracle community.

But the marketing of Qnxo has just gotten weird. My boss asked me for my wishlist for the software budget, and I tried to put Qnxo on it. On Qnxo's "buy it" page, I got

Qnxo is, for the time being, available only on a trial basis. The trial version offers the full range of Qnxo functionality and will work for 30 days after install. If you would like to continue using Qnxo after that point, please visit the Support page and fill out the Contact form. We will then provide you with a key that will enable Qnxo for use until the end of 2006. If you have any questions, please also submit your question through the Support page.

I thought refusing to give customers a straight up-front answer on a price was a hallmark of $10K+ software. Last I heard, Qnxo was $175.

If it were open-source, I'd dive in and use it. If it were proprietary with a clear price, we could decide whether to buy it. But this? I'm supposed to make it a part of my development process based on the hope that, when we're ultimately given a price, it'll be acceptable? I don't see how anybody can do that. I know we can't.

[EDIT 09-09-2007: Steven himself (!) just notified me via his comment that Qnxo is now QCGU, and it's free of charge. Thanks, Steven!]

dual-boot Ubuntu/Windows

I have a new laptop from my boss! A Dell XPS M1710; it feels a bit like an SUV of laptops... it weighs more than I do, comes with an on-board fusion reactor, and emits a menacing red glow from around its edges. It's just chock-full of gigabytes and megahertz and stuff. I will never complain that my boss skimps on our hardware. I think I need a mule to carry it, though.

Anyway, since I'm torn between my love of Linux and my workplace's Windows mandate, I decided to make the machine mirror my split. I need a dual-boot Windows/Ubuntu machine. Since this is the first time I've done so, I'm really glad it was on a clean machine... it was not as smooth as vanilla Ubuntu.

Here's what I did. You should skip step 2.

1. Found good documentation on the Ubuntu wiki
2. Let standard Ubuntu installer attempt to shrink the main Windows (NTFS) partition, to give me room for Linux partitions. I ended up with partitons of type "unknown". Oops!
3. Re-installed Windows from its CD, this time setting its NTFS partition up with about 1/3 the total disk space and leaving the rest uncommitted.
4. Followed the instructions from the "Issues with Windows XP and NTFS" section of WindowsDualBoot: created a System Rescue CD, booted from it, and used run_qtparted to redo the partitioning.
5. Created a single physical partition - the last one of the four I'm allowed.
6. Within the final physical partition, created a linux-swap partition (4 GB for my 2 GB of memory). Divided the rest of the disk between an ext3 partition for Linux root and a FAT32 partition for data that Windows and Linux will be able to share.
7. Started the Ubuntu installer again; chose "manually edit the partition table", and assigned the swap and ext3 partitions to Ubuntu.

Accepting the defaults, my FAT32 partition got labelled "sda5" under Ubuntu and "e:" under Windows. Sure enough, both OS's can access it OK.

Success! One dual boot laptop, hooray! (Yes, I know, if I'd been really bold, I would have used Xen to run both OS's virtually. Maybe next time.)

Blogging tools

I've seen so many blogging tools over the past year or so... many of them seem like interesting projects that have generated lots of excitement.

Here's the part I don't get, though. Why? Had people honestly been going around saying, "Wow, entering my blog entries conventionally is such a time drain"?

I blog a few times a month, and the challenge is having something worth saying and choosing words to say it well - not slinging those words into Blogger's standard posting interface. It's hard to imagine how shaving seconds off that could honestly be worth choosing, installing, and understanding a blogging tool, much less writing one. Maybe if I blogged six times a day, but who would read that?

Maybe someone who "gets it" can explain it to me?

I suspect that this is one of these cases that's being driven by the coolness of the solutions, not the actual need for them. Mind you, I've got nothing against that. I have often spent three hours writing code to avoid a one-hour manual job. (Which is not as illogical as it sounds, because when you finish the manual job, somebody's bound to say, "Oh, I'm so sorry, but we need it done again..." Not that a logical consideration of that possibility is what drives me; I do it because it's fun.)

dumpfile diving

The oracle EXP utility generates a dumpfile which, although technically binary, actually contains lots of readable ASCII. Sometime I need to see this information, especially since dumpfiles don't provide a way (that I know of) of summarizing their contents and the conditions of their generation. In a perfect world, we'd never have to work from with dumpfiles burned onto scratched CDs by unknown parties and abandoned in the dusty corner behind the recycle bin, but this is not a perfect world.

Anyway, I just went through several techniques of examining a mysterious.dmp, and thought I'd share the experience. Much of it would apply to delving into any mixed binary/ascii file.

• The worst way: more mysterious.dmp
  
  gave me frightening glyphs and angry beeps (xset b off to stop those), like R2-D2 invoking dread Cthulhu. Worse, my session would henceforth speak to me only in proto-Sumerian. I could kill the terminal window and open a new one, of course, but I still spent several hours plastering smooth curves over all the office's sharp corners, just to be safe.
  
  
• Not quite so bad: less mysterious.dmp
  
  let me look at the file, making harmless marks of the binary characters, and didn't mangle my session's character set. Yay! I do need to make a habit of using less instead of more.
  
  
• Still pretty painful: grep -a "what I'm looking for" mysterious.dmp
  
  The -a flag makes grep look into a file even though it's binary. It has no proper idea of where lines end in a binary file, though, so your hits can be really long. I had better luck grepping the files that resulted from the operations below.
  
  
• Good: imp me@mydb show=Y file=mysterious.dmp full=y log=mysterious_contents.txt
  
  This gives you a clean-looking file (well, except for all the gratuitous quotation marks). It's also the only technique I know that you can use on Windows (without Cygwin). You don't get data contents, though, just DDL.
  
  
• Best: strings mysterious.dmp > mysterious_contents.txt
  
  This GNU strings utility is really great! You get the ASCII, the whole ASCII, and nothing but the ASCII, quick and clean.

What I still don't have, though, is a way to examine a dumpfile and find out exactly what command was used to generate it. That would give all kinds of fantastic information: What instance was it? Which user did the export? Was it full? ... and so forth. If you know of a way to query a dumpfile for this kind of information, please comment!

xubuntu

Xubuntu is a variant of Ubuntu that uses the lightweight Xfce desktop environment, making it a good choice for low-powered systems.

Or so they say. So I dusted off (literally) a Gateway Solo laptop (Pentium II, 300 MHz, 64 MB RAM) and replaced its Windows 98 with Xubuntu 6.06. And that's what I'm posting from now! Go, Xubuntu! The only problem is that a naughty kitten tore several keys out of the keyboard several years ago, making typing difficult. And he would have to get the 'e', the little stinker.

I'm using a 2-year-old wireless card with it, though. I failed with my first attempt to make its old ethernet card work, and decided to take Tim Almond's advice and just use a known compatible device rather than go into ethernet-card archaeology.

Hmm, now there's a new working laptop in the house. Oh, the possibilities...

History

Cleanup of obsolete material continues.

I'm holding a boxed set of Oracle 7.3.4 Server software. It's still shrink-wrapped.

Can I really throw this out? 7.3.4 is where I started, after all. Then again, if I keep it, am I like the people who imagine that their comic book "investments" will pay off one day?

I love working in IT... yet it can be horrifying, realizing just how ephemeral our constructions are. If you're a mason, your work may outlive you by millenia. If you're a geek and you want your work to outlive you, you'd better get very sick or take up some dangerous hobbies.

obsolete books

Following a recent run of absurdly good luck at user-group drawings, I must face the fact that my cubicle will not tolerate my current inventory of books.

"So throw them out". But... but... books represent knowledge, how can I just throw them out? Especially the ones that I never did get around to devouring... sure, those skills may have proven irrelevant to my work, but it's stuff I never learned! How can I give up on learning it, send Knowledge away unlearned?

Ahem. Anyway, psychological issues aside, does anybody know a good destination for mildly obsolete technical books? The recycle bin seems so brutal, yet how can I find somebody who'd want them? This is mostly Oracle and Java stuff averaging five years old...

Digital cholesterol

That's my new term for the performance-clogging stuff that big-enterprise IT departments automatically install to user desktops via the enterprise network. Every week, a bit more gets piped in without my foreknowledge or consent, gradually crippling my machine.

I do my serious work on my Ubuntu laptop, which is barred from my workplace's network; I download software from home and transfer my finished products to work by USB drive. At first that seemed like an unfortunate price I had to pay; now it's looking more like a blessing. My Ubuntu laptop sizzles along as fast as the day I first booted it, while my plugged-in Windows machine creaks and groans and is slowly becoming unusable.

sqlWrap and oraDifference, packaged right

In the past few months, I've written sqlWrap.py, a database connection convenience wrapper, and oraDifference.py (which depends on sqlWrap.py). I didn't have any particularly sane way to distribute them, though, and I apologize to anybody who made the attempt.

Well, it may amount to delusions of grandeur, but I registered them as a SourceForge project. Now they have

• A single, sane place for downloads, properly versioned
• A regular distutils python installer: unzip it, run

  python setup.py install

  , and everything goes where it belongs (sqlWrap.py in your Python library, oraDifference.py in your python Scripts directory)
• a Windows executable installer (oooh, aaah)
• Homepages with documentation: for sqlWrap.py and oraDifference.py

Because oraDifference.py depends on sqlWrap.py, I put them together in a single install for simplicity.

This has been my first time working with Python's distutils module (so much easier than I expected!) and Sourceforge (not so much). It's been fun!

sqlpython enhancements

I told you that Luca Canali's sqlpython is wonderfully easy to customize.

I probably should have also told you that it's dangerously addictive to customize. I kind of went out of control, and produced sqlpyPlus.py, a module of enhancements to sqlpython.

- SQL*Plus-style bind variables
- Query result stored in special bind variable ":_" if one row, one item
- SQL buffer with list, run, ed, get, etc.; unlike SQL*Plus, buffer stores session's full history
- @script.sql loads and runs (like SQL*Plus)
- ! runs operating-system command
- SQL*Plus-style describe, spool
- write sends query result directly to file
- comments shows table and column comments
- compare ... to ... graphically compares results of two queries
- commands are case-insensitive
- show and set to control sqlpython parameters

sqlpyPlus.py is not as clean and elegant as sqlpython - that's one reason I put it in a separate module, so that you can keep it separate from the original sqlpython and your own homemade enhancements. But it should cover pretty much everything you usually use SQL*Plus for, plus some goodies I hope you'll like.

[EDIT: Since I wrote this, Luca has wrapped an enhanced and debugged version of sqlpyplus into his distribution of sqlpython itself. Now you should simply go and get or upgrade sqlpython, and you'll have these goodies automatically.]

The missing Mercurial manual

I think I've found my ideal solution for version control. I used bzr for a few weeks, and appreciated its distributed nature - no single repository has to be the ultimate authority, so it works well for machines that can't all be connected to the same network. I use a travelling USB drive to sync my machines, so it was perfect... except that bzr can take several minutes for just a few merges. It got annoying when the merge was keeping me from leaving work.

Mercurial is also distributed, but it's very, very fast. It's even (gasp) well-documented! There are, however, a few things I think a newbie should know up-front.

• The tutorial assumes you'll start by copying an existing mercurial repository. I wanted my own, though, and it took me several tries to figure out that I needed to do this:
  ~/existing$ hg init
~/existing$ hg add foo.bar
~/existing$ hg commit
~/existing$ cd ..
~$ hg clone existing newdir
  Later on, if I make new files in existing, they won't get to newdir until I
  ~/existing$ hg add newfoo.bar
~/existing$ hg commit
~/existing$ cd ../newdir
~/newdir$ hg pull
~/newdir$ hg update
pull brings fresh metadata in from the other repository, storing it in .hg, but doesn't actually go on to update the files themselves. That's what update does. It took a while to get it, but now that I do, it seems intuitive and helps me feel in control.
• Your EDITOR or HGEDITOR environment variable must be set, or else hg commit will hurl you pitilessly into vi (*nix) or throw up its hands and scoff at you (Windows).
• But, if you use gedit and already have another file open in it, you get
  ~/existing$ hg commit
transaction abort!
rollback completed
  I suspect that may happen with other multi-file editors, too. I'll use EXPORT HGEDITOR=pico.

Python in OTN again

Here's a cheer to Przemek Piotrowski for his recent OTN article, Build a Rapid Web Development Environment for Python Server Pages and Oracle. Python Server Pages are just one of the 10²² ways to build a web application with Python, but his methodical instructions would be useful for doing anything remotely related (including installation of Oracle XE and mod_python).

SQLpython - a SQL client of your very own

Luca Canali has written SQLpython, a lovely new SQL command-line tool for Oracle.

Right now, the most popular SQL command-line tools are

• SQLPlus, included with Oracle, is sometimes great, sometimes annoying, and impossible to modify (source code not available).
• gqlplus is open-source. It's written in C, though, which means (to my mind) that you'll need all of your strength and all of your courage if you want to modify it.

So, download sqlpython.tar, untar it, put sqlpython.py and mysqlpy.py somewhere handy (like your Python library), and then:

$ python
>>> import mysqlpy
SQL.NoConnection> connect hr/hr@xe
SQL.xe> select * from employees;

Now comes the fun part! Open up mysqlpy.py and sqlpython.py and start modifying. They're very basic right now, but very clean, concise, easy to understand, and easy to modify. For instance, I wanted to be able to issue Python commands like this:

SQL.xe> py print 'This is a python command';
This is a python command.

So I added this method to mysqlpy:

    def do_py(self, arg):
        exec(arg)

That's all I did - not one keystroke more - and it works. Now that's extensibility!

If you're not an Oracle person and you're envious, as far as I can tell, it should be easy to modify SQLpython to use any DB-API2 adapter.

Stored procedures from cx_Oracle

A couple of people have asked me about calling Oracle stored procedures from cx_Oracle. It's taken me a while to answer, because... I didn't know! I'd only had experience doing them the 'dumb' way:

>>> ora = cx_Oracle.Connection('scott/tiger@orcl')
>>> curs = ora.cursor()
>>> curs.execute('execute immediate myStoredProc(:a)',{'a':'the letter a'})

... but, of course, that won't do if (for instance) you want OUT variables. So I did a little research. cx_Oracle provides callproc and callfunc, but using them can get squirrely. Say you have PROCEDURE times_two(n IN NUMBER, result OUT NUMBER).

>>> n = 1
>>> curs.callproc('times_two',[2, n])
[2, 4]
>>> n
1

In other words, if you just pass a regular Python variable to callproc, the value won't actually change, OUT mode notwithstanding. If you want the new value, you'll just have to assign it there from callproc's return value.

Alternately, you can prepare the way by setting up your in/out variable as an instance of a special cx_Oracle object type, as follows...

>>> n = curs.var(cx_Oracle.NUMBER)
> curs.callproc('times_two',[5,n])
[5, 10.0]
>>> n
<cx_Oracle.NUMBER object at 0xb7cf2480>
>>> n.getvalue()
10.0

Pre-setting a variable's type? Calling .getvalue() just to see the contents? What an un-Pythonic pain! As far as I know, for the time being, cx_Oracle and PL/SQL procedures with IN-OUT parameters are simply two great tastes that do not taste great together. You can do it, you just won't feel like you're having Pythonic fun.

On the plus side, if the stored function or procedure is within a PL/SQL package, callproc accepts that in the way you'd guess:
> curs.callproc('multiplication_package.times_two',[5,n])
[5, 10.0]

Oh, and it looks like sqlWrap.py wasn't handling .callproc. I've posted a correction.

IOUG Collaborate! handouts

If you're on your way to Collaborate!, and getting annoyed at the way you need to search manually for each session just to download its session materials, this script may be handy. It lets you grab the session materials from your personal itinerary.

Be gentle, it was written in a huge hurry.

#!/usr/bin/python
"""Creates a version of your Collaborate! personal itinerary with links to
session materials.

To use:
0. Make sure your machine has Python.  www.python.org
1. Login to your personal itinerary at
http://iougew.prod.web.sba.com/displaymod/ITIntro.cfm?conference_id=44
2. Once your personal itinerary is showing, use Save As to save the webpage
to your hard drive.  Name it PersonalIT.cfm.html.  (This should be the 
default name.)
3. Put this script in the same directory with PersonalIT.cfm.html.
4. Run the script by issuing 'python makeLinks.py' at the command prompt.
5. Open the generated file PersonalIT.withLinks.cfm.html with a browser.
6. The (find materials) links for each title will search for session materials.

By Catherine Devlin (catherinedevlin.blogspot.com)"""
import re, urllib
titleRe = re.compile('(Title:</td>\s+<td.*?>(<a href.*?>(.*?)</a>))', re.DOTALL | re.MULTILINE)
f = open('PersonalIT.cfm.html')
contents = f.read()
newContents = contents
f.close()
sessions = titleRe.finditer(contents)
sessionLinks = [s.groups()[1:] for s in sessions]
for (wholeLink, title) in sessionLinks:
    withNewLink = '%s <a href="http://iougew.prod.web.sba.com/proceedingmod/SearchEvents.cfm?conference_id=44&searchType=2&title=%s">(find materials)</a>' % (wholeLink, urllib.quote(title))
    newContents = newContents.replace(wholeLink, withNewLink) 
newFile = open('PersonalIT.withLinks.cfm.html','w')
newFile.write(newContents)
newFile.close()

oraDifference.py

oraDifference.py - a tool for comparing items that differ between two Oracle schemas. The basic idea is to leverage the excellent graphical diff/merge tools available for file comparison and conveniently use them to inspect database object differences.

There are many programs that can compare two database schemas and tell you which objects are defined differently between them. That's really not good enough, though, because you then need to tediously dig into the definition of each (allegedly) differing object by hand, and perform any desired reconciliation by hand.

I wrote oraDifference.py to make comparing and reconciling schemas more convenient. For example, let's say you have the SCOTT schema in production and development instances. Stored function MYFUNC is defined in both, but the definition differs. View MYVIEW is defined only in development. Then running
python oraDifference.py scott@prod scott@dev
will generate the following batch files (Win) or shell scripts (*nix):

• oraDifferenceResults/FUNCTION/MYFUNC.bat, which will invoke a graphical diff/merge tool showing you precisely where MYFUNC's definition differs between the two instances
• oraDifferenceResults/FUNCTION/MYFUNC-copy-SCOTT-DEV.bat, which will write DEV's definition of FUNC into PROD
• oraDifferenceResults/VIEW/missingFrom-SCOTT-PROD/MYVIEW.sql, the definition of MYVIEW
• oraDifferenceResults/VIEW/missingFrom-SCOTT-PROD/MYVIEW-copy-SCOTT-DEV.bat, which writes MYVIEW into PROD

For now, you have to do the work of getting oraDifference.py (and sqlWrap.py) manually and putting them someplace appropriate. I do intend to wrap them up in a proper distutils distribution (maybe even with an .egg).

I'm posting this now because I find it really useful already. You may find some of my design decisions quirky - for instance, I mush all one-liner items (grants, synonyms) into big files by category, rather than making separate files for each grant or synonym. It's Python, though, so you should be able to tweak it to meet your tastes. Also, you can tweak the process oraDifference.py uses to decide whether two objects differ. I have always been annoyed that I can't stop TOAD's "Schema Compare" tool from turning up dozens of "differences" that I consider false hits. With oraDifference.py, you can just get in there and change it.

Eventually I hope to release something that will look polished and final, but for now, feel free to use it, and re-code any part that doesn't match your preferences - and let me know about any of your changes that you think should go into everybody's version.

For fairness, I'll mention some other options I found for schema comparison...

• LivingLogic's oradiff.py (part of ll.orasql) is the closest to oraDifference.py. It also compares the text for each object, but it outputs in unified diff format (or unidiff). If you can read unidiff comfortably - welcome, advanced extraterrestrial visitor! The oradiff.py source code looks tidy and well-organized, but it's still not obvious to me how to tweak it.
• schemaCompare, a Java program, was registered at SourceForge in June 2002, but has not yet released any files. I conceived oraDifference.py about two weeks ago. Not to suggest that this implies anything about the relative productivity of various languages. (jab, jab)

Python Core for Oracle

He put it in a comment, but it bears repeating:

Przemek Piotrowski has written up Python Core for Oracle, a set of instructions to put a top-to-bottom data-driven webserver stack on your machine in about half an hour. The installation is surprisingly straightforward. It's all fully functional, and it's all free.

This is the Golden Age!

Cheetah templating

Yesterday, Python's str.Template failed me, so it was finally time to learn Cheetah.

I wanted to use templates like
'my list has $len($myList) objects; the first is named $myList[0].name.upper()'
... but, of course, that sort of stuff is impossible with str.Template. I created a bunch of code to populate a dictionary to pass to str.Template, but that was clunky, and defeated the purpose of having a template that clearly describes its own contents. In Cheetah, it's perfectly straightforward.

from Cheetah.Template import Template
tmplt = 'my list has $len($myList) objects; the first is named $myList[0].name.upper()'
print Template(tmplt, [locals(),globals()])

The second argument is the list of dictionaries Cheetah will search for matches to variables in the Template. Using [locals(), globals()] is my way to cheat and say, "Look wherever the interpreter would".

Oracle XE and Ubuntu

WOW. I just installed Oracle XE on my Ubuntu machine. I absolutely cannot believe how easy it was. This is - honest to goodness - all I did.

1. Download oracle-xe_10.2.0.1-1.0_i386.deb
2. su - root
3. dpkg -i oracle-xe_10.2.0.1-1.0_i386.deb
   It ran for maybe thirty seconds - so short, I was certain there had been an error!
4. /etc/init.d/oracle-xe configure (it told me to do that)
5. pointed Firefox at http://127.0.0.1:8080/apex (it requested that, too)
6. Started using the database (plus its included Application Express).

The entire installation took less than five minutes. Unbelievable! "This is Oracle?"

The only glitches I've gotten so far were when using Python's cx_Oracle against XE, and I've puzzled them out. (I don't know whether other people will get these glitches; they could have resulted from some residue of the full-fledged 10.2 Oracle that was on the machine before.)

• import cx_Oracle gave ImportError: libclntsh.so.10.1: cannot open shared object file: No such file or directory until I set LD_LIBRARY_PATH=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server/lib/
• conn = cx_Oracle.Connection('scott/tiger@xe') gave RuntimeError: Unable to acquire Oracle environment handle until I set ORACLE_HOME=/usr/lib/oracle/xe/app/oracle/product/10.2.0/server

[EDIT Oct. 9, 2007: Configuring cx_Oracle with Oracle XE has turned out to be harder than expected. See my new blog post.]

[EDIT Mar. 6, 2008: Great instructions for installing straight from Oracle's repository with apt-get here]

summary of Oracle/Python discussion

My OTN article on Oracle and Python was kept very brief, to be non-intimidating and to fit within OTN's preferred length. If you've come here, though, you're ready for the rest of the story! I'll use this post to summarize that discussion.

• Python+Oracle on other Linux distributions - see Andy Todd's blog entry
• alternatives to fetchone(): fetchmany(), fetchall(), and looping directly on the cursor - see my last entry and this comment
• Passing an argument to split(), to avoid errors on more complex init.ora parameters - see this comment

OTN article addendum

If you've read my new article at the Oracle Technology Network, Wrapping Your Brain Around Oracle + Python, thank you! I'd like to add a few more details about fetching rows with cx_Oracle that can make your code even cleaner.

Several times, I demonstrate getting rows from a cursor by means of the cursor's .fetchone() method. .fetchone() is used in loops like this:

curs.execute(<some query>, <bind variables>)
aRowOfData = curs.fetchone()
while aRowOfData:
    <commands>
    aRowOfData = curs.fetchone()

Another, more concise, alternative was not mentioned in the article. The cursor object itself can be iterated over, like this:

curs.execute(<some query>, <bind variables>)
for aRowOfData in curs:
    <commands>

The effect is the same, but it works with two fewer lines of code.

Finally, the .fetchall() method, bringing the entire result set into a list at once, was only briefly mentioned in the article, but it would probably be preferable to .fetchone() for the small result sets we'll find in places like v$parameter. Only when a result set is very large (or your computer is very memory-limited) do you need to worry about .fetchall()'s impact on your system's available memory.

Alice: corrupting the youth

I wish I could remember which PyCon delegate told me about Alice. It's a graphical environment for programming animations in a very kid-friendly fashion, yet full of solid, object-oriented programming goodness. This generation's LOGO, I suppose.

I was hoping it would fire our nine-year-old's interest in computers. I think it's working; Star Wars: Battlefront never produced such delighted shrieks and giggles. Maybe it works too well. He refused dessert to spend the extra couple minutes with Alice. Choosing code over food - I always thought of that as late-stage geekery.

Cheer: Alice works hard to be girl-friendly. Boo: Not available for Linux.

new and improved sqlWrap.py

After reading the Python Cookbook and attending PyCon, I have enormously improved sqlWrap.py, my Python module for handling DB-API 2.0 connections conveniently.

Also at PyCon, I learned that I could be accused of having duplicated projects like

• ll-orasql
• db_row
• dtuple

Like them, I allow tuple-like, dict-like, and object-like access to fields. I like mine better, though; it requires less preparation, and has really nice reporting methods. For example,

conn = sqlWrap.SqliteConnection('myDb.sqlite')
print conn.select('myTable').xml()

... is all you need to get an XML report on myTable. Similar reporting methods exist for

• tables in pp (prettyprint), xhtml, ReStructured Text
• transposed tables in pp, xhtml, ReStructuredText
• SQL INSERT statements

Hey, if everybody gets to write their own web app platform, why shouldn't I write my own DB-API wrapper?

PyCon2006

No, Guido doesn't know I have this... I snagged it from the badge reuse box. You keep your smelly old rock star T-shirts, I'm keeping this.

So... PyCon. Wow. It was wonderful, of course. What else could it be? Put 400 people with that much intelligence, creativity, and energy in one place, and it can hardly help but be wonderful.

What surprised me is that I found so many good ideas no matter what I was doing at PyCon. Whether I was in a talk that I expected to benefit hugely from, or a "Probably useless but I guess I'll go anyway" talk, or just chatting between or after sessions, I seemed to learn good stuff constantly just the same. The serendipity was worth more than the "planned" learning, and maybe that's what makes conventions so much more useful than formal books and classes and so forth.

Python Cookbook

This may seem silly, but I really have to sing the praises of the Python Cookbook, 2nd Edition. Everybody else has known about it forever, but I just got my copy a month ago.

I am blown away. I can't believe how good this book is, far beyond any other programming book I've ever known. I'm now completely embarrassed about the quality of the code I wrote without it, and tempted to stay up all night refactoring everything. In fact, I've already largely rewritten sqlWrap.py based on what I've learned.

I'm afraid I dawdled about buying my copy because I never found the recipes at ActiveState all that compelling - occasionally nice, but usually nothing to jump and scream about. I figured the Cookbook would just be a bunch of them, bound together. Not so - the careful selection and excellent discussion make it amazing. It's like having a master programmer at your elbow to guide you.

The Geek Event Aggregator is ready!

The Geek Event Aggregator is more-or-less ready for prime time! It now collects many more events. Better yet, it is very easy to feed more event sources, so it is set for even more growth. In other words,

PLEASE FEED THE AGGREGATOR NEW SITE SUGGESTIONS!

Some design notes:

After fooling around with complex regular expressions, Beautiful Soup, etc., I found a quick-and-dirty way that works better. The Aggregator downloads a page's HTML, replaces all tags with carriage returns, breaks the remaining text into lines, and checks those lines for ones that appear to contain recognizable future dates. The Aggregator assumes that is an upcoming event date. (There are many reasons dates get put on websites, but future dates almost always refer to meetings or events.)

Wow, human beings have many, many, many ways to write dates. Fortunately, python's dateutil module can recognize most of them - I mainly have to modify that to avoid false hits (like interpreting '.' as 'today', or '2006' alone as 'Jan 1, 2006').

Some sites already aggregate events from several groups and places together; for those, the Aggregator uses a slightly different algorithm. It finds the dates as above, then finds the location and event name by their line-number position relative to the date. (A human being (me) needs to provide relative line-number positions for those values in advance; for instance, one site may always list location on the line immediately after the event date, and that fact is recorded in advance.)

For the multi-event sites, the Aggregator has a decent but kludgey algorithm to parse city and region, despite the great variety of ways to write a location. Part of that relies on a list of recognized city names. It can be used for the single-event sites, too; if a recognizable city name is in the site's Title, or in the text in the form "Blah Blah City Blah Blahware Group", the Aggregator can find it. But if your event is in Athens, GA, the Aggregator thinks it's in Greece.

HTML DB makes it very convenient to build a web interface to the data. Oracle is also very gracious to host a free sandbox for HTML DB projects (which is where the Aggregator lives right now.) Unfortunately, as far as I can see, HTML DB doesn't support RESTful interfaces, or serving up pages as XML. That's a pity, because this cries out to be a REST web service. Maybe eventually I'll buy/find a place to host the web app in TurboGears or something.

For now, I've given up on feeding upcoming.org. Upcoming mandates an actual street address, which is just too hard to find automatically. I'd still like to pull from it, although I'll have to check their legal requirements, and - dare I say it? - I don't know if it will really have many relevant events I don't already have.

I'm sorry the events are so U.S./Canada-centric. It only has a handful of events from elsewhere, and it doesn't break down regions within other countries. (What's wrong with going from St. Petersburg to Novosibirsk for a meeting, anyway? Isn't that what the Trans-Siberian Railway is for?) You can help fix this by suggesting new sites to scan, and volunteering to introduce regional granularity for other countries.

Actually, because I've been the only one to feed the Aggregator so far, the events are Ohio-centric. You folks in benighted backwaters like California and New York are just going to have to feed it your own favorite sites if you want to fix that.

Some of the many things that produce misses and false hits:

• Dates without years. Somebody puts on the website, "Our next meeting is Nov. 19." The Aggregator can't tell that they haven't updated the site since 2004.
• Years must be on the same line as the rest of the date. If you say,
  2005 events:
  Feb. 14
  Aug. 12
  ... the Aggregator doesn't see the "2005", and believes there are events on Feb. 14 and Aug 12 of this year.
• Frames. Well, you can't blame it; frames mess up everybody. But if you can dig into the HTML source and puzzle out the URL of the frame with the data, then that can be read. That's what I did for the OKCOUG webpage, for example.

Geek Event Aggregator's future

The strangest thing happened to me this morning. The clouds split open, and a beam of light shone down onto me. (Since I was sitting in my cubicle, this in itself was odd enough.) A Voice spoke from Heaven, and said,

"Catherine, remember that somewhat cheesy Geek Event Aggregator you put on HTML DB a while ago?"

"Yes, Lord?" I said. (That seemed like the obvious response. That the Almighty would speak with hyperlinks didn't seem too surprising.)

"Well, I have heard the cries of my people. I want you to rewrite your aggregator to both consume and provide events in iCalendar format. Also, interface it with upcoming.org - both to download and to upload. Behold, Python libraries for upcoming.org have already been written for you. And I'm thinking that Beautiful Soup might help you pluck event descriptions from the God-awful HTML jungles you find them in."

"It sounds wonderful, Lord," I said. "But when am I supposed to do this? I mean, you did just drop custody of our Godson into our lap. Time is not exactly abundant right now."

There was a long pause, then the Voice said, "Let me get back to you on that."

cleanPath executable ready

An executable version of cleanPath is now available here!

You do need .NET Framework 2.0 to run this. It's pretty painless to download and install. You don't need IronPython.

Thanks to the newest IronPython release for making this possible! When I run

ironpythonconsole cleanPath.py

, the code runs, and it also generates cleanPath.exe without even being asked to. Until now, though, that .exe didn't actually work. In IronPython 1.0 Beta 2, however, released just Wednesday, it works great! I do need to put IronPython.dll in my directory before I compile... other than that, it's so simple, even a DBA can do it.

cleanPath.py: an IronPython utility

During my CodeCamp presentation, I introduced a very sketchy (and logically flawed) script to examine a PATH environment variable and clean out unwanted elements.

Since then, I created a much-improved version in IronPython which, I think, is actually worth using. Features:

• Conveniently view and delete path items
• Nonexistent directories and duplicate items are flagged, and can be purged with one keystroke
• works on PATH, CLASSPATH, PYTHONPATH, PERL5LIB, etc.

It's Windows-only, but only Windows makes editing your PATH such a pain, anyway.

The code is here.

Right now, you need IronPython, and use "ironpythonconsole cleanPath.py" to run it. I need to figure out how to generate an .exe, since getting set up with IronPython is too much to ask just to clean up your PATH.

[EDIT: An executable is ready now. See my next blog entry.]

Smurfette syndrome

One thing about the CodeCamp, though: severe Smurfette syndrome. Maybe 10% of the attendees were women, but probably closer to 5%.

I don't think it's the CodeCamp's fault; I'm afraid they may have gotten an accurate sample of the programmers in the area, and that's the scary thought. Possibly things would have been a little better on a weekday; men might feel more comfortable in taking a day away from home duties for their professional development. Still.

I know, it's been talked to death, and there are a million theories and stuff, but... yikes. It's just scary that one year goes by after another, and the situation persists.

On the other hand, the GCOUG meeting was about half women, maybe even a majority. At a small meeting, granted, but it's still a bright spot.

Dayton-Cincinnati Code Camp review

If you missed the CodeCamp, don't miss the next one. It was great. I'm not even into .NET, but even the minority of the conference that was non-.NET was well worth my time. Plus, sampling a bit of the .NET take on a few topics was good for my general education.

I almost wish I were involved in .NET, purely for the sake of the Dayton .NET group, which has a vitality that you just don't see that much of in the Dayton area IT community (at least, in my tracks thereof.)

Anyway, a few things I learned...

• There really are Python-experienced people in the area; I'd just never found them before.
• Ruby on Rails supports Oracle. Uh-oh. It's not that I'm unwilling to learn Ruby; it sounds great, and (of course) simple to learn. It's just that I really want to use TurboGears, but it uses SQLObject, and Oracle support in SQLObject is the sword in the stone. Many have tugged, but none have completed it, and maybe I should stop waiting and just use RoR for now.
• Speaking of Ruby, never miss seeing Jim Weirich speak. I already knew that, but maybe you didn't.
• GCOUG really needs to work on getting more people involved. There were 15-20 people at Jimmy Brock's talk on Oracle Developer Tools for .NET - none of whom had more than the haziest awareness of GCOUG's existence.
• Speaking of Jimmy's talk, Oracle Developer Tools has a great and handy way to bring .NET assemblies from Visual Studio all the way into Oracle, where they can be used like stored procedures.
  So, one glorious day, this year I hope, Visual Studio will support IronPython, and I'll be able to (essentially) write my Oracle stored procedures in Python instead of PL/SQL. And choirs of angels will sing.

I'm sure I'll have more to say about the Camp as I think about it.

Presenting (with) Bruce

When I present at a meeting, I like to use plenty of live demos. I can get away with this, because I type very, very well. (I did get into IT via the secretarial pool, after all.) Still, even I get a little nervous about whether I can keep up typing speed and accuracy with the pressure of a whole audience staring at me.

Fortunately, This blog post by Andy Todd (You do read his blog, don't you?) pointed me to Bruce, a wonderful presentation tool freshly created by Richard Jones.

Bruce lets you wrap up decent-looking slides, graphics, and Python interpreter sessions all into a single presentation that scrolls smoothly along at your command. (Simply being spared the fuss of flipping between Presentation slides and a command-prompt window is a blessing.) For demos, you can pre-enter the text you want to type, then Bruce sends it to the interpreter one character at a time. You type 'dfjkasfjdska;dfsafd', but 'def plusOne(x): return x+1' appears on the screen, and the audience thinks you're a keyboard goddess.

My one disappointment in Bruce - as I understand it (I just found it, you know) - is that the wonderful interactive session can only be used with a Python session. I'll need to dig into Bruce and PyGame to verify that, and to see if it can be extended elsewhere. I dearly want to use it when I'm demonstrating SQL.

Until Bruce, incidentally, my best bet was TPP, the Text Presentation Program. I've seen presentations done with it, and it looks OK, except it's written in Ruby and has dependencies (ncurses) that need C compiling - not very practical where I am, unfortunately.

Search engines are not Zen

I decided that I should make myself a list of questions I've never been able to resolve, or that were very difficult to research, because they don't "search" well. That is, in everything from Metalink's Search bar to Google, it's hard to come up with relevant results.

For instance,

- I'm trying to use RMAN DUPLICATE, and it does indeed produce the data files on the auxiliary database, but it doesn't create a controlfile. The documentation clearly says it should. Searching on terms like RMAN DUPLICATE controlfile not generated only brings up more pages saying that it should.

- All sorts of questions like such-and-such service DOESN'T start, program WON'T run, logfile ISN'T created. Negation - "not" - is clearly a failing of a typical search. Searching for green eggs no ham will find you 10,000 pages on green eggs AND ham, with the one page on "My green eggs came without ham!" buried anonymously as result #4,312.

I'm not sure what I'll ultimately want to do with this list. But I began to wonder, more broadly, if someone out there had already started assembling issues of unsearchable questions. Ironically, Can't Find On Google is very easy to find on Google.

Dayton - Cincinnati Code Camp

Hooray! - to the Dayton .NET Developers Group for putting together the Code Camp on Jan. 21, 2006 - a whole day of community-provided seminars on a variety of programming topics. For free! I'm looking forward to it; in fact, I'm planning to present there (on Python, naturally).

If you're anywhere near southwest Ohio, come and enjoy. Better yet, come and share! Whatever cool programming tricks you have up your sleeve - come and show the rest of us!

LibraryLookup published

My article on LibraryLookup has been published in the October 2005 issue of Technology First, a tech newsletter for the Dayton area. (I'm on p. 8-9). (The print article's content ties in with this blog post)

The sixty-second version: LibraryLookup lets you query your library's catalog directly from a page of a book vendor like Amazon, looking up that particular book for you automatically with just one click. You can get it installed in your browser in, oh, about five seconds. It's really cool, not just for helping you find books, but also as an example of clever and sneaky use of JavaScript.

Why do almost all contracts and job postings specify exactly the software that they want you to use (or to have used)?

When they want to incorporate you tightly into a large project that has already gotten underway with some specific software, OK. But I think such over-specifying is also the custom in cases where that is not at all true. It's very annoying to somebody like me, who bizzarely imagines she might be able to select the best tools for her own work.

When I take my car to the mechanic, I don't say, "You must do all the work using a Sears Craftsman #3 box wrench." But maybe, if I were a writer of contracts, I would.

Announcing Dayton-Oracle mailing list

Q. How do Dayton-area users of Oracle stay in touch with one another and find out what's going on in their community?
A. They don't.

Q. Well, that's lame. What can we do about that?
A. You can sign up for the Dayton-Oracle mailing list I just set up at FreeLists.

Please spread the word!

cx_Oracle and Ubuntu

Naturally, after getting Oracle working, my next move was to get it Python-powered by installing cx_Oracle. Installation seemed to go OK, but when I tried to use it...

>>> import cx_Oracle
Traceback (most recent call last):
  File "", line 1, in ?
ImportError: libclntsh.so.10.1: cannot open shared object file: No such file or directory

which seemed odd, because

catherine@ubuntu:~$ locate libclntsh.so.10.1
/app/oracle/oracle/product/10.2.0/db_1/lib/libclntsh.so.10.1

I extrapolated a solution from an old mailing-list post by Karl Putland: I appended /app/oracle/oracle/product/10.2.0/db_1/lib to /etc/ld.so.conf and ran ldconfig.

That was the only trick - it's working fine now!

Oracle on Ubuntu

I love my new Ubuntu Linux (version 5.10). I love how easy it was to install, how it picked right up on my wireless card.

Oracle doesn't support Ubuntu, but I installed it anyway and it's doing fine. The process was tricky, though. Here's what I learned.

1. RTFM. The Installation Guide is very important, more so than when installing Oracle on Windows. It has to be the primary document you work from, and follow step-by-step. Any other advice on Oracle/Ubuntu that you may Google up is secondary. Where Red Hat and SuSE directions differ, follow the Red Hat ones.
2. This document is useful primarily for reassuring you that the error messages you get are OK.
3. Make sure the Universe is in your Synaptic repository and install these packages: gcc, make, libxp, libaio, lesstif2, lesstif2-dev, rpm, and libdb1.
4. You can't find all the packages that the install documents call for in the Ubuntu Universe. That seems to be OK; just find the ones you can.
5. The magic word is runInstaller -ignoreSysPrereqs.
6. One little misstep and you may get something broken. It took me a couple attempts. Try, try again, following that Installation Guide even more carefully.

Oracle - Python wish list

For my upcoming Oracle - Python talks at OOUG and (maybe) COLLABORATE 06, I want to write a nice Python script in realtime, before the very eyes of the audience, like a cooking show hostess would. I'm having trouble deciding on an objective for the script, though. The ideal script would make Oracle people sit up and scream, "I want that! That power must be mine!" It should also

• Be very Oracle relevant
• Be something that can't be done with SQL or PL/SQL alone
• But still use some SQL (to demonstrate cx_Oracle)
• Not duplicate the function of any existing Oracle utility or Oracle Enterprise Manager (this one worries me since I don't even know everything OEM can do)
• Be writeable within an hour (shouldn't be a problem)

I'm brainstorming for ideas, but I don't know if any of them are really "it".

• Compare parameters in INIT.ORA to live database (this is what I used for my paper)
• Condense or groom a TKPROF output file
• Populate a database table from a website (but what website? What's cool enough?)
• Find and compare TNSNAMES.ORA files scattered across filesystem. Find TNSNAMES entries that are "dead" (unpingable).
• Format alert.ora for better readability; color-code messages?
• Measure log switch frequency from alert.ora
• Find obsolete (unused) configuration files like init.ora, ORACLE_HOMEs, etc.
• Clean up PATH (mine gets cluttered with droppings from various installs/uninstalls)

Any comments on these? Anything you'd like to put on the wish list? Let your imagination run wild - what wishes would you make to a magic Oracle genie? That's the kind of "gotta have it" feeling I'd like to produce.

Ohio LinuxFest

I had a great time at Ohio LinuxFest. Highlights, in my book, were

• Jorge Castro's Ubuntu talk. He pointed us to a lot of great stuff to try out, and his enthusiasm was infectuous.
• Rich Bowen's mod_rewrite talk. I've never encountered Apache documentation that I was truly happy with, but Rich was wonderfully clear. I want his book.
• Ram Rao's virtualization talk. Xen sounds like it has jumped straight to #1 in my List of Reasons My Management Would Let Me Use Linux For The Database Server If They Had A Clue. Moving entire virtual machines easily between production and development servers, with almost no performance impact... wow, that would be incredible.
• The reception. Hey, the only thing better than dancing is dancing with geeks. Too bad I had to leave early, but somebody foolishly located Dayton too far from Columbus.

I heard that the attendance had more than doubled from the last year, and indeed, many of the people I talked to were quite new to Linux. That's the one improvement I'd suggest for the event. There were good talks for newbies, but not enough of them, nor did the schedule provide clear enough guidance to help newbies steer themselves that way.

COLLABORATE 06, featuring me, maybe

I just submitted a proposal for a technical session ("Python and Oracle: Coding that Cooks") at COLLABORATE 06, the IOUG's conference next April. Wish me luck! I'm not sure how hard it is to get a slot; I do know that the speaker quality there has been excellent for the past two years.

introducing sqlWrap.py

A Python script, for your use and suggestions.
Sorry, I know this is awfully long for a blog post - I have to figure out a better place to put it. I wonder if it would be appropriate for the Cheese Shop?

"""
sqlWrap
Adds convenience methods to DB-API2 connection objects.

Sep. 22, 2005 by Catherine Devlin (catherine.devlin@gmail.com, http://catherinedevlin.blogspot.com/)

This script is NOT intended as an object/relational mapper.
Rather, it helps experienced SQL users form their SQL more quickly.

Methods: insert, update, delete, select, genericSelect
These methods accept arguments for the WHERE, SET, etc. clauses
that should generally be provided as dictionaries; i.e.
whereClause = {'col1':'val1','col2':'val2%'} implies
  "WHERE col1 = 'val1' AND col2 LIKE 'val2%'",
setClause = {'col1':'val1'} implies 
  "SET col1 = 'val1'"
You may also pass object instances for whereClause and setClause,
with instance attributes corresponding to column names... but this 
has barely been tested!
They also automatically make use of bind variables, which have
performance and security benefits over hard-coding values in SQL.
The different ways of handling bind variables in various DB-API 
adapters are masked from the user.

Currently supports: Oracle (cx_Oracle), sqlite (pysqlite)

Sample usage:
# setup - unchanged from cx_Oracle
conn = OraConnection('scott/tiger@orcl') 
conn.cursor().execute('CREATE TABLE myTable (column1 varchar2(10), column2 varchar2(10), column3 varchar2(10))')
# now try out the sqlWrap convenience methods
conn.insert('myTable', setClause={'column1':'value1','column2':'value2'})
conn.insert('myTable', setClause={'column1':'value1a','column2':'value2a','column3':'value3a'})  
for row in conn.select(source='myTable'): 
    print row 
for row in conn.select(source='myTable', whereClause={'column1':'value1'}, resultProcessor=conn.dictionaryize):
    print row
conn.update('myTable', setClause={'column1':'value1','column2':'value2'}, whereClause={'column3':'value3'})
# as always, must explicitly commit  
conn.commit()
"""      

Get sqlWrap.py here

If you're in Ohio...

notes to self

Do not smash your head against a problem for consecutive hours. Instead:

• Ask for help. It doesn't matter whether you ever send the request; in the process of putting the problem into language, you're more than likely to solve it. (Yes, I know: "Pair programming!", you say. But I'm all alone here.)
• Stand up and walk away. Do not touch anything resembling a computer for at least ten minutes.

Will someone please remind me of these next time I get stuck?

Python for Oracle Geeks: unplugged!

I've been invited to present at the Oct. 27 Ohio Oracle User Group meeting in Columbus. Hooray! After I've practiced at a local group, I'll feel ready to start trying to get to wider areas, maybe even the national IOUG conference. I will bring tanker-trucks full of Python Kool-Aid!

We have a lot of catching up to do - Oracle's OTN has been publishing a LOT on PHP, and Oracle and Zend are providing a handy-looking integrated package. I salute the PHP folks, but I don't want that to be the only dynamic language active in Oracle-land.

sqlite: a flyswatter to kill flies

I'm now using sqlite to support an Oracle production database. I love it!

When I first went to download sqlite, I went away frustrated. I found an executable described as "A command-line program for accessing and modifying SQLite databases", and thought, "OK, so that's my SQL*Plus equivalent, but where's the actual server? The part that keeps the database process running?"

Because I have been an Oracle-only person so long, I didn't understand that sqlite doesn't need anything like that. It's simply this:

1. a single executable program that creates and modifies a database file
2. A database file

And that's it. Really! You can't imagine how dizzying and refreshing that is to someone who's spent hours before a certification exam memorizing Oracle architecture diagrams.

Anyway, there are other simple database engines, of course, but sqlite has gotten a lot of attention recently (like an Open Source Award) for its efficiency and its support across many languages. There's an excellent and honest rundown of its powers and limitations here.

So, anyway - why sqlite to support Oracle? Well, one of my Oracle instances has a bunch of logic applied to it by a nightly batch job. For every data record, a series of decisions are made, and our users ask questions like, "For record #12945, why did it decide X instead of Y last Tuesday?" And I have to be able to answer, "Well, the seventh of the nine tests conducted on that record determined that, since column 'product' was 'lutefisk' and 'quantity_kg' was 22, blah blah blah..." So all those decisions need to be logged every night.

That generates a quantity of data far outweighing the application data itself. It can be discarded after a week or two, but all that inserting and deleting was causing out-of-control generation of archive log files and making a mess of my disk space allocation. By moving that data out into a sqlite database, it becomes a single simple file that can be moved or deleted as easily as any other file.

So, thanks to sqlite, I'm living happily ever after. Hooray!

more on XML

The problem with my XML-generating views is that they break down for rows whose XML is longer than 4000 characters.

Meanwhile, the troubles I cited with XMLELEMENT really are limited to SQL*Plus. I was deterred at first, because I want to experiment with unfamiliar features through SQL*Plus first, but when I swallowed that reluctance and remembered that there are other ways to experiment with ad-hoc SQL - TOAD's SQL window, for instance - I was OK.

But then, I wanted to create views that would store the particular combinations of XMLELEMENTs I wanted for various circumstances. Unfortunately, I found that a SELECT query that runs fine can't be used to generate a working view when the result is longer than - you guessed it - 4000 characters.

I'm having a little trouble figuring out where exactly the 4000 character problem kicks in - at first I thought CLOBS were simply converted back to VARCHAR2's whenever I used the concatenation operator |, but that doesn't explain why views fail when the equivalent bare SELECT statements don't.

Anyway, the end of my adventure was that I simply wrote Python scripts to get what I want. I'm beginning to get some nice conveniences built into my personal wrapper for cx_Oracle, and maybe I'll eventually float it around for others' use. There are plenty of tools like SQLObject out there, of course, but as far as I know, they're all focused on OO programmers who hate to handle SQL directly or to think of their data as "rows in a table" rather than "instances of an object". I, on the other hand, think very naturally in SQL statements and relational concepts, and just use some functions to streamline my use of SQL.

Finally, there's one cute little possibility for producing XML that I haven't explored. SQL*Plus can generate an HTML table as output really easily. That could be coupled with a XSLT to produce XML.

querying XML without fighting with XDB

My coworker and I want to query some nice, simple XML from my Oracle table. For example, from the table
CREATE TABLE pet (name VARCHAR2(22), species VARCHAR2(22), weight_kg NUMBER);
I want to query up results like

  <pet>
    <name>Martin Luther</name>
    <species>cat</species>
    <weight_kg>6.2</weight_kg>
  </pet>
  <pet>
    <name>Jordache</name>
    <species>horse</species>
    <weight_kg>450</weight_kg>
  </pet>

My first thought was to use an XDB's XMLType View of my table. Okay... that looks... sort of intimidating. But I want to be able to query from it with the same old SQL I'm used to; I want to grab the XML for all rows
WHERE species = 'cat' OR (weight_kg > 100 AND name LIKE 'J%')
Now it looks really intimidating. I don't want to go back to school for a Master's of Science in XPath right now, and neither does my coworker.

Then I thought, "I know! I'll just use XMLForest, like this!"
SELECT XMLForest(name, species, weight_kg) FROM pet

On my platform (Windows 2003 / SQL*Plus 10.1.0.2.0 / Oracle 10.1.0.4.0 ), this produces
"Oracle SQL*Plus has encountered a problem and needs to close."
Attempted workarounds - like
EXEC SELECT XMLForest(name, species, weight_kg) INTO :petxml FROM pet
give me results like
"PLS-00801: Internal error [*** ASSERT at file pdw4.c, line 782; Cannot coerce between type 43 and type 30; _anon__2C9F5D70__AB[1, 7]]"
Okay, I give up - this XDB stuff is not mature enough for me today.

My handmade solution is fairly simple to use.
SELECT xml FROM pet_xmlvw WHERE species = 'cat' OR (weight_kg > 100 AND name LIKE 'J%')

Setting up views like pet_xmlvw is a headache and a half, but my ugly code will do it for you. (Can you hear PL/SQL crying? Can you hear it crying, "Somebody write Cheetah for me!"?)

CREATE OR REPLACE FUNCTION sf_columns_xml(table_name IN VARCHAR2)
RETURN CLOB
IS
  result CLOB := '';
  column_name VARCHAR2(30);
BEGIN
  FOR c IN ( SELECT utc.column_name
             FROM   user_tab_columns utc
             WHERE  utc.table_name = UPPER(sf_columns_xml.table_name) )
  LOOP
    column_name := lower(c.column_name);
    if length(result) > 0 
    then
      result := result || chr(10);
    end if;
    result := result || 
              '    <' || 
              column_name || '> '' || ' || column_name || ' || ''</' || 
              column_name || '>';
  END LOOP;
  RETURN result;
END;
/

CREATE OR REPLACE FUNCTION sf_table_xml(table_name IN VARCHAR2)
RETURN CLOB
IS
BEGIN
  RETURN 
    'CREATE OR REPLACE VIEW ' || lower(substr(table_name,1,24)) || '_xmlvw AS
SELECT ''
  <' || lower(table_name) || '>
' || to_char(sf_columns_xml(table_name)) ||
'
  </' || lower(table_name) || '>'' xml,
  ' || table_name || '.*
FROM   '  || table_name;
END;
/

Now running EXEC execute immediate TO_CHAR(sf_table_xml('PET')) will generate the view pet_xmlvw, whose column xml contains what I want.

To generate such views for all the tables in my schema, I run

spool buildXMLViews.sql
 select 'exec execute immediate to_char(sf_table_xml(''' || table_name || '''));'
 from user_tables
 where dropped = 'NO';
 select 'exec execute immediate to_char(sf_table_xml(''' || view_name || '''));'
 from user_views;
 spool off
 @buildXMLViews 

(Hey, I didn't use q'| |')! Well, you may not all be on Oracle 10g.)

Sometimes, it's a sausage factory in here. Don't look at how it gets done, just pass the ketchup.

Geek event aggregator

Click me!

I've written up an aggregator script (Python, of course) that browses an assortment of event announcement webpages and parses out the name/city/state/date information. Then I made an HTML DB application to serve it up according to your state or province (sorry, non-North-Americans).

No, I haven't written a brilliant AI to figure this out. It's just a bunch of regular expressions.

To do:

1. Mine from more event sources
2. Refactor the source code so it doesn't embarrass me and I can post it
3. Debug multiple hits for some events (InOUG especially)
4. Add non-North-American region support
5. Automate the mining (currently I kick it off and upload to HTML DB by hand)
6. get somebody to host the app in some more prominent site
7. Provide access to the pure XML as generated by the aggregator, so others don't have to go through the heck of parsing I do

Python for Oracle Geeks

IOUG posted my article, Python for Oracle Geeks, and referenced it in their 7/20/2005 "5 MINUTE BRIEFING: Oracle" e-mail. Ah, the fame, the adoring crowds, the paparazzi...

Tom Kyte

Tom Kyte was at OOUG in Columbus. He's even better in person than the is at AskTom.

Here's one little gem he gave that will change your life, if you're an oracle person. Or at least hurry you to upgrade to 10g, where it can be used.

The magic is q'| |'.

INSERT INTO my_tbl(str) VALUES 
  (q'|I don't have to worry when I write a string like 
    'INSERT 'r2d2' INTO droids'|');
INSERT INTO my_tbl(str) VALUES 
  (q'|It's almost as good as Perl!|');

SELECT * FROM my_tbl;

STR
-----------------------------------------------------
I don't have to worry when I write a string like
    'INSERT 'r2d2' INTO droids'

It's almost as good as Perl!

All those single-quote single-quote, single-quote single-quote single-quote monstrosites we used to make to get quote marks into SQL strings are history. Let the people rejoice!

LibraryLookup

For Mozilla, Firefox, or Netscape, drag-and-drop any of these links to your toolbar to create a LibraryLookup bookmarklet. For Internet Explorer, use Add to Favorites... and create it in Links.

Public libraries

• Dayton Library
• Greene Co. Library
• Clark Co. Library
• Troy Library
• Middletown Library

College and university libraries

• SCC Library (Sinclair Community College)
• UD Library (University of Dayton)
• WSU Library (Wright State)
• CSCC Library (Clark State Community College)
• Wittenburg Library
• Wilberforce Library

Now, when you're browsing any site that describes a book by its ISBN (amazon.com, for example), clicking your new toolbar button or link will automagically look that book up in your library's catalog. Yay!

Hooray for Jon Udell, creator of LibraryLookup! (And a little bit of hooray for me, who slaved over a hot keyboard to make these public library bookmarklets.)

TODO: These bookmarks are written to extract the ISBNs from the URL, not the webpage text. Some sites, like O'Reilly, don't use ISBNs in their books' URLs - but still list them in the webpage text. A more sophisticated bookmarklet might be able to find the ISBN there, too. I'd like to write bookmarklets that could find and use those ISBNs, too.

the easy tools

• If only I had known about tooltips months ago!
  A tooltip is what you get when you hover over a word like these ones. I went off looking for how to generate them, expecting to get myself hip-deep in JavaScript, and found that this is all you have to do:
  <span title="I love tooltips.">these ones<span>
  
• If I had known about CherryPy months ago!
  With all due respect to other Python-based web application platforms like Zope, CherryPy is so much easier to get into, and soooooo satisfying. And it really does run much faster than Zope, too. Yum!

Flaming about Curl and open source

Once upon a time, I read some interesting buzz about the Curl programming language. It sounded different, and it came from MIT (yeah, I know, alum chauvenism), so I had an inclination to check it out.

I'm a hopeless addict of the MicroCenter bargain rack, so when I spotted the Curl Programming Bible there for $5, I couldn't resist.

Wow, am I disappointed. It's no wonder Curl is apparently dying; it was doomed from the start.

One problem is the syntax. I didn't get very deep into it - just far enough to read

Notice that the entire herald is enclosed in curly braces ({ and }). All programming expressions in the Curl language are enclosed in these braces. They are used so often that the language itself was named after them!

Well, tee-hee-hee! Isn't that just precious? Maybe they should have named the language "Repetitive Motion Injury"! Okay, so I'm a Python geek; requiring gratuitous punctuation is an excellent way to send me reeling away in disgust..

But the real problem is the business model. The Curl plug-in is built so that, every time a user hits a Curl site, it notifies the Curl corporation to charge that site's owner. I don't care how small the charge per hit is, that's crazy. I'm going to get approval, in advance, to pay an unknown, usage-based fee, just to put up a site? For a tool that is (within our organization) totally unproven?

I think that open source really is the way to introduce a language these days. Very few languages are selected in advance by people with money-spending power. (Unless, of course, they are pushed by a megacorporation with the advertising budget to send flocks of salespeople to razzle managers and take them to lunch. But that's another discussion.) Rather, a developer or sysadmin or whoever finds a language she likes, starts using it informally for her own this and that, eventually builds something that her manager sees and likes, THEN gets permission to start working it into formal projects - the kind that have budgets. Then we'll start paying money for an IDE and training and maybe consulting and so on. At least, that's the way I've experienced it.

The sad thing is that the fate of Curl reminds me very much of the Water language - another MIT baby that interested me enough to sell me a book, but that is also more-or-less stalled. Again, I think the choice not to go open-source is a big part of that failure. (It probably doesn't help that Water is built on top of, and demands a little familiarity with, Java - so the ideal target audience is one that already has a preference for open-source.) Geeks just don't seize something and energetically play with it when they're told (1) This is ours, not yours and (2) Pay at the door.

I wonder if some foolish person in MIT's Office of Making Money Off Our Babies simply doesn't realize this, and is twisting arms to impose doomed business practices on MIT's babies. Ironic, for the institution that spawned the Free Software Foundation to go that way.

If anybody wants a barely-used Curl book, just ask.

HTML DB

A couple months ago, I built a D&D-suitable "random goods" generating website with HTML DB as an experiment. Parts of it are very frustrating, but overall it's an awfully powerful product. My example non-masterpiece is here.

making default column values work in PL/SQL's %ROWTYPE inserts

I'm going to look/post around the web for the answer to this puzzle...

I like PL/SQL's capability to insert a row all at once, using %ROWTYPE.

Example:

CREATE TABLE TEST
 (a NUMBER,
  b NUMBER DEFAULT 0 NOT NULL);

 declare
   t test%ROWTYPE;
 begin
   t.a := 22;
   t.b := 0;
   insert into test
   values t;
 end;
 /
 
PL/SQL procedure successfully completed.

However, I'm trying to use it to do inserts on a table with many NOT NULL, DEFAULT 'N' columns, and I get ORA-01400 CANNOT INSERT NULL errors.

declare
  t test%ROWTYPE;
begin
  t.a := 22;
  insert into test
  values t;
end;
/

ERROR at line 1:
ORA-01400: cannot insert NULL into ("EQDBW"."TEST"."B")
ORA-06512: at line 5

I don't want to clutter my PL/SQL by manually setting all these columns to their default values; after all, reducing clutter was the whole idea behind using %ROWTYPE in the first place. Besides, if I hard-code the default assignment into my PL/SQL, then it will become out-of-date if I change the column defaults in the table specification later.

If life were perfect, the automatic behavior would be to use the default values for these columns when nothing is specified in the %ROWTYPE variable. I'd settle for having some manual way to specify that as the behavior I want, but I can't find it.

Ironically, there is a way to do this for a record type explicitly defined in PL/SQL - "To set all the fields in a record to default values, assign to it an uninitialized record of the same type". But that doesn't work for %ROWTYPE records (I tried).

reference:
PL/SQL User's Guide and Reference
5 Using PL/SQL Collections and Records
http://download-west.oracle.com/docs/cd/B14117_01/appdev.101/b10807/05_colls.htm#sthref655

Win32::GuiTest

I have given laborious and painful birth to an automated GUI tester. I'm feeling a bit of postpartem depression, because it's nowhere near as clean as I'd like. I chose some complex ways of coding simply because the obvious ways produced errors or strange results that I didn't understand. It was also hard to get consistent results... things would stop and start working without apparent cause.

Bleh. I don't like programming voodoo.

This article says, "the first rule of GUI testing is: Don’t!", and it's really true. It would have been far better to get my developer to design his GUI app for testability in the first place, or at least get him to embed handy hooks in the code itself. Working totally from the outside, trying to write code that looks at and uses a GUI on the screen exactly as you do, is

But still, there are times when you do have to work as a complete outsider to the code. It is nice that there's a way, however painful. And here are some things I learned. Who knows if they will have any applicability to anybody else's Win32::GuiTest project? I'm sure it depends on a million quirks of the system you're testing.

1. Most things I saw on my screen could not be probed by Win32::GuiTest, as far as I could tell. About all I could see were the titles of windows and window elements, and not even all of those.
2. If you SendKeys, don't forget to bring your window to the foreground with SetForegroundWindow first. For me, the same GUI automatically seized the foreground on one machine, but on another machine, it stayed back, and the wrong window intercepted my keystrokes.
3. I wanted to know how long an hourglass wait lasted while a new window came up. I found no good solution. FindWindowLike succeeded too soon - it found the window immediately, long before the user could see it. What I settled for was to count the number of dependent items that GetChildWindows would find for the new window - and keep counting until the number was up to its maximum value. Then I knew the window was ready for business. Kludgey? Oh, yes!
4. Insert lots of sleep()s. They help reduce errors enormously.
5. I had trouble stringing several tests together into a single session. The first test in a single session would work fine, but inconsistent problems cropped up with subsequent tests. Eventually, I remembered that it's a machine and it doesn't get bored, so I made it close the application, get all the way out, and go all the way back in for each test.

timeout with threading

I'd gotten the impression that python's threading module was a very frightening thing, and had put off getting to know it. That's too bad, because when I finally looked at it, it provided a very simple way to add timeouts to some of my code. And - unlike os.fork(), etc. - it works on Windows!

    t = threading.Thread(target=self.code)    
    t.start()
    t.join(self.timeout)
    if t.isAlive():
        completed = False
        self.msg = 'Timed out after %s seconds' % (self.timeout)
        if self.killOnTimeout:
            t._Thread__stop()

interactive perl

I'm writing a GUI tester, which involves me deeply in Win32::GuiTest. It's been a mix of joys and frustrations. Two things have saved my mind.

1. I'm hooked on Python's interactivity, especially when exploring a poorly-documented module. I was missing that badly in Perl; Komodo's interactive mode is only a partial substitute. This tip saved me: http://www.devx.com/tips/Tip/17304?trk=DXRSS_WEBDEV
   
2. Getting a module to export its functions was giving me fits. Just adding the function names to EXPORT_OK in the .pm file only gave me "Can't locate
   auto/... (function).al" errors. This is black magic to me, but it fixed it: http://groups.yahoo.com/group/libwww-perl/message/1470
   

IOUG Live! sent me into rapturous joy. More on that later. I can't believe I was seriously considering not going.

Here's one of the problems that the conference set my mind to.

Some of my homemade procedures fill up some tables with huge amounts of somewhat ephemeral logging data. It's only needed for a couple weeks, after which I need to delete the rows. Doing it the conventional way generates cruel amounts of redo. I've wanted a good script to duplicate the effects of the imaginary command
DELETE FROM logtable WHERE blah blah blah NOLOGGING

The basic algorithm I've seen is
CREATE TABLE newlogtable AS
SELECT * FROM logtable
WHERE (criteria for keeper rows)
UNRECOVERABLE;
DROP TABLE logtable;
RENAME newlogtable TO logtable;

The trouble is that it's hard to get all the dependent DDL to move along. Especially since I don't want any names to change - I name all my constraints, down to the NOT NULLS. The script gets more complicated then.

I found out about DBMS_METADATA.GET_DDL and DBMS_METADATA.GET_DEPENDENT_DDL at Joe Trezzo's "New 10g & 9i features", and it looks like that might be a good basis. It's still tricky, though, because I can't create new dependent DDL until the originals have been dropped. It feels like juggling: generate the new DDL, drop the original dependent objects but not the original table, create the new table with its data based on the stored DDL, then finally drop the original table... hmm.

I'm thinking of relying on the recycle bin (more new IOUG knowledge) for some help here. Deleting objects in 10g really only renames them and designates them as being in a "recycle bin"; you can still access them, you just need to consult the right views to learn their new obscure system-assigned names. (They make an awful clutter in your schema viewed through TOAD, btw.)

Could I drop the table (with its objects) first, then build a new table based directly on the one in the recycle bin? That means all the old dependent object names would already be out of the way, and I can do it all in fewer steps.

Something seems scary about relying on the recycle bin that way, though. Like mailing checks against the deposit you expect to make while your envelopes are still in the mail. I guess Oracle makes no guarantees about the continued availability of objects in the recycle bin; they stay until they're manually purged or the tablespace fills up. But, since it is a very bulky table I'm working with, it's not too farfetched that the recycle-bin version could become unavailable while I'm halfway through this process. Hmm. Darn.

O-verkill-racle

Oracle's had a philosophy for a while now that, "We're the database company; so anytime our software has to interact with any data, for any reason, we'll have it create an Oracle database for the purpose."

The trouble is, an Oracle database represents a LOT of overhead. For instance, an empty Oracle database takes ten times the disk space of an empty postgresql database. Worse, every new database is a new administration burden.

We wanted to do a little bit - a very little bit - with web-based Oracle Discoverer. That meant installing Oracle Application Server, and OAS created its very own infrastructure database. Now I've got this "infrastructure" database sitting around, doing next to nothing, soaking up almost as much disk space and memory as a small production database would. It's 9.0.1 - way behind my own databases - and I have to attend to its obsolete 9.0.1 security needs, or the network security scan gripes at me.

And the shame of it is - a use like this doesn't need the things an RDBMS provides. A store of configuration data for an application server doesn't need multi-user access, transactional consistency, etc. etc. - totally unnecessary. A few flat files would do fine.

It's like a trucking company that won't use a hand dolly because, well, they're a trucking company. So, of course, they need diesel pumps and regular oil changes and overhauls for the big rigs they use to get the copy paper down to the copy room, and maneuvering the 18-wheelers between the cubicles may be a little troublesome sometimes.

passwords in LSNRCTL

Oracle's listener control (LSNRCTL) can take a password. The way you actually supply the password is very confusing, though.

c: > lsnrctl
LSNRCTL> stop
TNS-01169: The listener has not recognized the password

What do you mean, "Did not recognize the password?" You never asked for a password!

LSNRCTL> set password badguess
LSNRCTL > stop
TNS-01169: The listener has not recognized the password
LSNRCTL> set password goodguess
LSNRCTL > stop
The command completed successfully

In other words... 'set password' doesn't mean 'set password' the way normal human beings mean it - 'establish this as the password'. It means, 'this is what I think the password is, so transmit this whenever I do something that demands a password'.

Humph. There should be a category for "not exactly a bug, but nomenclature confusing enough that it may as well be".

For the first time in my life, I find myself worried about mutability.

I want to take advantage of Python's new sets (who wouldn't?). It turns out that sets can only be made of immutable, hashable objects, and a user-created object type is (by definition) mutable.

I can see where it makes sense. If I say
box1 = Box(contents='eggs')
box2 = Box(contents='eggs')
basket = set([box1, box2])
should basket contain one or two items?
Now suppose I
box2.contents = 'yogurt'
Now does my basket have a box of eggs and a box of yogurt, or just one box of yogurt? It's not clear.

The problem is, there are objects I will create that truly never will change, and it truly would be useful to use this wonderful set functionality with them. So now I'd like to be able to create immutable objects, but Python has no particular way to create them.

The only way I've found so far feels a little bit dangerous - to define __hash__. As far as I can tell, it means "You can know me by this". If I do forget and later change data that is the basis of my hash, my object no longer hashes to what it once did - it has moved and left no forwarding address. There are some clumsy ways to try to block later changes to my object's values, but they are... clumsy. (Redefining the object's __setattr__ and raising exceptions therein. Ick.)

Thus, it feels like I'm delving into eldritch secrets, like my soul is toast if I scuff the pentagram. __str__ and __cmp__ are the only double-underscore methods I'd defined previously. They feel downright warm and fuzzy compared to this.