Querying Oracle databases from cx_Oracle under El Capitan — January 31, 2016

Querying Oracle databases from cx_Oracle under El Capitan

The following is my attempt to document a remarkably painful process that got even more remarkably painful under El Capitan than it was under earlier versions of Mac OS. It concerns querying an Oracle database from within a Python script via the cx_Oracle library. If you don’t query Oracle databases from Python scripts via the cx_Oracle library, you are hereby advised to do literally anything else.

The general process of the steps below is:

  1. Get Oracle Instant Client (aka sqlplus) running. This does a couple things. First, it lets you confirm that you can run Oracle queries at all, outside of Python. Second, it installs some libraries and executables that cx_Oracle uses.
  2. Muck around in the libraries that sqlplus installed, to change internal references from /ade/... to @loader_path/....
  3. Install cx_Oracle.
  4. Muck around in the cx_Oracle shared-object (.so) files to change their internal references from relative paths to absolute ones.

Here goes, start to finish:

  1. Download Oracle Instant Client. You want to download “Instant Client Package – Basic”, “Instant Client Package – JDBC Supplement”, “Instant Client Package – SQL*Plus”, “Instant Client Package – SDK”, and “Instant Client Package – WRC: Workload Replay Client”, because why download one file when you can download five? Additional notes about downloading these files:
    1. You need to accept the license-agreement radio button at the top of the page first.
    2. You likely want the 64-bit files, unless your machine is ancient.
    3. You need to log in to download these files, which means you need to create an Oracle login first. Because why make downloading easy when you can make it annoying?
  2. Unzip all the files you just downloaded. For the purposes of this exercise, I assume you downloaded these files to ${HOME}/Downloads.
    mkdir oracle
    cp *.zip !$
    for i in *.zip; do unzip $i; done
  3. Make a system-level directory that will house sqlplus. Since El Cap, you can’t touch anything under /usr, so you’ll want to put it in /usr/local or /opt. For reasons that now elude me, I put it in /opt/oracle.
  4. Change the ownership on /opt/oracle so that you, rather than root, own all the files therein.
    cp -r ~/Downloads/oracle/instantclient_11_2 /opt/oracle
    sudo chown -R $(whoami): !$

  5. In pre-El Cap versions of OS X, you’d set the ${DYLD_LIBRARY_PATH} environment variable to point to where your libraries lived. In the sqlplus case, you’d add /directory/where/sqplus/lives to ${DYLD_LIBRARY_PATH} by adding a line like

    export DYLD_LIBRARY_PATH=${DYLD_LIBRARY_PATH}:/directory/where/sqplus/lives

    to your ~/.bashrc file.

    El Cap seems to have changed the treatment of DYLD_LIBRARY_PATH as part of System Integrity Protection. Maybe DYLD_LIBRARY_PATH stops working? That’s a problem, because all the pre-El Cap steps to configure cx_Oracle that you’ll find on the Internet involve setting that DYLD_LIBRARY_PATH to override the library paths inside the sqlplus executable. The library paths that sqlplus comes with are all absolute paths that seem to refer to Oracle’s own internal build system — like so:

    (16:40 -0500) slaniel@work_laptop:/opt/oracle/instantclient_11_2$ otool -L sqlplus
    sqlplus:
    /ade/dosulliv_sqlplus_mac/oracle/sqlplus/lib/libsqlplus.dylib (compatibility version 0.0.0, current version 0.0.0)
    /ade/b/2475221476/oracle/rdbms/lib/libclntsh.dylib.11.1 (compatibility version 0.0.0, current version 0.0.0)
    /ade/b/2475221476/oracle/ldap/lib/libnnz11.dylib (compatibility version 0.0.0, current version 0.0.0)
    /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 159.1.0)

    All those need to change to use the path to your sqlplus install.

  6. So if the $DYLD_LIBRARY_PATH option is out, one option is to muck around in the binary and change the library paths from /ade/... to some sort of variable that means “whatever path the executable is in”. Various places on the web suggested using @executable_path as that variable, but — again — that seems to work only under pre-El Cap versions of OS X. Now you need to use @loader_path instead (for reasons that I’ve not bothered to investigate). The method is like so:

    (16:43 -0500) slaniel@work_laptop:/opt/oracle/instantclient_11_2$ sudo install_name_tool -change /ade/dosulliv_sqlplus_mac/oracle/sqlplus/lib/libsqlplus.dylib @loader_path/libsqlplus.dylib sqlplus
    (16:43 -0500) slaniel@work_laptop:/opt/oracle/instantclient_11_2$ otool -L !$
    otool -L sqlplus
    sqlplus:
    @loader_path/libsqlplus.dylib (compatibility version 0.0.0, current version 0.0.0)
    /ade/b/2475221476/oracle/rdbms/lib/libclntsh.dylib.11.1 (compatibility version 0.0.0, current version 0.0.0)
    /ade/b/2475221476/oracle/ldap/lib/libnnz11.dylib (compatibility version 0.0.0, current version 0.0.0)
    /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 159.1.0)

    Note that the linked library is now @loader_path/.... Repeat this for all of the Oracle Instant Client files, and for all of the libraries linked within them. There’s a tool that does this. We end up, e.g., with

