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

Answer 1

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!!