ORA-01476 Divisor Is Equal to Zero - Fix Division by Zero Errors

ORA-01476: Divisor Is Equal to Zero

Error Overview

Error Text: ORA-01476: divisor is equal to zero

This error occurs when a SQL statement attempts to divide a number by zero, which is mathematically undefined. It commonly happens in calculations involving ratios, percentages, averages, or any division operation where the denominator can potentially be zero.

Understanding Division Operations

Division Contexts in Oracle

Division Operations
├── Direct Division (/)
├── MOD and REMAINDER functions
├── Ratio Calculations
├── Percentage Computations
├── Statistical Functions (STDDEV, VARIANCE)
└── Analytical Functions with division

Common Scenarios

• Zero denominators - Direct division by zero values
• NULL confusion - NULLs in calculations vs zeros
• Aggregate results - SUM or COUNT returning zero
• Dynamic calculations - User input or calculated denominators
• Edge cases - Empty result sets or filtered data

Diagnostic Steps

1. Identify the Division Operation

-- Find queries with division operations in V$SQL
SELECT
  sql_id,
  sql_text,
  executions,
  CASE
    WHEN INSTR(sql_text, '/') > 0 THEN 'Contains division'
    ELSE 'No division found'
  END as has_division,
  error_count
FROM v$sql
WHERE (sql_text LIKE '%/%' OR sql_text LIKE '%MOD%' OR sql_text LIKE '%RATIO%')
  AND parsing_schema_name = USER
  AND last_active_time > SYSDATE - 1
ORDER BY last_active_time DESC;

-- Check for zero values in commonly used denominator columns
SELECT
  table_name,
  column_name,
  num_distinct,
  num_nulls,
  low_value,
  high_value
FROM user_tab_columns
WHERE column_name LIKE '%TOTAL%'
   OR column_name LIKE '%COUNT%'
   OR column_name LIKE '%QUANTITY%'
   OR column_name LIKE '%AMOUNT%'
ORDER BY table_name, column_name;

-- Find actual zero values in numeric columns
DECLARE
  v_count NUMBER;
  v_sql VARCHAR2(4000);
BEGIN
  FOR col IN (
    SELECT table_name, column_name
    FROM user_tab_columns
    WHERE data_type IN ('NUMBER', 'FLOAT', 'INTEGER')
      AND table_name NOT LIKE '%$%'  -- Skip system tables
  ) LOOP
    v_sql := 'SELECT COUNT(*) FROM ' || col.table_name ||
             ' WHERE ' || col.column_name || ' = 0';

    BEGIN
      EXECUTE IMMEDIATE v_sql INTO v_count;
      IF v_count > 0 THEN
        DBMS_OUTPUT.PUT_LINE(col.table_name || '.' || col.column_name ||
                           ' has ' || v_count || ' zero values');
      END IF;
    EXCEPTION
      WHEN OTHERS THEN
        NULL;  -- Skip errors
    END;
  END LOOP;
END;
/

2. Analyze Problem Queries

-- Test query with potential division by zero
CREATE OR REPLACE PROCEDURE test_division_safety(
  p_sql VARCHAR2
) AS
  v_cursor SYS_REFCURSOR;
  v_error_msg VARCHAR2(4000);
BEGIN
  BEGIN
    OPEN v_cursor FOR p_sql;
    CLOSE v_cursor;
    DBMS_OUTPUT.PUT_LINE('Query executed successfully');
  EXCEPTION
    WHEN OTHERS THEN
      IF SQLCODE = -1476 THEN
        DBMS_OUTPUT.PUT_LINE('ERROR: Division by zero detected');
        DBMS_OUTPUT.PUT_LINE('SQL: ' || p_sql);

        -- Try to identify the problematic part
        IF INSTR(p_sql, '/') > 0 THEN
          DBMS_OUTPUT.PUT_LINE('Division operator found at position: ' ||
                             INSTR(p_sql, '/'));
        END IF;
      ELSE
        DBMS_OUTPUT.PUT_LINE('Other error: ' || SQLERRM);
      END IF;
  END;
END;
/

-- Find zero denominators in specific calculation
SELECT
  employee_id,
  salary,
  commission_pct,
  salary / commission_pct as problem_calculation  -- Will fail if commission_pct = 0
FROM employees
WHERE commission_pct = 0;  -- Find the problematic rows

