Oracle Testing Methods

This guide covers Oracle PL/SQL and SQL testing frameworks, best practices, and strategies for writing effective database tests.

🎯 Testing Fundamentals

Testing Pyramid for Oracle

• Unit Tests: Test individual PL/SQL functions and procedures
• Integration Tests: Test database interactions and transactions
• End-to-End Tests: Test complete business workflows
• Performance Tests: Load testing and optimization validation

Testing Principles

• AAA Pattern: Arrange, Act, Assert
• Test Isolation: Each test should be independent
• Data Setup: Use test data management strategies
• Cleanup: Ensure clean test environment

🧪 Unit Testing PL/SQL

UTPLSQL Framework

-- Install UTPLSQL
-- @utplsql/install.sql

-- Create test package
CREATE OR REPLACE PACKAGE test_employee_utils AS
    -- %suite
    -- %test(Calculate bonus correctly)
    PROCEDURE test_calculate_bonus;

    -- %test(Validate email format)
    PROCEDURE test_validate_email;

    -- %test(Handle null salary)
    PROCEDURE test_null_salary_handling;
END test_employee_utils;
/

-- Test package body
CREATE OR REPLACE PACKAGE BODY test_employee_utils AS
    -- %suite
    -- %test(Calculate bonus correctly)
    PROCEDURE test_calculate_bonus IS
        v_bonus employees.salary%TYPE;
        v_expected_bonus employees.salary%TYPE := 500;
    BEGIN
        -- Arrange
        -- Test data setup
        v_bonus := employee_utils.calculate_bonus(10000, 5);

        -- Assert
        ut.expect(v_bonus).to_equal(v_expected_bonus);

        -- %endtest
    END test_calculate_bonus;

    -- %test(Validate email format)
    PROCEDURE test_validate_email IS
        v_result BOOLEAN;
    BEGIN
        -- Arrange
        -- Test valid email
        v_result := employee_utils.validate_email('[email protected]');
        ut.expect(v_result).to_be_true;

        -- Test invalid email
        v_result := employee_utils.validate_email('invalid-email');
        ut.expect(v_result).to_be_false;

        -- %endtest
    END test_validate_email;

    -- %test(Handle null salary)
    PROCEDURE test_null_salary IS
        v_bonus employees.salary%TYPE;
    BEGIN
        -- Arrange
        -- Test null salary
        v_bonus := employee_utils.calculate_bonus(NULL, 5);

        -- Assert
        ut.expect(v_bonus).to_be_null();

        -- %endtest
    END test_null_salary_handling;
END test_employee_utils;
/

-- Run tests
-- @test_employee_utils.sql

Custom Test Framework

-- Simple test framework
CREATE OR REPLACE PROCEDURE run_tests AS
    v_test_count NUMBER := 0;
    v_passed_count NUMBER := 0;
    v_failed_count NUMBER := 0;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Running Oracle Tests...');
    DBMS_OUTPUT.PUT_LINE('================================');

    -- Test 1: Calculate bonus
    v_test_count := v_test_count + 1;
    DECLARE
        v_bonus NUMBER;
        v_expected NUMBER := 500;
    BEGIN
        v_bonus := employee_utils.calculate_bonus(10000, 5);
        IF v_bonus = v_expected THEN
            v_passed_count := v_passed_count + 1;
            DBMS_OUTPUT.PUT_LINE('✓ Test ' || v_test_count || ' PASSED: Calculate bonus');
        ELSE
            v_failed_count := v_failed_count + 1;
            DBMS_OUTPUT.PUT_LINE('✗ Test ' || v_test_count || ' FAILED: Calculate bonus');
            DBMS_OUTPUT.PUT_LINE('  Expected: ' || v_expected || ', Got: ' || v_bonus);
        END IF;
    END;

    -- Test 2: Validate email
    v_test_count := v_test_count + 1;
    DECLARE
        v_result BOOLEAN;
    BEGIN
        v_result := employee_utils.validate_email('[email protected]');
        IF v_result = TRUE THEN
            v_passed_count := v_passed_count + 1;
            DBMS_OUTPUT.PUT_LINE('✓ Test ' || v_test_count || ' PASSED: Validate email');
        ELSE
            v_failed_count := v_failed_count + 1;
            DBMS_OUTPUT.PUT_LINE('✗ Test ' || v_test_count || ' FAILED: Validate email');
        END IF;
    END;

    -- Summary
    DBMS_OUTPUT.PUT_LINE('================================');
    DBMS_OUTPUT.PUT_LINE('Tests Run: ' || v_test_count);
    DBMS_OUTPUT.PUT_LINE('Passed: ' || v_passed_count);
    DBMS_OUTPUT.PUT_LINE('Failed: ' || v_failed_count);
    DBMS_OUTPUT.PUT_LINE('Success Rate: ' || ROUND(v_passed_count/v_test_count*100, 2) || '%');
