totn Oracle Error Messages

Oracle / PLSQL: ORA-01789 Error Message

Learn the cause and how to resolve the ORA-01789 error message in Oracle.

Description

When you encounter an ORA-01789 error, the following error message will appear:

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

Cause

You tried to execute a SELECT statement (probably a UNION query or a UNION ALL query), and all of the queries did not contain the same number of result columns.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Re-write the SELECT statement so that each query contains the same number of result columns.

For example, if you tried to execute the following UNION query:

SELECT order_id, quantity
FROM orders
UNION
SELECT order_id
FROM orders_audit;

You would receive an error message as follows:

Oracle PLSQL

You can correct this UNION query by having the same number of columns in both SELECT's as follows:

SELECT order_id, quantity
FROM orders
UNION
SELECT order_id, quantity
FROM orders_audit;

Remember that the matching columns (from each query) must be the same data types.