Cand3lasville

Basic Differences between Oracle and SQL Servers

Posted on by Posted in Article, Computers, SQL .

Basic Differences between Oracle and SQL Servers

If you work with Oracle as well as SQL Server then you would sometimes need the cross functionality of certain codes. You may be working with a code in Oracle and then you will need to recreate the query in SQL only to find out that the code you are using does not translate. This may also happen the other way. You may be working on a query for SQL servers and when you want to replicate that into Oracle then it does not translate.

It is try that both systems are using the Structured Query Language or SQL but these systems are slightly different. This is because SQL Server uses Transact Structured Query Language or T-SQL while Oracle uses procedural language extension to Structured Query Language or PL/SQL. Based on the 2005 article by Margaret Rouse, T-SQL or Transact Structured Query Language is a set of programming extensions from Sybase and Microsoft that add unique features to the this programming language. Margaret Rouse also talks about PL/SQL to add procedural language extensions to the SQL programming language.

Therefore, both of these systems took the basic SQL programming language and added what each felt it was necessary to make it work for them. This explains, if you ever worked between these two systems, why the main code you with is the same but main other pieces of code have to be translated between these two systems. As an example to this let us consider the scenario where you need a number that may be between 3 to 5 characters long to be standard. You may have a report that requires that all these numbers to be 5 character’s long.

Here is what we could right in T-SQL:

USE COMPANYEMPLOYEES
GO

SELECT EMP_NUM
      ,EMPLOYEE_NAME

FROM GGO.EMPLOYEE_TABLE

GO
;

This code may produce the following data:

EMP_NUM EMPLOYEE_NAME
23 John Doe
1541 Jane Doe
245 John Smith
12342 Jane Smith
2 Joe Smith

 

However, if you write the code this way:

USE COMPANYEMPLOYEES
GO

SELECT RIGHT(REPLICATE('0',5) 
           + RTRIM(LTRIM(EMP_NUM)),5) AS "EMPLOYEE #"
      ,EMPLOYEE_NAME AS “EMPLOYEE NAME”

FROM GGO.EMPLOYEE_TABLE

GO
;

Then you will see this:

EMP_NUM EMPLOYEE #
00023 John Doe
01541 Jane Doe
00245 John Smith
12342 Jane Smith
00002 Joe Smith

As you can see the employee numbers are now all 5 characters long. Now if you would write this in PL/SQL to get the same result then things would be different. It will look something like this:

SELECT LPAD(EMP_NUM,5,'0') AS "EMPLOYEE #"
      ,EMPLOYEE_NAME AS "EMPLOYEE NAME"

FROM GGO.EMPLOYEE_TABLE

;

As you can see the grammar between these two languages are similar but the difference are apparent.

 

Works Cited

Rouse , M. (2008, October). PL/SQL (procedural language extension to Structured Query Language). Retrieved July 26, 2013, from SearchOracle: http://searchoracle.techtarget.com/definition/PL/SQL

Rouse, M. (2005, September). T-SQL (Transact-SQL). Retrieved July 26, 2013, from SearchSQLServer: http://searchsqlserver.techtarget.com/definition/T-SQL