Tags: AWS Schema Conversion Tool, database conversion errors
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.”
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; |
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.
