Migrate MySQL to Oracle

Conversion Samples of MySQL to Oracle

Ispirer Toolkit analyzes all object dependencies during the conversion process and provides not only line-by-line conversion, but resolves type conversions as well. The software understands and transforms the necessary inheritance dependencies. It parses the entire source code, builds an internal tree with all the information about the objects, and uses it in the migration process.

The code samples below provide you with more details.

• Result Sets conversion:

  MySQL

   
  CREATE PROCEDURE process_maintenance_request(request_id INT)
  BEGIN
      DECLARE tenant_name VARCHAR(50);
      DECLARE apartment_number INT;
      DECLARE request_description VARCHAR(255);
   
   
      SELECT r.request_description, t.tenant_name, a.apartment_number
      INTO request_description, tenant_name, apartment_number
      FROM maintenancerequests r
      JOIN tenants t ON r.tenant_id = t.tenant_id
      JOIN apartments a ON t.apartment_id = a.apartment_id
      WHERE r.request_id = request_id;
   
   
      UPDATE maintenancerequests
      SET request_description = 'In Progress'
      WHERE request_id = request_id;
   
   
      IF request_description is not null THEN
   
          INSERT INTO maintenance_log (request_id, tenant_name, apartment_number, region_name, date_completed)
          SELECT request_id, tenant_name, apartment_number, region_name, NOW()
          FROM regions r
          JOIN cities c ON r.region_id = c.region_id
          JOIN streets s ON c.city_id = s.city_id
          JOIN houses h ON s.street_id = h.street_id
          JOIN apartments a ON h.house_id = a.house_id
          JOIN tenants t ON a.apartment_id = t.apartment_id
          WHERE t.tenant_id = tenant_id;
   
   
          DELETE FROM maintenancerequests
          WHERE request_id = request_id;
   
          SELECT 'Request processed successfully' AS message;
      ELSE
          SELECT 'Request description is empty' AS message;
      END IF;
   
      COMMIT;
  END //
   

  → Oracle

   
  CREATE OR REPLACE PROCEDURE process_maintenance_request(request_id IN NUMBER)
     as
     v_refcur SYS_REFCURSOR;
     tenant_name  VARCHAR2(50);
     apartment_number  NUMBER(10,0);
     request_description  VARCHAR2(255);
   
   
  BEGIN
     begin
        SELECT r.request_description, t.tenant_name, a.apartment_number
        INTO request_description,tenant_name,apartment_number
        FROM maintenancerequests r
        JOIN tenants t ON r.tenant_id = t.tenant_id
        JOIN apartments a ON t.apartment_id = a.apartment_id
        WHERE r.request_id = request_id FETCH FIRST 1 ROWS ONLY;
        EXCEPTION
        WHEN NO_DATA_FOUND THEN
           NULL;
     end;
   
   
     UPDATE maintenancerequests
     SET request_description = 'In Progress'
     WHERE request_id = request_id;
   
   
     IF request_description is not null THEN
          INSERT INTO maintenance_log(request_id, tenant_name, apartment_number, region_name, date_completed)
        SELECT request_id, tenant_name, apartment_number, region_name, SYSTIMESTAMP
        FROM regions r
        JOIN cities c ON r.region_id = c.region_id
        JOIN streets s ON c.city_id = s.city_id
        JOIN houses h ON s.street_id = h.street_id
        JOIN apartments a ON h.house_id = a.house_id
        JOIN tenants t ON a.apartment_id = t.apartment_id
        WHERE t.tenant_id = tenant_id;
        DELETE FROM maintenancerequests
        WHERE request_id = request_id;
        open v_refcur for SELECT 'Request processed successfully' AS message from dual;
        dbms_sql.return_result(v_refcur);
     ELSE
        open v_refcur for SELECT 'Request description is empty' AS message from dual;
        dbms_sql.return_result(v_refcur);
     END IF;
   
     COMMIT;
  END; 
  /
   