-- Identify aggregate calculations that might produce zero
SELECT
  department_id,
  COUNT(*) as emp_count,
  SUM(salary) as total_salary,
  SUM(commission_pct) as total_commission,
  CASE
    WHEN COUNT(*) = 0 THEN 'Zero count - division risk'
    WHEN SUM(salary) = 0 THEN 'Zero sum - division risk'
    ELSE 'Safe for division'
  END as division_safety
FROM employees
GROUP BY department_id
HAVING COUNT(*) = 0 OR SUM(salary) = 0;

3. Debug Complex Calculations

-- Break down complex calculations to find division by zero
WITH calculation_breakdown AS (
  SELECT
    department_id,
    COUNT(*) as emp_count,
    SUM(salary) as total_salary,
    AVG(salary) as avg_salary,
    SUM(CASE WHEN commission_pct IS NOT NULL THEN 1 ELSE 0 END) as commission_count
  FROM employees
  GROUP BY department_id
)
SELECT
  department_id,
  emp_count,
  total_salary,
  commission_count,
  -- Show each calculation step
  CASE
    WHEN commission_count = 0 THEN 'Division by zero risk'
    ELSE 'Safe: ' || TO_CHAR(total_salary / commission_count)
  END as avg_commission_salary
FROM calculation_breakdown
ORDER BY department_id;

-- Trace calculation steps
CREATE OR REPLACE FUNCTION debug_division(
  p_numerator NUMBER,
  p_denominator NUMBER,
  p_description VARCHAR2 DEFAULT NULL
) RETURN NUMBER AS
  v_result NUMBER;
BEGIN
  DBMS_OUTPUT.PUT_LINE('=== Division Debug ===');
  DBMS_OUTPUT.PUT_LINE('Description: ' || NVL(p_description, 'N/A'));
  DBMS_OUTPUT.PUT_LINE('Numerator: ' || NVL(TO_CHAR(p_numerator), 'NULL'));
  DBMS_OUTPUT.PUT_LINE('Denominator: ' || NVL(TO_CHAR(p_denominator), 'NULL'));

  IF p_denominator = 0 THEN
    DBMS_OUTPUT.PUT_LINE('ERROR: Denominator is zero!');
    RAISE_APPLICATION_ERROR(-20001, 'Division by zero in: ' || p_description);
  ELSIF p_denominator IS NULL THEN
    DBMS_OUTPUT.PUT_LINE('Warning: Denominator is NULL');
    RETURN NULL;
  ELSE
    v_result := p_numerator / p_denominator;
    DBMS_OUTPUT.PUT_LINE('Result: ' || v_result);
    RETURN v_result;
  END IF;
END;
/

Immediate Solutions

Solution 1: Safe Division Techniques

Use NULLIF to Prevent Division by Zero

-- Basic safe division using NULLIF
SELECT
  employee_id,
  salary,
  commission_pct,
  -- Safe division - returns NULL if denominator is zero
  salary / NULLIF(commission_pct, 0) as safe_calculation
FROM employees;

-- With default value for NULL results
SELECT
  employee_id,
  salary,
  commission_pct,
  -- Returns 0 when division by zero would occur
  NVL(salary / NULLIF(commission_pct, 0), 0) as safe_with_default
FROM employees;

-- Using COALESCE for multiple fallbacks
SELECT
  department_id,
  total_sales,
  total_orders,
  COALESCE(
    total_sales / NULLIF(total_orders, 0),  -- Try division
    total_sales,                             -- If zero orders, return sales
    0                                        -- Final fallback
  ) as avg_order_value
FROM sales_summary;

Use CASE Statements for Conditional Division

-- Explicit checking with CASE
SELECT
  department_id,
  total_revenue,
  total_costs,
  CASE
    WHEN total_costs = 0 THEN NULL  -- or a default value
    WHEN total_costs IS NULL THEN NULL
    ELSE total_revenue / total_costs
  END as profit_ratio
FROM financial_summary;

-- With meaningful messages
SELECT
  product_id,
  units_sold,
  days_on_market,
  CASE
    WHEN days_on_market = 0 THEN 'New product'
    WHEN days_on_market IS NULL THEN 'No data'
    ELSE TO_CHAR(units_sold / days_on_market, '999.99') || ' units/day'
  END as sales_rate
FROM product_metrics;

