Migrate MySQL to Oracle

 
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 //
 

 
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; 
/
 

 
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;
 

 
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;
 

 
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
 

 
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;
 

 
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
 

 
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;