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

25 Apr 2022

Oleksii Savchenko

In this post we continue exploring the technical aspects of Oracle to PostgreSQL migration using AWS SCT. We were using AWS SCT build#660 to convert one of our Oracle test databases.

AWS SCT provides functionalities for the most efficient migration from Oracle to PostgreSQL. However, there is a common challenge with implementing the Oracle NumToDSInterval function in PostgreSQL.

The Oracle NumToDSInterval function is responsible for the conversion of the provided number value of specified time units or expressions to an interval day-to-second literal. Usually, such interval values are used with dates.

PostgreSQL provides some basic support for intervals but does not have a corresponding function. Such functions are typically used when it comes to date intervals in reporting and other types of queries.

Using NumToDSInterval directly

SCT converts NumToDSInterval function by using casting to INTERVAL type. Let us consider an example:

Oracle code:

1
2
3
4
5
6
7
8
9
10

CREATE OR REPLACE FUNCTION sct_demo.F_NUMTODSINTERVAL_001(p_date IN DATE)
RETURN DATE
AS
v_date DATE;
BEGIN
SELECT p_date + NumToDsInterval(1, 'DAY')
INTO v_date
FROM dual;
RETURN v_date;
END;

Converted PostgreSQL code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

CREATE OR REPLACE FUNCTION sct_demo.f_numtodsinterval_001(IN p_date TIMESTAMP WITHOUT TIME ZONE)
RETURNS TIMESTAMP WITHOUT TIME ZONE
AS
$BODY$
DECLARE
v_date TIMESTAMP(0) WITHOUT TIME ZONE;
BEGIN
SELECT
p_date + concat_ws(' ', (1)::TEXT, 'DAY')::INTERVAL
INTO STRICT v_date;
RETURN v_date;
END;
$BODY$
LANGUAGE plpgsql;

We can see that Oracle expression “p_date + NumToDsInterval(1, ‘DAY’)” has been converted to “p_date + concat_ws(‘ ‘, (1)::TEXT, ‘DAY’)::INTERVAL”

Two other options

Oracle also supports the Interval Literal syntax and the Date Arithmetic expression. Both these options can be correctly converted by SCT.

Interval Literal syntax

Oracle code:

1
2
3
4
5
6
7
8
9
10

CREATE OR REPLACE FUNCTION sct_demo.F_NUMTODSINTERVAL_002(p_date IN DATE)
RETURN DATE
AS
v_date DATE;
BEGIN
SELECT p_date + INTERVAL '1' DAY
INTO v_date
FROM dual;
RETURN v_date;
END;

Converted PostgreSQL code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

CREATE OR REPLACE FUNCTION sct_demo.f_numtodsinterval_002(IN p_date TIMESTAMP WITHOUT TIME ZONE)
RETURNS TIMESTAMP WITHOUT TIME ZONE
AS
$BODY$
DECLARE
v_date TIMESTAMP(0) WITHOUT TIME ZONE;
BEGIN
SELECT
p_date + '1 day'::INTERVAL
INTO STRICT v_date;
RETURN v_date;
END;
$BODY$
LANGUAGE plpgsql;

Date Arithmetic expression

Oracle code:

1
2
3
4
5
6
7
8
9
10

CREATE OR REPLACE FUNCTION sct_demo.F_NUMTODSINTERVAL_003(p_date IN DATE)
RETURN DATE
AS
v_date DATE;
BEGIN
SELECT p_date + 1
INTO v_date
FROM dual;
RETURN v_date;
END;

Converted PostgreSQL code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14

CREATE OR REPLACE FUNCTION sct_demo.f_numtodsinterval_003(IN p_date TIMESTAMP WITHOUT TIME ZONE)
RETURNS TIMESTAMP WITHOUT TIME ZONE
AS
$BODY$
DECLARE
v_date TIMESTAMP(0) WITHOUT TIME ZONE;
BEGIN
SELECT
p_date + (1::NUMERIC || ' days')::INTERVAL
INTO STRICT v_date;
RETURN v_date;
END;
$BODY$
LANGUAGE plpgsql;

The ABCloudz database team has strong expertise in solving common challenges with migration from Oracle to PostgreSQL. We use SCT and other AWS technologies to make sure that all source database features and functions are recreated in the target database. Contact us to see how the ABCloudz team can bring your database management to a new level.