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

Oracle to PostgreSQL migration with AWS SCT: How to handle unsupported NumToDSInterval function

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

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

Miragrate Oracle to PostgreSQL with AWS SCT

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;
  1. 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;

AWS SCT Oracle procedure

  1. 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;

AWS SCT Amazon RDS for PostgreSQL function

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.

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