Oracle to PostgreSQL migration with AWS SCT: How to convert INSERT ALL statement

15 Apr 2022 Oleksii Savchenko

In this post we continue exploring the technical aspects of Oracle to PostgreSQL migration using AWS SCT. AWS SCT promises a very high level of migration efforts, however it is very rare that 100% automation is achieved. In this article we’ll look into conversion of INSERT ALL statements, which are quite popular in Oracle code. We were using AWS SCT build #660 to convert one of our Oracle test databases.

Oracle gives you an option to use the conditional insert clauses. For example, you may use an INSERT ALL statement if you want to add multiple rows with a single command. There is also an option to add rows into one table or even multiple tables with just one SQL statement in Oracle. The problem is that PostgreSQL doesn’t support statements that attempt to add multiple rows.

As a result, each attempt to convert the source Oracle code that includes the INSERT ALL statement to PostgreSQL in the AWS SCT ends with the following action item: «9996 — Severity critical — Transformer error occurred».
Let’s take a look at methods for converting example procedures. First of all, it goes about creating several tables to test:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE TABLE sct_demo.tbl_CompanyEmployees (
ID INTEGER NOT NULL PRIMARY KEY,
FirstName varchar2(40) NOT NULL,
LastName varchar2(40) NOT NULL,
BirthDate DATE NOT NULL
);
CREATE TABLE sct_demo.tbl_CompanyEmployees_1 (
ID INTEGER NOT NULL PRIMARY KEY,
FirstName varchar2(40) NOT NULL,
LastName varchar2(40) NOT NULL,
BirthDate DATE NOT NULL
);
CREATE TABLE sct_demo.tbl_CompanyEmployees_2 (
ID INTEGER NOT NULL PRIMARY KEY,
FirstName varchar2(40) NOT NULL,
LastName varchar2(40) NOT NULL,
BirthDate DATE NOT NULL
);
CREATE TABLE sct_demo.tbl_NameData (
ID INTEGER NOT NULL PRIMARY KEY,
FirstName varchar2(40) NOT NULL,
LastName varchar2(40) NOT NULL
);
CREATE TABLE sct_demo.tbl_BirthDateData (
ID INTEGER NOT NULL PRIMARY KEY,
BirthDate DATE NOT NULL
);

Source code example

Check this Oracle code example with an INSERT ALL statement.

1
2
3
4
5
6
7
8
9
CREATE OR REPLACE PROCEDURE sct_demo.PRC_INSERTALL_MULTIPLEREC
IS
BEGIN
INSERT ALL
INTO sct_demo.tbl_CompanyEmployees VALUES (13, 'Bruce', 'Austin', TO_DATE('02/13/1985', 'MM/DD/YYYY'))
INTO sct_demo.tbl_CompanyEmployees VALUES (14, 'Kevin', 'Urman', TO_DATE('07/22/1995', 'MM/DD/YYYY'))
INTO sct_demo.tbl_CompanyEmployees VALUES (15, 'Steven','Marlow', TO_DATE('01/15/1974', 'MM/DD/YYYY'))
SELECT * FROM DUAL;
END;

Any attempt to convert this code in AWS SCT brings you an error.

Possible solutions

If you want to resolve this issue, replace the INSERT ALL statement with numerous regular insert stahttps://abcloudz.com/wp-content/uploads/2021/12/InsertALL-1.jpgtements.

1
2
3
4
5
6
7
CREATE OR REPLACE PROCEDURE sct_demo.PRC_INSERTALL_MULTIPLEREC_MOD1
IS
BEGIN
INSERT INTO sct_demo.tbl_CompanyEmployees VALUES (13, 'Bruce', 'Austin', TO_DATE('02/13/1985', 'MM/DD/YYYY'));
INSERT INTO sct_demo.tbl_CompanyEmployees VALUES (14, 'Kevin', 'Urman', TO_DATE('07/22/1995', 'MM/DD/YYYY'));
INSERT INTO sct_demo.tbl_CompanyEmployees VALUES (15, 'Steven','Marlow', TO_DATE('01/15/1974', 'MM/DD/YYYY'));
END;