• Cursors conversion:

  MySQL

   
  CREATE FUNCTION get_maintenanace_requests()
  RETURNS VARCHAR(255)
  begin
    DECLARE done INT DEFAULT 0;
    DECLARE maintenance_description VARCHAR(255);
    DECLARE maintenance_count INT;
    DECLARE tenant_name VARCHAR(50);
   
   
    DECLARE total_maintenance_count INT DEFAULT 0;
   
    DECLARE cursor_maintenance CURSOR FOR
      SELECT request_description, tenant_name
      FROM maintenancerequests
      INNER JOIN tenants ON maintenancerequests.tenant_id = tenants.tenant_id;
  	DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
   
    OPEN cursor_maintenance;
   
      FETCH cursor_maintenance INTO maintenance_description, tenant_name;
   
      WHILE done = 0 DO
   
      SET total_maintenance_count = total_maintenance_count + 1;
   
      CASE
        WHEN LENGTH(tenant_name) > 10 THEN
          SET tenant_name = CONCAT(LEFT(tenant_name, 10), '...');
        WHEN tenant_name IS NULL THEN
          SET tenant_name = 'N/A';
        ELSE
          SET tenant_name = UPPER(tenant_name);
      END CASE;
   
      SET maintenance_description = CONCAT('Maintenance request: ', maintenance_description);
   
     FETCH cursor_maintenance INTO maintenance_description, tenant_name;
   
     END WHILE;
    CLOSE cursor_maintenance;
   
    RETURN CONCAT('Total maintenance count: ', total_maintenance_count);
  END;
   

  → Oracle

   
  CREATE OR REPLACE FUNCTION get_maintenanace_requests RETURN VARCHAR2 as
     done  NUMBER(10,0) DEFAULT 0;
     maintenance_description  VARCHAR2(255);
     maintenance_count  NUMBER(10,0);
     tenant_name  VARCHAR2(50);
     total_maintenance_count  NUMBER(10,0) DEFAULT 0;
   
     CURSOR cursor_maintenance IS SELECT request_description, tenant_name
     FROM maintenancerequests
     INNER JOIN tenants ON maintenancerequests.tenant_id = tenants.tenant_id;
   
  begin
     OPEN cursor_maintenance;
     FETCH cursor_maintenance INTO maintenance_description,tenant_name;
     if(cursor_maintenance%NOTFOUND) Then
        done := 1;
     end if;
     WHILE done = 0 loop
        total_maintenance_count := total_maintenance_count+1;
   
        CASE
        WHEN LENGTH(tenant_name) > 10 THEN
           tenant_name := CONCAT(SUBSTR(tenant_name,1,10),'...');
        WHEN tenant_name IS NULL THEN
           tenant_name := 'N/A';
        ELSE
           tenant_name := UPPER(tenant_name);
        END CASE;
        maintenance_description := CONCAT('Maintenance request: ',maintenance_description);
        FETCH cursor_maintenance INTO maintenance_description,tenant_name;
        if(cursor_maintenance%NOTFOUND) Then
           done := 1;
        end if;
     end loop;
     CLOSE cursor_maintenance;
   
     RETURN CONCAT('Total maintenance count: ',total_maintenance_count);
  END;
   

• Transactions conversion:

  MySQL

   
  CREATE PROCEDURE workspace_assign_file_number(
  	in_uuid varchar(48),
  	in_file_no varchar(10))
  BEGIN
  	start transaction;
  	update r_workspace w1, r_workspace w2, r_quoteinfo q
  		set w2.file_no = in_file_no
  		where (q.uuid = in_uuid
  			and w1.uuid = q.workspace_uuid
  			and w1.originating_uuid = ''
  			and w2.uuid = w1.uuid);
   
      commit;
  END
   

  → Oracle

   
  CREATE OR REPLACE PROCEDURE workspace_assign_file_number(in_uuid IN VARCHAR2
   , in_file_no IN VARCHAR2)
     as
  BEGIN
     -- start transaction
  update r_workspace w2
     set file_no = in_file_no
     where w2.uuid in(SELECT w1.uuid FROM r_workspace w1,r_quoteinfo q where (q.uuid = in_uuid
     and w1.uuid = q.workspace_uuid
     and w1.originating_uuid = ' '));
   
     COMMIT;
  END;
   

• Dynamic SQL conversion:

  MySQL

   
  CREATE PROCEDURE pr_dynamic()
  BEGIN
  DECLARE TAB_NAME VARCHAR(15) DEFAULT 'Regions';
     DECLARE TAB_COL VARCHAR(12) DEFAULT 'region_id';
     DECLARE COL_VALUE INT DEFAULT 2000;
     DECLARE SQL_DELETE VARCHAR(200);
     SET @sqlstr = CONCAT('DELETE FROM ',TAB_NAME,' WHERE ',TAB_COL,' <%1$L ');
   
    prepare stmt from @sqlstr;
       execute stmt;
      deallocate prepare stmt;
  end
   

  → Oracle

   
  CREATE OR REPLACE PACKAGE SWP_GV
  IS
   v_sqlstr VARCHAR2(4000);
  END SWP_GV;
   
   
  CREATE OR REPLACE PROCEDURE pr_dynamic as
     TAB_NAME  VARCHAR2(15) DEFAULT 'Regions';
     TAB_COL  VARCHAR2(12) DEFAULT 'region_id';
     COL_VALUE  NUMBER(10,0) DEFAULT 2000;
     SQL_DELETE  VARCHAR2(200);
  BEGIN
     SWP_GV.v_sqlstr := 'DELETE FROM ' || TAB_NAME || ' WHERE ' || TAB_COL || ' <%1$L ';
   
     execute IMMEDIATE SWP_GV.v_sqlstr;
   
  end;