(16:45 -0500) slaniel@work_laptop:/opt/oracle/instantclient_11_2$ otool -L sqlplus
sqlplus:
    @loader_path/libsqlplus.dylib (compatibility version 0.0.0, current version 0.0.0)
    @loader_path/libclntsh.dylib.11.1 (compatibility version 0.0.0, current version 0.0.0)
    @loader_path/libnnz11.dylib (compatibility version 0.0.0, current version 0.0.0)
    /usr/lib/libSystem.B.dylib (compatibility version 1.0.0, current version 159.1.0)

Okay, so we fix up all the libraries. On my end, this allowed me to get a sqlplus console and connect to our Oracle server. Nice.

Next up: make the Python cx_Oracle library work with it. First, you need to create a couple of symlinks:

(16:49 -0500) slaniel@work_laptop:/opt/oracle/instantclient_11_2$ sudo ln -s libclntsh.dylib{.11.1,}
(16:50 -0500) slaniel@work_laptop:/opt/oracle/instantclient_11_2$ sudo ln -s libocci.dylib{.11.1,}

The reason here is apparently that cx_Oracle is trying to link in those libraries without referencing their version numbers; if you try to install cx_Oracle without those symlinks, you’ll get make(1) puke that contains this telling line:

    ld: library not found for -lclntsh

So you create the symlinks, then you do your classic pip install cx_oracle. Various places on the web suggested, more fully,

pip install --no-cache-dir --allow-external --allow-unverified cx_oracle

, and by this point I was past the point of arguing. That command worked, though it did warn me that --allow-external was deprecated and would eventually stop working.

Sure it’s installed, but that doesn’t mean it’ll actually work. The cx_Oracle.so file now links to libclntsh.dylib.11.1 — a relative path with no /path/to/libraries/... prefix. You need to find the .egg file that Python actually loads when you do import cx_Oracle; in my case, that path is /Library/Python/2.7/site-packages/cx_Oracle-5.2.1-py2.7-macosx-10.11-intel.egg . That’s just a zip file, so

  1. copy it to some new empty directory under ${HOME}
  2. unzip it
  3. change the .so path from relative to absolute, via
    install_name_tool -change {,/opt/oracle/instantclient_11_2/}libclntsh.dylib.11.1 ./cx_Oracle.so
  4. zip the directory back up into the .egg file and copy it from your ${HOME} subdirectory back into the global library path:
sudo mv cx_Oracle-5.2.1-py2.7-macosx-10.11-intel.egg /Library/Python/2.7/site-packages/cx_Oracle-5.2.1-py2.7-macosx-10.11-intel.egg
  1. Does it work?
(17:19 -0500) slaniel@work_laptop:~$ python -c 'import cx_Oracle' && echo 'worked'
worked

Yes! It worked! (Contrast with what happens if you try ‘import cx_Oracl’ with no trailing ‘e’.)

And finally — finally — I can run queries against

Tiny Mac suggestion of the evening — November 30, 2014
svn commit “unable to lock” error under Mac OS X — May 3, 2010

svn commit “unable to lock” error under Mac OS X

