SQL*Plus Tricks

I work with SQL*Plus on a daily basis and I thought I’d pass on a few tricks to make it easier to use. These tricks are for SQL*Plus on Windows, started from a dos prompt, not the gui SQL*Plus. I’ve never been a fan of the gui version, and I believe it’s gone in 11g anyway.

99% of the time when start SQL*Plus, here’s the routine I go through:

1. Open a dos prompt
2. Change to a directory that contains the sql scripts I’m working on
3. Login to SQL*Plus with the username and password of one of my databases

I wanted to automate this process as it’s a bit tedious when you repeat it every day.

For the first two steps, I recommend using a tool that allows you to store commands and execute them using keyboard shortcuts. About a year ago, a friend got me hooked on a great tool called SlickRun. It lets you define keyboard shortcuts to start one or more programs without moving your hand to the mouse, clicking Start… Now that I’ve moved from XP to Vista, I use a tool called Start++ that performs a similar function (better in some ways, not as good in others).

An example of the command you need to store is:

cmd.exe /k cd \sqlscripts\hr_demos\ && title hr@11g && sqlplus

That command (which you can also test from Start > Run):

• Starts a dos prompt. The /k tells dos not to terminate after executing any commands that follow it.
• Changes to the \sqlscripts\hr_demos\ directory
• Changes the title of the dos window to hr@11g (we’ll use this later)
• Starts sqlplus

For step three, I wanted a secure way to store the database connect information. I’ve been using KeePass to store account information for a while now, and it works well for database accounts as well. The thing I love about KeePass is it’s “Auto-Type” feature. This enables it to send a customizable username / password string to a window based on it’s title (remember that we set the title of the dos prompt with the “title” command). For instance, when I open Yahoo mail, I simply hit ctrl+alt+a and KeePass sends {username}{TAB}{password}{ENTER}. I defined this sequence, and told it to look for windows with the title Yahoo!*. I haven’t typed in a password for an account in months!

The string sequence I used for this database connection is the following:

{USERNAME}@11g{ENTER}{PASSWORD}{ENTER}

So, to login to this account I simply:

• Press the Windows key
• Type hr + [Enter] (Start++ executes the dos command described earlier)
• Press [Ctrl]+[Alt]+a (KeePass sends the correct db account info based on the title of the window)

This may sound a bit complicated, but I wanted to err on the side of being too detailed so people could adapt this to other combinations of utilities. It’s really very simple to set up and will save you a lot of time and clicking.

---

Possibly related posts: (automatically generated)

• Presenting at SQL Down under Code Camp 2007
• Logical I/O(consistent get) and Arraysize relation with SQL*PLUS
• One of those weird ones - ora-32036