How to Migrate Oracle Stored Procedures to PostgreSQL with Minimal Tweaks

1. Oracle Preparation

Create the test tables EMPLOYEES and DEPARTMENTS with appropriate column definitions.

CREATE TABLE EMPLOYEES (
  employee_id NUMBER PRIMARY KEY,
  first_name VARCHAR2(50),
  last_name VARCHAR2(50),
  email VARCHAR2(100),
  phone_number VARCHAR2(20),
  hire_date DATE,
  job_id VARCHAR2(10),
  salary NUMBER(8,2),
  commission_pct NUMBER(2,2),
  department_id NUMBER
);

CREATE TABLE DEPARTMENTS (
  department_id NUMBER PRIMARY KEY,
  department_name VARCHAR2(50),
  manager_id NUMBER,
  location_id NUMBER,
  budget NUMBER(12,2),
  start_date DATE,
  active CHAR(1),
  max_staff NUMBER(3)
);

Insert sample rows into EMPLOYEES.

INSERT ALL
INTO EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, department_id) VALUES (1001, 'John', 'Doe', '[email protected]', '123-456-7890', TO_DATE('2023-01-15','YYYY-MM-DD'), 'SA_REP', 8000.00, 0.10, 10)
INTO EMPLOYEES VALUES (1002, 'Jane', 'Smith', '[email protected]', '123-456-7891', TO_DATE('2023-02-20','YYYY-MM-DD'), 'SA_MAN', 12000.00, NULL, 10)
INTO EMPLOYEES VALUES (1003, 'Mike', 'Johnson', '[email protected]', '123-456-7892', TO_DATE('2023-03-10','YYYY-MM-DD'), 'IT_PROG', NULL, 0.05, 20)
INTO EMPLOYEES VALUES (1004, 'Emily', 'Davis', '[email protected]', '123-456-7893', TO_DATE('2023-04-05','YYYY-MM-DD'), 'IT_PROG', 9500.00, NULL, 20)
INTO EMPLOYEES VALUES (1005, 'David', 'Wilson', '[email protected]', '123-456-7894', TO_DATE('2023-05-18','YYYY-MM-DD'), 'SA_REP', 7500.00, 0.08, 10)
INTO EMPLOYEES VALUES (1006, 'Sarah', 'Brown', '[email protected]', '123-456-7895', TO_DATE('2023-06-22','YYYY-MM-DD'), 'HR_REP', 6000.00, NULL, 30)
INTO EMPLOYEES VALUES (1007, 'Robert', 'Lee', '[email protected]', '123-456-7896', TO_DATE('2023-07-30','YYYY-MM-DD'), 'FI_ACCOUNT', 8500.00, NULL, 40)
INTO EMPLOYEES VALUES (1008, 'Lisa', 'Wang', '[email protected]', '123-456-7897', TO_DATE('2023-08-15','YYYY-MM-DD'), 'MK_REP', 7200.00, 0.03, 50)
INTO EMPLOYEES VALUES (1009, 'Tom', 'Zhang', '[email protected]', '123-456-7898', TO_DATE('2023-09-05','YYYY-MM-DD'), 'PU_CLERK', 5800.00, NULL, 30)
INTO EMPLOYEES VALUES (1010, 'Amy', 'Chen', '[email protected]', '123-456-7899', TO_DATE('2023-10-12','YYYY-MM-DD'), 'AD_ASST', 4500.00, NULL, 60)
SELECT 1 FROM DUAL;

Insert sample rows into DEPARTMENTS.