This will be interesting to approximately none of you, but I feel I need to spread the knowledge of hell to a wider world.

I will perhaps go into greater detail at a future date about all the *other* problems preceding this one while I used svn (and Maven, and Eclipse) under OS X. They all essentially stem from the fact that OS X’s filesystem is case-insensitive. … Or rather, not case-insensitive: you can’t do

(16:41) slaniel@Steve-Laniels-MacBook-Pro:~$ mkdir test_dir
(16:42) slaniel@Steve-Laniels-MacBook-Pro:~$ mkdir test_Dir
mkdir: test_Dir: File exists

But you can do

(16:42) slaniel@Steve-Laniels-MacBook-Pro:~$ mv test_dir test_Dir
(16:42) slaniel@Steve-Laniels-MacBook-Pro:~$ rm -rf test_dir

Since it’s not entirely case-insensitive, it is *sometimes* — but not always — a problem to have both ‘foo’ and ‘Foo’ in the same svn checkout. More to the point here: what if you have ‘Foo’ and want to rename it to ‘foo’? You’d want to do `svn mv Foo foo`. There will be bequeathed unto you a sadness:

(16:45) slaniel@Steve-Laniels-MacBook-Pro:~/svn/sandbox/slaniel$ svn mkdir foo
A foo
(16:45) slaniel@Steve-Laniels-MacBook-Pro:~/svn/sandbox/slaniel$ svn mv foo Foo
svn: Unable to lock ‘Foo’

The reason it can’t lock ‘Foo’ is that it already has ‘foo’ locked, and it thinks that ‘foo’ is the same as ‘Foo’. So it can’t move, in other words, because the filesystem is case-insensitive.

To redress this just now, I had to do something like

svn mv foo bar &&
svn ci -m “Temporarily moving foo to bar” &&
svn mv bar Foo &&
svn ci -m “Moving bar to Foo”

You need to do the commit after each move; you can’t just do

svn mv foo bar && svn mv bar Foo

Maybe all of this was obvious to all OS X svn users other than me. I assure you that it was *not* obvious to me.

Mac filesystem case-insensitivity just wasted an inordinate quantity of time and money from some of my company’s smartest engineers. I am displeased. Perhaps this post will save someone else some time in the future.

At a later date, after I’ve actually completed some work, perhaps I will explain all the *other* badness that resulted from this case-insensitivity.

The iPhone is a gateway Apple product — April 3, 2010

The iPhone is a gateway Apple product

I bought an iPhone a year and a half ago. This made me really want to develop an iPhone app — something I’ve not yet done, but which I intend to start very soon. [1].

Now, the thing about the iPhone’s UI, which I don’t think you can fully grasp until you’ve actually used one, is that nearly everything — at every scale — works as it should. There is not a single sharp corner left in the product; everything has been rounded for your pleasure. The first place you see this is when you scroll to the end of a long list. When you hit the end, the iPhone doesn’t jarringly stop there. Instead, it bounces you, as though you just ran into a rubber wall on the way to catch a fly ball in the outfield.

It turns out that there is *nothing* jarring in the iPhone. If you’re rocking out to some tunes and a call comes in, the iPhone doesn’t just turn off your music and start ringing. Instead it gradually fades out the music, then starts ringing. When you’re done with the call, it gradually fades the music back in. Nothing about the product will ever put you ill at ease. That’s why I say you have to use it to understand this: in principle, written out like that, it seems like most products should and do behave like that, right? But they don’t. Getting all the details right — every detail, at every scale — is apparently so difficult that virtually no one does it. You really don’t notice how rare it is until you find yourself absolutely pleased with the iPhone, in a way that you’ve never been pleased with a piece of consumer technology before.

Having decided to do iPhone development, my terrific employer very graciously offered to buy me a MacBook Pro. The first couple days were a little difficult for me: I do a lot of command-line stuff, so I needed to get MacPorts or Fink going. And I had to get used to all the Mac OS X keyboard shortcuts.

