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, titlefrom authorsinner join titleauthoron(authors.au_id = titleauthor.au_id)inner join titleson (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, enamefromdept, empwheredept.deptno = emp.deptno

Your Third Hurdle: Typing, Running, and Editing a Query
Now try entering and executing a simple query. First enter the simple example below:

select *from scott.dept

As you enter each row, SQL*Plus assigns a line number. In order to run the query after you have finished typing it, you have two choices. Before hitting the Enter key on the last line, you can type a semicolon (or after hitting Enter, you can type the “/” key).

Below is the query and its result:

SQL> select  2  *  3  from scott.dept  4  /    DEPTNO DNAME          LOC———- ————– ————-        10 ACCOUNTING     NEW YORK        20 RESEARCH       DALLAS        30 SALES          CHICAGO        40 OPERATIONS     BOSTONSQL>

Now suppose you wanted to modify the query to add a join to the “emp” table. The editing capabilities built into SQL*Plus are all based on keystroke commands. For example, “L” lists the code. However, SQL*Plus also supports the “edit” command. The edit command shells out to the editor of your choice. By default the editor is Microsoft Notepad.

Type the word edit followed by the SQL> prompt and a Notepad window with the previous SQL command you typed should appear. Use Cut and Paste and any other common commands to modify the code.

In my case, I’ve changed the above code to this:

selectdname,enamefrom scott.dept d, scott.emp ewhered.deptno = e.deptno

To get back to SQL*Plus, simply save and exit Notepad. The code will then appear in the SQL*Plus window. Hit “/” to execute it. You can choose which editor will be invoked by choosing Define Editor in the Edit menu.

SQL*Plus can also load and execute code from a file in one step. Simply type “@” followed by the name of the file?as in @c:example.sql. Experienced Oracle developers or DBAs will likely have numerous scripts that they use this way.

Your Fourth Hurdle: Browsing Objects in the Schema
As I’ve pointed out, SQL*Plus doesn’t come with an object browser. Here are some commands that will at least show you the basics about the tables in Oracle.

The command “DESCRIBE [‘desc’ for short] table name” returns a list of the columns with their data types for the given table:

SQL> desc scott.emp Name                 Null?    Type ——————– ——– —————- EMPNO                NOT NULL NUMBER(4) ENAME                         VARCHAR2(10) JOB                           VARCHAR2(9) MGR                           NUMBER(4) HIREDATE                      DATE SAL                           NUMBER(7,2) COMM                          NUMBER(7,2) DEPTNO                        NUMBER(2)SQL>

Oracle also contains a slew of views to access Oracle’s metadata. There are three basic types of views:

1. USER_? Views prefaced with “user_” show only the information about objects in the schema of the user. So if I log in as “scott,” then the following code supplies me with the list below:

SQL> select table_name  2  from user_tables;TABLE_NAME——————————BONUSDEPTEMPPLAN_TABLESALGRADESQL>

2. ALL_? Views prefaced with “all_” show information about all objects to which a user has access. The code below therefore returns more than just the four tables listed in the previous example:

SQL> select owner,table_name  2  from all_tables;OWNER              TABLE_NAME—————— ——————————SYS                DUALSYS                SYSTEM_PRIVILEGE_MAPSYS                TABLE_PRIVILEGE_MAPSYS                STMT_AUDIT_OPTION_MAPSYS                AUDIT_ACTIONSSYS                PSTUBTBLSYSTEM             DEF$_TEMP$LOBSYS                AURORA$IIOP$SYSTEM$PROPERTIESMDSYS              OGIS_SPATIAL_REFERENCE_SYSTEMSMDSYS              MD$DICTVERMDSYS              CS_SRSSYSTEM             HELPSCOTT              DEPTSCOTT              EMPSCOTT              BONUSSCOTT              SALGRADESCOTT              PLAN_TABLE

3. DBA_? If you have the necessary permissions, you can access views prefaced by “dba_.” These show information about all objects in the system. The code below lists every table in the Oracle database:

SQL> select owner, table_name  2  from dba_tables

As you’ve seen, accessing a database using SQL*Plus and Oracle is different and harder than with the familiar Query Analyzer in SQL Server. It does take time to become proficient in all the details and nuances of a new environment. I hope I’ve given you enough to get you started and make your first time that much easier.