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?

more ▼

asked Oct 29, 2009 at 01:21 PM in Default

Ian Ringrose gravatar image

Ian Ringrose
225 6 6 8

+1 A very good question, I found so late. Had the problems in company when oracle guys talk with sql server guys.
Dec 16, 2009 at 07:40 PM Christian13467
(comments are locked)
10|1200 characters needed characters left

5 answers: sort voted first

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.
more ▼

answered Oct 29, 2009 at 02:10 PM

David Bick gravatar image

David Bick
144 1 1 2

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Oct 29, 2009 at 02:12 PM

Maz 1 gravatar image

Maz 1

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
Dec 18, 2009 at 05:18 AM Ian Ringrose
(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Oct 29, 2009 at 01:51 PM

Maz 1 gravatar image

Maz 1

(comments are locked)
10|1200 characters needed characters left

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

more ▼

answered Dec 16, 2009 at 07:38 PM

Christian13467 gravatar image

846 2

(comments are locked)
10|1200 characters needed characters left

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
more ▼

answered Oct 29, 2009 at 02:38 PM

Hector Minaya gravatar image

Hector Minaya
183 4 5 7

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Oct 29, 2009 at 01:21 PM

Seen: 1245 times

Last Updated: Oct 29, 2009 at 01:52 PM