Oracle Bundle for TextMate, Preview

Posted by Jesper on September 25, 2006

After reading The Pragmatic Programmer this summer, I’ve been trying to follow some of their tips, one of them being

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, 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 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.

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:

Oracle Bundle Preview

Go ahead and grab your copy here.

Trackbacks

Trackbacks are closed.

Comments

Comments are closed.

  1. Adi Alurkar Thu, 05 Oct 2006 21:52:16 UTC

    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:
    http://www.oracle.com/pls/db102/search?remark=quick_search&word=$query

    I had to also change the following

    ”$ORA_USER”/”$ORA_PWD”@”$ORA_CONNECT”
    TO
    $ORA_USER/$ORA_PWD@$ORA_CONNECT

    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
    as follows

    Create a new command in the bundle as :

    ----------cut----------
    query=${TM_SELECTED_TEXT:-$TM_CURRENT_LINE}

    . "$TM_SUPPORT_PATH/lib/webpreview.sh"
    html_header "Run Selection or Line"

    NLS_LANG=AMERICAN_AMERICA.UTF8
    export NLS_LANG

    sqlplus -S -L /NOLOG <<EOF
    set define off
    set pagesize 0
    whenever sqlerror exit failure rollback
    prompt </pre>
    set markup html on
    $query
    set markup html off
    prompt <pre>
    show errors
    prompt </pre>
    EOF

    echo "SQL*Plus exited."
    html_footer
    ----------cut----------

    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 ?

  2. Jesper Fri, 06 Oct 2006 07:29:36 UTC

    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.

  3. Mark Bush Wed, 12 Sep 2007 00:16:15 UTC

    I have created a more complete Oracle PL/SQL bundle for TextMate initially based on yours at:

    http://code.google.com/p/oracle-textmate-bundle/

    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.

  4. Jesper Thu, 13 Sep 2007 10:00:39 UTC

    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.

  5. jon Mead Wed, 21 May 2008 17:18:51 UTC

    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.

    Jon

  6. Billy Wed, 11 Jun 2008 16:26:57 UTC

    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!!!

  7. Xbdequet Sat, 13 Dec 2008 18:20:19 UTC

    Thanks!,

  8. Oracle dinosaur Mon, 16 Feb 2009 01:18:15 UTC

    Great help for all Oracle boys. Looked for hours on the web for something like that.
    Great work Mark and Jesper.
    Cheers!

  9. Glenn Wed, 18 Feb 2009 20:18:39 UTC

    Thanks.

    I can submit queries but when I try to create a procedure it does not work.

    create or replace procedure test1
    as

    v_table varchar2(100);
    v_space_name varchar2(100);
    begin
    for rec in (select table_name, tablespace_name from user_tables)
    loop
    v_table := rec.table_name;
    v_space_name := rec.tablespace_name;
    dbms_output.put_line(‘Table ’||v_table||’ is in ’||v_space_name||’ tablespace.’);
    end loop;
    end;

    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?

  10. Jesper Thu, 19 Feb 2009 23:01:26 UTC

    Glenn,

    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:

    ...
    end loop;
    end;
    /