Using SQLPlus with rlwrap on MS Windows

At this year’s HOTSOS Symposium Tanel Poder presented a training day that really highlighted some very practical ways to tailor your environment so that common tasks will be easier. One tip that I have really appreciated is how to use the Unix/Linux package rlwrap (readline wrapper). Using this with a few parameters and in conjunction with SQL*Plus makes a huge difference. It is now possible to have a command history and tab-enabled auto-completion of keywords available in SQL*Plus.

To simplify things even more aliases can be created to shorten a lengthy command. These aliases can be set to connect to specific databases so that Oracle’s Easy Connect strings or TNS aliases don’t have to be entered every time SQL*Plus is started. Here’s an example of what you would store in your ~/.bash_aliases file:

alias sqltestdb=’rlwrap -D2 -irc -b’\”@(){}[],+=&^%#;|\’\” -f ~/sql/wordfile_11gR2.txt $ORACLE_HOME/bin/sqlplus system/PASSWORD@test.mydomain.com/testdb.mydomian.com’

Now all your would have to enter is sqltesetdb to start the session. I won’t break down all of the parameters but the wordfile_11gR2.txt is important because that’s where the keywords are stored that the auto-complete capability uses. This can be handmade or (even better) you can get a copy of it along with Tanel’s very useful SQL library at http://files.e2sn.com/scripts/tpt_public.zip.

This works beautifully in any Unix/Linux environment where the rlwrap utility is installed. But, what if you carry around a Windows laptop and want to keep all of your aliases with you? The easiest option is to set things up on a central Unix/Linux server that you can log in to and use as your alias’ home. However, there is another option.

Windows has a "shell" environment called PowerShell that is flexible enough to allow similar functionality. This can be downloaded and installed in your current Windows environment.

PowerShell – http://www.microsoft.com/downloads/details.aspx?displaylang=en&FamilyID=6ccb7e0d-8f1d-4b97-a397-47bcc8ba3806

First though, you need to install Cygwin and make sure that you specify rlwrap as one of the packages to be included. Then make sure that you add the c:\cygwin\bin directory to your Windows PATH.

Cygwin – cygwin.com

Now it’s simply a matter of creating a few aliases and a function that is similar to the Unix/Linux version. PowerShell aliases are managed using these commands:

set-alias rw "c:\cygwin\bin\rlwrap.exe"

set-alias sqlplus "c:\oracle\11.2.0\bin\sqlplus.exe"

The function can be created using a DOS path to the wordfile_11gR2.txt file(c:\sql). But, PowerShell prefers a POSIX equivalent (/cygdrive/c/sql) for functions. Once this is complete simply execute "sqltestdb" to start SQL*Plus using the rlwrap capabilities. Using this modular approach allows you to have a separate function for each database.

function sqltestdb {rw -irc -f /cygdrive/c/sql/wordfile_11gR2.txt sqlplus mpaddock/PASSWORD@testsrv.mydomain.com/testdb.mydomain.com}
set-alias sqltestdb

If you want to get rid of it:
remove-item function:sqltestdb:

To list the function definitions use this command:
get-content function:\FUNCTION NAME (You can replace FUNCTION NAME with * to list all functions).

It would be a good idea to list these and save the results to a text file as a backup.

The functions are persistent between functions so they won’t need to be recreated. To make sure that the aliases for rw and sqlplus remain include the definitions in your $profile. You can find this file by typing $profile at the command prompt.

$profile
C:\Documents and Settings\mpaddock\My Documents\WindowsPowerShell\Microsoft.PowerShell_profile.ps1

This a simple text file that can be edited be typing "notepad $profile". You can add the set-alias commands here and they will be available every time you start PowerShell.

PowerShell has a lot of interesting capabilities you can dig in to and explore. For me, I’m just looking to simplify things. The easier the command, the better off I am.

 

About Michael Paddock

Comments

10 Responses to “Using SQLPlus with rlwrap on MS Windows”
  1. Jimmy says:

    Well, I almost got it working. It seems PowerShell cannot access the word file location – even though I gave Everybody read, write and execute permissions on the SQL directory.

    rlwrap: error: Could not open /cygdrive/c/sql/wordfile_10gR2.txt: No such file or directory

    I copied Tanel’s word file and renamed it. In PowerShell I set the scripts permission to Unrestricted, is there another PowerShell setting so it can write history and read the word file?

    • Michael Paddock says:

      Are you able to change directories to the word file location and then view the file?

      Also, try to execute just “rlwrap sqlplus user/password@db”. I just want to make sure that the basics are working.

  2. Jimmy says:

    Well, changing directories worked, Thanks! I moved it up one level to, /cygdrive/c/word_file_10gR2.txt

    I don’t know why,but it didn’t like the SQL sub-directory.

    I do get a warning about the sqlplus_history file that is creates.

    MS-DOS style path detected: C:\cygwin\cygdrive\c\sql/sqlplus_history
    Preferred POSIX equivalent is: /cygdrive/c/sql/sqlplus_history

    Is there a parameter setting for this?

    • Michael Paddock says:

      Sorry for the slow reply. I don’t know of a parameter setting. But, the warning shouldn’t keep the function from working. You can always try the suggested path and see what it does now.

  3. Younes El karama says:

    @Jimmy: Add this env variable to your Windows

  4. Great Post! Thanks for giving detailed description about blog.

  5. jl says:

    Sorry to reply to this ancient post, Following your instructions I can get powershell to run rlwrap with a completion file, however I have found that I cannot get to the editor anymore. I have notepad++ set up as the default editor, but afiedit.buf pops up with an old query and regardless of what I change it to, I cannot change what I’ve entered into sqlplus. I’ve looked at afiedit.buf in sys32, syswow32, home, etc., and I’m yet to find which one powershell ‘defaults’ to. I’ve tried setting notepad in ps with no luck. Notepad++ shows the right sql in cygwin.

    Any idea on what to set in powershell?

    thanks

Trackbacks

Check out what others are saying about this post...
  1. [...] enjoy hearing Tanel – in fact just today I was telling someone on IRC about Tanel’s rlwrap bonus tips (remembering screen text and wordfile autocomplete).  After everyone finished their presentations, [...]

  2. [...] (although you can use rlwrap to accomplish this – see this post for more details on that: Using rlwrap on Windows) And regex also provides a very powerful pattern matching capability although it’s still a [...]



Speak Your Mind

Tell us what you're thinking...
and oh, if you want a pic to show with your comment, go get a gravatar!