Migrating from Oracle to Amazon RDS for MySQL or Amazon Aurora MySQL with the AWS Schema Conversion Tool - AWS Schema Conversion Tool
Privileges for MySQL as a targetOracle to MySQL conversion settingsMigration considerationsConverting the WITH statement

Migrating from Oracle to Amazon RDS for MySQL or Amazon Aurora MySQL with the AWS Schema Conversion Tool

To emulate Oracle database functions in your converted MySQL code, use the Oracle to MySQL extension pack in AWS SCT. For more information about extension packs, see Using extension packs with AWS Schema Conversion Tool.

Privileges for MySQL as a target database

The privileges required for MySQL as a target are as follows:

  • CREATE ON *.*

  • ALTER ON *.*

  • DROP ON *.*

  • INDEX ON *.*

  • REFERENCES ON *.*

  • SELECT ON *.*

  • CREATE VIEW ON *.*

  • SHOW VIEW ON *.*

  • TRIGGER ON *.*

  • CREATE ROUTINE ON *.*

  • ALTER ROUTINE ON *.*

  • EXECUTE ON *.*

  • CREATE TEMPORARY TABLES ON *.*

  • AWS_LAMBDA_ACCESS

  • INSERT, UPDATE ON AWS_ORACLE_EXT.*

  • INSERT, UPDATE, DELETE ON AWS_ORACLE_EXT_DATA.*

If you use a MySQL database version 5.7 or lower as a target, then grant the INVOKE LAMBDA *.* permission instead of AWS_LAMBDA_ACCESS. For MySQL databases version 8.0 and higher, grant the AWS_LAMBDA_ACCESS permission.

You can use the following code example to create a database user and grant the privileges.

CREATE USER 'user_name' IDENTIFIED BY 'your_password';
GRANT CREATE ON *.* TO 'user_name';
GRANT ALTER ON *.* TO 'user_name';
GRANT DROP ON *.* TO 'user_name';
GRANT INDEX ON *.* TO 'user_name';
GRANT REFERENCES ON *.* TO 'user_name';
GRANT SELECT ON *.* TO 'user_name';
GRANT CREATE VIEW ON *.* TO 'user_name';
GRANT SHOW VIEW ON *.* TO 'user_name';
GRANT TRIGGER ON *.* TO 'user_name';
GRANT CREATE ROUTINE ON *.* TO 'user_name';
GRANT ALTER ROUTINE ON *.* TO 'user_name';
GRANT EXECUTE ON *.* TO 'user_name';
GRANT CREATE TEMPORARY TABLES ON *.* TO 'user_name';
GRANT AWS_LAMBDA_ACCESS TO 'user_name';
GRANT INSERT, UPDATE ON AWS_ORACLE_EXT.* TO 'user_name';
GRANT INSERT, UPDATE, DELETE ON AWS_ORACLE_EXT_DATA.* TO 'user_name';

In the preceding example, replace user_name with the name of your user. Then, replace your_password with a secure password.

If you use a MySQL database version 5.7 or lower as a target, then use GRANT INVOKE LAMBDA ON *.* TO 'user_name' instead of GRANT AWS_LAMBDA_ACCESS TO 'user_name'.

To use Amazon RDS for MySQL or Aurora MySQL as a target, set the lower_case_table_names parameter to 1. This value means that the MySQL server handles identifiers of such object names as tables, indexes, triggers, and databases as case insensitive. If you have turned on binary logging in your target instance, then set the log_bin_trust_function_creators parameter to 1. In this case, you don't need to use the DETERMINISTIC, READS SQL DATA or NO SQL characteristics to create stored functions. To configure these parameters, create a new DB parameter group or modify an existing DB parameter group.

Oracle to MySQL conversion settings

To edit Oracle to MySQL conversion settings, choose Settings in AWS SCT, and then choose Conversion settings. From the upper list, choose Oracle, and then choose Oracle – MySQL. AWS SCT displays all available settings for Oracle to MySQL conversion.

Oracle to MySQL conversion settings in AWS SCT include options for the following:

  • To limit the number of comments with action items in the converted code.

    For Add comments in the converted code for the action items of selected severity and higher, choose the severity of action items. AWS SCT adds comments in the converted code for action items of the selected severity and higher.

    For example, to minimize the number of comments in your converted code, choose Errors only. To include comments for all action items in your converted code, choose All messages.

  • To address that your source Oracle database can use the ROWID pseudocolumn but MySQL doesn't support similar functionality. AWS SCT can emulate the ROWID pseudocolumn in the converted code. To do so, choose Generate as identity for Generate row ID?.

    If your source Oracle code doesn't use the ROWID pseudocolumn, choose Don't generate for Generate row ID? In this case, the converted code works faster.

  • To work with your source Oracle code when it includes the TO_CHAR, TO_DATE, and TO_NUMBER functions with parameters that MySQL doesn't support. By default, AWS SCT emulates the usage of these parameters in the converted code.

    When your source Oracle code includes only parameters that PostgreSQL supports, you can use native MySQL TO_CHAR, TO_DATE, and TO_NUMBER functions. In this case, the converted code works faster. To include only these parameters, select the following values:

    • Function TO_CHAR() does not use Oracle specific formatting strings

    • Function TO_DATE() does not use Oracle specific formatting strings

    • Function TO_NUMBER() does not use Oracle specific formatting strings

  • To addess whether your database and applications run in different time zones. By default, AWS SCT emulates time zones in the converted code. However, you don't need this emulation when your database and applications use the same time zone. In this case, select Time zone on the client side matches the time zone on server.

