SQL Server to Oracle: 9 Easy Steps

Microsoft SQL Server is a relational database management system. The purpose of the system is to manage and store information. Various business intelligence, analytics, and transaction processing operations are supported by the system.

Oracle is a computer technology company known for its Java-based software and services. Oracle sells cloud-engineering services and systems as well as database management systems through its four main business segments: cloud and license, hardware, and services.

This article discusses the different steps to link SQL Server to Oracle database extensively. In addition to that, it also describes SQL Server and Oracle briefly.

What is Microsoft SQL Server?

Developed in 1989, SQL Server is a Relational Database Management System (RDBMS) developed by Microsoft. SQL Server is based on SQL, a widely-used programming language for working with relational databases. SQL Server has been available on Linux since 2016, but it has previously only been available on Windows operating systems.

Key Features of  Microsoft SQL Server

• Analytics Support: Organizations seek insights into stored and managed data to make better decisions. With Microsoft SQL, you can analyze your stored data using integration with Microsoft SQL Server Analysis (SSAS) which is an online analytical processing tool.
• Multiple Integrations: SQL enables users to perform various tasks like Data Extraction, Transformation, Data Migration, Data Loading, and more on their stored data using Microsoft SQL Server Integration Services (SSIS).

What is Oracle?

Oracle is a Relational Database System(RDBMS) developed by Oracle Corporation on 16 June 1977. Oracle is the most famous among all the relational databases, sometimes also called Oracle DB. Oracle is the RDBMS that implements object-oriented features like user-defined types, inheritance, and polymorphism, so it is also called an Object-Relational Database Management System (ORDBMS). SQL queries are used to access data from Oracle.

Key Features of Oracle

• Reliability: The main reason for choosing Oracle is its reliability. OracleDB provides the most secured and private database services to its clients.
• Availability: The OracleDB is never offline or out of service. It offers and maintains the 24*7 availability of the database.
• Scalability and Performance: Oracle has features like Real Application Clustering and Portability which make it highly scalable.
• Portability: The Oracle database can be ported over 100 different hardware platforms and around 20 networking protocols.

Benefits of connecting SQL Server to Oracle

A few Benefits of connecting SQL Server to Oracle are:

• You get access to data stored in Oracle right from the SQL server without the need for coding. 
• You can perform different actions on data stored in Oracle from SQL Server, such as distributed queries. 
• You can address data stored in Oracle the same way you would SQL Server.

Steps for Connecting SQL Server to Oracle 

• SQL Server to Oracle: Creating the mwrep User
• SQL Server to Oracle: Creating the Migration Repository
• SQL Server to Oracle: Capturing the Microsoft SQL Server Exported Files
• SQL Server to Oracle: Checking Conversion Preferences
• SQL Server to Oracle: Converting to the Oracle Model
• SQL Server to Oracle: Generating and Executing the Script to Create the Oracle Database Objects
• SQL Server to Oracle: Checking Offline Data Move Preferences
• SQL Server to Oracle: Analysis and Estimation
• SQL Server to Oracle: Migrating the Data
• SQL Server to Oracle: Testing and Deployment

SQL Server to Oracle: Creating the mwrep User

This is the first step to connecting SQL Server to Oracle. Perform the following actions to create a new database user:

It should be noted that you can skip these steps if you already have a system orcl connection and a mwrep user.

• Step 1: Use the desktop icon to launch Oracle SQL Developer.
• Step 2: Select the Connections option under View.

• Step 3: Right-click Connections in the Connections tab and choose New Connection. You’ll see a window asking you to choose a new database connection.

• Step 4: Fill in the correct details. In the Username and Password fields, type system and your password, respectively. By checking the Save Password box. In the Hostname field, type hostname>, and in the SID field, type orcl. Select Test.

• Step 5: Verify the connection’s status on the bottom left side (above the Help button). Success should be written there. Click Connect to keep the connection.

• Step 6: The connection has been saved, and the Connections tab shows it listed under Connections.

• Step 7: Extend the system orcl connection. It should be noted that a SQL Worksheet automatically opens when a connection is established.

• Step 8: Create a user for the migration repository by entering the code below in the SQL Worksheet.

CREATE USER MWREP 
IDENTIFIED BY mwrep 
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;
GRANT CONNECT, RESOURCE, CREATE SESSION, CREATE VIEW TO MWREP;

• Step 9: Use the “Run Script (F5)” icon to execute the script.

• Step 10: The mwrep user was successfully created.

SQL Server to Oracle: Creating the Migration Repository

You must set up a repository to house the necessary repository tables and PL/SQL packages before you can convert Microsoft SQL Server to Oracle. Follow these steps to achieve this:

You can skip these steps if you already have a migration repository and a mwrep orcl connection.

• Step 1: To establish a connection with the mwrep user, click on Connections in the Connections tab and choose New Connection. You’ll see a window asking you to choose a new database connection. Note that you must choose View > Connections to make this tab visible.

• Step 2: Put in the correct details in the connection box. Use mwrep for the Username and Password fields. In the Hostname field, type hostname>, and in the SID field, type orcl. Select Test.

• Step 3: Verify the connection’s status on the bottom left side (above the Help button). Success should be written there. Click Connect to keep the connection.

• Step 4: The connection has been saved, and the Connections tab shows it listed under Connections.

• Step 5: You can choose Migration Repository > Associate Migration Repository by right-clicking the mwrep orcl connection.

