question

Ian Ringrose avatar image
Ian Ringrose asked

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?

sql-serverdevelopersubjective
1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

+1 A very good question, I found so late. Had the problems in company when oracle guys talk with sql server guys.
0 Likes 0 ·
Maz 1 avatar image
Maz 1 answered

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

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

David Bick avatar image
David Bick answered

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.
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Maz 1 avatar image
Maz 1 answered

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

1 comment
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

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
0 Likes 0 ·
Hector Minaya avatar image
Hector Minaya answered

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
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Christian13467 avatar image
Christian13467 answered

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

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.