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]