Migration considerations

When you convert Oracle to RDS for MySQL or Aurora MySQL, to change the order that statements run in, you can use a GOTO statement and a label. Any PL/SQL statements that follow a GOTO statement are skipped, and processing continues at the label. You can use GOTO statements and labels anywhere within a procedure, batch, or statement block. You can also next GOTO statements.

MySQL doesn't use GOTO statements. When AWS SCT converts code that contains a GOTO statement, it converts the statement to use a BEGIN…END or LOOP…END LOOP statement.

You can find examples of how AWS SCT converts GOTO statements in the table following.

Oracle statement MySQL statement 
BEGIN
   ....
   statement1;
   ....
   GOTO label1;
   statement2;
   ....
   label1:
   Statement3;
   ....
END

BEGIN
 label1:
 BEGIN
   ....
   statement1;
   ....
   LEAVE label1;
   statement2;
   ....
 END;
   Statement3;
   ....
END

BEGIN
   ....
   statement1;
   ....
   label1:
   statement2;
   ....
   GOTO label1;
   statement3;
   ....
   statement4;
   ....
END

BEGIN
   ....
   statement1;
   ....
   label1:
   LOOP
    statement2;
    ....
    ITERATE label1;
    LEAVE label1;
   END LOOP; 
    statement3;
    ....
    statement4;
    ....
END

BEGIN
   ....
   statement1;
   ....
   label1:
   statement2;
   ....
   statement3;
   ....
   statement4;
   ....
END

BEGIN
   ....
   statement1;
   ....
   label1:
   BEGIN
    statement2;
    ....    
    statement3;
    ....
    statement4;
    ....    
   END; 
END

Converting the WITH statement in Oracle to RDS for MySQL or Amazon Aurora MySQL

You use the WITH clause (subquery_factoring) in Oracle to assign a name (query_name) to a subquery block. You can then reference the subquery block multiple places in the query by specifying query_name. If a subquery block doesn't contain links or parameters (local, procedure, function, package), then AWS SCT converts the clause to a view or a temporary table.

The advantage of converting the clause to a temporary table is that repeated references to the subquery might be more efficient. The greater efficiency is because the data is easily retrieved from the temporary table rather than being required by each reference. You can emulate this by using additional views or a temporary table. The view name uses the format <procedure_name>$<subselect_alias>.

You can find examples in the table following.

Oracle statement MySQL statement 
CREATE PROCEDURE 
 TEST_ORA_PG.P_WITH_SELECT_VARIABLE_01
     (p_state IN NUMBER)
AS
  l_dept_id NUMBER := 1; 
BEGIN
FOR cur IN  
           (WITH dept_empl(id, name, surname, 
              lastname, state, dept_id)
              AS
                  (
                    SELECT id, name, surname,  
                     lastname, state, dept_id 
                      FROM test_ora_pg.dept_employees
                     WHERE state = p_state AND 
                       dept_id = l_dept_id)
            SELECT id,state   
              FROM dept_empl
            ORDER BY id)  LOOP
  NULL;
END LOOP;

CREATE PROCEDURE test_ora_pg.P_WITH_SELECT_VARIABLE_01(IN par_P_STATE DOUBLE)
BEGIN
    DECLARE var_l_dept_id DOUBLE DEFAULT 1;
    DECLARE var$id VARCHAR (8000);
    DECLARE var$state VARCHAR (8000);
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur CURSOR FOR SELECT
        ID, STATE
        FROM (SELECT
            ID, NAME, SURNAME, LASTNAME, STATE, DEPT_ID
            FROM TEST_ORA_PG.DEPT_EMPLOYEES
            WHERE STATE = par_p_state AND DEPT_ID = var_l_dept_id) AS dept_empl
        ORDER BY ID;
    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET done := TRUE;
    OPEN cur;

    read_label:
    LOOP
        FETCH cur INTO var$id, var$state;

        IF done THEN
            LEAVE read_label;
        END IF;

        BEGIN
        END;
    END LOOP;
    CLOSE cur;
END;

