Oracle to Azure SQL Managed Instance Migration¶

-- Oracle PL/SQL
DECLARE
    v_employee_id   NUMBER(10);
    v_salary        NUMBER(10,2);
    v_hire_date     DATE;
    v_name          VARCHAR2(100);
    v_is_active     BOOLEAN := TRUE;
BEGIN
    -- logic
END;
/

-- T-SQL (Azure SQL MI)
DECLARE
    @employee_id    int,
    @salary         decimal(10,2),
    @hire_date      datetime2(0),
    @name           nvarchar(100),
    @is_active      bit = 1;
-- logic (no BEGIN/END block needed for simple scripts)

-- Oracle PL/SQL cursor FOR loop
DECLARE
    CURSOR c_employees IS
        SELECT employee_id, salary FROM employees WHERE department_id = 10;
BEGIN
    FOR emp_rec IN c_employees LOOP
        DBMS_OUTPUT.PUT_LINE('Employee: ' || emp_rec.employee_id);
        UPDATE employees SET salary = emp_rec.salary * 1.1
        WHERE employee_id = emp_rec.employee_id;
    END LOOP;
END;
/

-- T-SQL equivalent (prefer set-based)
-- Set-based approach (preferred):
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;

-- Cursor approach (when row-by-row logic is required):
DECLARE @employee_id int, @salary decimal(10,2);
DECLARE c_employees CURSOR FOR
    SELECT employee_id, salary FROM employees WHERE department_id = 10;
OPEN c_employees;
FETCH NEXT FROM c_employees INTO @employee_id, @salary;
WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT 'Employee: ' + CAST(@employee_id AS nvarchar(10));
    UPDATE employees SET salary = @salary * 1.1 WHERE employee_id = @employee_id;
    FETCH NEXT FROM c_employees INTO @employee_id, @salary;
END;
CLOSE c_employees;
DEALLOCATE c_employees;

-- Oracle PL/SQL
BEGIN
    INSERT INTO audit_log (event_type, event_date, details)
    VALUES ('LOGIN', SYSDATE, 'User login');
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        UPDATE audit_log SET event_date = SYSDATE
        WHERE event_type = 'LOGIN';
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20001, 'Unexpected error: ' || SQLERRM);
END;
/

-- T-SQL (Azure SQL MI)
BEGIN TRY
    INSERT INTO audit_log (event_type, event_date, details)
    VALUES ('LOGIN', GETDATE(), 'User login');
END TRY
BEGIN CATCH
    IF ERROR_NUMBER() = 2601 OR ERROR_NUMBER() = 2627  -- Unique constraint violation
        UPDATE audit_log SET event_date = GETDATE()
        WHERE event_type = 'LOGIN';
    ELSE
        THROW 50001, 'Unexpected error', 1;
END CATCH;

-- Oracle
CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1;

-- Usage in INSERT
INSERT INTO employees (employee_id, name)
VALUES (emp_seq.NEXTVAL, 'John Doe');

-- T-SQL (Azure SQL MI) - Option 1: Sequence
CREATE SEQUENCE emp_seq START WITH 1 INCREMENT BY 1;

INSERT INTO employees (employee_id, name)
VALUES (NEXT VALUE FOR emp_seq, 'John Doe');

-- T-SQL - Option 2: IDENTITY column (preferred for surrogate keys)
CREATE TABLE employees (
    employee_id int IDENTITY(1,1) PRIMARY KEY,
    name nvarchar(100)
);

INSERT INTO employees (name) VALUES ('John Doe');
-- employee_id auto-generated

-- Oracle BEFORE INSERT trigger
CREATE OR REPLACE TRIGGER trg_emp_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    :NEW.created_date := SYSDATE;
    :NEW.created_by := SYS_CONTEXT('USERENV', 'SESSION_USER');
    IF :NEW.employee_id IS NULL THEN
        :NEW.employee_id := emp_seq.NEXTVAL;
    END IF;
END;
/

