Oracle to SQL Server migration with SSMA: Converting interval expressions

28 Sep 2021 Andrey Khudyakov, Roman Muzyka

Oracle databases have many specific functions and features. For example, it stores a period of time with specific interval expressions. This may cause issues during the database migration from Oracle to SQL Server. When you use SQL Server Migration Assistant (SSMA) for converting Oracle with interval expressions, you will observe the O2SS0006 error message. This may happen because that SSMA does not support type mapping for such expressions.

There are intervals of 2 kinds in the source Oracle code: INTERVAL DAY TO SECOND and INTERVAL YEAR TO MONTH. The first kind stores the difference in seconds, minutes, hours, and days. Meanwhile, the second type stores the difference in years and months.

If you try to convert source Oracle code with interval expressions of both kinds, you will get 2 SSMA error messages:

«O2SS0006: Type ‘INTERVAL DAY(5) TO SECOND(3)’ was not converted because there is no mapping for it. Add a mapping and then convert again».
«O2SS0006: Type ‘INTERVAL YEAR(3) TO MONTH’ was not converted because there is no mapping for it. Add a mapping and then convert again».

Let’s find out why this problem occurs and how you can solve it.

The essence of the problem

Suppose, the Oracle source code looks as follows:

1
2
3
4
5
6
7
8
9
10

CREATE TABLE TEST_FUNCTIONS.T_INTERVAL_DAY_TO_SECOND
(
ID NUMBER(38, 0),
INT_DTS INTERVAL DAY(5) TO SECOND(3)
);
CREATE TABLE TEST_FUNCTIONS.T_INTERVAL_YEAR_TO_MONTH
(
ID NUMBER(38, 0),
INT_YTM INTERVAL YEAR(3) TO MONTH
);

SSMA will generate the following converted code:

1
2
3
4
5
6
7
8
9

CREATE TABLE dbo].[T_INTERVAL_YEAR_TO_MONTH]
( [ID] NUMERIC(38, 0) NULL,

/* SSMA warning messages:
* O2SS0006: Type 'INTERVAL YEAR(3) TO MONTH' was not converted because
* there is no mapping. Add a mapping and then convert again. */

[INT_YTM] VARCHAR(8000) NULL)
GO

In this case, SSMA will suggest how to solve the problem: “Add a mapping and then convert again”. Unfortunately, SSMA does not provide an option to add a mapping for the interval expression data type.

Possible solutions

The first step in solving this problem is to change the varchar (8000) data type for the [INT_DTS] column to the varchar (100) data type. The point is that SSMA converts all unsupported data types to varchar (8000). Meanwhile, such a large column size is not required in your case.

Here is how the updated SQL Server cove should look like:

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

CREATE TABLE dbo].[T_INTERVAL_DAY_TO_SECOND]
(
[ID] NUMERIC(38, 0) NULL,
[INT_DTS] VARCHAR(100) NULL
)
GO
CREATE TABLE [dbo].[T_INTERVAL_YEAR_TO_MONTH]
(
[ID] NUMERIC(38, 0) NULL,
[INT_YTM] VARCHAR(100) NULL
)
GO

The next step is using SSMA for data migration. Unfortunately, you will get an error message while migrating your data from Oracle to SQL Server at this point.

How can you avoid this problem? Configure the migration of the interval data type to varchar. This will enable you to avoid data loss. At this point, set the ‘Show’ value for the ‘Extended data migration option’ in the ‘Project Settings/General/Migration.’

The next step requires you to choose the ‘Data Migration‘ tab, check the ‘Use custom select’ option, and alter the SELECT data migration statement with the code that looks as follows:

1 2	SELECT CAST("ID" AS VARCHAR2(100)) AS "ID", CAST ("INT_YTM" AS VARCHAR2(100)) AS "INT_YTM" FROM TEST_FUNCTIONS.T_INTERVAL_YEAR_TO_MONTH t;

Once you have applied these changes, you may successfully complete the database migration.

The values like ‘INTERVAL’+001-02’YEAR(3)TO MONTH’ will be stored in the “INT_YTM” SQL Server column. A remaining step is to develop a solution for varchar value parsing. It should emulate Oracle’s behavior for the interval expression data type.

Final thoughts

Do you have more questions on potential SSMA issues pertaining to Oracle to SQL Server migration? Please check ourJumpstart for the SSMA offer article or contact ABCloudz to get assistance with the database migration.

Also, please review our table dedicated to the most common SSMA errors in Oracle to SQL Server migration for relevant insights.