call us toll-free +1 855 855 3600
 
Home > Blog > Oracle to MySQL migration with AWS SCT: How to handle unsupported NumToYmInterval function

Oracle to MySQL migration with AWS SCT: How to handle unsupported NumToYmInterval function

Posted by | On | In Amazon Web Services, AWS Database Migration Service, Database Migration, MySQL, Oracle Database | Tags:
 

Oracle’s NumToYmInterval function converts the provided number value of specified time units or expression to an interval year to month literal. Usually, we use the interval values when dealing with dates.

MySQL provides a certain level of support for intervals but does not support storing values of this type in variables. Hence, MySQL does not have any functions that are able to return interval values. The functions working with date intervals are commonly used by reporting and other type of queries.

So, when you try to convert code with the NumToYmInterval function call, SCT will generate the following message “340 — Severity CRITICAL — MySQL doesn’t support the STANDARD.NUMTOYMINTERVAL(NUMBER,VARCHAR2) function. Create a user defined function.”

AWS SCT unable to convert functions

Thus, SCT provides a basic recommendation on how you may solve this critical issue. Below you will find the proven solution from our team.

Possible Remedies

Consider the following example:

1
2
3
4
5
6
7
8
9
CREATE PROCEDURE P_NUMTOYMINTERVAL_001
AS
v_date DATE;
BEGIN
SELECT opendate + NumToYmInterval(1, 'YEAR')
INTO v_date
FROM account
WHERE id = 998;
END;

However, MySQL supports the interval literal syntax. So, we will need to modify the source query code to use the Oracle’s Interval Literal syntax.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
PROCEDURE P_NUMTOYMINTERVAL_001
AS
v_date DATE;
BEGIN
SELECT opendate + INTERVAL '1' YEAR
INTO v_date
FROM account
WHERE id = 998;
END;
[/SQL]

SCT will successfully CONVERT this code TO the following MySQL code:

[SQL]CREATE PROCEDURE SCT_DEMO.P_NUMTOYMINTERVAL_001()
BEGIN
DECLARE var_v_date DATETIME;
SELECT
OPENDATE + INTERVAL '1' YEAR
INTO var_v_date
FROM ACCOUNT
WHERE ID = 998;
END;

AWS SCT converts code from Oracle to MySQL

References

Make sure to check out our new AWS Schema Conversion Tool Jumpstart offer to get you up and running fast for your database migration projects.

Related posts

Be sure to check the regularly updated table of contents for our series of blog posts on typical AWS SCT conversion issues.

 
       
 

Leave a Reply

  • CAPTCHA Image
    Play CAPTCHA Audio
    Reload Image