msgbartop
News, views, tips and tricks on Oracle and other fun stuff
msgbarbottom

One more reason to upgrade your Oracle 8i

On my Oracle database 8i (8.1.7.4) instance, I ran the following:

SQL> SELECT 1 my_number
  2    FROM DUAL
  3  UNION
  4  SELECT NULL my_number
  5    FROM DUAL
  6  /
SELECT 1 my_number
       *
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression


SQL> SELECT SYSDATE my_date
  2    FROM DUAL
  3  UNION
  4  SELECT NULL my_date
  5    FROM DUAL
  6  /
SELECT SYSDATE my_date
       *
ERROR at line 1:
ORA-01790: expression must have same datatype as corresponding expression

What’s up with the ORA-01790 error?

As you know, NULL doesn’t have a data type, and NULL can be used in place of a value of any data type. If you purposely select NULL as a column value in a union query, Oracle no longer has two datatypes to compare to see whether the union queries are compatible. As you can see from the above queries, this is an issue for the DATE and NUMBER datatypes in Oracle 8i . However, Oracle does not have a problem with character datatypes:

SQL> SELECT 'C' my_char
  2    FROM DUAL
  3  UNION
  4  SELECT NULL my_char
  5    FROM DUAL
  6  /

M
-
C

To solve this issue, you have two options:

Option 1: Explicitly convert NULL to the right datatype:

SQL> SELECT 1 my_number
  2    FROM DUAL
  3  UNION
  4  SELECT TO_NUMBER (NULL) my_number
  5    FROM DUAL
  6  /

 MY_NUMBER
----------
         1


SQL> SELECT SYSDATE my_date
  2    FROM DUAL
  3  UNION
  4  SELECT TO_DATE (NULL) my_date
  5    FROM DUAL
  6  /

MY_DATE
---------
15-JUN-06

Option 2: Upgrade your Oracle database to 9i or above. Oracle 9i, and later releases, are “smart enough” to know which flavor of NULL to use. I executed the following in 9.2.0.6:

SQL> SELECT 1 my_number
  2    FROM DUAL
  3  UNION
  4  SELECT NULL my_number
  5    FROM DUAL
  6  /

 MY_NUMBER
----------
         1


SQL> SELECT SYSDATE my_date
  2    FROM DUAL
  3  UNION
  4  SELECT NULL my_date
  5    FROM DUAL
  6  /

MY_DATE
---------
15-JUN-06

Go for option 2, if you have not done so already.


Filed in Oracle, Tips on 20 Jun 06 | Tags: ,


Reader's Comments

  1. |

    You should probably use “union all” here. Make it a habbit to use that by default and only use “union” if needed.

  2. |

    Anders, thanks for the good and valid tip. For the purpose of demonstrating datatype incompatibilities using only the dual table, “union” should be ok here.