CREATE PROCEDURE 
 TEST_ORA_PG.P_WITH_SELECT_REGULAR_MULT_01
AS    
BEGIN

 FOR cur IN  (
               WITH dept_empl AS
                   (
                        SELECT id, name, surname, 
                         lastname, state, dept_id 
                          FROM test_ora_pg.dept_employees
                         WHERE state = 1),
                    dept AS 
                   (SELECT id deptid, parent_id, 
                      name deptname
                      FROM test_ora_pg.department                
                   )
                SELECT dept_empl.*,dept.*          
                 FROM dept_empl, dept
                 WHERE dept_empl.dept_id = dept.deptid
              ) LOOP
              NULL;
            END LOOP;

CREATE VIEW TEST_ORA_PG.`P_WITH_SELECT_REGULAR_MULT_01$dept_empl
 `(id, name, surname, lastname, state, dept_id)
AS
(SELECT id, name, surname, lastname, state, dept_id 
   FROM test_ora_pg.dept_employees
  WHERE state = 1);
  
CREATE VIEW TEST_ORA_PG.`P_WITH_SELECT_REGULAR_MULT_01$dept
 `(deptid, parent_id,deptname)
AS
(SELECT id deptid, parent_id, name deptname
   FROM test_ora_pg.department);  


CREATE PROCEDURE test_ora_pg.P_WITH_SELECT_REGULAR_MULT_01()
BEGIN
    DECLARE var$ID DOUBLE;
    DECLARE var$NAME VARCHAR (30);
    DECLARE var$SURNAME VARCHAR (30);
    DECLARE var$LASTNAME VARCHAR (30);
    DECLARE var$STATE DOUBLE;
    DECLARE var$DEPT_ID DOUBLE;
    DECLARE var$deptid DOUBLE;
    DECLARE var$PARENT_ID DOUBLE;
    DECLARE var$deptname VARCHAR (200);
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur CURSOR FOR SELECT
        dept_empl.*, dept.*
        FROM TEST_ORA_PG.`P_WITH_SELECT_REGULAR_MULT_01$dept_empl
          ` AS dept_empl,
             TEST_ORA_PG.`P_WITH_SELECT_REGULAR_MULT_01$dept
          ` AS dept
        WHERE dept_empl.DEPT_ID = dept.DEPTID;
    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET done := TRUE;
    OPEN cur;

    read_label:
    LOOP
    FETCH cur INTO var$ID, var$NAME, var$SURNAME, 
     var$LASTNAME, var$STATE, var$DEPT_ID, var$deptid, 
     var$PARENT_ID, var$deptname;

        IF done THEN
            LEAVE read_label;
        END IF;

        BEGIN
        END;
    END LOOP;
    CLOSE cur;
END;

call test_ora_pg.P_WITH_SELECT_REGULAR_MULT_01()

CREATE PROCEDURE 
  TEST_ORA_PG.P_WITH_SELECT_VAR_CROSS_02(p_state IN NUMBER)
AS    
   l_dept_id NUMBER := 10;
BEGIN
 FOR cur IN  (
               WITH emp AS              
                    (SELECT id, name, surname, 
                      lastname, state, dept_id 
                       FROM test_ora_pg.dept_employees
                      WHERE dept_id > 10                 
                    ),
                    active_emp AS
                    (
                      SELECT id
                        FROM emp
                       WHERE emp.state = p_state 
                    )
                    
                SELECT *          
                  FROM active_emp                 
              ) LOOP
         NULL;
  END LOOP;
  
END;

CREATE VIEW TEST_ORA_PG.`P_WITH_SELECT_VAR_CROSS_01$emp
    `(id, name, surname, lastname, state, dept_id)
AS
(SELECT
       id, name, surname, lastname, 
       state, dept_id
  FROM TEST_ORA_PG.DEPT_EMPLOYEES
  WHERE DEPT_ID > 10);


CREATE PROCEDURE 
   test_ora_pg.P_WITH_SELECT_VAR_CROSS_02(IN par_P_STATE DOUBLE)
BEGIN
    DECLARE var_l_dept_id DOUBLE DEFAULT 10;
    DECLARE var$ID DOUBLE;
    DECLARE done INT DEFAULT FALSE;
    DECLARE cur CURSOR FOR SELECT *
                             FROM (SELECT
                                      ID
                                     FROM 
                             TEST_ORA_PG.
                              `P_WITH_SELECT_VAR_CROSS_01$emp` AS emp
                                   WHERE emp.STATE = par_p_state) 
                                    AS active_emp;
    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET done := TRUE;
    OPEN cur;

    read_label:
    LOOP
        FETCH cur INTO var$ID;

        IF done THEN
            LEAVE read_label;
        END IF;

        BEGIN
        END;
    END LOOP;
    CLOSE cur;
END;