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) 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;

Concatination of strings in Oracle SQL

In Microsoft SQL server I am used to be able to concatinate strings with the CONCAT() function that also works well in Oracle.

SELECT CONCAT('Test ', '1')
-- Oracle SQL
SELECT CONCAT('Test ', '1') from dual;

What I sometimes just want to do is to use a string column and add some other strings to it.

SELECT name+' ('+classid+')' AS TestName
FROM testTable
-- Oracle SQL
SELECT name||' ('||classid||')' AS TestName
FROM testTable;

This is different in Oracle and I have to use the |sign to be able to concatinate. If I try to use the + this ends up with an error unvalid number.

Teis Lindemark

Read more posts by this author.