END run_tests;
/

🎭 Integration Testing

Database Transaction Testing

-- Test transaction rollback
CREATE OR REPLACE PROCEDURE test_transaction_rollback AS
    v_initial_count NUMBER;
    v_after_count NUMBER;
BEGIN
    -- Arrange
    SELECT COUNT(*) INTO v_initial_count FROM test_employees;

    -- Act
    SAVEPOINT test_savepoint;

    INSERT INTO test_employees (employee_id, name, salary)
    VALUES (999, 'Test Employee', 50000);

    -- Rollback the transaction
    ROLLBACK TO test_savepoint;

    -- Assert
    SELECT COUNT(*) INTO v_after_count FROM test_employees;

    IF v_initial_count = v_after_count THEN
        DBMS_OUTPUT.PUT_LINE('✓ Transaction rollback test PASSED');
    ELSE
        DBMS_OUTPUT.PUT_LINE('✗ Transaction rollback test FAILED');
    END IF;
END test_transaction_rollback;
/

-- Test constraint violations
CREATE OR REPLACE PROCEDURE test_constraint_violations AS
BEGIN
    -- Test foreign key constraint
    BEGIN
        INSERT INTO test_employees (employee_id, department_id, name, salary)
        VALUES (1000, 9999, 'Test Employee', 50000);  -- Invalid department_id
        DBMS_OUTPUT.PUT_LINE('✗ Foreign key constraint test FAILED');
    EXCEPTION
    WHEN OTHERS THEN
        IF SQLCODE = -02291 THEN  -- ORA-02291: integrity constraint violated
            DBMS_OUTPUT.PUT_LINE('✓ Foreign key constraint test PASSED');
        ELSE
            DBMS_OUTPUT.PUT_LINE('✗ Foreign key constraint test FAILED: ' || SQLERRM);
        END IF;
    END;
END test_constraint_violations;
/

Data Integrity Testing

-- Test data validation
CREATE OR REPLACE PROCEDURE test_data_validation AS
    v_employee_count NUMBER;
    v_null_count NUMBER;
    v_duplicate_count NUMBER;
BEGIN
    -- Test for null values in required columns
    SELECT COUNT(*) INTO v_null_count
    FROM test_employees
    WHERE name IS NULL OR salary IS NULL;

    IF v_null_count = 0 THEN
        DBMS_OUTPUT.PUT_LINE('✓ Null value validation PASSED');
    ELSE
        DBMS_OUTPUT.PUT_LINE('✗ Null value validation FAILED: ' || v_null_count || ' null values found');
    END IF;

    -- Test for duplicate emails
    SELECT COUNT(*) - COUNT(DISTINCT email) INTO v_duplicate_count
    FROM test_employees
    WHERE email IS NOT NULL;

    IF v_duplicate_count = 0 THEN
        DBMS_OUTPUT.PUT_LINE('✓ Duplicate email validation PASSED');
    ELSE
        DBMS_OUTPUT.PUT_LINE('✗ Duplicate email validation FAILED: ' || v_duplicate_count || ' duplicates found');
    END IF;

    -- Test salary range validation
    SELECT COUNT(*) INTO v_employee_count
    FROM test_employees
    WHERE salary < 0 OR salary > 1000000;

    IF v_employee_count = 0 THEN
        DBMS_OUTPUT.PUT_LINE('✓ Salary range validation PASSED');
    ELSE
        DBMS_OUTPUT.PUT_LINE('✗ Salary range validation FAILED: ' || v_employee_count || ' invalid salaries');
    END IF;
