Oracle EXECUTE IMMEDIATE and MySQL EXECUTE and PREPARE - Oracle to Aurora MySQL Migration Playbook
Oracle UsageMySQL UsageSummary
Services or capabilities described in Amazon Web Services documentation might vary by Region. To see the differences applicable to the China Regions, see Getting Started with Amazon Web Services in China (PDF).

Oracle EXECUTE IMMEDIATE and MySQL EXECUTE and PREPARE

Feature compatibility Amazon SCT / Amazon DMS automation level Amazon SCT action code index Key differences

Four star feature compatibility

Two star automation level

EXECUTE IMMEDIATE

Make sure that you use the PREPARE command in MySQL. MySQL doesn’t support running SQL with results and bind variables or anonymous blocks using EXECUTE.

Oracle Usage

You can use Oracle EXECUTE IMMEDIATE statement to parse and run a dynamic SQL statement or an anonymous PL/SQL block. It also supports bind variables.

Examples

Run a dynamic SQL statement from within a PL/SQL procedure:

  1. Create a PL/SQL procedure named raise_sal.

  2. Define a SQL statement with a dynamic value for the column name included in the WHERE statement.

  3. Use the EXECUTE IMMEDIATE command supplying the two bind variables to be used as part of the SELECT statement: amount and col_val.

    CREATE OR REPLACE PROCEDURE raise_sal (col_val NUMBER,
emp_col VARCHAR2, amount NUMBER) IS
  col_name VARCHAR2(30);
  sql_stmt VARCHAR2(350);
BEGIN
  -- determine if a valid column name has been given as input
  SELECT COLUMN_NAME INTO col_name FROM USER_TAB_COLS
  WHERE TABLE_NAME = 'EMPLOYEES' AND COLUMN_NAME = emp_col;

  -- define the SQL statment (with bind variables)
  sql_stmt := 'UPDATE employees SET salary = salary + :1 WHERE ' ||
  col_name || ' = :2';

  -- Run the command
  EXECUTE IMMEDIATE sql_stmt USING amount, col_val;
END raise_sal;
/

  4. Run the DDL operation from within an EXECUTE IMMEDIATE command.

    EXECUTE IMMEDIATE 'CREATE TABLE link_emp (idemp1 NUMBER, idemp2 NUMBER)';
EXECUTE IMMEDIATE 'ALTER SESSION SET SQL_TRACE TRUE';

  5. Run an anonymous block with bind variables using EXECUTE IMMEDIATE.

    EXECUTE IMMEDIATE 'BEGIN raise_sal (:col_val, :col_name, :amount); END;'
  USING 134, 'EMPLOYEE_ID', 10;

For more information, see EXECUTE IMMEDIATE Statement in the Oracle documentation.

MySQL Usage

The EXECUTE command in MySQL runs commands that were prepared by the PREPARE command. It can also run DDL statements and retrieve data using SQL commands. Similar to Oracle, you can use the MySQL EXECUTE command with bind variables.

The PREPARE command can receive a SELECT, INSERT, UPDATE, DELETE, or VALUES statement and parse it with a user-specified qualifying name so that you can use the EXECUTE command later without the need to re-parse the SQL statement for each run.

  • Statement names are not case-sensitive. A Statement name is either a string literal or a user variable containing the text of the SQL statement.

  • If a prepared statement with the given name already exists, it is deallocated implicitly before the new statement is prepared.

  • The scope of a prepared statement is the session in which it is created.

Examples

Run a SQL SELECT query with the table name as a dynamic variable using bind variables. This query returns the number of employees under a manager with a specific ID.

PREPARE stmt1 FROM 'SELECT count(*) FROM employees WHERE ID=?';
SET @man_id = 3;
EXECUTE stmt1 USING @a;

count(*)
2

Run a DML command with no variables and then with variables.

PREPARE stmt1 FROM 'INSERT INTO numbers (a) VALUES (1)';
EXECUTE stmt1;

PREPARE stmt1 FROM 'INSERT INTO numbers (a) VALUES (?)';
SET @man_id = 3;
EXECUTE stmt1 USING @a;

Run a DDL command.

PREPARE stmt1 FROM 'CREATE TABLE numbers (num integer)';
EXECUTE stmt1;

Summary

Functionality Oracle EXECUTE IMMEDIATE MySQL EXECUTE and PREPARE

Run SQL with results and bind variables

 
EXECUTE IMMEDIATE 'select salary
from employees WHERE ' || col_name ||
' = :1' INTO amount USING col_val;

N/A

Run DML with variables and bind variables

 
EXECUTE IMMEDIATE 'UPDATE
employees SET salary = salary + :1
WHERE ' || col_name || ' = :2'
USING amount, col_val;
 
PREPARE stmt1 FROM 'UPDATE
employees SET salary = salary + ?
WHERE ? = ?'

EXECUTE stmt1 USING @amount,@
col,@colval;

Run DDL

 
EXECUTE IMMEDIATE 'CREATE
TABLE link_emp (idemp1 NUMBER,
idemp2 NUMBER)';
 
PREPARE stmt1 FROM 'CREATE
TABLE link_emp (idemp1 INTEGER,
idemp2 INTEGER)'

EXECUTE stmt1;

Run an anonymous block

 
EXECUTE IMMEDIATE 'BEGIN
DBMS_OUTPUT.PUT_LINE
("Anonymous Block"); END;';

N/A

For more information, see EXECUTE Statement and PREPARE Statement in the MySQL documentation.