INSERT ALL
INTO DEPARTMENTS (department_id, department_name, manager_id, location_id, budget, start_date, active, max_staff) VALUES (10, 'Human Resources', 101, 1, 50000.00, TO_DATE('2020-01-15','YYYY-MM-DD'), 'Y', 10)
INTO DEPARTMENTS VALUES (11, 'Finance', 102, 2, 48000.00, TO_DATE('2020-02-20','YYYY-MM-DD'), 'Y', 8)
INTO DEPARTMENTS VALUES (12, 'Engineering', 103, 3, 50000.00, TO_DATE('2020-03-10','YYYY-MM-DD'), 'Y', 15)
INTO DEPARTMENTS VALUES (13, 'Marketing', 104, 4, 43000.00, TO_DATE('2020-04-05','YYYY-MM-DD'), 'Y', 7)
INTO DEPARTMENTS VALUES (14, 'Sales', 105, 5, 52000.00, TO_DATE('2020-05-12','YYYY-MM-DD'), 'Y', 12)
INTO DEPARTMENTS VALUES (15, 'IT Support', 106, 6, 46000.00, TO_DATE('2020-06-18','YYYY-MM-DD'), 'Y', 9)
INTO DEPARTMENTS VALUES (16, 'Operations', 107, 7, 44000.00, TO_DATE('2020-07-22','YYYY-MM-DD'), 'Y', 6)
INTO DEPARTMENTS VALUES (17, 'Legal', 108, 8, 49000.00, TO_DATE('2020-08-30','YYYY-MM-DD'), 'Y', 5)
INTO DEPARTMENTS VALUES (18, 'Research', 109, 9, 51000.00, TO_DATE('2020-09-15','YYYY-MM-DD'), 'Y', 11)
INTO DEPARTMENTS VALUES (19, 'Admin', 110, 10, 41000.00, TO_DATE('2020-10-08','YYYY-MM-DD'), 'Y', 4)
SELECT 1 FROM DUAL;

2. PostgreSQL Preparation

Create equivalent tables in PostgreSQL.

CREATE TABLE EMPLOYEES (
  employee_id INT PRIMARY KEY,
  first_name VARCHAR(50),
  last_name VARCHAR(50),
  email VARCHAR(100),
  phone_number VARCHAR(20),
  hire_date DATE,
  job_id VARCHAR(10),
  salary DECIMAL(8,2),
  commission_pct DECIMAL(2,2),
  department_id INT
);

CREATE TABLE DEPARTMENTS (
  department_id INT PRIMARY KEY,
  department_name VARCHAR(50),
  manager_id INT,
  location_id INT,
  budget DECIMAL(12,2),
  start_date DATE,
  active CHAR(1),
  max_staff INT
);

Insert the same test data into PostgreSQL.

INSERT INTO EMPLOYEES (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, commission_pct, department_id) VALUES
(1001, 'John', 'Doe', '[email protected]', '123-456-7890', '2021-01-10', 'HR_REP', 8500.00, 0.05, 10),
(1002, 'Jane', 'Smith', '[email protected]', '123-456-7891', '2021-02-15', 'HR_MGR', 12000.00, 0.10, 10),
(1003, 'Mike', 'Johnson', '[email protected]', '123-456-7892', '2021-03-20', 'HR_ASSOC', 6000.00, 0.03, 10),
(1004, 'Emily', 'Davis', '[email protected]', '123-456-7893', '2021-04-25', 'HR_SPEC', 9200.00, 0.07, 10),
(1005, 'David', 'Wilson', '[email protected]', '123-456-7894', '2021-05-30', 'HR_COORD', 7800.00, 0.04, 10),
(1006, 'Sarah', 'Brown', '[email protected]', '123-456-7895', '2021-06-05', 'HR_JR', NULL, NULL, 10),
(1007, 'Robert', 'Lee', '[email protected]', '123-456-7896', '2021-07-10', 'FIN_REP', 7500.00, 0.06, 11),
(1008, 'Lisa', 'Wang', '[email protected]', '123-456-7897', '2021-08-15', 'ENG_DEV', 9800.00, 0.08, 12),
(1009, 'Tom', 'Zhang', '[email protected]', '123-456-7898', '2021-09-20', 'MKT_SPEC', 6800.00, 0.02, 13),
(1010, 'Amy', 'Chen', '[email protected]', '123-456-7899', '2021-10-25', 'SALES_REP', 8200.00, 0.12, 14);