END test_data_validation;
/

📊 Performance Testing

Query Performance Testing

-- Test query execution time
CREATE OR REPLACE PROCEDURE test_query_performance AS
    v_start_time TIMESTAMP;
    v_end_time TIMESTAMP;
    v_elapsed_seconds NUMBER;
    v_execution_count NUMBER := 100;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Testing query performance...');

    v_start_time := SYSTIMESTAMP;

    FOR i IN 1..v_execution_count LOOP
        -- Test query
        SELECT COUNT(*) INTO dummy
        FROM employees e
        JOIN departments d ON e.department_id = d.department_id
        WHERE e.salary > 50000;
    END LOOP;

    v_end_time := SYSTIMESTAMP;
    v_elapsed_seconds := EXTRACT(SECOND FROM (v_end_time - v_start_time));

    DBMS_OUTPUT.PUT_LINE('Query executed ' || v_execution_count || ' times');
    DBMS_OUTPUT.PUT_LINE('Total time: ' || v_elapsed_seconds || ' seconds');
    DBMS_OUTPUT.PUT_LINE('Average time per execution: ' || (v_elapsed_seconds/v_execution_count) || ' seconds');

    -- Performance assertion
    IF v_elapsed_seconds/v_execution_count < 0.1 THEN
        DBMS_OUTPUT.PUT_LINE('✓ Query performance test PASSED');
    ELSE
        DBMS_OUTPUT.PUT_LINE('✗ Query performance test FAILED: Too slow');
    END IF;
END test_query_performance;
/

-- Test index usage
CREATE OR REPLACE PROCEDURE test_index_usage AS
    v_execution_plan VARCHAR2(4000);
    v_index_used BOOLEAN := FALSE;
BEGIN
    -- Get execution plan
    SELECT plan_table_output INTO v_execution_plan
    FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(
        sql_id => (
            SELECT sql_id 
            FROM v$sql 
            WHERE sql_text LIKE '%employees%'
            AND rownum = 1
        ),
        format => 'BASIC'
    ));

    -- Check if index is used
    IF UPPER(v_execution_plan) LIKE '%INDEX%' THEN
        v_index_used := TRUE;
    END IF;

    IF v_index_used THEN
        DBMS_OUTPUT.PUT_LINE('✓ Index usage test PASSED');
    ELSE
        DBMS_OUTPUT.PUT_LINE('✗ Index usage test FAILED: No index used');
    END IF;
END test_index_usage;
/

Load Testing

-- Load testing procedure
CREATE OR REPLACE PROCEDURE load_test_employees AS
    v_batch_size CONSTANT NUMBER := 1000;
    v_total_records NUMBER := 10000;
    v_start_time TIMESTAMP;
    v_end_time TIMESTAMP;
    v_elapsed_seconds NUMBER;
