Pentaho Geek Zone

Abhi SQL - Oracle to PostgreSQL Converter

Oracle SQL/PLSQL

/* Complex Oracle SQL/PLSQL with Enhanced Loops */
CREATE TABLE employees (
    emp_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50) NOT NULL,
    last_name VARCHAR2(50),
    email VARCHAR2(100) UNIQUE,
    salary NUMBER(10,2) DEFAULT 0,
    hire_date DATE,
    notes CLOB,
    photo BLOB,
    dept_id NUMBER,
    CONSTRAINT fk_dept FOREIGN KEY (dept_id) REFERENCES departments(dept_id),
    CONSTRAINT chk_salary CHECK (salary >= 0)
);

CREATE SEQUENCE emp_seq 
    START WITH 1 
    INCREMENT BY 1 
    NOCACHE;

DECLARE
    v_emp_id NUMBER;
    v_count NUMBER := 0;
    v_temp VARCHAR2(10) := '';
    CURSOR emp_cursor IS 
        SELECT emp_id, salary 
        FROM employees 
        WHERE hire_date > TO_DATE('2023-01-01', 'YYYY-MM-DD');
BEGIN
    FOR i IN 1..3 LOOP
        INSERT INTO employees (
            emp_id, first_name, last_name, email, salary, hire_date, dept_id
        ) VALUES (
            emp_seq.NEXTVAL,
            'Employee_' || i,
            'Test',
            'emp' || i || '@example.com',
            NVL(NULL, 5000 + (i * 100)),
            SYSDATE,
            10
        );
        DBMS_OUTPUT.PUT_LINE('Inserted employee ' || i);
    END LOOP;

v_emp_id := 1;
    WHILE v_emp_id <= 5 LOOP
        SELECT COUNT(*) INTO v_count
        FROM employees
        WHERE emp_id = v_emp_id
        AND ROWNUM = 1;
        
        IF v_count > 0 THEN
            DBMS_OUTPUT.PUT_LINE('Found employee ID: ' || v_emp_id);
        END IF;
        v_emp_id := v_emp_id + 1;
    END LOOP;

FOR emp_rec IN emp_cursor LOOP
        v_temp := DECODE(emp_rec.salary,
            5000, 'Low',
            5100, 'Medium',
            'High'
        );
        DBMS_OUTPUT.PUT_LINE('Employee ' || emp_rec.emp_id || ' salary level: ' || v_temp);
    END LOOP;

IF v_temp = '' THEN
        DBMS_OUTPUT.PUT_LINE('Empty string detected');
    END IF;

EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        DBMS_OUTPUT.PUT_LINE('Duplicate email found');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

SELECT 
    emp_id,
    first_name || ' ' || last_name AS full_name,
    LEVEL AS hierarchy_level,
    SYS_CONNECT_BY_PATH(last_name, '/') AS path
FROM employees
START WITH emp_id = 1
CONNECT BY PRIOR emp_id = dept_id;

SELECT 
    e.emp_id,
    e.first_name,
    e.salary,
    (SELECT NVL(MAX(salary), 0) 
     FROM employees 
     WHERE dept_id = e.dept_id) AS max_dept_salary
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM DUAL);

CREATE OR REPLACE TRIGGER emp_audit
BEFORE UPDATE ON employees
FOR EACH ROW
DECLARE
    v_rowid ROWID;
    v_counter NUMBER := 0;
BEGIN
    v_rowid := :OLD.ROWID;
    
    FOR i IN 1..2 LOOP
        INSERT INTO emp_audit_log (
            emp_id,
            old_salary,
            new_salary,
            change_date,
            iteration
        ) VALUES (
            :OLD.emp_id,
            :OLD.salary,
            :NEW.salary,
            SYSDATE,
            i
        );
        v_counter := v_counter + 1;
    END LOOP;
    
    DBMS_OUTPUT.PUT_LINE('Employee ' || :OLD.emp_id || ' updated ' || v_counter || ' times');
END;
/

PostgreSQL Output

Status	Original	Converted

Pentaho Geek Zone

Abhi SQL - Oracle to PostgreSQL Converter

Oracle SQL/PLSQL

PostgreSQL Output

No comments:

Post a Comment

world wide visitor

ora2pg