How to convert interval expressions from Oracle to SQL Server

Oracle uses specific interval expressions to store a period of time. SQL Server does not have a direct analog for this data type. Thus, when you try to convert interval expressions using SQL Server Migration Assistant (SSMA) for Oracle, you will get an error O2SS0006.

Your source Oracle code may include 2 kinds of intervals: INTERVAL YEAR TO MONTH and INTERVAL DAY TO SECOND. The first one stores the difference in years and months, while the second type stores the difference in days, hours, minutes and seconds. SSMA does not provide type mapping for Oracle’s interval expressions.

Let us try to convert the source Oracle code that includes both of these interval expressions. SSMA will generate 2 following 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 see how you can convert Oracle’s interval expressions to SQL Server.

Examine the Problem

Consider the following example of the Oracle’s source code.

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

You can see that SSMA gives us a hint: “Add a mapping and then convert again”. But you cannot add a mapping for the interval data type in SSMA.

Possible Remedies

In order to resolve this issue, you should change the data type for [INT_DTS] column from varchar (8000) to [INT_DTS] varchar (100). SSMA converts all unsupported types to varchar (8000), but in that particular case, you do not need to define that huge column size.

So, the updated SQL Server code will look as follows:

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

Data Migration

Now you can use SSMA for data migration. Although trying to migrate data from Oracle to SQL Server, you will receive the following error:

You need to configure migration of the interval data type to varchar in order to avoid data loss. To do that, you should set the ‘Show’ value for the ‘Extended data migration option’ option in the ‘Project Settings’.

Then choose ‘Data Migration’ tab, check ‘Use custom select’ and alter the SELECT statement for data migration with the following code:

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;

After applying changes, you can successfully complete the data migration.

The “INT_YTM” column in SQL Server will store values like this ‘INTERVAL’+001-02’YEAR(3)TO MONTH’. After that you have to develop a solution for varchar value parsing that will emulate Oracle’s behavior for interval data type.

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 interval expressions

Examine the Problem

Possible Remedies

Data Migration

Related posts

Related posts

Getting started