Launching SQL*Plus from SQL Developer on Mac OS X

I recently upgraded my Macbook Pro to Leopard, and along with all of the other goodies that come with that upgrade, I've been able to install and use the long-awaited (and Intel Leopard-only) Oracle Instant Client for OS X. I've been using SQL Developer on my Mac for a long time, but I've missed the ability to launch SQL*Plus from the command line. Once I got the Instant Client installed (which was exactly as easy as "Instant" implies), I thought it would be useful to be able to launch SQL*Plus from within SQL Developer. The script runner in SQL Developer offers limited SQL*Plus emulation, and you never know when you might want to use one of your favorite SQL scripts from days gone by...

The setup

As it turns out, while the release notes provide an example for configuring the desired behavior, getting SQL Developer to effectively launch SQL*Plus is non-trivial on a system where Instant Client is the only other client software available. I'll discuss the reasons in the next section, but for the benefit of any impatient googlers, here's what I ended up doing.

First, I created a small shell script, called, as shown below. The values for the three environment variables (ORACLE_HOME, PATH, DYLD_LIBRARY_PATH) on your system will likely be different from mine, but you get the idea.

export ORACLE_HOME=/u01/app/oracle/product/10gClient
dbconn=`echo $2 | awk -F \@ '{print $2}'`
sqlplus $dbuser@\'$dbconn\' @$sqlfile

This shell script does the following:

  1. Adds the location of sqlplus on my system to the PATH. I've deviated slightly from the Instant Client install docs here, and created a separate bin directory for sqlplus.
  2. Adds the location of the Instant Client shared libraries to DYLD_LIBRARY_PATH.
  3. Assigns more human-readable names to the input parameters for the script, and parses the database connect string generated by SQL Developer to something usable by the Instant Client (more on that later).
  4. Launches SQL*Plus with the arguments passed from SQL Developer

After saving the script in an appropriate place, and setting it executable (chmod u+x, I moved on to setting up SQL Developer. The following screenshots demonstrate the steps:

Quick notes on the contents of the fields, especially since the 'Arguments' field would require a ridiculously wide (and large) screenshot:

Program executable
I'm launching SQL*Plus in an xterm. If you have a favorite terminal window that takes arguments in a way that's similar to xterm's -e option, you could probably substitute the path to that window here. I briefly played around with trying to launch SQL*Plus with, but decided I just didn't care enough and stuck with xterm instead. :-)
Pasted here in full since they don't show in the screenshot:
-e /Users/jpiwowar/bin/ ${sqldev.dbuser} ${sqldev.conn} ${file.path}
Note the full path to the shell script, and the use of some of the macros discussed in the SQL Developer release notes.
Run Directory
I don't have anything defined here, but if your SQL script dumps out log information without an absolute path for the logfile, you might want to set up a default directory here.
Command sample
Not user-editable, but a thoughtful feature nonetheless for debugging purposes. Here's how it looks for my demo:
/usr/X11/bin/xterm -e /Users/jpiwowar/bin/ system jdbc:oracle:thin:@vir:1563/JPTEST.dbdomain /Applications/

Not too much to say here. Specify whatever you like for the menu item label...

Choose what you like for Step 3 as well. I leave the "Save All before tool starts" option unchecked, because I'd rather forget to save before running the script than accidentally wreck a file by running SQL*Plus. But maybe that's just me. :-)

...and we're done!

The payoff

After putting it all together, here's the outcome:

Some things to note:

  • I ran the script in SQL Developer with the "Run script" option before invoking SQL*Plus. As far as I know, this is necessary to associate the script with a database connection and populate the sqldev.user and sqldev.conn macros used in the External Tools definition. As you can see from the screenshot, SQL Developer strips out the 'set linesize' output format command.
  • The password prompt for the SQL*Plus connection appears in the xterm. It may be possible to make SQL Developer prompt for the password and pass it along to the shell script, probably via the "Prompt" or "Prompt with Label" macro. I haven't tinkered with that option yet.
  • The log window at the bottom of the SQL Developer UI can be a useful place to start investigation if SQL*Plus doesn't launch as you'd expect.

The annoying bits

Why go through all the work of setting up a shell script wrapper for launching SQL*Plus? Well, for a handful of reasons. First, SQL Developer's database connection string generated by the sqldev.conn macro doesn't work as-is with the Instant Client. The JDBC Thin connect string provided needs to be parsed a little to strip out the jdbc:oracle:thin:@, which is the purpose of the awk command in the shell script.

There are a lot of ways to work around the above issue without resorting to a shell script. The tougher problem is that SQL Developer doesn't seem to care about your environment. Well, you know, it cares, but it's just been really busy... :-) I not only had to call executables by absolute path, but also had to find a way to set DYLD_LIBRARY_PATH in order for SQL*Plus to run. In theory, it's possible to use environment.plist at the user level to set these environment variables, or to modify the Info.plist file for SQL Developer, or even insert the environment variable definitions in the script that launches the application. In practice, however, the ability to set DYLD_LIBRARY_PATH via environment.plist was apparently broken a while back, perhaps intentionally as part of a security update, and I'm reluctant to modify files shipped with the application that are likely to be overwritten during an update.

I'll admit that I haven't researched all of these issues to the point where I am 100% certain that they are unresolvable. After spending a little time playing around with different options (and engaging in some decidedly unprofessional muttering), I just decided to slap together a short shell script to do what I wanted. Inelegant, but predictable. I'm sure a cleaner solution exists, and would love a demonstration that I'm making this way more complicated than it needs to be.

Post a Comment

Your email is never published nor shared. Required fields are marked *