-- Multiple conditions
SELECT
  store_id,
  total_sales,
  customer_count,
  employee_count,
  CASE
    WHEN customer_count = 0 AND employee_count = 0 THEN 'Store closed'
    WHEN customer_count = 0 THEN 'No customers'
    WHEN employee_count = 0 THEN 'No staff'
    ELSE TO_CHAR(total_sales / customer_count, '$999,999.99')
  END as avg_transaction,
  CASE
    WHEN employee_count > 0 THEN total_sales / employee_count
    ELSE 0
  END as sales_per_employee
FROM store_metrics;

Solution 2: Create Safe Division Functions

Reusable Safe Division Function

-- Create a safe division function
CREATE OR REPLACE FUNCTION safe_divide(
  p_numerator NUMBER,
  p_denominator NUMBER,
  p_default NUMBER DEFAULT NULL
) RETURN NUMBER
DETERMINISTIC AS
BEGIN
  IF p_denominator IS NULL OR p_denominator = 0 THEN
    RETURN p_default;
  ELSE
    RETURN p_numerator / p_denominator;
  END IF;
END safe_divide;
/

-- Usage examples
SELECT
  employee_id,
  salary,
  commission_pct,
  safe_divide(salary, commission_pct, 0) as commission_ratio,
  safe_divide(100, 0, -1) as test_zero,
  safe_divide(100, NULL, -999) as test_null
FROM employees;

-- Enhanced version with options
CREATE OR REPLACE FUNCTION safe_divide_enhanced(
  p_numerator NUMBER,
  p_denominator NUMBER,
  p_options VARCHAR2 DEFAULT 'DEFAULT_NULL'
) RETURN NUMBER
DETERMINISTIC AS
  v_result NUMBER;
BEGIN
  -- Handle various options
  IF p_denominator IS NULL OR p_denominator = 0 THEN
    CASE p_options
      WHEN 'DEFAULT_NULL' THEN
        RETURN NULL;
      WHEN 'DEFAULT_ZERO' THEN
        RETURN 0;
      WHEN 'DEFAULT_ONE' THEN
        RETURN 1;
      WHEN 'DEFAULT_NUMERATOR' THEN
        RETURN p_numerator;
      WHEN 'RAISE_ERROR' THEN
        RAISE_APPLICATION_ERROR(-20001, 'Division by zero attempted');
      ELSE
        RETURN TO_NUMBER(p_options);  -- Use option as default value
    END CASE;
  END IF;

  RETURN p_numerator / p_denominator;
END safe_divide_enhanced;
/

Solution 3: Handle Aggregate Division

Safe Aggregate Calculations

-- Safe average calculation
SELECT
  department_id,
  SUM(salary) as total_salary,
  COUNT(*) as emp_count,
  -- Safe average
  CASE
    WHEN COUNT(*) > 0 THEN SUM(salary) / COUNT(*)
    ELSE 0
  END as avg_salary_safe,
  -- Using AVG function (already NULL-safe)
  AVG(salary) as avg_salary_builtin
FROM employees
GROUP BY department_id;

-- Percentage calculations with safety
SELECT
  department_id,
  COUNT(*) as dept_count,
  SUM(COUNT(*)) OVER () as total_count,
  -- Safe percentage calculation
  CASE
    WHEN SUM(COUNT(*)) OVER () > 0 THEN
      ROUND(100 * COUNT(*) / SUM(COUNT(*)) OVER (), 2)
    ELSE 0
  END as percentage
FROM employees
GROUP BY department_id;

-- Ratio calculations with NULL handling
WITH dept_stats AS (
  SELECT
    department_id,
    COUNT(*) as emp_count,
    COUNT(CASE WHEN commission_pct IS NOT NULL THEN 1 END) as commission_count
  FROM employees
  GROUP BY department_id
)
SELECT
  department_id,
  emp_count,
  commission_count,
  ROUND(safe_divide(commission_count, emp_count, 0) * 100, 2) as commission_percentage
FROM dept_stats;

Long-Term Solutions

1. Implement Data Validation

Add Constraints to Prevent Zero Values

-- Add check constraint to prevent zero values
ALTER TABLE financial_metrics
ADD CONSTRAINT chk_positive_denominators
CHECK (
  total_orders > 0 AND
  total_customers > 0 AND
  days_active > 0
);

