How to convert database links from Oracle to SQL Server

Oracle uses database links to access objects on another database. In SQL Server you can use linked server or an OPENQUERY function to emulate that functionality. The problem is that SQL Server Migration Assistant (SSMA) for Oracle doesn’t automatically convert database links. Instead, when you try to convert a query that refers to the remote objects through the database link, SSMA will generate an error O2SS0083.

Source Code Example

Consider the following code example that includes the database link to the remote Oracle server on the local Oracle server.

1
2
3

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

You can execute the following query on the local Oracle server.

1
2
3
4
5
6

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;

But when you try to convert this query in SSMA, you will get the following error message: «O2SS0083: Unresolved identifier» as shown on the picture above.

Automatically Converted Code

When SSMA tries to convert our source query, it generates the following SQL Server code.

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.
* O2SS0083: Identifier JB.JOB_NAME cannot be converted because it was not resolved.
* O2SS0083: Identifier TEST_ORACLE_MSSQL.TEST_FUNC_EMP_CONSTR cannot be converted
* because it was not resolved.
* O2SS0083: Identifier TEST_ORACLE_MSSQL.TEST_FUNC_DEPT_CONSTR cannot be converted
* because it was not resolved.
* 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 TEST_ORACLE_MSSQL.TEST_FUNC_JOB_CONSTR cannot be converted
* because it was not resolved.
* O2SS0083: Identifier JB.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, 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 TEST_ORACLE_MSSQL.TEST_FUNC_JOB_CONSTR AS JB
ON JB.JOB_ID = EMP.JOB_ID
*/

Possible Remedies

In order to resolve this issue, you can create the linked server on the SQL Server or use the OPENQUERY function. Let’s take a look at these approaches.

Using Linked Server

If you decide to create a linked server, consider using the same name for it as Oracle’s database link.

1
2
3
4

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='########'

Then you have to rewrite the query using SQL Server linked server syntax. So, the updated SQL Server code will look as follows:

1
2
3
4
5
6

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

Using OPENQUERY Function

An even faster approach relates to using the OPENQUERY function instead of linked server. In this case you don’t need to migrate the query from Oracle to SQL Server. All you need to do is to remove the database links @TEST_DB_LINK from Oracle query and define SQL Server linked server that refers to the Oracle remote database.

So the updated SQL Server code with OPENQUERY function will look as follows:

1
2
3
4
5

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')
AS OpenQuery1;

Another Example

Consider another example, when we have two tables that refer to the remote Oracle server and one table on local Oracle server.

1
2
3
4
5

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;

SSMA converts local Oracle table to SQL Server, but doesn’t convert remote tables.

Converted code

The automatically generated converted code will look as follows.

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

/*
* 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.
* O2SS0083: Identifier TEST_ORACLE_MSSQL.TEST_FUNC_EMP_CONSTR cannot be converted
* because it was not resolved.
* O2SS0083: Identifier TEST_ORACLE_MSSQL.TEST_FUNC_DEPT_CONSTR cannot be converted
* because it was not resolved.
* 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
*/

Solution

In that case you have to get access to the remote Oracle tables via OPENQUERY. And then you have to join SQL Server tables. The updated SQL Server code will look as follows:

1
2
3
4
5
6
7

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

Do you have other questions or concerns about SSMA or Oracle to SQL Server migration issues? Make sure to check out our new Jumpstart for SSMA offer!

Be sure to check the regularly updated table of contents for our series of blog posts on typical SSMA conversion errors.

Source of technical information: Oksana Eremenko, Technical Lead at DB Best.

Blog: Oracle to SQL Server migration with SSMA: How to convert database links