A Warning About Automatic Conversions in Oracle 12c

By Bob Bryla, Kevin Loney on June 3, 2015


The issue of whether allowing SQL to do automatic conversion of datatypes is a good practice or not has arguments on either side. On one hand, this practice considerably simplifies and reduces the functions necessary to make a SELECT statement work. On the other hand, if your assumption about what will be in the column is wrong (for example, you assume a particular character column will always have a number in it, meaning you can use it in a calculation), then, at some point, a query will stop working, Oracle will produce an error, and time will have to be spent trying to find the problem. Further, another person reading your SELECT statement may be confused by what appear to be inappropriate functions at work on characters or numbers. Using TO_NUMBER makes it clear that a numeric value is always expected, even if the column uses the VARCHAR2 datatype. You will still get an error if the TO_NUMBER function encounters a non-numeric value in the column.

The biggest problem with implicit conversions is that an index on the column will not be used if conversion takes place. If you have a column such as ID or ORDER_NUM as a VARCHAR2 column and you then create a PL/SQL procedure to access the data and pass a number in the procedure, the underlying index on ID or ORDER_NUM does not get used. Thus, performance suffers.

A simple rule of thumb might be that it is best to use functions where the risk is low, such as string-manipulation functions on numbers, rather than arithmetic functions on strings. For your benefit and that of others using your work, always put a note near the SELECT statement signaling the use of automatic type conversion.

Related Posts

Leave a Reply