ogging in and querying an Oracle database can be a daunting task for anyone used to SQL Server.

Instead of the familiar Query Analyzer tool (see Figure 1), which provides a color-coded syntax, an object browser, multiple windows, and an output grid, you most typically have to contend with Oracle's SQL*Plus—a tool lacking these features (see Figure 2).

In this article, I show you how to survive your first Oracle query with your dignity intact.

Your First Hurdle: Logging In
As with Query Analyzer, SQL*Plus comes in two versions: a command-line version and a GUI version represented respectively by sqlplus.exe or sqlplusw.exe. As I've previously stated, even the GUI version is rather difficult to use. But it's still easier than the command-line version. For the rest of this article I will use the GUI version.

Figure 1. Query Analyzer with a Simple Query: You can see the object browser to the left, color-coded syntax in the middle, and the results in a grid on the bottom.

When you start SQL*Plus, you are presented with a dialog box (see Figure 3) asking for the username, password, and hostname. The first two items are self-explanatory; Host String represents the name of the Oracle instance you are logging in to. You will typically be provided with the values for all three by your DBA. Unlike Query Analyzer, you only get three tries to log in before SQL*Plus exits. You have to restart SQL*Plus in order to try again.

If you like, you can pass the three parameters to the program on the command-line. Here's the format:

sqlplusw username/password@hostname

Be careful not to put a space between the password and the @ sign.

Your Second Hurdle: Understanding the Different Syntax
Although SQL Server and Oracle both use SQL, there are enough differences in the syntax to make life difficult if you are unaware of them. It's kind of like what happens to tourists when they visit New York City; they believe they should know what we're saying—after all, it sounds like English—but somehow they can't seem to understand us.

Figure 2. SQLPLUSW, the GUI Version of SQL*Plus: As you can see, there are no frills attached

Referring to Objects  In SQL Server, groups of objects are organized together and stored in separate databases. You are all familiar with the various databases created upon installing SQL Server, such as the master, pubs, and Northwind databases. After logging in with Query Analyzer, you typically first execute the "use database name" command to work in a particular database. For example, if you wanted to work in the pubs database you would first type use pubs. This way, you can refer to all the tables in the database by using their names—as in:

select * from authors

Otherwise, if you were in another database, such as Northwind, and still wanted to select from the authors table in pubs, you would have to preface the name of the table with the database name and the owner—as in:

select * from pubs.dbo.authors".

Figure 3. The SQL*Plus Login Dialog Box: The first two items are self-explanatory; Host String represents the name of the Oracle instance you are logging in to

In the Oracle world, groups of objects are organized into schemas, which every user has. When you refer to an object you must preface the name with the name of the schema unless the object is in your schema. Here are two examples to illustrate the point. (For an explanation of what database means in Oracle, see my article, "Oracle Concepts: The Database and the Instance.")

In the first case I've logged in to Oracle as "scott." User "scott" has a schema with some very simple tables that are used to provide examples of SQL coding in the same way that the pubs database is used in SQL Server. To retrieve rows from the "dept" table I can simply type:

select *
from dept

On the other hand, suppose I logged in as SYS. I would then have to type

select *
from 
scott.dept

There is no equivalent of the "use" statement in Oracle. (Your DBA can set up something called a synonym, which is a way of providing a simpler name for tables. But that is beyond the scope of this article.)

  Joining Tables  SQL Server supports the keyword "join" to specify the joins between tables:

select au_lname, title
from authors
inner join titleauthor
on(authors.au_id = titleauthor.au_id)
inner join titles
on (titleauthor.title_id = titles.title_id)

However, Oracle does not support the "join" syntax. Instead, the older style syntax is used by defining the relationships between the tables in the "where" clause. Those of you who used earlier versions of SQL Server, such as 4.2 or 6, will already be familiar with this syntax (if perhaps rusty).

Following is an example of a simple join between two tables using Oracle:

select dname, ename
from
dept, emp
where
dept.deptno = emp.deptno