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.

UNION v UNION ALL

This was tested on an Oracle 11 database. A UNION ALL will merge 2 result sets. A UNION does the same then removes duplicate values:

SQL> create table union_test1 (one_col number)

  2  /

 

Table created.

 

SQL> insert into union_test1 values (1)

  2  /

 

1 row created.

 

SQL> insert into union_test1 values (2)

  2  /

 

1 row created.

 

SQL> insert into union_test1

  2  select * from union_test1

  3  /

 

2 rows created.

 

SQL> select * from union_test1

  2  /

 

   ONE_COL

----------

         1

         2

         1

         2

 

SQL> create table union_test2 (one_col number)

  2  /

 

Table created.

 

SQL> insert into union_test2 values (2)

  2  /

 

1 row created.

 

SQL> insert into union_test2 values (3)

  2  /

 

1 row created.

 

SQL> insert into union_test2

  2  select * from union_test2

  3  /

 

2 rows created.

 

SQL> select * from union_test2

  2  /

 

   ONE_COL

----------

         2

         3

         2

         3

 

SQL> select * from union_test1

  2  union all

  3  select * from union_test2

  4  /

 

   ONE_COL

----------

         1

         2

         1

         2

         2

         3

         2

         3

 

8 rows selected.

 

SQL> select * from union_test1

  2  union

  3  select * from union_test2

  4  /

 

   ONE_COL

----------

         1

         2

         3

 

SQL>