You can correctly convert this procedure with SCT:

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE OR REPLACE PROCEDURE sct_demo.prc_insertall_multiplerec_mod1()
AS
$BODY$
BEGIN
INSERT INTO sct_demo.tbl_companyemployees
VALUES (13, 'Bruce', 'Austin', aws_oracle_ext.TO_DATE('02/13/1985', 'MM/DD/YYYY'));
INSERT INTO sct_demo.tbl_companyemployees
VALUES (14, 'Kevin', 'Urman', aws_oracle_ext.TO_DATE('07/22/1995', 'MM/DD/YYYY'));
INSERT INTO sct_demo.tbl_companyemployees
VALUES (15, 'Steven', 'Marlow', aws_oracle_ext.TO_DATE('01/15/1974', 'MM/DD/YYYY'));
END;
$BODY$
LANGUAGE plpgsql;

Unfortunately, this solution may not work in cases when you need to insert a lot of data. Here’s a relevant automated workaround that is based on the UNION ALL statement.

1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE OR REPLACE PROCEDURE sct_demo.PRC_INSERTALL_MULTIPLEREC_MOD2
IS
BEGIN
INSERT INTO sct_demo.tbl_CompanyEmployees
SELECT 13, 'Bruce', 'Austin', TO_DATE('02/13/1985', 'MM/DD/YYYY')
FROM DUAL
UNION ALL
SELECT 14, 'Kevin', 'Urman', TO_DATE('07/22/1995', 'MM/DD/YYYY')
FROM DUAL
UNION ALL
SELECT 15, 'Steven','Marlow', TO_DATE('01/15/1974', 'MM/DD/YYYY')
FROM DUAL;
END;

SCT converts this code without any errors.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE OR REPLACE PROCEDURE sct_demo.prc_insertall_multiplerec_mod2()
AS
$BODY$
BEGIN
INSERT INTO sct_demo.tbl_companyemployees
SELECT
13, 'Bruce', 'Austin', aws_oracle_ext.TO_DATE('02/13/1985', 'MM/DD/YYYY')
UNION ALL
SELECT
14, 'Kevin', 'Urman', aws_oracle_ext.TO_DATE('07/22/1995', 'MM/DD/YYYY')
UNION ALL
SELECT
15, 'Steven', 'Marlow', aws_oracle_ext.TO_DATE('01/15/1974', 'MM/DD/YYYY');
END;
$BODY$
LANGUAGE plpgsql;

Take a look at one more source code example.

One more example

Let’s consider Oracle’s INSERT ALL statement that inserts data in multiple tables.

1
2
3
4
5
6
7
8
9
10
11
CREATE OR REPLACE PROCEDURE sct_demo.PRC_INSERTALL_MULTITAB
IS
BEGIN
INSERT ALL
INTO sct_demo.tbl_NameData VALUES (ID, FirstName, LastName)
INTO sct_demo.tbl_BirthDateData VALUES (ID, BirthDate)
SELECT *
FROM sct_demo.tbl_CompanyEmployees
WHERE BirthDate BETWEEN TO_DATE('01/01/1980', 'MM/DD/YYYY')
AND TO_DATE('12/01/1989', 'MM/DD/YYYY');
END;

SCT is unable to convert this procedure:

Therefore, change your source Oracle code as follows.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE OR REPLACE PROCEDURE sct_demo.PRC_INSERTALL_MULTITAB_MOD1
IS
BEGIN
INSERT INTO sct_demo.tbl_NameData (ID, FirstName, LastName)
SELECT ID, FirstName, LastName
FROM sct_demo.tbl_CompanyEmployees
WHERE BirthDate BETWEEN TO_DATE('01/01/1980', 'MM/DD/YYYY')
AND TO_DATE('12/01/1989', 'MM/DD/YYYY');

INSERT INTO sct_demo.tbl_BirthDateData (ID, BirthDate)
SELECT ID, BirthDate
FROM sct_demo.tbl_CompanyEmployees
WHERE BirthDate BETWEEN TO_DATE('01/01/1980', 'MM/DD/YYYY')
AND TO_DATE('12/01/1989', 'MM/DD/YYYY');
END;

