ORA-01789 and ORA-01790

This was tested on Oracle 11.1.0.6.0 running on Windows XP. You can merge the results from two or more SELECT statements with UNION:

SQL> l
  1  select owner, table_name
  2  from dba_tables
  3  union
  4  select owner, index_name
  5* from dba_indexes
SQL> /

OWNER                     TABLE_NAME
------------------------- -------------------------
CTXSYS                    DR$ACTIVELOGS
CTXSYS                    DR$CLASS
CTXSYS                    DR$DBO
CTXSYS                    DR$DELETE
CTXSYS                    DR$FEATURE_USED
CTXSYS                    DR$FEAT_KEY
CTXSYS                    DR$FREQTOKS
CTXSYS                    DR$INDEX
CTXSYS                    DR$INDEX_CDI_COLUMN
CTXSYS                    DR$INDEX_ERROR
CTXSYS                    DR$INDEX_OBJECT
CTXSYS                    DR$INDEX_PARTITION
etc.

Each SELECT statements must return the same number of columns. If not, you get an ORA-01789:

SQL> l
  1  select owner, table_name, last_analyzed
  2  from dba_tables
  3  union
  4  select owner, index_name
  5* from dba_indexes
SQL> /
select owner, table_name, last_analyzed
*
ERROR at line 1:
ORA-01789: query block has incorrect number of result
columns

SQL>

The column definitions from each SELECT must match the corresponding column definitions from the other SELECTs. Otherwise, you get an ORA-01790:

SQL> l
  1  select table_name, last_analyzed
  2  from dba_tables
  3  union
  4  select index_name, index_type
  5* from dba_indexes
SQL> /
select table_name, last_analyzed
                   *
ERROR at line 1:
ORA-01790: expression must have same datatype as
corresponding expression

You get the same error messages in these situations with UNION ALL, INTERSECT and MINUS.