Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Whitepapers          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

Next Webcast - The Cloud won't fix that (click for more info)
 

Comparing Oracle and SQL Server Code - Part 1


By:   |   Read Comments (8)   |   Related Tips: More > Other Database Platforms

Problem

In this tip we are going to compare the SQL Server (T-SQL) and Oracle (PL-SQL) extensions of the SQL language. The tip will compare both T-SQL and PL-SQL languages with respect to retrieving data, creating databases, tables, variables, etc.

Solution

This tip will compare the code between SQL Server and Oracle for the following:

  • Database Creation
  • Changing database usage
  • List databases
  • Table creation
  • Data insertion
  • Create Referential Integrity
  • Create auto incrementing values
  • Capture and format the date
  • Capture the variance
  • Determine the position of a word
  • Generate random numbers
  • Assign a value to a variable
  • List of tables and views in a database

Database Creation in SQL Server vs. Oracle

SQL Server

Oracle

CREATE DATABASE [DB2]
CONTAINMENT = NONE
ON PRIMARY
( NAME = N'DB2', FILENAME = N'C:\SQL\db2.mdf' , SIZE = 3072KB , MAXSIZE = UNLIMITED, FILEGROWTH = 1024KB )
LOG ON
( NAME = N'ssis_log', FILENAME = N'C:\sql\db2_log.ldf' , SIZE = 1024KB , MAXSIZE = 2048GB , FILEGROWTH = 10%)
GO

ALTER DATABASE [db2] SET COMPATIBILITY_LEVEL = 120
GO

ALTER DATABASE [db2] SET RECOVERY FULL
GO

ALTER DATABASE [DB2] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
GO

The example creates a data file named db2.mdf which stores the data. The log file is stored in the db2_log.ldf the maximum size for the data file is unlimited and the log file can have a maximum size of 2048 GB. The compatibility level is used for backward compatibility with earlier SQL Server versions. The recovery model is used to set the mode to recover information using the transaction log files. Finally, the filestream option is used to store unstructured data.

In Oracle, creating a database is a more complex process. In order to see the steps, refer to this link:

Creating a Database with the CREATE DATABASE Statement.


Change Databases in SQL Server vs. Oracle

SQL Server

Oracle

USE DB2
GO

In Oracle a Database is a complete instance. You cannot switch contexts with a USE command.

 

List of Databases in SQL Server vs. Oracle

SQL Server

Oracle

exec sp_databases
GO

In UNIX, Linux you can use the ps command to see the list of processes and find the ones related to Oracle. In Windows, query the following registry key: HKEY_LOCAL_MACHINE\ SOFTWARE\ORACLE\oracle_home and check the ORA_SID related parameters

 

Table Creation in SQL Server vs. Oracle

SQL Server

Oracle

CREATE TABLE customer
(
id INT NOT NULL ,
NAME VARCHAR(30) NULL ,
GENDER CHAR(1) NULL ,
BIRTH_DT DATE NULL ,
LASTNAME VARCHAR(30) NULL ,
CITY VARCHAR(30) DEFAULT 'NY'
CONSTRAINT PK_PERSON PRIMARY KEY (id)
)
GO

In this example, the primary key is the id which does not accept nulls, the other columns accept null values. By default the city is NY.

For more information about creating tables and about SQL Server data types, refer to these links:

CREATE TABLE customer
(
id INT NOT NULL ,
NAME VARCHAR(30) NULL ,
GENDER CHAR(1) NULL ,
BIRTH_DATE DATE NULL ,
LASTNAME VARCHAR2(30) NULL ,
CITY VARCHAR2(30) DEFAULT 'NY'
CONSTRAINT PK_PERSON PRIMARY KEY (id)
)
/

As you can see, Oracle and SQL Server are very similar, but in some cases the data types can differ.

For more information about creating tables and about Oracle data types, refer to these links:

 

Insert data in a table in SQL Server vs. Oracle

SQL Server

Oracle

insert into dbo.customer
values (5,'John','m','10-21-1980',NULL,default)
go

Default is used to insert the value by default defined when creating a table. You can assign null values to columns if they accept null values.  Check out the SQL Server INSERT Tutorial.

insert into customer values(5,'John','m',to_date('10/21/1980','mm/dd/yyyy'), NULL,default);

The main difference between SQL Server and Oracle is that in Oracle you have to use the function to_date and specify the date format to insert the information.

 

Foreign keys in SQL Server vs. Oracle

SQL Server

Oracle

CREATE TABLE products_sold
(
product_id numeric(10) not null,
customer_id INT not null,
CONSTRAINT fk_pr
FOREIGN KEY (customer_id)
REFERENCES customer(id) )
GO

The syntax for foreign keys is the same in Oracle and SQL Server.  Learn more about Foreign Keys.

CREATE TABLE products_sold
(
product_id numeric(10) not null,
customer_id INT not null,
CONSTRAINT fk_pr
FOREIGN KEY (customer_id)
REFERENCES customer(id) );

 

Autonumeric values in SQL Server vs. Oracle

SQL Server

Oracle

An identity is used to creates auto-generated values for rows. In this case (IDENTITY(1,1)) the value starts in 1 and the incremental value is 1.

CREATE TABLE CUSTOMER2
(id_num int IDENTITY(1,1),
NAME VARCHAR(30) NULL ,
GENDER CHAR(1) NULL ,
BIRTH_DT DATE NULL ,
LASTNAME VARCHAR(30) NULL ,
CITY VARCHAR(30) DEFAULT 'NY'
CONSTRAINT PK_PERSON PRIMARY KEY (id)
)
GO

