Tags: AWS Schema Conversion Tool, database conversion errors
Oracle 9i introduced the predefined SYS_REFCURSOR type meaning we no longer have to define our own REF CURSOR types. We normally use Oracle’s SYS_REFCURSOR datatype to declare the CURSOR variable of a weak type, which means that we don’t know exactly the number of columns and their type. Then this SYS_REFCURSOR variable has to be used together with a dynamic SQL code to open the cursor variable.
MySQL does not support cursors of a weak type. So, when you try to convert any variables of SYS_REFCURSOR datatype in your source code, SCT will generate the following action item: “Issue 337 — MySQL does not support a variable of SYS_REFCURSOR type”.
Below you will find the proven solution on how you can address this issued from our team.
Possible Remedies
Consider the following example:
1 2 3 4 5 6 7 8 9 | CREATE PROCEDURE p_dyn_curs_001 AS v_id pls_integer; My_Cursor SYS_REFCURSOR; BEGIN OPEN My_Cursor FOR 'select id from account'; fetch My_Cursor INTO v_id; close My_Cursor; END; |
When SCT tries to convert such code structure, it returns the message “Issue 337 — MySQL does not support a variable of SYS_REFCURSOR type”.
You may change the source code in a way, trying to use the cursor of a strong type and making SQL code static instead of dynamic. Thus, we have to change the declaration of My_Cursor variable to the cursor variable, using exactly the same query that was inside the single quotes in the original example.
1 2 3 4 5 6 7 8 9 | CREATE PROCEDURE p_dyn_curs_001 AS v_id pls_integer; cursor My_Cursor IS SELECT id FROM account; BEGIN OPEN My_Cursor; fetch My_Cursor INTO v_id; close My_Cursor; END; |
This procedure will be converted by the SCT to the following MySQL code without any issues:
1 2 3 4 5 6 7 8 | CREATE PROCEDURE SCT_DEMO.P_DYN_CURS_001() BEGIN DECLARE var_v_id INT; DECLARE My_Cursor CURSOR FOR SELECT ID FROM ACCOUNT; OPEN My_Cursor; FETCH My_Cursor INTO var_v_id; CLOSE My_Cursor; END; |
Do you have other questions regarding AWS SCT Action Codes or other features? Make sure to check out our new AWS Schema Conversion Tool Jumpstart offer to get you up and running fast for your database migration projects.
