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.

Another Example Using Intersect

In the course of helping a colleague today, I had to find a database user who had 2 roles assigned. You can do this with a couple of subqueries but it is easier with an INTERSECT. I was logged in as user ORACLE when I did this and I was surprised to see ORACLE in the list alongside USER_2. It seems that, if you create a role, it is automatically assigned to you. I did not know this. You can see this demonstrated at the end of the example. I ran it on an Oracle 9 database but the same thing happens in Oracle 10 and 11:

 

SQL> conn /

Connected.

SQL> show user

USER is "ORACLE"

SQL> create role role_a

  2  /

 

Role created.

 

SQL> create role role_b

  2  /

 

Role created.

 

SQL> grant role_a to user_1

  2  identified by user_1

  3  /

 

Grant succeeded.

 

SQL> grant role_a, role_b to user_2

  2  identified by user_2

  3  /

 

Grant succeeded.

 

SQL> grant role_b to user_3

  2  identified by user_3

  3  /

 

Grant succeeded.

 

SQL> select distinct grantee from dba_role_privs

  2  where grantee in

  3  (select grantee from dba_role_privs

  4   where granted_role = 'ROLE_A')

  5  and grantee in

  6  (select grantee from dba_role_privs

  7   where granted_role = 'ROLE_B')

  8  /

 

GRANTEE

------------------------------

ORACLE

USER_2

 

SQL> select grantee from dba_role_privs

  2  where granted_role = 'ROLE_A'

  3  intersect

  4  select grantee from dba_role_privs

  5  where granted_role = 'ROLE_B'

  6  /

 

GRANTEE

------------------------------

ORACLE

USER_2

 

SQL> create role blah

  2  /

 

Role created.

 

SQL> select grantee from dba_role_privs

  2  where granted_role = 'BLAH'

  3  /

 

GRANTEE

------------------------------

ORACLE

 

SQL>

Intersect

I read about this in Oracle Database 10g The Complete Reference by Kevin Loney but I tested it on an Oracle 9 database. If you join two or more select statements by intersect, only the rows  which appear in the output from every one of those statements will be displayed. The example below demonstrates this. First create a table of boys' names:

SQL> create table boys_names

  2  (boys_name varchar2(10))

  3  /
  

Table created.

SQL>
  

And also a table of girls' names:
  

SQL> create table girls_names

  2  (girls_name varchar2(10))

  3  /

Table created.

SQL> 

Add some data to the boys_names table then duplicate it:

SQL> insert into boys_names values('Tom');

1 row created.

SQL> insert into boys_names values('Dick');

1 row created.

SQL> insert into boys_names values('Harry');

1 row created.

SQL> insert into boys_names values('Kim');

1 row created.

SQL> insert into boys_names values('Alex');

1 row created.

SQL> insert into boys_names values('Sam');

1 row created.

SQL> insert into boys_names select * from boys_names;

6 rows created.

SQL> 

Do the same with the girls_names table:

SQL> insert into girls_names values('Sarah');

1 row created.

SQL> insert into girls_names values('Tina');

1 row created.

SQL> insert into girls_names values('Brenda');

1 row created.

SQL> insert into girls_names values('Kim');

1 row created.

SQL> insert into girls_names values('Alex');

1 row created.

SQL> insert into girls_names values('Sam');

1 row created.

SQL> insert into girls_names select * from girls_names;

6 rows created.

SQL> 

Display the data in both tables:

SQL> select * from boys_names

  2  /

BOYS_NAME

----------

Tom

Dick

Harry

Kim

Alex

Sam

Tom

Dick

Harry

Kim

Alex

Sam

12 rows selected.

SQL> select * from girls_names

  2  /

GIRLS_NAME

----------

Sarah

Tina

Brenda

Kim

Alex

Sam

Sarah

Tina

Brenda

Kim

Alex

Sam

12 rows selected.

SQL>

Note that 3 names appear twice in both tables. Use intersect to display these rows. Although each of these names appears twice in both tables, they are each displayed only once:

SQL> select * from boys_names

  2  intersect

  3  select * from girls_names

  4  /

BOYS_NAME

----------

Alex

Kim

Sam

SQL>

This could also be achieved as follows:

SQL> select distinct boys_name

  2  from boys_names, girls_names

  3  where boys_name = girls_name

  4  /

BOYS_NAME

----------

Alex

Kim

Sam
  

SQL>