Oracle PL/SQL Common Mistakes¶

This guide covers common Oracle PL/SQL programming mistakes and how to avoid them, with practical solutions and examples.

🚨 Most Common Oracle Errors¶

1. SQL Injection¶

Using string concatenation in dynamic SQL leading to security vulnerabilities.

Problem¶

-- Bad: Vulnerable to SQL injection
CREATE OR REPLACE PROCEDURE get_student_by_name(
    p_name IN VARCHAR2
) AS
    v_sql VARCHAR2(1000);
    v_student students%ROWTYPE;
BEGIN
    v_sql := 'SELECT * FROM students WHERE name = ''' || p_name || '''';
    EXECUTE IMMEDIATE v_sql INTO v_student;

    DBMS_OUTPUT.PUT_LINE('Student: ' || v_student.name);
END;
/

-- Vulnerable input: ' OR '1'='1
-- This would return all students!

Solutions¶

-- Good: Use bind variables
CREATE OR REPLACE PROCEDURE get_student_by_name_safe(
    p_name IN students.name%TYPE
) AS
    v_sql VARCHAR2(1000);
    v_student students%ROWTYPE;
BEGIN
    v_sql := 'SELECT * FROM students WHERE name = :student_name';
    EXECUTE IMMEDIATE v_sql INTO v_student 
        USING p_name;

    DBMS_OUTPUT.PUT_LINE('Student: ' || v_student.name);
END;
/

-- Better: Use static SQL when possible
CREATE OR REPLACE PROCEDURE get_student_by_name_static(
    p_name IN students.name%TYPE
) AS
    v_student students%ROWTYPE;
BEGIN
    SELECT * INTO v_student
    FROM students
    WHERE name = p_name;

    DBMS_OUTPUT.PUT_LINE('Student: ' || v_student.name);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Student not found: ' || p_name);
END;
/

2. Cursor Memory Leaks¶

Not closing cursors properly or leaving them open.

Problem¶

-- Bad: Cursor not closed
CREATE OR REPLACE PROCEDURE process_students_bad AS
    CURSOR student_cursor IS
        SELECT student_id, name FROM students;

    v_student student_cursor%ROWTYPE;
BEGIN
    OPEN student_cursor;
    FETCH student_cursor INTO v_student;

    -- Process student
    DBMS_OUTPUT.PUT_LINE('Student: ' || v_student.name);

    -- Forgot to close cursor!
END;
/

-- Bad: Exception without cleanup
CREATE OR REPLACE PROCEDURE process_students_exception AS
    CURSOR student_cursor IS
        SELECT student_id, name FROM students;

    v_student student_cursor%ROWTYPE;
BEGIN
    OPEN student_cursor;
    LOOP
        FETCH student_cursor INTO v_student;
        EXIT WHEN student_cursor%NOTFOUND;

        -- This might raise an exception
        IF v_student.student_id = 999 THEN
            RAISE_APPLICATION_ERROR(-20001, 'Invalid student ID');
        END IF;

        DBMS_OUTPUT.PUT_LINE('Student: ' || v_student.name);
    END LOOP;

    CLOSE student_cursor;
END;
/

Solutions¶

-- Good: Proper cursor management
CREATE OR REPLACE PROCEDURE process_students_good AS
    CURSOR student_cursor IS
        SELECT student_id, name FROM students;

    v_student student_cursor%ROWTYPE;
BEGIN
    OPEN student_cursor;
    LOOP
        FETCH student_cursor INTO v_student;
        EXIT WHEN student_cursor%NOTFOUND;

        DBMS_OUTPUT.PUT_LINE('Student: ' || v_student.name);
    END LOOP;

    CLOSE student_cursor;
END;
/

-- Better: Exception handling with cleanup
CREATE OR REPLACE PROCEDURE process_students_safe AS
    CURSOR student_cursor IS
        SELECT student_id, name FROM students;

    v_student student_cursor%ROWTYPE;
BEGIN
    OPEN student_cursor;
    LOOP
        FETCH student_cursor INTO v_student;
        EXIT WHEN student_cursor%NOTFOUND;

        IF v_student.student_id = 999 THEN
            RAISE_APPLICATION_ERROR(-20001, 'Invalid student ID');
        END IF;

        DBMS_OUTPUT.PUT_LINE('Student: ' || v_student.name);
    END LOOP;

    CLOSE student_cursor;

EXCEPTION
    WHEN OTHERS THEN
        IF student_cursor%ISOPEN THEN
            CLOSE student_cursor;
        END IF;
        RAISE;
END;
/

-- Best: Use implicit cursor
CREATE OR REPLACE PROCEDURE process_students_implicit AS
BEGIN
    FOR v_student IN (SELECT student_id, name FROM students) LOOP
        IF v_student.student_id = 999 THEN
            RAISE_APPLICATION_ERROR(-20001, 'Invalid student ID');
        END IF;

        DBMS_OUTPUT.PUT_LINE('Student: ' || v_student.name);
    END LOOP;
END;
/

3. Transaction Management Errors¶

Improper commit/rollback handling leading to data inconsistency.

Problem¶

-- Bad: Commit in loop (performance issues)
CREATE OR REPLACE PROCEDURE update_grades_bad AS
BEGIN
    FOR v_student IN (SELECT student_id FROM students) LOOP
        UPDATE grades
        SET grade = 'A'
        WHERE student_id = v_student.student_id;

        COMMIT;  -- Bad: Commit in loop!
    END LOOP;
END;
/

-- Bad: No transaction management
CREATE OR REPLACE PROCEDURE transfer_funds_bad(
    p_from_account IN NUMBER,
    p_to_account IN NUMBER,
    p_amount IN NUMBER
) AS
BEGIN
    UPDATE accounts
    SET balance = balance - p_amount
    WHERE account_id = p_from_account;

    -- If this fails, first update remains committed!
    UPDATE accounts
    SET balance = balance + p_amount
    WHERE account_id = p_to_account;

    COMMIT;
END;
/

Solutions¶

-- Good: Single transaction
CREATE OR REPLACE PROCEDURE update_grades_good AS
BEGIN
    FOR v_student IN (SELECT student_id FROM students) LOOP
        UPDATE grades
        SET grade = 'A'
        WHERE student_id = v_student.student_id;
    END LOOP;

    COMMIT;  -- Single commit after all updates
END;
/

-- Better: Batch processing for large datasets
CREATE OR REPLACE PROCEDURE update_grades_batch AS
    v_batch_size CONSTANT NUMBER := 1000;
    v_processed NUMBER := 0;
BEGIN
    FOR v_student IN (SELECT student_id FROM students) LOOP
        UPDATE grades
        SET grade = 'A'
        WHERE student_id = v_student.student_id;

        v_processed := v_processed + 1;

        IF MOD(v_processed, v_batch_size) = 0 THEN
            COMMIT;  -- Commit every 1000 records
        END IF;
    END LOOP;

    IF MOD(v_processed, v_batch_size) != 0 THEN
        COMMIT;  -- Final commit
    END IF;
END;
/

-- Best: Proper transaction management
CREATE OR REPLACE PROCEDURE transfer_funds_safe(
    p_from_account IN NUMBER,
    p_to_account IN NUMBER,
    p_amount IN NUMBER
) AS
    v_from_balance NUMBER;
    v_to_balance NUMBER;
BEGIN
    -- Start transaction
    SAVEPOINT start_transfer;

    -- Check balances
    SELECT balance INTO v_from_balance
    FROM accounts
    WHERE account_id = p_from_account;

    IF v_from_balance < p_amount THEN
        RAISE_APPLICATION_ERROR(-20001, 'Insufficient funds');
    END IF;

    -- Perform transfers
    UPDATE accounts
    SET balance = balance - p_amount
    WHERE account_id = p_from_account;

    UPDATE accounts
    SET balance = balance + p_amount
    WHERE account_id = p_to_account;

    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK TO start_transfer;
        DBMS_OUTPUT.PUT_LINE('Transfer failed: ' || SQLERRM);
END;
/

4. Performance Issues¶

Inefficient queries and poor index usage.

Problem¶

-- Bad: Functions on indexed columns
CREATE OR REPLACE PROCEDURE get_students_by_name_bad AS
    v_student students%ROWTYPE;
BEGIN
    FOR v_student IN (
        SELECT * FROM students
        WHERE UPPER(name) = 'JOHN SMITH'  -- Can't use index on name
    ) LOOP
        DBMS_OUTPUT.PUT_LINE('Student: ' || v_student.name);
    END LOOP;
END;
/

-- Bad: Row-by-row processing
CREATE OR REPLACE PROCEDURE calculate_totals_bad AS
    v_total NUMBER;
BEGIN
    FOR v_student IN (SELECT student_id FROM students) LOOP
        SELECT SUM(amount) INTO v_total
        FROM payments
        WHERE student_id = v_student.student_id;

        UPDATE students
        SET total_payments = v_total
        WHERE student_id = v_student.student_id;
    END LOOP;
END;
/

Solutions¶

-- Good: Use indexes effectively
CREATE OR REPLACE PROCEDURE get_students_by_name_good AS
    v_student students%ROWTYPE;
BEGIN
    FOR v_student IN (
        SELECT * FROM students
        WHERE name = 'John Smith'  -- Can use index on name
    ) LOOP
        DBMS_OUTPUT.PUT_LINE('Student: ' || v_student.name);
    END LOOP;
END;
/

-- Better: Function-based index
CREATE INDEX idx_students_upper_name ON students(UPPER(name));

CREATE OR REPLACE PROCEDURE get_students_by_name_indexed AS
    v_student students%ROWTYPE;
BEGIN
    FOR v_student IN (
        SELECT * FROM students
        WHERE UPPER(name) = 'JOHN SMITH'  -- Can use function-based index
    ) LOOP
        DBMS_OUTPUT.PUT_LINE('Student: ' || v_student.name);
    END LOOP;
END;
/

-- Best: Bulk operations
CREATE OR REPLACE PROCEDURE calculate_totals_bulk AS
BEGIN
    UPDATE students s
    SET total_payments = (
        SELECT SUM(p.amount)
        FROM payments p
        WHERE p.student_id = s.student_id
    );

    COMMIT;
END;
/

5. Exception Handling Errors¶

Poor exception handling leading to silent failures or information leakage.

Problem¶

-- Bad: Catching all exceptions
CREATE OR REPLACE PROCEDURE process_student_bad(
    p_student_id IN NUMBER
) AS
    v_student students%ROWTYPE;
BEGIN
    SELECT * INTO v_student
    FROM students
    WHERE student_id = p_student_id;

    -- Process student
    DBMS_OUTPUT.PUT_LINE('Student: ' || v_student.name);

EXCEPTION
    WHEN OTHERS THEN
        NULL;  -- Silent failure!
END;
/

-- Bad: Information leakage
CREATE OR REPLACE PROCEDURE login_user_bad(
    p_username IN VARCHAR2,
    p_password IN VARCHAR2
) AS
    v_user_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_user_count
    FROM users
    WHERE username = p_username AND password = p_password;

    IF v_user_count = 1 THEN
        DBMS_OUTPUT.PUT_LINE('Login successful');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Login failed: User ' || p_username || ' not found');
    END IF;
END;
/

Solutions¶

-- Good: Specific exception handling
CREATE OR REPLACE PROCEDURE process_student_good(
    p_student_id IN NUMBER
) AS
    v_student students%ROWTYPE;
BEGIN
    SELECT * INTO v_student
    FROM students
    WHERE student_id = p_student_id;

    DBMS_OUTPUT.PUT_LINE('Student: ' || v_student.name);

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Student not found: ' || p_student_id);
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Multiple students found: ' || p_student_id);
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error processing student: ' || SQLERRM);
        RAISE;
END;
/

-- Better: Secure error messages
CREATE OR REPLACE PROCEDURE login_user_secure(
    p_username IN VARCHAR2,
    p_password IN VARCHAR2
) AS
    v_user_count NUMBER;
BEGIN
    SELECT COUNT(*) INTO v_user_count
    FROM users
    WHERE username = p_username AND password = p_password;

    IF v_user_count = 1 THEN
        DBMS_OUTPUT.PUT_LINE('Login successful');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Invalid username or password');
    END IF;

EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Login system error');
        -- Log actual error for debugging
        INSERT INTO error_log (error_time, error_message)
        VALUES (SYSDATE, SQLERRM);
END;
/

🔧 Data Type Mistakes¶

6. Implicit Type Conversion¶

Relying on implicit type conversions leading to unexpected behavior.

Problem¶

-- Bad: Implicit conversion
CREATE OR REPLACE PROCEDURE process_numbers_bad AS
    v_number VARCHAR2(10) := '123';
    v_result NUMBER;
BEGIN
    v_result := v_number + 456;  -- Implicit conversion
    DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
END;
/

Solutions¶

-- Good: Explicit conversion
CREATE OR REPLACE PROCEDURE process_numbers_good AS
    v_number VARCHAR2(10) := '123';
    v_result NUMBER;
BEGIN
    v_result := TO_NUMBER(v_number) + 456;  -- Explicit conversion
    DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
END;
/

🛠️ Debugging Tips¶

Common Debugging Strategies¶

DBMS_OUTPUT: Add debug output statements
Exception Logging: Log errors for later analysis
SQL Trace: Use Oracle's trace facilities
Explain Plan: Analyze query performance
Error Tables: Create custom error logging

Debugging Checklist¶

Use bind variables instead of string concatenation
Close all cursors properly
Handle exceptions appropriately
Use proper transaction management
Optimize queries for index usage
Avoid row-by-row processing
Use explicit type conversions
Log errors for debugging

Oracle Best Practices - Prevent these mistakes with good practices
Oracle Performance Tips - Optimize your PL/SQL code
Oracle Testing Methods - Testing strategies
Oracle Resources - Learning materials and tools

🔗 Common Programming Mistakes¶

Logic Errors - General logic mistakes
Runtime Errors - Common runtime issues
SQL Injection - Security best practices

Oracle PL/SQL Common Mistakes¶

🚨 Most Common Oracle Errors¶

1. SQL Injection¶

Problem¶

Solutions¶

2. Cursor Memory Leaks¶

Problem¶

Solutions¶

3. Transaction Management Errors¶

Problem¶

Solutions¶

4. Performance Issues¶

Problem¶

Solutions¶

5. Exception Handling Errors¶

Problem¶

Solutions¶

🔧 Data Type Mistakes¶

6. Implicit Type Conversion¶

Problem¶

Solutions¶

🛠️ Debugging Tips¶

Common Debugging Strategies¶

Debugging Checklist¶

📚 Related Resources¶

🔗 Common Programming Mistakes¶