call us toll-free +1 855 855 3600
 
Home > Blog > Oracle to SQL Server migration with SSMA: How to convert interval expressions

Oracle to SQL Server migration with SSMA: How to convert interval expressions

Posted by | On | In Database Migration, Microsoft SQL Server, Microsoft SQL Server Migration Assistant (SSMA), Operational Data Management, Oracle Database | Tags:
 

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».

SSMA for Oracle convertion results

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.

type mapping with 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:

Oralce data migration 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’.

extended data migration options

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.

migrate Oracle to SQL SErver with SSMA

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.

Related posts

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.

 

 
       
 

Comments are closed.