-- Create trigger to validate before insert/update
CREATE OR REPLACE TRIGGER validate_denominators
BEFORE INSERT OR UPDATE ON calculation_table
FOR EACH ROW
BEGIN
  -- Validate denominators
  IF :NEW.denominator_column = 0 THEN
    RAISE_APPLICATION_ERROR(-20001,
      'Denominator cannot be zero for calculations');
  END IF;

  -- Convert zeros to NULLs for safety
  IF :NEW.optional_denominator = 0 THEN
    :NEW.optional_denominator := NULL;
  END IF;
END;
/

-- Create validation function
CREATE OR REPLACE FUNCTION validate_calculation_inputs(
  p_table_name VARCHAR2,
  p_denominator_column VARCHAR2
) RETURN VARCHAR2 AS
  v_zero_count NUMBER;
  v_null_count NUMBER;
  v_sql VARCHAR2(4000);
BEGIN
  -- Count zeros
  v_sql := 'SELECT COUNT(*) FROM ' || p_table_name ||
           ' WHERE ' || p_denominator_column || ' = 0';
  EXECUTE IMMEDIATE v_sql INTO v_zero_count;

  -- Count NULLs
  v_sql := 'SELECT COUNT(*) FROM ' || p_table_name ||
           ' WHERE ' || p_denominator_column || ' IS NULL';
  EXECUTE IMMEDIATE v_sql INTO v_null_count;

  IF v_zero_count > 0 THEN
    RETURN 'WARNING: ' || v_zero_count || ' zero values found';
  ELSIF v_null_count > 0 THEN
    RETURN 'INFO: ' || v_null_count || ' NULL values found';
  ELSE
    RETURN 'OK: No zero or NULL values';
  END IF;
END;
/

2. Create Calculation Framework

Build Robust Calculation Package

CREATE OR REPLACE PACKAGE calculation_utils AS

  -- Constants for handling division by zero
  C_DIV_BY_ZERO_NULL CONSTANT NUMBER := NULL;
  C_DIV_BY_ZERO_ZERO CONSTANT NUMBER := 0;
  C_DIV_BY_ZERO_MAX CONSTANT NUMBER := 999999999;

  -- Safe division functions
  FUNCTION divide(
    p_numerator NUMBER,
    p_denominator NUMBER,
    p_precision NUMBER DEFAULT 2
  ) RETURN NUMBER;

  FUNCTION percentage(
    p_part NUMBER,
    p_whole NUMBER,
    p_precision NUMBER DEFAULT 2
  ) RETURN NUMBER;

  FUNCTION ratio(
    p_value1 NUMBER,
    p_value2 NUMBER,
    p_format VARCHAR2 DEFAULT '999,999.99'
  ) RETURN VARCHAR2;

  -- Validation functions
  FUNCTION is_safe_denominator(p_value NUMBER) RETURN BOOLEAN;

  PROCEDURE validate_calculation(
    p_expression VARCHAR2,
    p_result OUT NUMBER,
    p_error OUT VARCHAR2
  );

END calculation_utils;
/

CREATE OR REPLACE PACKAGE BODY calculation_utils AS

  FUNCTION divide(
    p_numerator NUMBER,
    p_denominator NUMBER,
    p_precision NUMBER DEFAULT 2
  ) RETURN NUMBER AS
  BEGIN
    IF p_denominator IS NULL OR p_denominator = 0 THEN
      RETURN C_DIV_BY_ZERO_NULL;
    END IF;

    RETURN ROUND(p_numerator / p_denominator, p_precision);
  END divide;

  FUNCTION percentage(
    p_part NUMBER,
    p_whole NUMBER,
    p_precision NUMBER DEFAULT 2
  ) RETURN NUMBER AS
  BEGIN
    IF p_whole IS NULL OR p_whole = 0 THEN
      RETURN 0;
    END IF;

    RETURN ROUND(100 * p_part / p_whole, p_precision);
  END percentage;

  FUNCTION ratio(
    p_value1 NUMBER,
    p_value2 NUMBER,
    p_format VARCHAR2 DEFAULT '999,999.99'
  ) RETURN VARCHAR2 AS
    v_ratio NUMBER;
  BEGIN
    IF p_value2 IS NULL OR p_value2 = 0 THEN
      RETURN 'N/A';
    END IF;

    v_ratio := p_value1 / p_value2;
    RETURN TO_CHAR(v_ratio, p_format);
  EXCEPTION
    WHEN OTHERS THEN
      RETURN 'Error';
  END ratio;

  FUNCTION is_safe_denominator(p_value NUMBER) RETURN BOOLEAN AS
  BEGIN
    RETURN (p_value IS NOT NULL AND p_value != 0);
  END is_safe_denominator;

  PROCEDURE validate_calculation(
    p_expression VARCHAR2,
    p_result OUT NUMBER,
    p_error OUT VARCHAR2
  ) AS
  BEGIN
    -- Use dynamic SQL to evaluate expression
    EXECUTE IMMEDIATE 'SELECT ' || p_expression || ' FROM dual' INTO p_result;
    p_error := NULL;
  EXCEPTION
    WHEN OTHERS THEN
      p_result := NULL;
      IF SQLCODE = -1476 THEN
        p_error := 'Division by zero in expression: ' || p_expression;
      ELSE
        p_error := SQLERRM;
      END IF;
  END validate_calculation;