INSERT INTO DEPARTMENTS (department_id, department_name, manager_id, location_id, budget, start_date, active, max_staff) VALUES
(10, 'Human Resources', 101, 1, 50000.00, '2020-01-15', 'Y', 10),
(11, 'Finance', 102, 2, 48000.00, '2020-02-20', 'Y', 8),
(12, 'Engineering', 103, 3, 50000.00, '2020-03-10', 'Y', 15),
(13, 'Marketing', 104, 4, 43000.00, '2020-04-05', 'Y', 7),
(14, 'Sales', 105, 5, 52000.00, '2020-05-12', 'Y', 12),
(15, 'IT Support', 106, 6, 46000.00, '2020-06-18', 'Y', 9),
(16, 'Operations', 107, 7, 44000.00, '2020-07-22', 'Y', 6),
(17, 'Legal', 108, 8, 49000.00, '2020-08-30', 'Y', 5),
(18, 'Research', 109, 9, 51000.00, '2020-09-15', 'Y', 11),
(19, 'Admin', 110, 10, 41000.00, '2020-10-08', 'Y', 4);

3. Oracle Stored Procedure

The original Oracle procedure that checks a department, gathers statistics, builds a JSON result, updates salaries, and validates budget.

CREATE OR REPLACE PROCEDURE CHECKANDUPDATESALARIES(
  p_dept_id IN NUMBER,
  p_result OUT CLOB,
  p_percent IN NUMBER,
  p_min_dept IN NUMBER DEFAULT NULL,
  p_max_dept IN NUMBER DEFAULT NULL
) IS
  TYPE stats_rec IS RECORD (
    total_emp NUMBER,
    avg_salary NUMBER,
    max_salary NUMBER
  );
  TYPE p_result_rec IS RECORD (
    employee_id NUMBER,
    last_name VARCHAR2(50),
    salary NUMBER(8,2)
  );
  v_stats stats_rec;
  v_result p_result_rec;
  v_cursor SYS_REFCURSOR;
  v_sql VARCHAR2(1000);
  v_dept_name VARCHAR2(50);
  v_rows_updated NUMBER := 0;
  v_total_budget NUMBER := 0;
  CURSOR dept_cur IS
    SELECT department_id, budget FROM DEPARTMENTS
    WHERE (department_id >= p_min_dept OR p_min_dept IS NULL)
      AND (department_id <= p_max_dept OR p_max_dept IS NULL);
BEGIN
  -- department validation
  SELECT department_name INTO v_dept_name FROM DEPARTMENTS WHERE department_id = p_dept_id;
  -- basic statistics
  SELECT COUNT(*), AVG(salary), MAX(salary)
    INTO v_stats.total_emp, v_stats.avg_salary, v_stats.max_salary
    FROM EMPLOYEES WHERE department_id = p_dept_id;
  -- dynamic SQL for top earners
  v_sql := 'SELECT * FROM (SELECT employee_id, last_name, salary FROM employees WHERE department_id = :1 ORDER BY salary DESC) WHERE ROWNUM <= 5';
  OPEN v_cursor FOR v_sql USING p_dept_id;
  -- build JSON output
  p_result := '{"department":"' || v_dept_name || '","total_employees":' || v_stats.total_emp || ',"salary_stats":{"average":' || v_stats.avg_salary || ',"max":' || v_stats.max_salary || '},"top_earners":[';
  LOOP
    FETCH v_cursor INTO v_result;
    EXIT WHEN v_cursor%NOTFOUND;
    p_result := p_result || '{"id":' || v_result.employee_id || ',"name":"' || v_result.last_name || '","salary":' || v_result.salary || '},';
  END LOOP;
  p_result := RTRIM(p_result, ',') || ']}' ;
  CLOSE v_cursor;
  -- salary update per department
  FOR dept_rec IN dept_cur LOOP
    UPDATE EMPLOYEES SET salary = NVL(salary, 3000) * (1 + p_percent/100) WHERE department_id = dept_rec.department_id;
    v_rows_updated := v_rows_updated + SQL%ROWCOUNT;
    SELECT SUM(salary) INTO v_total_budget FROM EMPLOYEES WHERE department_id = dept_rec.department_id;
    IF v_total_budget > dept_rec.budget THEN
      RAISE_APPLICATION_ERROR(-20001, 'Budget exceeded in department ' || dept_rec.department_id);
    END IF;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Updated ' || v_rows_updated || ' records, Time ' || SYSDATE);
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    p_result := '{"error":"Department not found"}';
  WHEN OTHERS THEN
    p_result := '{"error":"' || SQLERRM || '"}';