With that out of the way, I fell into the same feeling of comfort with OS X that I have with the iPhone. The first step was realizing that every piece of the Mac UI is exactly as it should be. The second step, having gained such confidence in Apple’s UI design, was to ponder how I would do something in OS X, then ask, “If everything worked as it should, how would I perform this task?” It turns out, uniformly, that OS X’s UI always behaves the way it should. This gives you the confidence, as a friend pointed out to me last night, to go forth and try new things, and to really engage with the product in a way that you wouldn’t with some (forgive me) Microsoft piece of shit.

The first time this really took hold for me was when I asked whether I could plug my iPhone headset into my MacBook Pro and use it there the way I use it on my iPhone. For those who don’t know, the iPhone headset has a little clicky piece that performs two functions: it’s the microphone through which you carry out phone conversations, and it’s a control device for iTunes on the phone. Click the microphone once to pause the music you’re listening to or hang up the phone call you’re on; click it twice to advance to the next track. (It probably does other things as well.) If Apple designed its products the way you should expect (but which you’ve come *not* to expect from any consumer-electronics company), you should be able to pause iTunes on the laptop, advance to the next track and so forth using the iPhone headset. It turns out that you can do exactly that. And I’m not even using an Apple-manufactured headset: I’m using an incredible pair of Sennheiser MM 50 earbuds. It must just be that Apple requires single clicks to issue a certain signal and double-clicks to issue another, which both the iPhone and the MacBook Pro are programmed to respond to in the same way (namely firing off an event in iTunes). I don’t trust any other company to manage this amount of integration.

Stephanie and I discovered yesterday that there’s an app called Keynote Remote that lets you control Apple’s presentation software from your iPhone via WiFi. This is integration that everyone can use, and of course it helps Apple: the more Apple products you buy, the more value you get out of any one of them.

I’m probably going to buy a Time Capsule, which (so I gather) is so thoroughly integrated with OS X that you never even have to think about backing up; it just does it automatically. I gather that you could use other remote-backup devices in place of a Time Capsule (I believe the Time Machine software works with any number of devices), but — again — experience shows that Apple has integrated its devices spectacularly well; why would I want to use anyone else’s? Yes, I know that this is Kool-Aid drinking, but it’s Kool-Aid drinking based on a lot of positive experiences.

The final step in Apple fanboydom is to proselytize, which I unashamedly do now. But it’s proselytizing to those who could actually get a lot out of the product. Take my girlfriend, for instance (not literally; I enjoy dating her). She needed to make a movie on her ThinkPad, running Vista, so she used the built-in Microsoft Movie Maker. She spent a large fraction of a day trying to convert from the QuickTime-formatted movie that nearly every point-and-shoot camera generates to something that Movie Maker could process. Having never used iMovie, I nonetheless knew its reputation as the product you use when you want to make movies. So I brought the MacBook Pro up to New Hampshire one night, we plugged her camera into the USB port, and within a minute she was editing video. 24 hours later, she had bought a MacBook Pro of her own. People want to get shit done; they don’t care that Microsoft lacks QuickTime support because it wants to screw one of its competitors.

As a friend pointed out: Apple knows that you want to look cool. Even if Microsoft had made it easy to import .MOV files into Movie Maker, you know that it would have botched the execution after that; you would not look cool when it was done. It would offer you “wizards,” which would lead to very boring videos resembling animated PowerPoint. And those wizards would somehow, miraculously, not make your life any easier. They’d be a needless abstraction piled on top of a crummy user experience. Apple would fix the user experience so you wouldn’t *need* the wizard.

A coworker was giving me some good-natured ribbing the other day about using a Mac. He, like me, grew up during the Mac-versus-Windows wars of the early 90s. News flash: those wars are over, and the Mac has unquestionably won. I would be shocked if anyone who’s considered the matter actually believed that Windows was more usable, or more technically well-assembled, than OS X. (Though I’m fairly certain that Windows is easier to manage for enterprise installations than either OS X or Linux. But that’s not the realm that my coworker was arguing in.)

If there is still an OS battle going on, it is Linux-versus-Mac. But that battle has nothing to do with UI; again, no one could seriously assert that Linux’s UI is better than Apple’s. If there’s a Linux-Mac battle, it’s a battle over the open Linux model versus the closed OS X model. Windows is not seriously in competition with OS X for its end-user experience; it succeeds because it has succeeded. Windows is the Martha Coakley of operating systems: you hold your nose and use it because you have to, not because you want to.

