What are the most important things for a developer to know when moving from SqlServer to Oracle?

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

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