END;

4. Conversion Process with SQLShift

Step‑by‑step UI actions to convert the Oracle procedure to PostgreSQL.

Log in, claim a trial quota, and create a new conversion project.

Enter the Oracle stored‑procedure code, choose "Single SQL conversion", and start the conversion.

After conversion finishes, view the generated PostgreSQL function.

5. Converted PostgreSQL Function

The function produced by SQLShift, with notes on the few manual tweaks required.

CREATE OR REPLACE FUNCTION CHECKANDUPDATESALARIES(
  p_dept_id NUMERIC,
  p_percent NUMERIC,
  p_min_dept NUMERIC DEFAULT NULL,
  p_max_dept NUMERIC DEFAULT NULL
) RETURNS TEXT AS $$
DECLARE
  p_result TEXT;
  v_stats RECORD;
  v_result RECORD;
  v_cursor REFCURSOR;
  v_sql TEXT;
  v_dept_name VARCHAR(50);
  v_rows_updated INTEGER := 0;
  v_total_budget NUMERIC := 0;
  dept_cursor CURSOR FOR
    SELECT department_id, budget FROM departments
    WHERE (department_id >= p_min_dept OR p_min_dept IS NULL)
      AND (department_id <= p_max_dept OR p_max_dept IS NULL);
BEGIN
  SELECT department_name INTO v_dept_name FROM departments WHERE department_id = p_dept_id;
  SELECT COUNT(*) AS total_emp, AVG(salary) AS avg_salary, MAX(salary) AS max_salary
    INTO v_stats FROM employees WHERE department_id = p_dept_id;
  v_sql := 'SELECT employee_id, last_name, salary FROM employees WHERE department_id = $1 ORDER BY salary DESC LIMIT 5';
  OPEN v_cursor FOR EXECUTE v_sql USING p_dept_id;
  p_result := '{"department":"' || v_dept_name || '","total_employees":' || COALESCE(v_stats.total_emp,0) || ',"salary_stats":{"average":' || COALESCE(v_stats.avg_salary,0) || ',"max":' || COALESCE(v_stats.max_salary,0) || '},"top_earners":[';
  LOOP
    FETCH v_cursor INTO v_result;
    EXIT WHEN NOT FOUND;
    p_result := p_result || '{"id":' || v_result.employee_id || ',"name":"' || v_result.last_name || '","salary":' || v_result.salary || '},';
  END LOOP;
  p_result := RTRIM(p_result, ',') || ']}' ;
  CLOSE v_cursor;
  FOR dept_rec IN dept_cursor LOOP
    UPDATE employees SET salary = COALESCE(salary, 3000) * (1 + p_percent/100) WHERE department_id = dept_rec.department_id;
    GET DIAGNOSTICS v_rows_updated = ROW_COUNT;  -- manual adjustment
    SELECT SUM(salary) INTO v_total_budget FROM employees WHERE department_id = dept_rec.department_id;
    IF v_total_budget > dept_rec.budget THEN
      RAISE EXCEPTION 'Budget exceeded in department %', dept_rec.department_id;
    END IF;
  END LOOP;
  RAISE NOTICE 'Updated % records, Time %', v_rows_updated, NOW();
  RETURN p_result;
EXCEPTION
  WHEN OTHERS THEN
    RAISE EXCEPTION '%', SQLERRM;
END;
$$ LANGUAGE plpgsql;

Two small incompatibilities need manual correction: replace the Oracle‑specific v_rows_updated := v_rows_updated + SQL%ROWCOUNT with GET DIAGNOSTICS v_rows_updated = ROW_COUNT, and change the cursor declaration from CURSOR dept_cursor IS … to dept_cursor CURSOR FOR …. After these edits the function runs successfully on PostgreSQL 9.2.