Oracle SQL*Plus help

I guess the assumption is that anyone who gets an Oracle client on their desktop knows what they are doing. Or they are a DBA so they are using a fancy front end with a nice gui. Well include me out of both those groups.

I'm just a Coldfusion programmer trying to make my life easier. I hope some basic demos make your life easier, too.

Note, these run on Oracle 10g with Oracle SQL*Plus Version 9.2.0.1.0 on Windows XP. Not sure about other versions.

To run the program, copy the code between the "--Begin" and "--End" comments into a plain text file. Save the file with a .sql extension. Copy the file into your C:\oracle\oraclexx\bin folder. From your SQL*Plus prompt, type the file name with an @ prefix.

Basic loop

The challenge: I had a column in a table that stored phone numbers. Some of the phone numbers had a "+1" prefix, some didn't. I wanted them consistent.

Here's what I did:

(Shout out to the folks at Stanford for getting me on the right path.)

What server am I connected to?

If you connect to multiple Oracle instances, say development, test, and production servers, you probably often find yourself wondering, "Where am I?" Well, here's your answer!

Save the following line of code in a text file with a .sql extension and place it in your Oracle bin folder. In my case, I named it whereami.sql. Whenever I want to know what server I'm connected to, I just type "@whereami" at the prompt and Bob's my uncle.