Bingo! The code is converted by SCT without any issues!

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
CREATE OR REPLACE PROCEDURE sct_demo.prc_insertall_multitab_mod1()
AS
$BODY$
BEGIN
INSERT INTO sct_demo.tbl_namedata (id, firstname, lastname)
SELECT
id, firstname, lastname
FROM sct_demo.tbl_companyemployees
WHERE birthdate BETWEEN (SELECT
aws_oracle_ext.TO_DATE('01/01/1980', 'MM/DD/YYYY')) AND (SELECT
aws_oracle_ext.TO_DATE('12/01/1989', 'MM/DD/YYYY'));
INSERT INTO sct_demo.tbl_birthdatedata (id, birthdate)
SELECT
id, birthdate
FROM sct_demo.tbl_companyemployees
WHERE birthdate BETWEEN (SELECT
aws_oracle_ext.TO_DATE('01/01/1980', 'MM/DD/YYYY')) AND (SELECT
aws_oracle_ext.TO_DATE('12/01/1989', 'MM/DD/YYYY'));
END;
$BODY$
LANGUAGE plpgsql;

Using the WHEN clause to convert the INSERT ALL statement

Here’s one more example with a condition in the executed INSERT ALL statement. You will get the following source Oracle code:

1
2
3
4
5
6
7
8
9
10
11
CREATE OR REPLACE PROCEDURE sct_demo.PRC_INSERTALL_CONDTAB
IS
BEGIN
INSERT ALL
WHEN BirthDate <= TO_DATE('01/01/1985', 'MM/DD/YYYY') THEN INTO sct_demo.tbl_CompanyEmployees_1 WHEN BirthDate > TO_DATE('01/01/1985', 'MM/DD/YYYY') THEN
INTO sct_demo.tbl_CompanyEmployees_2
SELECT *
FROM sct_demo.tbl_CompanyEmployees
WHERE BirthDate BETWEEN TO_DATE('01/01/1980', 'MM/DD/YYYY')
AND TO_DATE('12/01/1989', 'MM/DD/YYYY');
END;

SCT fails to convert this procedure and generates an error message:

In this case, you should separate the condition manually. Besides, you should create a pair of INSERT-SELECT statements for each option.

If you have the following original code, SCT will have no issues with converting it to PostgreSQL correctly.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
CREATE OR REPLACE PROCEDURE sct_demo.PRC_INSERTALL_CONDTAB_MOD1
IS
BEGIN
INSERT INTO sct_demo.tbl_CompanyEmployees_1
SELECT *
FROM sct_demo.tbl_CompanyEmployees
WHERE BirthDate BETWEEN TO_DATE('01/01/1980', 'MM/DD/YYYY')
AND TO_DATE('01/01/1985', 'MM/DD/YYYY');

INSERT INTO sct_demo.tbl_CompanyEmployees_2
SELECT *
FROM sct_demo.tbl_CompanyEmployees
WHERE BirthDate > TO_DATE('01/01/1985', 'MM/DD/YYYY')
AND BirthDate <= TO_DATE('12/01/1989', 'MM/DD/YYYY'); END; Converted code IS CREATE OR REPLACE PROCEDURE sct_demo.prc_insertall_condtab_mod1() AS $BODY$ BEGIN INSERT INTO sct_demo.tbl_companyemployees_1 SELECT * FROM sct_demo.tbl_companyemployees WHERE birthdate BETWEEN (SELECT aws_oracle_ext.TO_DATE('01/01/1980', 'MM/DD/YYYY')) AND (SELECT aws_oracle_ext.TO_DATE('01/01/1985', 'MM/DD/YYYY')); INSERT INTO sct_demo.tbl_companyemployees_2 SELECT * FROM sct_demo.tbl_companyemployees WHERE birthdate > (SELECT
aws_oracle_ext.TO_DATE('01/01/1985', 'MM/DD/YYYY')) AND birthdate <= (SELECT
aws_oracle_ext.TO_DATE('12/01/1989', 'MM/DD/YYYY'));
END;
$BODY$
LANGUAGE plpgsql;

If you have other questions or concerns about issues with using AWS SCT for Oracle to PostgreSQL migrations, make sure to check our AWS Database Migration Support Offer. It is a perfect solution for your database migration project run fast and efficiently.

Ready to start the conversation?