END calculation_utils;
/

3. Monitoring and Logging

Track Division by Zero Errors

-- Create error logging table
CREATE TABLE division_error_log (
  error_id NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  error_date TIMESTAMP DEFAULT SYSTIMESTAMP,
  username VARCHAR2(30) DEFAULT USER,
  sql_text CLOB,
  numerator NUMBER,
  denominator NUMBER,
  context VARCHAR2(200),
  resolved CHAR(1) DEFAULT 'N'
);

-- Create logging procedure
CREATE OR REPLACE PROCEDURE log_division_error(
  p_sql_text VARCHAR2,
  p_numerator NUMBER DEFAULT NULL,
  p_denominator NUMBER DEFAULT NULL,
  p_context VARCHAR2 DEFAULT NULL
) AS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO division_error_log (
    sql_text, numerator, denominator, context
  ) VALUES (
    p_sql_text, p_numerator, p_denominator, p_context
  );
  COMMIT;
END;
/

-- Create monitoring view
CREATE OR REPLACE VIEW division_error_summary AS
SELECT
  TRUNC(error_date) as error_day,
  username,
  COUNT(*) as error_count,
  COUNT(DISTINCT sql_text) as unique_queries,
  COUNT(CASE WHEN denominator = 0 THEN 1 END) as zero_denominators,
  COUNT(CASE WHEN denominator IS NULL THEN 1 END) as null_denominators
FROM division_error_log
WHERE resolved = 'N'
GROUP BY TRUNC(error_date), username
ORDER BY error_day DESC, error_count DESC;

Prevention Strategies

1. Design Patterns

-- Use views with built-in safety
CREATE OR REPLACE VIEW safe_financial_metrics AS
SELECT
  period_id,
  revenue,
  costs,
  -- All divisions are protected
  calculation_utils.divide(revenue, costs, 4) as profit_margin,
  calculation_utils.percentage(profit, revenue) as profit_percentage,
  calculation_utils.ratio(revenue, costs) as revenue_cost_ratio
FROM financial_data;

-- Create materialized view with pre-calculated safe values
CREATE MATERIALIZED VIEW mv_safe_calculations
BUILD IMMEDIATE
REFRESH COMPLETE ON DEMAND
AS
SELECT
  department_id,
  SUM(salary) as total_salary,
  COUNT(*) as emp_count,
  -- Pre-calculate safe averages
  CASE
    WHEN COUNT(*) > 0 THEN SUM(salary) / COUNT(*)
    ELSE 0
  END as avg_salary
FROM employees
GROUP BY department_id;

2. Application Best Practices

// Java example with safe division
public class SafeMathOperations {

    public static Double safeDivide(Double numerator, Double denominator) {
        if (denominator == null || denominator == 0) {
            return null;  // or return 0.0 based on requirements
        }
        return numerator / denominator;
    }

    public static BigDecimal safeDivide(BigDecimal numerator,
                                       BigDecimal denominator,
                                       int scale) {
        if (denominator == null || denominator.compareTo(BigDecimal.ZERO) == 0) {
            return BigDecimal.ZERO;
        }
        return numerator.divide(denominator, scale, RoundingMode.HALF_UP);
    }

