Ravi Khanal

Microsoft’s T-SQL is similar to Oracle’s PL-SQL to some extent, but they are very different in some important and basic ways. Some of the basic differences are:

1. The difference in the datatype:

T-SQL	PL-SQL
Integer, SmallInt, TinyInt, Bit	NUMBER
Varchar	Varchar2
Text	CLOB
IMAGE	BLOB
BINARY(n), VARBINARY	RAW(n) or BLOB
MONEY	NUMBER(19,4)
DATETIME, SMALL-DATETIME	DATE
NVARCHAR(n)	VARCHAR(n*2)
TIMESTAMP	NUMBER

 

2. In T-SQL Datetime is represented as 1/300th Second and in PL-SQL date is represented as 1 second and Timestamp as 1/100 millionth Second.

 

3. The default date format of PL-SQL doesnot include time part.

 

4. In T-SQL, there is no need of dual. The T-SQL Syntax will look like:
       select getdate()
   The PL-SQL will look like
      slect sysdate from dual

 

5. INSTEAD OF trigger is used in SQL Server as an equivalent to Oracle’s BEFORE trigger.

 

6. CASE expressions can be used in SQL Server for Oracle’s DECODE() function.

 

7. In T-SQL there is an IDENTITY function which is more simpler than the SEQUENCE of PL-SQL. For implementing Sequence in PL-SQL, you have to create a new object called SEQUENCE and also we should have the trigger.

 

8. You cant have multiple databases on an Oracle Instance but you can have multiple databases on a SQL Server Instance.

 

9. In T-SQL there is SELECT INTO. In PL-SQL you have to use INSERT INTO.
   T-SQL: select getdate() newcolumn into newtable;
   PL-SQL: insert into newtable(newcolumn) values (sysdate);

 
 

10. In PL-SQL there is MINUS operator. In T-SQL you have to use NOT EXISTS clause in Select statement to generate the same result.

 

11. T-SQL’s RAISEERROR does not break the flow. It simply returns an error string or message but the stored procedure still returns normally. PL-SQL’s raise_application_error throws an exception, exits the current stored procedure, rolls back till implicit savepoint at the beginning of the stored procedure.

 

12. In T-SQL, stored procedure do not rollback automatically if something fails. You can use TRY CATCH but still the roll back is optional. In PL-SQL, stored procedures are atomic. Any error inside a stored procedure rolls back up to the point where the stored procedure was called.