In this blog post we continue covering AWS SCT conversion issues, adding to the series where we help developers to deal with complex migration issues that can’t be automated with SCT. Today we are going to look into how SCT Build #660 addresses the conversion of statements with BULK COLLECT clause.

In Oracle, you can retrieve an entire result set and store it in a PL/SQL collection type variable in a single operation. This can be completed with a SELECT INTO statement with BULK COLLECT clause. Using this syntax, you can avoid using a loop statement to retrieve one result row at a time. This syntax makes PL\SQL code compact and effective.

The challenge

Unfortunately, the BULK COLLECT INTO operation is not supported by MySQL. Therefore, AWS SCT build number 600 has issues with converting the source Oracle code correctly. If you try to convert the source code with the SELECT INTO statement with BULK COLLECT Clause, you receive the following messages from SCT: “119 — Severity CRITICAL — MySQL doesn’t support collection methods. Use an ordinary table.” and”118 – MySQL doesn’t support the table type. Use an ordinary table.”

To solve such issues, you should create a loop statement that will enable you to extract the results row-by-row. Here’s how we coped with this problem with the help of AWS SCT build #600.

Potential solutions

Take a look at the following code example:

CREATE OR REPLACE PROCEDURE sct_demo.p_bulk_collect_001
AS
TYPE MyTable IS TABLE OF account%rowtype;
v_list MyTable;
BEGIN
SELECT * bulk collect INTO v_list FROM account;
FOR i IN v_list.first .. v_list.last loop
UPDATE account
SET accountno = - v_list(i).accountno
WHERE id = v_list(i).id;
END loop;
END;

While trying to convert this code with SCT, you will get the following messages:

  • 140 — Severity CRITICAL — MySQL doesn’t support BULK COLLECT INTO. You can try to include all of the fields from your table in an INTO clause.
  • 119 — Severity CRITICAL — MySQL doesn’t support collection methods. Use an ordinary table.
  • 218 — Severity CRITICAL — MySQL doesn’t support user types. Revise your architecture with a custom solution to substitute the user type using.

How about taking a closer look at these action items? In this case, we have the source code that includes the collection variable v_list, along with the SELECT statement that has the BULK COLLECT clause to fill it. A vital step involves removing the declaration of the collection variable, putting the SELECT statement into a FOR loop, and using the name of this collection variable as an implicitly declared record. The next step involves replacing the collection item reference v_list(i).id with the record reference v_list.id. Even though MySQL doesn’t support record-type variables, SCT manages to handle them properly. Take a look at the example of the source code:

CREATE OR REPLACE PROCEDURE sct_demo.p_bulk_collect_001
AS
BEGIN
FOR v_list IN (
SELECT * FROM account
) loop
UPDATE account
SET accountno = - v_list.accountno
WHERE id = v_list.id;
END loop;
END;

Conversion result

SCT will successfully convert this code to the following MySQL code:

CREATE PROCEDURE SCT_DEMO.P_BULK_COLLECT_001()
BEGIN
DECLARE var$ID DECIMAL (14, 0);
DECLARE var$ACCOUNTNO VARCHAR (16);
DECLARE var$CURRENCYID DECIMAL (3, 0);
DECLARE var$DESCRIPTION VARCHAR (160);
DECLARE var$CUSTOMERID DECIMAL (14, 0);
DECLARE var$STATEID DECIMAL (2, 0);
DECLARE var$ACCOUNTBALANCE DECIMAL (14, 3);
DECLARE var$BLOCKEDAMOUNT DECIMAL (14, 3);
DECLARE var$OPENDATE DATETIME;
DECLARE var$CLOSEDATE DATETIME;
DECLARE var$RESPMANAGERID DECIMAL (5, 0);
DECLARE var$BANKID VARCHAR (10);
DECLARE done INT DEFAULT FALSE;
DECLARE v_list CURSOR FOR SELECT
*
FROM ACCOUNT;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done := TRUE;
OPEN v_list;
read_label:
LOOP
FETCH v_list INTO var$ID, var$ACCOUNTNO, var$CURRENCYID, var$DESCRIPTION,
var$CUSTOMERID, var$STATEID, var$ACCOUNTBALANCE,
var$BLOCKEDAMOUNT, var$OPENDATE, var$CLOSEDATE,
var$RESPMANAGERID, var$BANKID;
IF done THEN
LEAVE read_label;
END IF;
UPDATE ACCOUNT
SET ACCOUNTNO = - var$ACCOUNTNO
WHERE ID = var$ID;
END LOOP;
CLOSE v_list;
END;

Did you find this post insightful? Make sure to check out our other posts on the topic. Also, pay attention to our AWS Database Migration Support offer. Here you will get some top-quality solutions to the most common SCT code conversion issues.

Ready to start the conversation?