What are the most important things for a developer to know when moving from SqlServer to Oracle?
At times even hard core Microsoft developers must sometimes work on project that use oracle.
What are the most important differences to keep in mind as a developer?
A very good question, I found so late. Had the problems in company when oracle guys talk with sql server guys.
Here's a random list to get things started:
- Default locking is different -- writers don't block readers, and vice-versa.
- Empty strings are null.
- Packages for stored procs are your friend. Use them.
- Identities don't exist -- you need to use sequences.
- Use different schemas where you used to use different databases.
- Trees are now easy (via CONNECT BY).
- Cursors are no longer evil.
Let me add this ubiquitous pseudo-table used in Oracle named DUAL
For example, In SQL server you will say SELECT getdate()
The Oracle equivalent will be SELECT SYSDATE FROM DUAL
I found that adding a table called "emptyTable" to my database lets the same SQL work on Oracle and Sql server by advoiding the to use DAUL
Some of the points to take care 1. Basic SQL development is same as both of the dbs follow the same ANSI standards. 1. Oracle and SQL Server treat cursors differently for performance optimization 3. Different approaches when building .NET applications using the Oracle db and the SQL Server db. You can take hint from the .NET Developer Center from Oracle web site http://www.oracle.com/technology/tech/dotnet/index.html?msgid=6705607
There are some naming differences:
- An oracle database/instance is a sql server instance.
- An oracle schema (defined by a user) is similar to an sql server database.
- An oracle user might be similar to sql server login credentials.
An oracle real application cluster (rac) with more than 1 node is always an active-active cluster. An sql server database cluster means the same but is primarly an active-passive cluster.
The SQL syntax differs in various points.
- Outer joins are defined different
- NULL handling of string variables is different
SELECT CONCAT('hallo', null) FROM dual; -- Oracle = 'hallo'
SELECT CONCAT('hallo', null); -- SQL Server = NULL - ...
- a function reference http://www.sql-ref.com/
The best reference for differences is this link http://www.psoug.org/reference/sqlserver.html
I've been working with both for years, one of the biggest differences is how they each handle StoredProcedures.
Oracle has:
- Packages, which in turn have procedures or functions
- Individual procedures and functions
SQL Server: - StoredProcedures and Functions.
They also differ in:
- Cursors
- Some syntax variations
