Comparing Oracle and SQL Server Code - Part 1
By: Daniel Calbimonte | Comments (8) | Related: 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] ALTER DATABASE [db2] SET COMPATIBILITY_LEVEL = 120 ALTER DATABASE [db2] SET RECOVERY FULL ALTER DATABASE [DB2] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF ) 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: |
Change Databases in SQL Server vs. Oracle
SQL Server |
Oracle |
---|---|
USE DB2 |
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 |
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 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 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 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 The syntax for foreign keys is the same in Oracle and SQL Server.
Learn more about
Foreign Keys. |
CREATE TABLE products_sold
|
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 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 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()) 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 For more information, refer to this link. |
SELECT TO_CHAR (SYSDATE, 'MM-DD-YYYY') "DATE" FROM DUAL; For more information, refer to this link: |
Variance of a value in SQL Server vs. Oracle
SQL Server |
Oracle |
---|---|
select var(id) from customer |
select variance(id) from customer; or |
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) |
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 |
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 |
select table_name from user_tables; |
Views in the current database in SQL Server vs. Oracle
SQL Server |
Oracle |
---|---|
SELECT * FROM information_schema.views |
SELECT View_name from user_views; |
Next Steps
For more information, refer to the following links:- http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/toc.htm
- https://msdn.microsoft.com/en-us/bb510741
About the author

View all my tips