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.
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:
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.
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:
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 authors
select * from pubs.dbo.authors”.
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:
On the other hand, suppose I logged in as SYS. I would then have to type
select *from 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.)
select *from scott.dept
??Joining Tables? SQL Server supports the keyword “join” to specify the joins between tables:
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).
select au_lname, titlefrom authorsinner join titleauthoron(authors.au_id = titleauthor.au_id)inner join titleson (titleauthor.title_id = titles.title_id)
Following is an example of a simple join between two tables using Oracle:
Your Third Hurdle: Typing, Running, and Editing a Query
select dname, enamefromdept, empwheredept.deptno = emp.deptno
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:
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.
SQL> select 2 * 3 from scott.dept 4 / DEPTNO DNAME LOC———- ————– ————- 10 ACCOUNTING NEW YORK 20 RESEARCH DALLAS 30 SALES CHICAGO 40 OPERATIONS BOSTONSQL>
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:
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.
selectdname,enamefrom scott.dept d, scott.emp ewhered.deptno = e.deptno
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:
Oracle also contains a slew of views to access Oracle’s metadata. There are three basic types of views:
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>
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:
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.
SQL> select owner, table_name 2 from dba_tables