Tags: AWS Schema Conversion Tool, database conversion errors
Oracle’s NumToDSInterval function converts the provided number value of specified time units or expression to an interval day to second literal. Usually, we use the interval values when dealing with dates.
PostgreSQL provides some support for intervals but does not support storing values of such kind in variables. Hence, there are no functions in PostgreSQL able to return interval values.We commonly use the functions working with date intervals in reporting and other type of queries.
So, when you try to convert the code with the NumToDSInterval function call, SCT will generate the following message “5340 — Severity CRITICAL — PostgreSQL doesn’t support the STANDARD.NUMTODSINTERVAL(NUMBER,VARCHAR2) function. Use suitable function or create user-defined function.”
Thus, SCT provides some recommendations on how you may solve this critical issue. Below you will find some proven solutions from our team.
Possible Remedies
Consider the following example:
1 2 3 4 5 6 7 8 9 | PROCEDURE P_NUMTODSINTERVAL_001 AS v_date DATE; BEGIN SELECT opendate + NumToDsInterval(1, 'DAY') INTO v_date FROM account WHERE id = 999; END; |
- Use the Interval Literal syntax. Fortunately, PostgreSQL supports interval literal syntax. So, we will need to modify the source query code to use Oracle’s Interval Literal syntax.
1 2 3 4 5 6 7 8 9 | PROCEDURE P_NUMTODSINTERVAL_001 AS v_date DATE; BEGIN SELECT opendate + INTERVAL '1' DAY INTO v_date FROM account WHERE id = 999; END; |
SCT will successfully convert this code to the following PostgreSQL code:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | CREATE OR REPLACE FUNCTION sct_demo.p_numtodsinterval_001() RETURNS void AS $BODY$ DECLARE v_date TIMESTAMP(0) WITHOUT TIME ZONE; BEGIN SELECT opendate + '1 day'::INTERVAL INTO STRICT v_date FROM sct_demo.account WHERE id = 999; END; $BODY$ LANGUAGE  plpgsql; |
- Use the Date Arithmetic expression. Oracle allows us to modify date values by a number value so that they are treated as a day interval expression.
1 2 3 4 5 6 7 8 9 | CREATE PROCEDURE P_NUMTODSINTERVAL_001 AS v_date DATE; BEGIN SELECT opendate + 1 INTO v_date FROM account WHERE id = 999; END; |
SCT will also successfully convert this procedure to the similar PostgreSQL code, as in the first case:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 | CREATE OR REPLACE FUNCTION sct_demo.p_numtodsinterval_001() RETURNS void AS $BODY$ DECLARE v_date TIMESTAMP(0) WITHOUT TIME ZONE; BEGIN SELECT opendate + (1::NUMERIC || ' days')::INTERVAL INTO STRICT v_date FROM sct_demo.account WHERE id = 999; END; $BODY$ LANGUAGE  plpgsql; |
References
Do you have other questions or concerns about AWS SCT or Oracle to PostgreSQL migration issues? 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.