Next, Your new repository will be installed.

SQL Server to Oracle: Capturing the Microsoft SQL Server Exported Files

Follow these steps to import the scripts from the captured Microsoft SQL Server to Oracle SQL Developer:

• Step 1: In the Migration menu, choose Third-Party Database Offline Capture > Load Database Capture Script Output.

• Step 2: Sqlserver2005.ocp can be found by browsing the Capture directory.

• Step 3: The Captured Models tab displays SQLServer2005. Expand SQLServer2005.

• Step 4: To view the list of captured objects, expand dbo.

SQL Server to Oracle: Checking Conversion Preferences

Reviewing the conversion preferences at this time is crucial. Follow these steps to accomplish this:

• Step 1: To access Preferences, choose Tools.

• Step 2: Select Identifier Options after expanding Migration.

• Step 3: Verify that Is Quoted Identifier On is chosen. Select OK.

SQL Server to Oracle: Converting to the Oracle Model

Follow these steps to transform the captured model into the Oracle model:

• Step 1: Convert to Oracle Model can be chosen by right-clicking the captured model SQLServer2005.

• Step 2: The Source Data Type and what it will be converted to in the Oracle Model is displayed in the Set Data Map window, which appears. Select Apply.

• Step 5: Next, your Model will be converted. The converted objects can be seen by expanding dbo Northwind.

SQL Server to Oracle: Generating and Executing the Script to Create the Oracle Database Objects

Follow these steps to create the SQL script with DDL statements that will be used to create the objects in an Oracle Database:

• Step 1: Select Generate from the context menu by selecting Converted:SQLServer2005.

• Step 2: Oracle SQL is currently being created.

• Step 3: The script’s SQL is displayed. From the drop-down menu next to the right icon, choose system orcl.

Next, you can run the script by clicking F5.

• Step 4: You can now establish a connection for the dbo Northwind user after your scripts have completed running successfully. New Connection can be chosen by right-clicking Connection.

• Step 5: Fill in the correct details and dbo Northwind in the Username and Password fields. By checking the Save Password box. In the Hostname field, type hostname>, and in the SID field, type orcl. Select Test.

• Step 6: Verify the connection’s status on the bottom left side (above the Help button). Success should be written there. Click Connect to keep the connection.

• Next, you will see that your table is created, however the data is yet to be loaded.

SQL Server to Oracle: Checking Offline Data Move Preferences

For the Offline Data Move scripts, particularly the Oracle SQL*Loader control files, to reference the correct format, a date format mask can be specified in the preferences. Follow these steps to accomplish this:

• Step 1: To access Preferences, choose Tools.

• Step 2: Select Data Move Options after expanding Migration.

• Step 3: Check that the Date Mask format is the same as what is listed below.

• Step 4: Make sure the Timestamp Mask is the same as what is listed below.

Select OK.

SQL Server to Oracle: Analysis and Estimation

It may be simple to switch from Microsoft SQL Server to Oracle database. However, it is important to note that not all objects and syntax are migrated automatically. Consequently, manual intervention is needed.

The estimated time needed for manual tasks can be determined by analyzing the Capture Model and determining the quantity, kind, and complexity of objects.

You can use the reports below to estimate your tasks and projects.

• Migration Reports> Migration Summary
• Migration Reports> Migration Details
• Migration Reports> Automatic Name Changes

Time estimate for:

• Manually fix each object that couldn’t be converted.
• Verify and evaluate each item.
• Application modifications brought on by name changes

SQL Server to Oracle: Migrating the Data

Follow these steps to import the data using the provided scripts:

• Step 1: Run the following commands at a DOS command prompt:

<prompt> cd <location where files are>
<prompt> oracle_ctl

You unzipped the files, and they are now in the Data directory. A bat file called oracle ctl contains instructions for loading the data. The data is loaded using sqlldr.

• Step 2: The command is successfully carried out.

• Step 3: Use Oracle SQL Developer instead, then retry the connection.

• Step 4: The table’s data has been successfully loaded.

• Step 5: Click on the CATEGORIES table.

• Step 6: The first cell in the PICTURE column should be double-clicked and click …

• Step 7: Click OK after selecting the Image checkbox.

SQL Server to Oracle: Testing and Deployment

Depending on how complex the database to be migrated is, testing may account for a sizeable portion of the migration project. Oracle does not currently offer the tools required to automatically validate a migrated database. This stage needs to be planned for and taken into account before the migration begins.

Your project plan needs to include the following:

• Verify Database Structure
• Verify Database Security
• Verify Data
• Verify the Logical Correctness of Views
• Test Application

Conclusion

This article explains how to connect SQL Server to Oracle using a few steps. It also gives an overview of Oracle and SQL Server.

visit our website to explore hevo

Hevo Data, a No-code Data Pipeline provides you with a consistent and reliable solution to manage data transfer between a variety of sources and a wide variety of Desired Destinations, with a few clicks. Hevo Data with its strong integration with 150+ sources (including 50+ free sources) allows you to not only export data from your desired data sources & load it to the destination of your choice but also transform & enrich your data to make it analysis-ready so that you can focus on your key business needs and perform insightful analysis using BI tools.

Want to take Hevo for a spin? 

Sign Up for a 14-day free trial and experience the feature-rich Hevo suite firsthand. You can also have a look at the unbeatable pricing that will help you choose the right plan for your business needs.

Frequently Asked Questions