SOLUTION: ORA-01789 Error in Oracle

Let us see here how to solve ORA-01789 Error Message in Oracle.

ORA-01789 Error Message

You will get below error message once you encounter ORA-01789 error:

ORA-01789: query block has incorrect number of result columns

Cause

Queries joined by UNION or UNION ALL clause do not have same number of columns with same data types. Even if column names are same, different data types will give you below error

Solution

Have same number of columns in queries joined together by UNION or UNION ALL clause.

For example

Error SQL Statement

SELECT empno, deptno
  FROM emp
 UNION
SELECT empno
  FROM emp;

Change the query by adding one more column in second query of UNION clause.

Correct SQL Statement

SELECT empno, deptno
 FROM emp
UNION
 SELECT NULL empno, deptno
 FROM dept;

Correct the datatypes of columns selected in SELECT statement of UNION query.

Error SQL Statement

SELECT sal, empno
  FROM emp
 UNION
SELECT 'OracleMine' sal, empno
  FROM emp;

Correct SQL statement

SELECT to_char(sal) sal, empno
  FROM emp
 UNION
SELECT 'OracleMine' as sal , empno
  FROM emp;

Check out all other solutions here.

Hope you like the article and find it useful. We highly appreciate comments and feedback.

You can also Subscribe here to stay updated on latest posts of Oraclemine.com.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.