Wrapping my head around Oracle database coming from MSSql

I have for the last 9 years used Microsoft SQL Server and when I changed my job to a senior consultant, the first mission used Oracle as their database server. Things work differently between Microsoft SQL Server and Oracle, so here is a little wrap up of the things I use most that I found the Oracle way of doing it. This is a collection post where I will add things as I go along with using Oracle.

SELECT @@Version

Retrieving the version number of the database server is very easy in MS Sql and quite different in Oracle.

-- Oracle
SELECT * FROM v$version;

SELECT TOP(10) * FROM TESTTABLE

SELECT TOP(10) in MS Sql returns the first 10 rows of a table.

-- Old Oracle way
select * from
    ( select   *
      from     TESTTABLE
      order by Id )
where rownum <= 10;

-- New Oracle way
select   *
from     TESTTABLE
order by Id
fetch first 10 rows only;

The new way has a lot better performance than the old way in Oracle. Read more at the Oracle blog.

Just make small simple SELECT statements

-- What I did in MSSQL
SELECT 1000*3.14 Calculation, DATEADD(DAY,25,GETDATE()) DATEIN25DAYS

When I worked quite a lot with Microsoft SQL Server, I also used it a lot with simple SELECT without a from clause. For example, for simple math calculations or finding dates, it was my goto (at least when I was at work). Above is just two simple examples. When I had to use Oracle, I can not write a SELECT without a FROM clause, that gives me an error telling me missing FROM clause. Instead, Oracle have a dummy table they call dual.

DUAL is a table automatically created by Oracle Database along with the data dictionary. DUAL is in the schema of the user SYS but is accessible by the name DUAL to all users. It has one column, DUMMY, defined to be VARCHAR2(1), and contains one row with a value X. Selecting from the DUAL table is useful for computing a constant expression with the SELECT statement. Because DUAL has only one row, the constant is returned only once. Alternatively, you can select a constant, pseudocolumn, or expression from any table, but the value will be returned as many times as there are rows in the table. Please refer to "SQL Functions" for many examples of selecting a constant value from DUAL.

To get the same result as I did in MSSQL, I then need to include FROM dual.

-- The Oracle way
SELECT 1000*3.14 Calculation, SYSDATE+25 DATEIN25DAYS FROM dual;

Teis Lindemark

Read more posts by this author.