BEGIN
    DBMS_OUTPUT.PUT_LINE('Starting load test...');

    v_start_time := SYSTIMESTAMP;

    FOR i IN 1..(v_total_records/v_batch_size) LOOP
        INSERT INTO test_employees (employee_id, name, email, salary)
        SELECT 
            seq_employee_id.NEXTVAL,
            'Employee ' || (i * v_batch_size + rownum),
            'employee' || (i * v_batch_size + rownum) || '@company.com',
            DBMS_RANDOM.VALUE(30000, 100000)
        FROM dual
        CONNECT BY rownum <= v_batch_size;

        COMMIT;

        IF MOD(i, 10) = 0 THEN
            DBMS_OUTPUT.PUT_LINE('Inserted ' || (i * v_batch_size) || ' records');
        END IF;
    END LOOP;

    v_end_time := SYSTIMESTAMP;
    v_elapsed_seconds := EXTRACT(SECOND FROM (v_end_time - v_start_time));

    DBMS_OUTPUT.PUT_LINE('Load test completed');
    DBMS_OUTPUT.PUT_LINE('Total records: ' || v_total_records);
    DBMS_OUTPUT.PUT_LINE('Total time: ' || v_elapsed_seconds || ' seconds');
    DBMS_OUTPUT.PUT_LINE('Records per second: ' || (v_total_records/v_elapsed_seconds));
END load_test_employees;
/

🛠️ Test Data Management

Test Data Setup

-- Create test data generator
CREATE OR REPLACE PROCEDURE setup_test_data AS
    v_dept_count CONSTANT NUMBER := 10;
    v_emp_per_dept CONSTANT NUMBER := 100;
BEGIN
    -- Clean existing test data
    DELETE FROM test_employees;
    DELETE FROM test_departments;

    -- Insert test departments
    FOR i IN 1..v_dept_count LOOP
        INSERT INTO test_departments (department_id, department_name, location)
        VALUES (i, 'Department ' || i, 'Location ' || i);
    END LOOP;

    -- Insert test employees
    FOR i IN 1..v_dept_count LOOP
        FOR j IN 1..v_emp_per_dept LOOP
            INSERT INTO test_employees (
                employee_id, 
                name, 
                email, 
                salary, 
                department_id,
                hire_date
            ) VALUES (
                (i-1) * v_emp_per_dept + j,
                'Employee ' || (i-1) * v_emp_per_dept + j,
                'emp' || (i-1) * v_emp_per_dept + j || '@company.com',
                DBMS_RANDOM.VALUE(30000, 100000),
                i,
                SYSDATE - DBMS_RANDOM.VALUE(0, 365)
            );
        END LOOP;
    END LOOP;

    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Test data setup completed');
    DBMS_OUTPUT.PUT_LINE('Departments: ' || v_dept_count);
    DBMS_OUTPUT.PUT_LINE('Employees: ' || v_dept_count * v_emp_per_dept);
END setup_test_data;
/

-- Clean test data
CREATE OR REPLACE PROCEDURE cleanup_test_data AS
BEGIN
    DELETE FROM test_employees;
    DELETE FROM test_departments;
    COMMIT;
    DBMS_OUTPUT.PUT_LINE('Test data cleaned up');
END cleanup_test_data;
/

Test Data Validation

-- Validate test data integrity
CREATE OR REPLACE PROCEDURE validate_test_data AS
    v_dept_count NUMBER;
    v_emp_count NUMBER;
    v_orphaned_emps NUMBER;
BEGIN
    -- Check department count
    SELECT COUNT(*) INTO v_dept_count FROM test_departments;

    -- Check employee count
    SELECT COUNT(*) INTO v_emp_count FROM test_employees;

    -- Check for orphaned employees
    SELECT COUNT(*) INTO v_orphaned_emps
    FROM test_employees e
    LEFT JOIN test_departments d ON e.department_id = d.department_id
    WHERE d.department_id IS NULL;

    DBMS_OUTPUT.PUT_LINE('Test Data Validation Results:');
    DBMS_OUTPUT.PUT_LINE('============================');
    DBMS_OUTPUT.PUT_LINE('Departments: ' || v_dept_count);
    DBMS_OUTPUT.PUT_LINE('Employees: ' || v_emp_count);
    DBMS_OUTPUT.PUT_LINE('Orphaned employees: ' || v_orphaned_emps);

    IF v_orphaned_emps = 0 THEN
        DBMS_OUTPUT.PUT_LINE('✓ Test data validation PASSED');
    ELSE
        DBMS_OUTPUT.PUT_LINE('✗ Test data validation FAILED: Orphaned employees found');
    END IF;
