MS SQL vs Oracle database operational differences

I was working on a project where I need to migrate a database running on MS SQL server 2000 to Oracle 11gR2. I was using Oracle SQL Developer to convert codes such as functions, procedures, etc,. I had to do a lot of programming logic changes. I have noticed a few differences in both the flavours of RDBMS which I'll try to cover it up in this post.
This post covers only the technical coding differences and not about the general differences such as adaptability, licensing, usage, etc,.

1. MS SQL 'IF EXISTS'

We have codes in MS SQL like

begin
if exists
(select * from TABLE_01 where COL_01 = @var_01)
insert into .. 
else 
do something ..
end if
end

In Oracle, the situation can be handled with MERGE statement for "if exists insert or update" only when the tables involved are 2.
An example of MERGE statement is as below

MERGE INTO TABLE_01 a
USING TABLE_02 b
 ON (a.first_name = b.first_name)
-- orabliss.blogspot.com
WHEN MATCHED
 THEN
  UPDATE
  SET a.last_name = b.last_name
WHEN NOT MATCHED
 THEN
  INSERT (
   first_name
   ,last_name
   ,school
   )
  VALUES (
   b.first_name
   ,b.last_name
   ,b.school
   );

You can't have more than 2 tables to use MERGE statement. Also if the above is not for "if exists insert or update", then Oracle can handle this as below.

DECLARE v_temp NUMBER ( 1, 0) : = 0;
BEGIN
 SELECT COUNT(*)
 INTO v_temp
 FROM TABLE_01
 WHERE KEY_COL = v_var_01; -- v_var_01 is value passed or already declared
 IF v_temp > 0 -- This becomes "IF EXISTS" of MS SQL
 Do some operation
 ELSE 
 Do other operation
 END IF;
END;
/

2. IDENTITY data type

In MS SQL database we have IDENTITY type which is basically a primary key sequentially inserted value column defined as below.

CREATE TABLE [dbo].[TABLE_01] (
 [SERIAL_ID] [SMALLINT] IDENTITY (1, 1) NOT NULL ,
 [COL_02] [CHAR] NOT NULL ,
 ..
 ..
 )
GO

In Oracle we don't have this functionality until Oracle release 12c. So we have to create a sequence, create a trigger and use the trigger to fetch the next_value from the sequence every time the table is inserted with a new row.
Starting Oracle 12c, we have this IDENTITY column implemented which internally creates a sequence and increments when row inserted.

3. Data handling

We are fetching data into variable from a table to do some process. The predicate filter columns are not primary key columns.

MS SQL code

SELECT @Id_No = ID_NO
 ,@First_Name = FIRST_NAME
 ,@R_Code = R_CODE
 ,@Last_Name = LAST_NAME
FROM TABLE_01
WHERE CLASS_NO = @Class_No
 AND STD_QTY = @StdQty;

Oracle Code

SELECT ID_NO
 ,FIRST_NAME
 ,R_CODE
 ,LAST_NAME
INTO v_Id_No
 ,v_First_Name
 ,v_R_Code
 ,v_Last_Name
FROM TABLE_01
WHERE CLASS_NO = v_Class_No
 AND STD_QTY = v_StdQty;

Here we have 3 conditions either...
1. We have a single row returned as expected
2. We don't have any matching rows
3. We have more then 1 row for the combination of predicate

When we have the first condition satisfied, then both MS SQL and Oracle handles data in the same way.
When we have no matching rows, MS SQL by default assigns null values to all the variables whereas in Oracle the code errors out as NO_DATA_FOUND exception.
So here we need to handle the exception explicitly. One might think this is bad, but this is advantageous than MS SQL as we would know whether we have data for this combination or not before processing with the data using exception handler as below

EXCEPTION 
 WHEN NO_DATA_FOUND THEN
 -- handle exception stmt

Now coming to the third condition, when we have more than 1 row, MS SQL by default assigns the last fetched data to the variables ignoring all other values.
Oracle errors out with another exception TOO_MANY_ROWS.
If filter column is a primary key, we won't encounter this issue, but in our case it is not.
When one encounters TOO_MANY_ROWS exception (if expected), this can be handled through a loop statement to process the operation for every rows returned.

EXCEPTION WHEN TOO_MANY_ROWS THEN LOOP
FOR i IN
(SELECT v_Id_No
FROM TABLE_01
WHERE CLASS_NO = v_Class_No
 AND STD_QTY = v_StdQty)
 -- Do something
 ..
 ..
 END LOOP;

If you are concerned only about particular fields and not all other fields involved, this can also be handled with limiting the query to get only one row as below.

SELECT ID_NO
 ,FIRST_NAME
 ,R_CODE
 ,LAST_NAME
INTO v_Id_No
 ,v_First_Name
 ,v_R_Code
 ,v_Last_Name
FROM TABLE_01
WHERE CLASS_NO = v_Class_No
 AND STD_QTY = v_StdQty 
 -- This will limit the code to fetch only one row
 and rownum =1;

4. Transaction Control

In MS SQL, when we run a block of statements/queries, it treats each statement/query as a single unit. For eg. check the block below.

insert into table t1 values (v1);
insert into table t1 values (v2);
delete from t1 where field=v1;
exec some_proc;

We have 3 statements and when we run this in MS SQL database, then each statement will be committed individually irrespective of their previous statement is success or failure where as in Oracle all the 3 statements are treated as a single unit and should be committed (COMMIT) or rolled back (ROLLBACK) for all the changes to take effect at once.  As queries are executed and commands are issued, changes are made only in memory and nothing is committed until an explicit COMMIT statement is given (exclusion for DDL statements where an immediate commit is issued after execution).

In order to make the MS SQL group the all the transactions to a single group, BEGIN TRANSACTION with either COMMIT or ROLLBACK has to be specified as below.

BEGIN TRANSACTION
insert into table t1 values (v1);
insert into table t1 values (v2);
delete from t1 where field=v1;
exec some_proc;
COMMIT; -- or ROLLBACK

5. Find and replace

STUFF function serves the find and replace task in MS SQL which is powerful function used in many places.
REGEXP_REPLACE function is even more powerful compared to MS SQL's stuff as the control to user on how to find and replace is very useful in complex programming.

6. Objects

MS SQL stored procedures can return values whereas Oracle procedures will not return values. We can use OUT or IN OUT argument to get the required output. Oracle FUNCTIONs serves the purpose of doing the same as procedures and can return values.
In Oracle, a group of procedures and functions can be collectively put in as PACKAGE whereas in MS SQL this can't be done.

7. Miscellaneous

a) There a few data type differences that has to be taken care while converting from MS SQL to Oracle database. A few noticeable differences are in this Oracle document link.

b) User defined error messages can be created using sp_addmessage stored procedure in MS SQL. In Oracle, the user messages doesn't need to be created separately and can be raised as an user defined exception which ranges from -20000 to -20999.

The more we share, the more we learn. I'll update the post as and when I get additional differences while working on the flavours again.

Happy working!