We continue our series of common issues pertaining to code conversions with AWS SCT. This time, we analyze the peculiarities of converting REF CURSOR types from Oracle to MySQL with the help of AWS SCT build number 660.

In Oracle 9i, there is the predefined SYS_REFCURSOR type, which means that defining our own REF CURSOR types is no longer needed. Normally, we define the CURSOR variable of a weak type with Oracle SYS_REFCURSOR datatype. This applies to the cases when we don’t know the exact number of columns and their type. The given SYS_REFCURSOR should be used together with a dynamic SQL code because it enables you to open the cursor variable.

The challenge

The problem is that MySQL doesn’t support cursors of a weak type. Once you try to convert any variables of SYS_REFCURSOR datatype in your source code, you receive the following action item from SCT: “Issue 337 — MySQL does not support a variable of SYS_REFCURSOR type”.

Our team has a tried and trusted solution to this problem. Make sure to check the example of successful conversion with AWS SCT build #660 below.

Potential solutions

Take a look at the following example:

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 you try to convert such code structure with SCT, you get the following message: “Issue 337 — MySQL does not support a variable of SYS_REFCURSOR type”.

A possible solution is trying to use the cursor of a strong type and using static SQL code instead of dynamic. You should change the declaration of My_Cursor variable to the cursor variable. In this case, it is important to apply exactly the same query that was inside the single quotes in the initial example.

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;

SCT will convert the given procedure into the following MySQL code:

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;

Final thoughts

So, that was the solution to the problem of REF CURSOR conversion. Surely, there are many more challenges you may encounter while working with AWS Schema Conversion Tool. You may find solutions to many of them in our blog posts. Or make sure to check the AWS Database Migration Support offer for some top-quality help in database conversion.

Ready to start the conversation?