END validate_test_data;
/

🔄 Automated Testing

Test Suite Runner

-- Master test suite
CREATE OR REPLACE PROCEDURE run_test_suite AS
    v_test_count NUMBER := 0;
    v_passed_count NUMBER := 0;
    v_failed_count NUMBER := 0;
    v_start_time TIMESTAMP;
    v_end_time TIMESTAMP;
BEGIN
    v_start_time := SYSTIMESTAMP;

    DBMS_OUTPUT.PUT_LINE('Oracle Test Suite');
    DBMS_OUTPUT.PUT_LINE('==================');

    -- Unit Tests
    DBMS_OUTPUT.PUT_LINE('Running Unit Tests...');
    test_employee_utils.test_calculate_bonus;
    v_test_count := v_test_count + 1;

    test_employee_utils.test_validate_email;
    v_test_count := v_test_count + 1;

    test_employee_utils.test_null_salary_handling;
    v_test_count := v_test_count + 1;

    -- Integration Tests
    DBMS_OUTPUT.PUT_LINE('Running Integration Tests...');
    test_transaction_rollback;
    v_test_count := v_test_count + 1;

    test_constraint_violations;
    v_test_count := v_test_count + 1;

    test_data_validation;
    v_test_count := v_test_count + 1;

    -- Performance Tests
    DBMS_OUTPUT.PUT_LINE('Running Performance Tests...');
    test_query_performance;
    v_test_count := v_test_count + 1;

    test_index_usage;
    v_test_count := v_test_count + 1;

    v_end_time := SYSTIMESTAMP;

    -- Summary
    DBMS_OUTPUT.PUT_LINE('==================');
    DBMS_OUTPUT.PUT_LINE('Test Suite Summary:');
    DBMS_OUTPUT.PUT_LINE('Tests Run: ' || v_test_count);
    DBMS_OUTPUT.PUT_LINE('Total Time: ' || EXTRACT(SECOND FROM (v_end_time - v_start_time)) || ' seconds');

    -- Note: In a real implementation, you would track passed/failed counts
    DBMS_OUTPUT.PUT_LINE('Test Suite Completed');
END run_test_suite;
/

Continuous Integration

-- CI/CD test procedure
CREATE OR REPLACE PROCEDURE ci_test_pipeline AS
    v_test_result VARCHAR2(1);
BEGIN
    DBMS_OUTPUT.PUT_LINE('CI/CD Test Pipeline');
    DBMS_OUTPUT.PUT_LINE('==================');

    -- Setup test environment
    DBMS_OUTPUT.PUT_LINE('Setting up test environment...');
    setup_test_data;

    -- Run tests
    DBMS_OUTPUT.PUT_LINE('Running tests...');
    run_test_suite;

    -- Validate results
    DBMS_OUTPUT.PUT_LINE('Validating results...');
    validate_test_data;

    -- Cleanup
    DBMS_OUTPUT.PUT_LINE('Cleaning up...');
    cleanup_test_data;

    -- Set result (in real CI, this would be returned to CI system)
    v_test_result := 'P';

    DBMS_OUTPUT.PUT_LINE('CI/CD Pipeline Completed: ' || v_test_result);
END ci_test_pipeline;
/

📈 Test Reporting

Test Result Logging

-- Create test results table
CREATE TABLE test_results (
    test_id NUMBER GENERATED ALWAYS AS IDENTITY,
    test_name VARCHAR2(100),
    test_suite VARCHAR2(50),
    status VARCHAR2(10),
    execution_time NUMBER,
    error_message VARCHAR2(4000),
    test_date TIMESTAMP DEFAULT SYSDATE
);

