After reading “The Pragmatic Programmer”:http://www.amazon.com/exec/obidos/ASIN/020161622X/hvirringdk-20 this summer, I’ve been trying to follow some of their tips, one of them being
bq. *Tip 22: Use a Single Editor Well*
Choose an editor, know it thoroughly, and use it for all editing tasks.
My editor of choice is “TextMate”:http://macromates.com, one of the very first applications I bought for my Mac. I’ve been using it more and more lately, but unfortunately there’s no support for working with Oracle databases. The supplied SQL bundle works only with MySQL and Postgres, and Google wasn’t any help in finding a bundle specifically for Oracle.
After spending a few hours looking into what these “TextMate bundles”:http://macromates.com/wiki/Main/Bundles are, it turns out they’re very, very nifty and it’s not all that difficult to create your own. So, without tiring you with more chatter, I present my first TextMate bundle, the “Oracle Bundle”:http://productive.dk/pub/Oracle.tmbundle.zip.
I could possibly have added Oracle functionality to the existing SQL bundle, but there didn’t seem to be a lot of overlap between MySQL/Postgres and Oracle in terms of the code implementing the bundles, so I decided to create a standalone bundle. So far, it’s only got a few basic features, but I’m hoping to add more as time allows for it. The syntax just piggybacks on the SQL syntax from the SQL bundle, and I haven’t added any support for PL/SQL.
This “preview” has the following commands:
!http://productive.dk/images/oracle-bundle-preview.png(Oracle Bundle Preview)!
Go ahead and “grab your copy here”:http://productive.dk/pub/Oracle.tmbundle.zip.
I have been using TextMate for most of my work lately. Most of the work I am
doing is a mix of XML and SQL (both MySQL and Oracle). I used to spend a
quite a bit of time switching between TM and Aqua Data Studio (which is what
I used to test stuff in oracle) till I found your TM Oracle bundle last
night. I have been playing with it and liked it so far.
Before I forget, *Thank you* for creating it!.
I had to make the following changes to get it to work on my Macbook Pro,
especially since I don’t have a full Oracle install on my Macbook and only
have instant client setup.
Here are the few changes I made for your reference:
For search I used this url:
I had to also change the following
Since I am a programming noob, just wanted to see if the following would be
possible in the oracle bundle.
Since I regularly have to connect to multiple servers it would be nice if I
could override the default connection parameters $ORA_USER, $ORA_PWD,
$ORA_CONNECT (set in env) in each file/script in lets say the first line. (
or even better enter it interactively)
I know I can easily hack this in a very simple/basic/dumb way
Create a new command in the bundle as :
html_header “Run Selection or Line”
sqlplus -S -L /NOLOG <<EOF
set define off
set pagesize 0
whenever sqlerror exit failure rollback
set markup html on
set markup html off
echo “SQL*Plus exited.”
But this requires that I expose the username and password.
Do you know if its possible to either obfuscate at least the password or
enter it on demand ?
Thanks for your feedback and I’m happy to hear that you like the bundle.
To answer your questions:
The URL you use for search is great! I will definitely change the URL in the bundle to use this.
I don’t know why you need to remove the quotes from the connect string, it’s working fine for me with Instant Client on a MBP. I will look into it when I have more time.
I usually set my username/password/connectstring in the project and not in the general TextMate preferences. See chapter 9.4 (Project Dependent Variables) in the TextMate Help. Of course you could also use a combination of this, e.g. set the connectstring in TextMate preferences and username/password on a per project basis.
You could also modify the “Open in SQL*Plus” command to fit your needs. Remove the password from the commandline and add the script you want to run. This way SQL*Plus should launch in Terminal and prompt you for a password.
I have created a more complete Oracle PL/SQL bundle for TextMate initially based on yours at:
I have kept the commands, but I have incorporated a syntax (rather than use the builtin TextMate “SQL” bundle which is not really suited to PL/SQL), better folding and indenting support and a number of code snippets which people should find useful.
If you wish to include any (or all!) of my changes into your copy, then please, feel free. 🙂
I will try to keep my version up-to-date with features I feel are useful.
Mark, that is great! I haven’t had much time to work on the bundle, but proper syntax was definitely one of the features I had initially planned for. Using the SQL-bundle syntax was just a lazy hack to have something to work with.
I think it would be best to have just one version of the bundle, and since you already have everything setup at Google Code, I suggest that’s the current version going forward.
I have made a few minor changes to the bundle since posting the preview on my blog. I’ll shoot you an email about how we get the changes merged — that is, if you think it’s a good idea making your version the current from now on.
I just download the bundle from Google Code, looks really useful. I have read the Pragmatic Programmer too, so see where you are coming from. Do you have any plans to extend it? Do you think it would be possible/feasible to add SQL formating to it? I may try and have a look in the future some time.
Jesper & Mark, Thank you for this great bundle, I’m trying to focus on TM for my everyday work and you guys are helping me tons!!!
Great help for all Oracle boys. Looked for hours on the web for something like that.
Great work Mark and Jesper.
I can submit queries but when I try to create a procedure it does not work.
create or replace procedure test1
for rec in (select table_name, tablespace_name from user_tables)
v_table := rec.table_name;
v_space_name := rec.tablespace_name;
dbms_output.put_line(‘Table ‘||v_table||’ is in ‘||v_space_name||’ tablespace.’);
I select the whole command and run it using shift-command-R.I get connected as … and SQL*Plus existed.
I check and the procedure is not created. I copy and paste it in sqlplus in terminal and it compiles fine.
Anyone else with this issue? Any way to get the error message back in Textmate?
I don’t have an Oracle database to test on right now, but as I remember it, you just need to add a “/” after the last line, as you would in a script: