Oracle to SQL Server migration with SSMA: Converting database links

08 Oct 2021

Andrey Khudyakov, Roman Muzyka

In Oracle, database links are important features that enable you to access objects on another database. SQL Server has a direct alternative to this function. It provides a linked server or an OPENQUERY feature that allows you to emulate the database links’ functionality. Unfortunately, SQL Server Migration Assistant (SSMA) fails to convert these links automatically.

When you try converting a database link query that refers to remote objects, you get the following error: “O2SS0563: Conversion of database link … is not supported”.

Let’s analyze this problem in more detail and determine how you can solve it.

The essence of the problem

Let’s use the following example to analyze the problem. Here is a part of the source code from the local Oracle server that contains the database link to the remote Oracle server.

CREATE PUBLIC DATABASE LINK TEST_DB_LINK
CONNECT TO SYSTEM IDENTIFIED BY system
USING '192.777.77.777/orcl';

You might execute the given query on the local Oracle server.

SELECT EMP.FIRST_NAME,
EMP.LAST_NAME,
DEPT.DEPT_NAME,
JOB.JOB_NAME
FROM TEST_ORACLE_MSSQL.TEST_FUNC_EMP_CONSTR@TEST_DB_LINK EMP
LEFT JOIN TEST_ORACLE_MSSQL.TEST_FUNC_DEPT_CONSTR@TEST_DB_LINK DEPT
ON DEPT.DEPT_ID = EMP.DEPT_ID
LEFT JOIN TEST_ORACLE_MSSQL.TEST_FUNC_JOB_CONSTR@TEST_DB_LINK JOB
ON JOB.JOB_ID = EMP.JOB_ID;

However, when you use SSMA to convert this query, you receive the following error messages:

“O2SS0083: Identifier … cannot be converted because it was not resolved”.

“O2SS0563: Conversion of database link … is not supported”.

In particular, when SSMA tries to convert the source query, the following code appears:

/*

* SSMA error messages:

* O2SS0083: Identifier EMP.FIRST_NAME cannot be converted because it was not resolved.

* O2SS0083: Identifier EMP.LAST_NAME cannot be converted because it was not resolved.

* O2SS0083: Identifier DEPT.DEPT_NAME cannot be converted because it was not resolved.

* O2SS0478: The following statement cannot be converted because it includes user defined type attribute: JOB.JOB_NAME

* O2SS0563: Conversion of database link 'TEST_ORACLE_MSSQL.TEST_FUNC_EMP_CONSTR@TEST_DB_LINK' is not supported.

* O2SS0563: Conversion of database link 'TEST_ORACLE_MSSQL.TEST_FUNC_DEPT_CONSTR@TEST_DB_LINK' is not supported.

* O2SS0083: Identifier DEPT.DEPT_ID cannot be converted because it was not resolved.

* O2SS0083: Identifier EMP.DEPT_ID cannot be converted because it was not resolved.

* O2SS0563: Conversion of database link 'TEST_ORACLE_MSSQL.TEST_FUNC_JOB_CONSTR@TEST_DB_LINK' is not supported.

* O2SS0083: Identifier JOB.JOB_ID cannot be converted because it was not resolved.

* O2SS0083: Identifier EMP.JOB_ID cannot be converted because it was not resolved.

SELECT EMP.FIRST_NAME, EMP.LAST_NAME, DEPT.DEPT_NAME, JOB.JOB_NAME

FROM

TEST_ORACLE_MSSQL.TEST_FUNC_EMP_CONSTR AS EMP

LEFT JOIN TEST_ORACLE_MSSQL.TEST_FUNC_DEPT_CONSTR AS DEPT

ON DEPT.DEPT_ID = EMP.DEPT_ID

LEFT JOIN TEST_ORACLE_MSSQL.TEST_FUNC_JOB_CONSTR AS JOB

ON JOB.JOB_ID = EMP.JOB_ID

*/

How to approach the problem

There are numerous possible solutions to the given problem. Specifically, you can use the OPENQUERY function or create the linked server on the SQL Server. Let’s examine these approaches in detail.

The OPENQUERY function

It is a faster approach among in comparison to the one that involves the Linked server. In this case, you should remove the database links @TEST_DB_LINK from the Oracle query. After that, define SQL Server linked server referring to the remote Oracle database. As a result, you will receive the following SQL Server code:

1
2
3
4
5
6
7

SELECT OpenQuery1.* FROM OPENQUERY (TEST_DB_LINK, 'SELECT EMP.FIRST_NAME, EMP.LAST_NAME,

DEPT.DEPT_NAME, JOB.JOB_NAME FROM TEST_ORACLE_MSSQL.TEST_FUNC_EMP_CONSTR EMP

LEFT JOIN TEST_ORACLE_MSSQL.TEST_FUNC_DEPT_CONSTR DEPT ON DEPT.DEPT_ID = EMP.DEPT_ID

LEFT JOIN TEST_ORACLE_MSSQL.TEST_FUNC_JOB_CONSTR JOB ON JOB.JOB_ID = EMP.JOB_ID')

Linked server

If you create a linked server, make sure that it has the same name as the Oracle database link. An updated SQL Server code with OPENQUERY function will look as follows:

1
2
3
4
5
6
7

EXEC master.dbo.sp_addlinkedserver @server = N'TEST_DB_LINK', @srvproduct=N'Oracle',

@provider=N'OraOLEDB.Oracle', @datasrc=N'TEST_DB'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TEST_DB_LINK',@useself=N'False',

@locallogin=NULL,@rmtuser=N'system',@rmtpassword='########'

The next step is to rewrite the query with SQL Server linked server syntax. Hence, you will receive SQL Server code that looks at follows:

1
2
3
4
5
6
7
8
9
10
11

SELECT EMP.FIRST_NAME, EMP.LAST_NAME, DEPT.DEPT_NAME, JB.JOB_NAME

FROM [TEST_DB_LINK]..TEST_ORACLE_MSSQL.TEST_FUNC_EMP_CONSTR AS EMP

LEFT JOIN [TEST_DB_LINK]..TEST_ORACLE_MSSQL.TEST_FUNC_DEPT_CONSTR AS DEPT

ON dbo.DEPT.DEPT_ID = dbo.EMP.DEPT_ID

LEFT JOIN [TEST_DB_LINK]..TEST_ORACLE_MSSQL.TEST_FUNC_JOB_CONSTR AS JB

ON JB.JOB_ID = EMP.JOB_ID

Example: remote and local server

You may also face a scenario in which two tables refer to the remote Oracle server and one table refers to the local Oracle server.

1
2
3
4
5
6
7
8
9

SELECT EMP.FIRST_NAME, EMP.LAST_NAME, DEPT.DEPT_NAME, JB.JOB_NAME

FROM TEST_ORACLE_MSSQL.TEST_FUNC_EMP_CONSTR@TEST_DB_LINK EMP

LEFT JOIN TEST_ORACLE_MSSQL.TEST_FUNC_DEPT_CONSTR@TEST_DB_LINK DEPT

ON DEPT.DEPT_ID = EMP.DEPT_ID

LEFT JOIN TEST_ORACLE_MSSQL.TEST_FUNC_JOB_CONSTR JB ON JB.JOB_ID = EMP.JOB_ID;

In this case, SSMA successfully converts the local Oracle table but fails to convert the remote Oracle table.

The automatically converted by SSMA code looks as follows:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25

SSMA error messages:
O2SS0083: Identifier EMP.FIRST_NAME cannot be converted because it was NOT resolved.

O2SS0083: Identifier EMP.LAST_NAME cannot be converted because it was NOT resolved.

O2SS0083: Identifier DEPT.DEPT_NAME cannot be converted because it was NOT resolved.

O2SS0563: Conversion OF DATABASE link 'TEST_ORACLE_MSSQL.TEST_FUNC_EMP_CONSTR@TEST_DB_LINK' IS NOT supported.

O2SS0563: Conversion OF DATABASE link 'TEST_ORACLE_MSSQL.TEST_FUNC_DEPT_CONSTR@TEST_DB_LINK' IS NOT supported.

O2SS0083: Identifier DEPT.DEPT_ID cannot be converted because it was NOT resolved.

O2SS0083: Identifier EMP.DEPT_ID cannot be converted because it was NOT resolved.

O2SS0083: Identifier EMP.JOB_ID cannot be converted because it was NOT resolved.

SELECT EMP.FIRST_NAME, EMP.LAST_NAME, DEPT.DEPT_NAME, JB.JOB_NAME
FROM
TEST_ORACLE_MSSQL.TEST_FUNC_EMP_CONSTR AS EMP
LEFT JOIN TEST_ORACLE_MSSQL.TEST_FUNC_DEPT_CONSTR AS DEPT
ON DEPT.DEPT_ID = EMP.DEPT_ID
LEFT JOIN dbo.TEST_FUNC_JOB_CONSTR AS JB
ON JB.JOB_ID = EMP.JOB_ID
*/

One more time, OPENQUERY will come in handy. You can use it to get access to remote Oracle tables. After that, join them with SQL Server tables. As a result, you will get the following SQL Server code:

1
2
3
4
5
6
7
8
9
10
11
12

SELECT
OpenQuery1.FIRST_NAME, OpenQuery1.LAST_NAME, OpenQuery1.DEPT_NAME, JB.JOB_NAME

FROM OPENQUERY (TEST_DB_LINK, 'SELECT EMP.FIRST_NAME, EMP.LAST_NAME, DEPT.DEPT_NAME

FROM TEST_ORACLE_MSSQL.TEST_FUNC_EMP_CONSTR EMP

LEFT JOIN TEST_ORACLE_MSSQL.TEST_FUNC_DEPT_CONSTR DEPT ON DEPT.DEPT_ID = EMP.DEPT_ID')

AS OpenQuery1

LEFT JOIN dbo.TEST_FUNC_JOB_CONSTR AS JB ON JB.JOB_ID = OpenQuery1.JOB_ID

Final thoughts

Do you have other questions regarding how to solve problems with SSMA conversion of Oracle database code to SQL Server? Check our Jumpstart for the SSMA offer and contact ABCloudz to find out how we can facilitate your database migration.

Also, please review our Blog section for the blogpost series on common SSMA Oracle to SQL Server migration issues.