Oracle For SQL Server Developers, Vol. 2

Here’s another post in my Oracle for SQL Server Developers series. 

Tip #7: What is SQL*Plus and how do I connect to Oracle with SQL*Plus?

SQL*Plus is a command-line client for talking to Oracle.  It’s similar to SQL Server’s sqlcmd.exe.  It’s usually installed in $ORACLE_HOMEBINsqlplus.exe so on my machine with a default install that path ends up being C:appusernameproduct11.1.0client_1BIN. 

There are (at least) 3 ways to connect using SQL*Plus.

1. Go to the command line and type sqlplus.exe and walk through the prompts
2. Connect with the username, password, and instance name:
   sqlplus scott/tiger@win2008oracle
3. Connect with just username and instance name:
   sqlplus scott@win2008oracle

Tip #8: What is the equivalent of the “sa” user in Oracle?

In SQL Server, the “sa” username is the adminstrator for the database instance.  The rough equivalent in Oracle is the SYS username.

Tip #9: I’m getting an error when I try to connect to Oracle as an administrator.

Let me guess.  You’re getting this error:

ORA-28009: connection as SYS should be as SYSDBA or SYSOPER

Oracle wants you to acknowledge that you’re connecting as an administrator (aka “super user”) when you log in.  In order to do this you need to specify “AS SYSDBA” at the end of your connection info.  In sqlplus.exe, it would look like this:

sqlplus sys/password@win2008oracle AS SYSDBA

Tip #10: How to get around in SqlPlus.

Yah.  SqlPlus is old, clunky, and obtuse.  Here are some ideas to get you going.

• Stuff that you type goes in to something called the command buffer
• End your commands with a semi-colon (‘;’)
• Press forward slash (‘/’) to run the commands in the command buffer
• To get a lifeline out of the technological stone-age, type ‘edit’ and your command buffer will show up and be editable in notepad.exe.  (Yah…notepad’s not a hell of a lot better than command line but well, it’s Oracle.  It’s supposed to be hard.)  When you’re done editing your command buffer, save the file using the filename that SqlPlus assigned to you, and exit notepad.
• Have I mentioned yet that you can run the contents of the command buffer by typing ‘/’?  (Yah…you’ll want that after you just did the notepad thing.)
• If you want to execute a query that is stored in a file, type in ‘@’ followed by the filename.  For example, if you’ve got a query stored at c:myQuery.sql, type in “@c:myQuery.sql”. 
• To exit SQLPlus, type ‘quit’ or ‘exit’.
• If you want to learn more about SQLPlus, type ‘help index’ for a list of help topics. 
• For more information about a specific help topic type “help topic_name”.

Tip #11: How do can I see what columns are in a table?

Type “desc tablename”

Tip #12: How can I put row numbers in front of every record in my result set?

This feature is legitimately cool and SQL Server doesn’t have it.  (Well, maybe SQL2008 has it but I haven’t looked.)  Yes, I’m saying something nice about Oracle.  (Can you believe it?) 

When you run a select statement, there’s a psuedo-column in the result set called “rownum”.  If you add the “rownum” column to your select statement, you get a sequential row number for each record.  Try it.  Type in

select rownum, * from EMP;

Cool, huh? 

Tip #13: What’s the equivalent of “SELECT TOP” in Oracle?

For this one, you’ll use the ROWNUM psuedo-column.  So in SQL Server let’s say that you’ve got a table called Employee and you want to get the first 6 records.  The query would be

SELECT TOP 6 * FROM EMPLOYEE

For the same query in Oracle using the EMP table you need to use ROWNUM:

SELECT * FROM EMP WHERE ROWNUM <= 6

Tip #14: ROWNUM is a mess when I use an ORDER BY clause — OR — How can I do a TOP query and sort the results?

Let’s say that you want to bring back the top 6 records, sort the results, and use ROWNUM to put the record number in front of the resultset.  Try out this query and check out the results:

SELECT ROWNUM, EMPNO, ENAME FROM EMP
WHERE ROWNUM <= 6
ORDER BY ENAME;

The ROWNUM values are out of order. 

What’s going on is that the ROWNUM values are being set before the ORDER BY executes so the values are messed up.  To make this work properly you need to use two selects:

SELECT ROWNUM, EMPNO, ENAME FROM
(SELECT EMPNO, ENAME
FROM EMP
WHERE ROWNUM <= 6
ORDER BY ENAME)

The SELECT in the parenthesis brings back the first 6 records and sorts them.  Then the outer SELECT queries that result and adds the ROWNUM. 

Click here to read the next Oracle for SQL Server Developers post.

-Ben