Why is ORACLE throwing ORA-01790?

Question

I have a query with UNION ALL statements.

SELECT * FROM HULL_A
    UNION ALL
SELECT * FROM HULL_B;

It throws the error

ORA-01790 "expression must have same datatype as corresponding expression"

However, I checked that, and I think it is not the case. I used the following to check:

select db1.data_type, db2.data_type, db1.data_length, db2.data_length, db1.data_precision, db2.data_precision, db1.data_scale, db2.data_scale
from all_tab_columns db1
     inner join all_tab_columns db2
       on (db1.owner = db2.owner
           and db1.column_name = db2.column_name)       
where db1.table_name = 'HULL_A'
  and db2.table_name = 'HULL_B'
  and (
        db1.data_type = db2.data_type
        OR 
        db1.data_length = db2.data_length
      )

The result is:

I was able to link HULL_A to HULL_C and HULL_D using UNION ALL.

So why is ORACLE throwing the error? What other test could I perform in order to be able to perform the UNION ALL?

I am working on WINDOWS 10 ORACLE 11g

Oracle does not allow mixing datatypes for a good reasons as it is strictly disallowed by ANSI/ISO SQL standards. see demo — Raymond Nijland
"However, I checked that, and I think it is not the case." Computers/Software do not lie or give invalid error messages.. — Raymond Nijland
Also see Why should I provide a Minimal Reproducible Example for a very simple SQL query? for providing better example data and expected results — Raymond Nijland
The JOIN is not done by COLUMN_NAME but by the position based on the COLUMN_ID, perhaps someone added or deleted columns? Better use qualified names instead of *... — Radagast81
Why is it throwing an error? Because the data types between the columns don't match. Maybe the order of the columns are different in the two tables (columns are matched by position in an UNION not by name) or those two tables really have different types for the "same" column. Please edit your question (by clicking on the edit link below it) and add the create table statements for both tables as formatted text — a_horse_with_no_name

Popeye Popeye · Accepted Answer · 2019-06-25T12:40:27

Columns in both the table must have the same data type and order of the columns must be the same.

You can try the following query to identify the mismatch in columns of both the tables:

SELECT
    A.COLUMN_ID COLUMN_HULL_A,
    A.DATA_TYPE DATA_TYPE_HULL_A,
    B.COLUMN_ID COLUMN_HULL_B,
    B.DATA_TYPE DATA_TYPE_HULL_B
FROM
    (
        SELECT
            COLUMN_ID,
            DATA_TYPE
        FROM
            USER_TAB_COLUMNS
        WHERE
            TABLE_NAME = 'HULL_A'
    ) A
FULL OUTER JOIN (
    SELECT
        COLUMN_ID,
        DATA_TYPE
    FROM
        USER_TAB_COLUMNS
    WHERE
        TABLE_NAME = 'HULL_B'
)B ON (A.COLUMN_ID = B.COLUMN_ID AND A.DATA_TYPE = B.DATA_TYPE)
WHERE
    A.COLUMN_ID IS NULL
    OR B.COLUMN_ID IS NULL;

Cheers!!

Why is ORACLE throwing ORA-01790?

1 Answers