-- T-SQL INSTEAD OF / AFTER trigger (no BEFORE triggers in SQL Server)
-- Use DEFAULT constraints and IDENTITY for the simple cases:
ALTER TABLE employees ADD CONSTRAINT df_created_date
    DEFAULT GETDATE() FOR created_date;
ALTER TABLE employees ADD CONSTRAINT df_created_by
    DEFAULT SUSER_SNAME() FOR created_by;

-- For complex logic, use AFTER trigger:
CREATE OR ALTER TRIGGER trg_emp_after_insert
ON employees
AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;
    UPDATE e
    SET created_date = GETDATE(),
        created_by = SUSER_SNAME()
    FROM employees e
    INNER JOIN inserted i ON e.employee_id = i.employee_id
    WHERE e.created_date IS NULL;
END;

-- Oracle hierarchical query
SELECT employee_id, manager_id, name,
       LEVEL as depth,
       SYS_CONNECT_BY_PATH(name, '/') as path
FROM employees
START WITH manager_id IS NULL
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY name;

-- T-SQL recursive CTE
WITH org_hierarchy AS (
    -- Anchor member
    SELECT employee_id, manager_id, name,
           1 AS depth,
           CAST('/' + name AS nvarchar(max)) AS path
    FROM employees
    WHERE manager_id IS NULL

    UNION ALL

    -- Recursive member
    SELECT e.employee_id, e.manager_id, e.name,
           h.depth + 1,
           CAST(h.path + '/' + e.name AS nvarchar(max))
    FROM employees e
    INNER JOIN org_hierarchy h ON e.manager_id = h.employee_id
)
SELECT * FROM org_hierarchy
ORDER BY path;

-- Oracle autonomous transaction
CREATE OR REPLACE PROCEDURE log_event(p_message VARCHAR2) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    INSERT INTO event_log (message, log_date) VALUES (p_message, SYSDATE);
    COMMIT;  -- Commits independently of caller's transaction
END;
/

-- T-SQL workaround: Use a loopback linked server or separate connection
-- Option 1: Table variable (not rolled back on ROLLBACK)
CREATE OR ALTER PROCEDURE dbo.log_event @message nvarchar(max)
AS
BEGIN
    -- Table variables are not affected by ROLLBACK
    DECLARE @log TABLE (message nvarchar(max), log_date datetime2);
    INSERT INTO @log VALUES (@message, GETDATE());

    -- Insert from table variable (survives ROLLBACK in some patterns)
    INSERT INTO event_log (message, log_date)
    SELECT message, log_date FROM @log;
END;

-- Option 2: Service Broker or Azure Service Bus for async logging
-- (Recommended for production -- fire-and-forget pattern)

-- Row count comparison
SELECT 'employees' AS table_name, COUNT(*) AS row_count FROM employees
UNION ALL
SELECT 'departments', COUNT(*) FROM departments
UNION ALL
SELECT 'audit_log', COUNT(*) FROM audit_log;

-- Checksum comparison (run on both Oracle and Azure SQL MI)
-- Oracle:
SELECT ORA_HASH(LISTAGG(employee_id || salary || name, ',')
    WITHIN GROUP (ORDER BY employee_id)) AS checksum
FROM employees;

-- Azure SQL MI:
SELECT CHECKSUM_AGG(CHECKSUM(employee_id, salary, name)) AS checksum
FROM employees;

-- Azure SQL MI: Enable Query Store (enabled by default)
-- Review top resource-consuming queries
SELECT TOP 20
    qs.query_id,
    qt.query_sql_text,
    rs.avg_duration / 1000.0 AS avg_duration_ms,
    rs.avg_cpu_time / 1000.0 AS avg_cpu_ms,
    rs.avg_logical_io_reads,
    rs.count_executions
FROM sys.query_store_runtime_stats rs
JOIN sys.query_store_plan qp ON rs.plan_id = qp.plan_id
JOIN sys.query_store_query qs ON qp.query_id = qs.query_id
JOIN sys.query_store_query_text qt ON qs.query_text_id = qt.query_text_id
ORDER BY rs.avg_duration DESC;