call us toll-free +1 855 855 3600
 
Home > Blog > Oracle to MySQL migration with AWS SCT: How to convert BULK COLLECT clause

Oracle to MySQL migration with AWS SCT: How to convert BULK COLLECT clause

Posted by | On | In Amazon Web Services, Database Migration, MySQL, Oracle Database | Tags:
 

Oracle’s SELECT INTO statement with BULK COLLECT clause allows you to retrieve an entire result set and store it in a PL/SQL collection type variable in a single operation. This approach allows to avoid the use of a loop statement to retrieve one result row at a time, thus making the code compact and effective.

MySQL doesn’t support the BULK COLLECT INTO operation, so, SCT can not convert the source Oracle code correctly. When you try to convert the source code that contains the SELECT INTO statement with BULK COLLECT Clause, SCT will generate the following message “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.”

OracleMySQL AWS Schema Convertion Tool

The right approach to solve the occurred issue implies creating the loop statement to extract the results row-by-row.

Possible Remedies

Consider the following example:

1
2
3
4
5
6
7
8
9
10
11
12
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;

When SCT converts this code, you will receive 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.

Let’s analyze these action items. The source code includes the collection variable v_list and the SELECT statement with BULK COLLECT clause to fill it. We should remove the declaration of the collection variable, put the SELECT statement into a FOR loop and use the name of the collection variable as implictly declared record. Then we should replace the collection item reference v_list(i).id with the record reference v_list.id. Despite the fact that MySQL doesn’t support record-type variables, SCT handles them properly. So, the source code will look as follows:

1
2
3
4
5
6
7
8
9
10
11
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:

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
28
29
30
31
32
33
34
35
36
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;

AWS SCT convert Oracle to MySQL

References

Be sure to check the regularly updated table of contents for our series of blog posts on typical AWS SCT conversion issues.

 
       
 

Related posts


Leave a Reply

  • CAPTCHA Image
    Play CAPTCHA Audio
    Reload Image