You can also use sequences in SQL Server like in Oracle. For more information, check out this resource on  sequence numbers.

In Oracle there was not an auto increment option like in SQL Server until Oracle 12c.

You can use sequences that are similar than in SQL Server (they existed before than the SQL Server sequences). The following example shows a sequence from 1 to 1000.

CREATE SEQUENCE myseq
MINVALUE 1
MAXVALUE 1000
START WITH 1
INCREMENT BY 1
CACHE 20;

In order to insert a sequence use this example:

insert into customer values(myseq.nextval)

In Oracle 12c, you can use the identity option:

CREATE TABLE product (id NUMBER GENERATED ALWAYS AS IDENTITY, prod_name VARCHAR2(30) );

 

Current date in SQL Server vs. Oracle

SQL Server

Oracle

select CONVERT (date, GETDATE())
go

Learn more about SQL Server Dates.

SELECT TO_CHAR (SYSDATE, 'MM-DD-YY') "NOW" FROM DUAL;

 

Modify the date format in SQL Server vs. Oracle

SQL Server

Oracle

select FORMAT (getdate(), 'MM-dd-yyyy') as date
GO

For more information, refer to this link.

SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY') "DATE" FROM DUAL;

For more information, refer to this link:

http://docs.oracle.com/cd/B19306_01/server.102/

b14200/functions172.htm

 

Variance of a value in SQL Server vs. Oracle

SQL Server

Oracle

select var(id) from customer
go

select variance(id) from customer;

or

select var(id) from customer;

 

Find the position within a string in SQL Server vs. Oracle

SQL Server

Oracle

SELECT CHARINDEX( 'Gates', 'Bill Gates', 1);

This example shows the position of the word Gates in Bill Gates.

SELECT INSTR('Bill Gates', 'Gates', 1) FROM dual;

In Oracle you also have INSTR2 (UCS2 code points), INSTR4 (UCS2 code points), INTRB (bytes), INSTRC (UNICODE) functions.

 

Generate random numbers in SQL Server vs. Oracle

SQL Server

Oracle

SELECT ROUND(RAND()*6,0)
GO

select trunc(dbms_random.value(1,6)) from dual;

Assign a value to a variable and show the results in SQL Server vs. Oracle

SQL Server

Oracle

declare @var int=1
select @var
go

VARIABLE var NUMBER BEGIN :varl:=1; END; / PRINT var;

 

Tables in the current database in SQL Server vs. Oracle

SQL Server

Oracle

SELECT table_name FROM information_schema.tables
go

select table_name from user_tables;

Views in the current database in SQL Server vs. Oracle

SQL Server

Oracle

SELECT * FROM information_schema.views
go

SELECT View_name from user_views;

 

Next Steps
For more information, refer to the following links:

Last Update:


next webcast button


next tip button



About the author
MSSQLTips author Daniel Calbimonte Daniel Calbimonte is a Microsoft SQL Server MVP, Microsoft Certified Trainer and Microsoft Certified IT Professional.

View all my tips
Related Resources





Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Monday, August 24, 2015 - 3:45:04 PM - Marcelo Back To Top

"The main difference between SQL Server and Oracle is that in Oracle you have to use the function to_date and specify the date format to insert the information"

It is not entirely true. While it is recommended, if we know the exact format we can insert without using to_date function.


Wednesday, August 19, 2015 - 1:36:39 PM - Daniel Back To Top

Thanks. The new articles will come soon.


Wednesday, August 19, 2015 - 4:40:02 AM - Saša Stefanović Back To Top

This is absolutely great, thanks Daniel! As a DBA/DB developer who works with both Oracle and SQL Server, i always wanted some reference like this one.

But ... where are the links for part 2, part 3, and so on? :)


Tuesday, June 23, 2015 - 11:54:39 AM - Daniel Back To Top

Thanks for the comments. We can insert data using the to_date function or without it. About the pre DB12c, I think I will add some notes and updates to the article. There are many companies still using the pre DB12c and the article is for them.  


Tuesday, June 23, 2015 - 11:20:49 AM - Kellyn Gorman Back To Top

The Oracle terminology and syntax should be noted is pre-DB12c multi-tenant.  The new version of Oracle uses a container database, (think system db) and then pluggables, (think user databases).  The terminilogy and switches required are now very similar to what you are trying to compare here and should have been used as the best comparison, as 11g, which is the version you are showing here, is soon to be desupported.

Sincerely,

Kellyn Pot'Vin-Gorman

DBAKevlar


Monday, June 22, 2015 - 7:40:37 PM - John Back To Top

I have never used to_date when inserting into Oracle. The following line:

insert into customer values(5,'John','m',to_date('10/21/1980','mm/dd/yyyy'), NULL,default);

Should work fine as:

insert into customer values(5,'John','m','21-oct-1980', NULL,default);


Monday, June 22, 2015 - 4:24:21 AM - Prashant Sharma Back To Top

This is a great article. Thanks for that.

People who work in either of Oracle or SQL Server and want to work in the other database will find this very helpful.

Since the article gives a comparative study, its very easy to correlate the nuances of each database system.


Friday, June 19, 2015 - 3:28:21 PM - Kris Maly Back To Top

Awesome article having important informations links.

I think some articles are needed  in comparision of SQL Svr and Oracle

 

Thanks for educating the community and appreciate your volunteership


Learn more about SQL Server tools