call us toll-free +1 855 855 3600
 
Home > Blog > Oracle to MySQL migration with AWS SCT: How to convert variables of SYS_REFCURSOR datatype

Oracle to MySQL migration with AWS SCT: How to convert variables of SYS_REFCURSOR datatype

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

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”.

MySQL does not support a variable of SYS_REPCURSOR 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;

AWS SCT support syfrefcursor Oracle to MySQL migration corrected

 

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.

Related posts

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

 
       
 

Leave a Reply

  • CAPTCHA Image
    Play CAPTCHA Audio
    Reload Image