[1] — One thing I’ve realized about my work style, and maybe about work styles more generally, is that I need to get something utterly trivial but functional done ASAP, and can move from there to getting something real working. As of now, I know nothing about iPhone development, so the field seems vast and intimidating. The point is to kill that feeling of intimidation as fast as I can. The way to kill it is to just get something, anything, done in the platform so that it no longer seems beyond my grasp. Had I used this technique in college, I think I would be a far better mathematician than I am.

An update on Diamond DVI-to-USB adapters and Belkin USB hubs — March 12, 2010

An update on Diamond DVI-to-USB adapters and Belkin USB hubs

All is not rosy in the land of multi-monitor MacBook Pros. As I mentioned there, I’m driving two large external monitors through USB, using Diamond adapters to connect USB to DVI; the USB plugs run into a Belkin USB hub, which runs into a single USB port on the side of the MacBook Pro with which my employer generously supplied me. (I will give you guys an iPhone app very soon; promise.) The dream is then that I can then run a bunch of other USB devices off the hub as well: iPhone, mouse, camera, etc.

It sadly hasn’t worked out that well, for reasons that illustrious Stevereads commentator mrz explained in comments to that post:

1. There’s just not enough bandwidth in USB — much less in a USB hub, which has to split one USB port’s worth of bandwidth across seven devices — to power a high-resolution monitor (much less two high-resolution monitors). My monitors would periodically slow to a crawl, and would slowly repaint the screen from top to bottom. I had to unplug the USB hub at this point, so that OS X could shift everything onto the built-in monitor; once it did that, the speed returned to where it should have been.

2. The USB hub — possibly because of item 1 — has died. None of the components plugged into it work, individually or together. When I unplug any of them — say, the mouse or a monitor — and plug them directly into the MacBook Pro, they return to life.

I can live with item 1: sure, I move windows around, and the rendering doesn’t really keep up with the movement, but it’s better by far to have two slow monitors than only a built-in MacBook Pro screen. Obviously I can’t live with item 2: I can’t stand to have a hub die after only a few days of use.

I tried to call Belkin support, but it’s another Indian call center. I find few things more disheartening than finding Indian tech support on the other end of the call; it speaks of a tech company that wants to save money (hmm: flimsy, cheap product?) and doesn’t care at all about helping its customers.

I may try to find another, better, more reliable USB hub, but the Belkin one gets fine reviews on Amazon. I’ll have to look around more closely.

Driving two external monitors off a MacBook Pro — March 8, 2010

Driving two external monitors off a MacBook Pro

Thanks to my employer for hooking me up with a beautiful MacBook Pro and two huge external monitors.

If you’re trying to do the thing mentioned in the title of this post, you’ve probably already found the perfectly comprehensive post I’m going to link to. If not, it’s this guy right here. The Cliffs Notes version is as follows:

* Your MacBook Pro has one Mini-DVI port. You want to drive two external monitors. *Problem*.
* So buy two Diamond BVU195 USB display adapters. These allow you to connect DVI cables to USB cables, of which your MacBook Pro has a few.
* “But wait!” you might say here, “I only have two or so USB ports, and I want to drive two external monitors. How will I plug in an external mouse *and* an iPod/iPhone, *and* those two monitors?” Fear not: here’s where you buy a USB hub. I got a 7-port Belkin external USB hub for $28. I run a cable from there to a USB port on the MacBook Pro, and I’m done.

To review: up to here, you’re running one DVI cable from each of your monitors into a DVI-to-USB adapter from Diamond. Then you run the resulting USB cables into a USB hub. Then you run one cable from the hub into your MacBook Pro. Now both your monitors, in summary, are being run off a single USB port on your MacBook Pro. *Sexy*.

The final step, again as detailed in that article, is

* Download and install the DisplayLink OS X drivers. Now you can use System Preferences to arrange your three monitors — two external, one built-in — in any configuration you like.

[foreign: FIN].

I would include pictures of how these things all work on my end, but the fellow who wrote that piece included everything I would have.

My only question now is how to get control of the ridiculous quantities of cabling I have laying on my desk at work as a result of these contortions:

Messy desk, lots of cables