DEV Community

umzzil nng
umzzil nng

Posted on • Originally published at oraerror.com

Oracle ORA-01789 Error: Causes and Solutions Complete Guide

#oracle #database #dba #sql

ORA-01789: Query Block Has Incorrect Number of Result Columns

ORA-01789 is a common Oracle error that occurs when using set operators such as UNION, UNION ALL, INTERSECT, or MINUS, and the number of columns returned by each query block does not match. Oracle strictly enforces that every SELECT statement joined by a set operator must return the same number of columns. This error is typically straightforward to diagnose but can be tricky in complex queries with nested subqueries or long SQL scripts.

Top 3 Causes

1. Mismatched Column Count in UNION / UNION ALL

The most frequent cause. One SELECT block returns a different number of columns than the other.

-- ERROR: First block has 3 columns, second has 2
SELECT emp_id, emp_name, department
FROM employees
WHERE department = 'IT'
UNION ALL
SELECT emp_id, emp_name  -- Missing: department
FROM employees
WHERE department = 'HR';
Enter fullscreen mode Exit fullscreen mode

Fix: Ensure all blocks return the same number of columns. Use NULL or literals as placeholders if needed.

-- FIXED
SELECT emp_id, emp_name, department
FROM employees
WHERE department = 'IT'
UNION ALL
SELECT emp_id, emp_name, department
FROM employees
WHERE department = 'HR';
Enter fullscreen mode Exit fullscreen mode

2. Partially Updated Query After Adding a Column

When maintaining existing views or reports, developers often add a column to one UNION block but forget to update the others.

-- BROKEN VIEW after adding hire_date to only one block
CREATE OR REPLACE VIEW v_all_staff AS
SELECT emp_id, emp_name, 'EMP' AS type, hire_date  -- Added hire_date
FROM employees
UNION ALL
SELECT con_id, con_name, 'CON' AS type  -- hire_date missing!
FROM contractors;

-- FIXED: All blocks updated
CREATE OR REPLACE VIEW v_all_staff AS
SELECT emp_id, emp_name, 'EMP' AS type, hire_date
FROM employees
UNION ALL
SELECT con_id, con_name, 'CON' AS type, start_date AS hire_date
FROM contractors;
Enter fullscreen mode Exit fullscreen mode

3. Column Mismatch Inside Subqueries or Inline Views

ORA-01789 can also be triggered inside subqueries where a UNION is embedded within a FROM clause.

-- ERROR inside inline view
SELECT *
FROM (
    SELECT emp_id, emp_name, salary
    FROM employees
    UNION
    SELECT emp_id, emp_name  -- salary missing
    FROM temp_employees
) t;

-- FIXED: Use 0 or NULL as a placeholder
SELECT *
FROM (
    SELECT emp_id, emp_name, salary
    FROM employees
    UNION
    SELECT emp_id, emp_name, 0 AS salary
    FROM temp_employees
) t;
Enter fullscreen mode Exit fullscreen mode

Quick Fix Checklist

  • Count the columns in each SELECT block manually or run them individually.
  • Use NULL AS column_name or literal values to pad missing columns.
  • After modifying a view with UNION, recompile and verify with DESCRIBE view_name. 
-- Run each block separately to quickly identify the mismatch
SELECT emp_id, emp_name, department FROM employees;   -- 3 columns
SELECT emp_id, emp_name FROM contractors;              -- 2 columns → MISMATCH found!
Enter fullscreen mode Exit fullscreen mode

Prevention Tips

  • Always list columns explicitly — avoid SELECT * in UNION queries. Explicitly naming columns makes it easy to spot count mismatches during code review.
  • Validate in DEV/STAGING first — never apply UNION query changes directly to production. Use automated SQL validation tools (SQLcl, Flyway, or custom scripts) in your CI/CD pipeline to catch ORA-01789 before deployment.

Related Errors

  • ORA-01790 — Column count matches, but data types are incompatible across UNION blocks.
  • ORA-00904 — Invalid column identifier, often appears alongside ORA-01789 during query refactoring.

📖 Want a more detailed guide?
Check out the full in-depth version (Korean) on oraerror.com — includes detailed analysis, additional SQL examples, and prevention tips.

Top comments (0)