Tyler Muth’s Blog

Technology with a focus on Oracle, Application Express and Linux

SQL*Plus Tricks

Posted by Tyler Muth on October 14, 2007

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.

4 Responses to “SQL*Plus Tricks”

  1. tylermuth said

    One additional note on storing passwords. Another technique for storing passwords that will also work when called from scripts (unlike the KeePass solution), is to use the Oracle Wallet Manager. This is licensed with the Advanced Security Option for the database, and is one of ASO’s little known features.

    Tyler

  2. Brian Tkatch said

    # Open a dos prompt
    # Change to a directory that contains the sql scripts I’m working on

    Make a “shortcut”
    – right click on sqlplus
    – choose create shortcut

    Change the working directory
    – Right-click on the shortcut
    – Under “Start-In” change it to your scripts directory

    Add a shortcut key
    – Same as above
    – Choose shortcut key

    This has been in Windows since 3.1 (probably from 1.0, but i don’t know. It is *very* convenient.

  3. Louis said

    Hi,
    for the first 2 steps (assuming Windows-platform) I would recommend ‘Command Prompt Here’.
    See this page

  4. Jignesh said

    Alternate to use of KeePass for yahoo is…

    If you are using Mozilla Firefox, check for Webmail Notifier Add-on. This is useful if you’ve got multiple email accounts like yahoo, hotmail, gmail and so on.

    Also if you are looking for similar Add-on for pop3/imap accounts, search for “Simple Mail”.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

 
%d bloggers like this: