`.
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;
|
# Migrating from Oracle Database to Amazon RDS for Oracle with AWS Schema Conversion Tool
Some things to consider when migrating Oracle schema and code to Amazon RDS for Oracle:
+ AWS SCT can add directory objects to the object tree. *Directory objects* are logical structures that each represent a physical directory on the server's file system. You can use directory objects with packages such as DBMS\$1LOB, UTL\$1FILE, DBMS\$1FILE\$1TRANSFER, the DATAPUMP utility, and so on.
+ AWS SCT supports converting Oracle tablespaces to an Amazon RDS for Oracle DB instance. Oracle stores data logically in tablespaces and physically in data files associated with the corresponding tablespace. In Oracle, you can create a tablespace with data file names. Amazon RDS supports Oracle Managed Files (OMF) for data files, log files, and control files only. AWS SCT creates the needed data files during conversion.
+ AWS SCT can convert server-level roles and privileges. The Oracle database engine uses role-based security. A role is a collection of privileges that you can grant to or revoke from a user. A predefined role in Amazon RDS, called DBA, normally allows all administrative privileges on an Oracle database engine. The following privileges are not available for the DBA role on an Amazon RDS DB instance using the Oracle engine:
+ Alter database
+ Alter system
+ Create any directory
+ Grant any privilege
+ Grant any role
+ Create external job
You can grant all other privileges to an Amazon RDS for Oracle user role, including advanced filtering and column privileges.
+ AWS SCT supports converting Oracle jobs into jobs that can run on Amazon RDS for Oracle. There are a few limitations to the conversion, including the following:
+ Executable jobs are not supported.
+ Schedule jobs that use the ANYDATA data type as an argument are not supported.
+ Oracle Real Application Clusters (RAC) One Node is an option to the Oracle Database Enterprise Edition that was introduced with Oracle Database 11g Release 2. Amazon RDS for Oracle doesn’t support the RAC feature. For high availability, use Amazon RDS Multi-AZ.
In a Multi-AZ deployment, Amazon RDS automatically provisions and maintains a synchronous standby replica in a different Availability Zone. The primary DB instance is synchronously replicated across Availability Zones to a standby replica. This functionality provides data redundancy, eliminates I/O freezes, and minimizes latency spikes during system backups.
+ Oracle Spatial provides a SQL schema and functions that facilitate the storage, retrieval, update, and query of collections of spatial data in an Oracle database. Oracle Locator provides capabilities that are typically required to support internet and wireless service-based applications and partner-based GIS solutions. Oracle Locator is a limited subset of Oracle Spatial.
To use Oracle Spatial and Oracle Locator features, add the SPATIAL option or LOCATOR option (mutually exclusive) to the option group of your DB instance.
There are some prerequisites to using Oracle Spatial and Oracle Locator on an Amazon RDS for Oracle DB instance:
+ The instance should use Oracle Enterprise Edition version 12.1.0.2.v6 or higher, or 11.2.0.4.v10 or higher.
+ The instance should be inside a virtual private cloud (VPC).
+ The instance should the DB instance class that can support the Oracle feature. For example, Oracle Spatial is not supported for the db.m1.small, db.t1.micro, db.t2.micro, or db.t2.small DB instance classes. For more information, see [DB instance class support for Oracle](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/CHAP_Oracle.html#Oracle.Concepts.InstanceClasses).
+ The instance must have the Auto Minor Version Upgrade option enabled. Amazon RDS updates your DB instance to the latest Oracle PSU if there are security vulnerabilities with a CVSS score of 9\$1 or other announced security vulnerabilities. For more information, see
[Settings for Oracle DB instances](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_ModifyInstance.Oracle.html#USER_ModifyInstance.Oracle.Settings).
+ If your DB instance is version 11.2.0.4.v10 or higher, you must install the XMLDB option. For more information, see
[Oracle XML DB](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.Options.XMLDB.html).
+ You should have an Oracle Spatial license from Oracle. For more information, see [Oracle Spatial and Graph](https://shop.oracle.com/apex/product?p1=OracleSpatialandGraph) in the Oracle documentation.
+ Data Guard is included with Oracle Database Enterprise Edition. For high availability, use Amazon RDS Multi-AZ feature.
In a Multi-AZ deployment, Amazon RDS automatically provisions and maintains a synchronous standby replica in a different Availability Zone. The primary DB instance is synchronously replicated across Availability Zones to a standby replica. This functionality provides data redundancy, eliminates I/O freezes, and minimizes latency spikes during system backups.
+ AWS SCT supports converting Oracle DBMS\$1SCHEDULER objects when migrating to Amazon RDS for Oracle. The AWS SCT assessment report indicates if a schedule object can be converted. For more information on using schedule objects with Amazon RDS, see the [Amazon RDS documentation](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.System.html#Appendix.Oracle.CommonDBATasks.ModifyScheduler).
+ For Oracle to Amazon RDS for Oracle conversions, DB Links is supported. A database link is a schema object in one database that enables you to access objects on another database. The other database doesn’t need to be an Oracle database. However, to access non-Oracle databases you must use Oracle Heterogeneous Services.
Once you create a database link, you can use the link in SQL statements to refer to tables, views, and PL/SQL objects in the other database. To use a database link, append `@dblink` to the table, view, or PL/SQL object name. You can query a table or view in the other database with the SELECT statement. For more information about using Oracle database links, see the [Oracle documentation](https://docs.oracle.com/cd/B28359_01/server.111/b28310/ds_concepts002.htm#ADMIN12083).
For more information about using database links with Amazon RDS, see the [Amazon RDS documentation](https://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/Appendix.Oracle.CommonDBATasks.Database.html#Appendix.Oracle.CommonDBATasks.DBLinks).
+ The AWS SCT assessment report provides server metrics for the conversion. These metrics about your Oracle instance include the following:
+ Computation and memory capacity of the target DB instance.
+ Unsupported Oracle features such as Real Application Clusters that Amazon RDS doesn't support.
+ Disk read-write load
+ Average total disk throughput
+ Server information such as server name, OS, host name, and character set.
## Privileges for RDS for Oracle as a target
To migrate to Amazon RDS for Oracle, create a privileged database user. You can use the following code example.
```
CREATE USER user_name IDENTIFIED BY your_password;
-- System privileges
GRANT DROP ANY CUBE BUILD PROCESS TO user_name;
GRANT ALTER ANY CUBE TO user_name;
GRANT CREATE ANY CUBE DIMENSION TO user_name;
GRANT CREATE ANY ASSEMBLY TO user_name;
GRANT ALTER ANY RULE TO user_name;
GRANT SELECT ANY DICTIONARY TO user_name;
GRANT ALTER ANY DIMENSION TO user_name;
GRANT CREATE ANY DIMENSION TO user_name;
GRANT ALTER ANY TYPE TO user_name;
GRANT DROP ANY TRIGGER TO user_name;
GRANT CREATE ANY VIEW TO user_name;
GRANT ALTER ANY CUBE BUILD PROCESS TO user_name;
GRANT CREATE ANY CREDENTIAL TO user_name;
GRANT DROP ANY CUBE DIMENSION TO user_name;
GRANT DROP ANY ASSEMBLY TO user_name;
GRANT DROP ANY PROCEDURE TO user_name;
GRANT ALTER ANY PROCEDURE TO user_name;
GRANT ALTER ANY SQL TRANSLATION PROFILE TO user_name;
GRANT DROP ANY MEASURE FOLDER TO user_name;
GRANT CREATE ANY MEASURE FOLDER TO user_name;
GRANT DROP ANY CUBE TO user_name;
GRANT DROP ANY MINING MODEL TO user_name;
GRANT CREATE ANY MINING MODEL TO user_name;
GRANT DROP ANY EDITION TO user_name;
GRANT CREATE ANY EVALUATION CONTEXT TO user_name;
GRANT DROP ANY DIMENSION TO user_name;
GRANT ALTER ANY INDEXTYPE TO user_name;
GRANT DROP ANY TYPE TO user_name;
GRANT CREATE ANY PROCEDURE TO user_name;
GRANT CREATE ANY SQL TRANSLATION PROFILE TO user_name;
GRANT CREATE ANY CUBE TO user_name;
GRANT COMMENT ANY MINING MODEL TO user_name;
GRANT ALTER ANY MINING MODEL TO user_name;
GRANT DROP ANY SQL PROFILE TO user_name;
GRANT CREATE ANY JOB TO user_name;
GRANT DROP ANY EVALUATION CONTEXT TO user_name;
GRANT ALTER ANY EVALUATION CONTEXT TO user_name;
GRANT CREATE ANY INDEXTYPE TO user_name;
GRANT CREATE ANY OPERATOR TO user_name;
GRANT CREATE ANY TRIGGER TO user_name;
GRANT DROP ANY ROLE TO user_name;
GRANT DROP ANY SEQUENCE TO user_name;
GRANT DROP ANY CLUSTER TO user_name;
GRANT DROP ANY SQL TRANSLATION PROFILE TO user_name;
GRANT ALTER ANY ASSEMBLY TO user_name;
GRANT CREATE ANY RULE SET TO user_name;
GRANT ALTER ANY OUTLINE TO user_name;
GRANT UNDER ANY TYPE TO user_name;
GRANT CREATE ANY TYPE TO user_name;
GRANT DROP ANY MATERIALIZED VIEW TO user_name;
GRANT ALTER ANY ROLE TO user_name;
GRANT DROP ANY VIEW TO user_name;
GRANT ALTER ANY INDEX TO user_name;
GRANT COMMENT ANY TABLE TO user_name;
GRANT CREATE ANY TABLE TO user_name;
GRANT CREATE USER TO user_name;
GRANT DROP ANY RULE SET TO user_name;
GRANT CREATE ANY CONTEXT TO user_name;
GRANT DROP ANY INDEXTYPE TO user_name;
GRANT ALTER ANY OPERATOR TO user_name;
GRANT CREATE ANY MATERIALIZED VIEW TO user_name;
GRANT ALTER ANY SEQUENCE TO user_name;
GRANT DROP ANY SYNONYM TO user_name;
GRANT CREATE ANY SYNONYM TO user_name;
GRANT DROP USER TO user_name;
GRANT ALTER ANY MEASURE FOLDER TO user_name;
GRANT ALTER ANY EDITION TO user_name;
GRANT DROP ANY RULE TO user_name;
GRANT CREATE ANY RULE TO user_name;
GRANT ALTER ANY RULE SET TO user_name;
GRANT CREATE ANY OUTLINE TO user_name;
GRANT UNDER ANY TABLE TO user_name;
GRANT UNDER ANY VIEW TO user_name;
GRANT DROP ANY DIRECTORY TO user_name;
GRANT ALTER ANY CLUSTER TO user_name;
GRANT CREATE ANY CLUSTER TO user_name;
GRANT ALTER ANY TABLE TO user_name;
GRANT CREATE ANY CUBE BUILD PROCESS TO user_name;
GRANT ALTER ANY CUBE DIMENSION TO user_name;
GRANT CREATE ANY EDITION TO user_name;
GRANT CREATE ANY SQL PROFILE TO user_name;
GRANT ALTER ANY SQL PROFILE TO user_name;
GRANT DROP ANY OUTLINE TO user_name;
GRANT DROP ANY CONTEXT TO user_name;
GRANT DROP ANY OPERATOR TO user_name;
GRANT DROP ANY LIBRARY TO user_name;
GRANT ALTER ANY LIBRARY TO user_name;
GRANT CREATE ANY LIBRARY TO user_name;
GRANT ALTER ANY MATERIALIZED VIEW TO user_name;
GRANT ALTER ANY TRIGGER TO user_name;
GRANT CREATE ANY SEQUENCE TO user_name;
GRANT DROP ANY INDEX TO user_name;
GRANT CREATE ANY INDEX TO user_name;
GRANT DROP ANY TABLE TO user_name;
GRANT SELECT_CATALOG_ROLE TO user_name;
GRANT SELECT ANY SEQUENCE TO user_name;
-- Database Links
GRANT CREATE DATABASE LINK TO user_name;
GRANT CREATE PUBLIC DATABASE LINK TO user_name;
GRANT DROP PUBLIC DATABASE LINK TO user_name;
-- Server Level Objects (directory)
GRANT CREATE ANY DIRECTORY TO user_name;
GRANT DROP ANY DIRECTORY TO user_name;
-- (for RDS only)
GRANT EXECUTE ON RDSADMIN.RDSADMIN_UTIL TO user_name;
-- Server Level Objects (tablespace)
GRANT CREATE TABLESPACE TO user_name;
GRANT DROP TABLESPACE TO user_name;
-- Server Level Objects (user roles)
/* (grant source privileges with admin option or convert roles/privs as DBA) */
-- Queues
grant execute on DBMS_AQADM to user_name;
grant aq_administrator_role to user_name;
-- for Materialized View Logs creation
GRANT SELECT ANY TABLE TO user_name;
-- Roles
GRANT RESOURCE TO user_name;
GRANT CONNECT TO user_name;
```
In the preceding example, replace *user\$1name* with the name of your user. Then, replace *your\$1password* with a secure password.
## Limitations when converting Oracle to Amazon RDS for Oracle
Some limitations you should consider when migrating Oracle schema and code to Amazon RDS for Oracle:
+ A predefined role in Amazon RDS, called DBA, normally allows all administrative privileges on an Oracle database engine. The following privileges are not available for the DBA role on an Amazon RDS DB instance using the Oracle engine:
+ Alter database
+ Alter system
+ Create any directory
+ Grant any privilege
+ Grant any role
+ Create external job
You can grant all other privileges to an Oracle RDS user role.
+ Amazon RDS for Oracle supports traditional auditing, fine-grained auditing using the DBMS\$1FGA package, and Oracle Unified Auditing.
+ Amazon RDS for Oracle doesn’t support change data capture (CDC). To do CDC during and after a database migration, use AWS Database Migration Service.