    // Use in queries
    public Double calculateRatio(Connection conn, Long departmentId)
            throws SQLException {
        String sql =
            "SELECT " +
            "  SUM(salary) as total_salary, " +
            "  COUNT(*) as emp_count " +
            "FROM employees " +
            "WHERE department_id = ?";

        try (PreparedStatement stmt = conn.prepareStatement(sql)) {
            stmt.setLong(1, departmentId);
            ResultSet rs = stmt.executeQuery();

            if (rs.next()) {
                Double totalSalary = rs.getDouble("total_salary");
                Integer empCount = rs.getInt("emp_count");

                // Safe division in application layer
                return safeDivide(totalSalary, empCount.doubleValue());
            }
        }
        return null;
    }
}

3. Testing Strategies

-- Create test suite for division operations
CREATE OR REPLACE PACKAGE test_division_safety AS

  PROCEDURE run_all_tests;
  PROCEDURE test_safe_divide;
  PROCEDURE test_percentage_calc;
  PROCEDURE test_edge_cases;

END test_division_safety;
/

CREATE OR REPLACE PACKAGE BODY test_division_safety AS

  PROCEDURE assert_equals(
    p_expected NUMBER,
    p_actual NUMBER,
    p_message VARCHAR2
  ) AS
  BEGIN
    IF NVL(p_expected, -999999) != NVL(p_actual, -999999) THEN
      RAISE_APPLICATION_ERROR(-20001,
        'Test failed: ' || p_message ||
        ' Expected: ' || p_expected || ' Actual: ' || p_actual);
    END IF;
  END;

  PROCEDURE test_safe_divide AS
  BEGIN
    -- Test normal division
    assert_equals(5, safe_divide(10, 2, 0), 'Normal division');

    -- Test division by zero
    assert_equals(0, safe_divide(10, 0, 0), 'Division by zero returns default');

    -- Test NULL handling
    assert_equals(NULL, safe_divide(10, NULL, NULL), 'NULL denominator');

    DBMS_OUTPUT.PUT_LINE('✓ Safe divide tests passed');
  END;

  PROCEDURE test_percentage_calc AS
  BEGIN
    -- Test normal percentage
    assert_equals(50, calculation_utils.percentage(50, 100), 'Normal percentage');

    -- Test zero whole
    assert_equals(0, calculation_utils.percentage(50, 0), 'Zero whole');

    DBMS_OUTPUT.PUT_LINE('✓ Percentage tests passed');
  END;

  PROCEDURE test_edge_cases AS
  BEGIN
    -- Test very small denominators
    assert_equals(1000000, safe_divide(1, 0.000001, 0), 'Small denominator');

    -- Test negative numbers
    assert_equals(-5, safe_divide(-10, 2, 0), 'Negative numerator');

    DBMS_OUTPUT.PUT_LINE('✓ Edge case tests passed');
  END;

  PROCEDURE run_all_tests AS
  BEGIN
    test_safe_divide;
    test_percentage_calc;
    test_edge_cases;
    DBMS_OUTPUT.PUT_LINE('All tests passed successfully!');
  END;

END test_division_safety;
/

-- Run tests
EXEC test_division_safety.run_all_tests;

Related Errors

• ORA-01426 - Numeric overflow
• ORA-01438 - Value larger than specified precision
• ORA-01722 - Invalid number
• ORA-06502 - PL/SQL numeric or value error

Quick Reference

Emergency Response Steps

1. ✓ Identify the division operation in the query
2. ✓ Check for zero or NULL denominators
3. ✓ Apply NULLIF or CASE to handle zeros
4. ✓ Test with edge cases
5. ✓ Implement permanent safe division solution
6. ✓ Add validation to prevent future occurrences

Quick Commands

-- Quick safe division
SELECT value1 / NULLIF(value2, 0) FROM table;

-- With default value
SELECT NVL(value1 / NULLIF(value2, 0), 0) FROM table;

-- Using CASE
SELECT CASE WHEN value2 = 0 THEN 0 ELSE value1/value2 END FROM table;

-- Check for zeros
SELECT * FROM table WHERE denominator_column = 0;

-- Safe percentage
SELECT ROUND(100 * value1 / NULLIF(value2, 0), 2) || '%' FROM table;

Prevention Guidelines

• Always use NULLIF - Wrap denominators with NULLIF(value, 0)
• Validate inputs - Check for zeros before division
• Use safe functions - Create reusable safe division functions
• Handle NULLs - Decide how to handle NULL results
• Test edge cases - Include zero and NULL in test data
• Monitor errors - Log and track division by zero attempts