-- Test result logger
CREATE OR REPLACE PROCEDURE log_test_result(
    p_test_name VARCHAR2,
    p_test_suite VARCHAR2,
    p_status VARCHAR2,
    p_execution_time NUMBER,
    p_error_message VARCHAR2 DEFAULT NULL
) AS
BEGIN
    INSERT INTO test_results (
        test_name, test_suite, status, execution_time, error_message
    ) VALUES (
        p_test_name, p_test_suite, p_status, p_execution_time, p_error_message
    );

    COMMIT;
END log_test_result;
/

-- Generate test report
CREATE OR REPLACE PROCEDURE generate_test_report AS
    v_total_tests NUMBER;
    v_passed_tests NUMBER;
    v_failed_tests NUMBER;
    v_success_rate NUMBER;
BEGIN
    -- Calculate statistics
    SELECT COUNT(*) INTO v_total_tests FROM test_results;
    SELECT COUNT(*) INTO v_passed_tests FROM test_results WHERE status = 'PASSED';
    SELECT COUNT(*) INTO v_failed_tests FROM test_results WHERE status = 'FAILED';

    v_success_rate := (v_passed_tests / v_total_tests) * 100;

    -- Generate report
    DBMS_OUTPUT.PUT_LINE('Test Execution Report');
    DBMS_OUTPUT.PUT_LINE('====================');
    DBMS_OUTPUT.PUT_LINE('Total Tests: ' || v_total_tests);
    DBMS_OUTPUT.PUT_LINE('Passed: ' || v_passed_tests);
    DBMS_OUTPUT.PUT_LINE('Failed: ' || v_failed_tests);
    DBMS_OUTPUT.PUT_LINE('Success Rate: ' || ROUND(v_success_rate, 2) || '%');
    DBMS_OUTPUT.PUT_LINE('====================');

    -- Show failed tests
    IF v_failed_tests > 0 THEN
        DBMS_OUTPUT.PUT_LINE('Failed Tests:');
        DBMS_OUTPUT.PUT_LINE('-------------');
        FOR rec IN (SELECT test_name, error_message FROM test_results WHERE status = 'FAILED') LOOP
            DBMS_OUTPUT.PUT_LINE(rec.test_name || ': ' || rec.error_message);
        END LOOP;
    END IF;
END generate_test_report;
/

🎯 Testing Best Practices

Test Organization

-- Test naming conventions
-- test_<module>_<functionality>
-- test_<module>_<functionality>_edge_case
-- test_<module>_<functionality>_performance

-- Test structure template
CREATE OR REPLACE PROCEDURE test_template AS
    -- %suite
    -- %test(Descriptive test name)
    PROCEDURE test_descriptive_name AS
        -- Arrange: Set up test data and conditions
        -- Act: Execute the code being tested
        -- Assert: Verify expected results
        -- %endtest
    END test_descriptive_name;
END test_template;
/

Data Management

-- Use transactions for test isolation
CREATE OR REPLACE PROCEDURE test_with_transaction AS
    PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    -- Test code here

    -- Rollback changes
    ROLLBACK;
END test_with_transaction;
/

-- Use savepoints for partial rollback
CREATE OR REPLACE PROCEDURE test_with_savepoint AS
BEGIN
    SAVEPOINT test_start;

    -- Test code here

    -- Rollback to savepoint if needed
    ROLLBACK TO test_start;
END test_with_savepoint;
/

📚 Related Resources

• Oracle Best Practices - Write testable code
• Oracle Common Mistakes - Avoid testing pitfalls
• Oracle Performance Tips - Performance testing
• Oracle Resources - Testing tools and documentation

🔗 Related Testing Guides

• Testing Strategies - General testing approaches
• Code Review Checklist - Review test quality
• Self-Assessment Techniques - Assess testing skills

🔗 Language-Specific Testing

• Java Testing Frameworks - Java testing
• Python Testing Frameworks - Python testing
• C Testing Methods - C testing