Tags: AWS Schema Conversion Tool, database conversion errors
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.”
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; |
References
Be sure to check the regularly updated table of contents for our series of blog posts on typical AWS SCT conversion issues.
