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.
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;
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.
-- MSSQL 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.
-- MSSQL 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.