Implicit Conversions

A final method of handling conversions between numbers and strings is to just leave it all to PL/SQL. Such conversions are referred to as implicit conversions, because you don't explicitly specify them in your code. Following are some straightforward implicit conversions that will work just fine:

DECLARE a NUMBER; b VARCHAR2(30);BEGIN a:= '-123.45'; b:= -123.45;...

We have several problems with implicit conversions. We are strong believers in maintaining control over our code, and when you use an implicit conversion you are giving up some of that control. You should always know when conversions are taking place, and the best way to do that is to code them explicitly. Don't just let them happen. If you rely on implicit conversions, you lose track of when conversions are occurring, and your code is less efficient as a result. Explicit conversions also make your intent clear to other programmers, making your code more self-documenting and easier to understand.

Another problem with implicit conversions is that while they may work just fine (or seem to) in simple cases, sometimes they can be ambiguous. Consider the following:

DECLARE a NUMBER;BEGIN a:= '123.400' || 999;

What value will the variable "a" hold when this code executes? It all depends on how PL/SQL evaluates the expression on the right side of the assignment operator. If PL/SQL begins by converting the string to a number, you'll get the following result:

a:= '123.400' || 999;a:= 123.4 || 999;a:= '123.4' || '999';a:= '123.4999';a:= 123.4999;

On the other hand, if PL/SQL begins by converting the number to a string, you'll get the following result:

a:= '123.400' || 999;a:= '123.400' || '999';a:= '123.400999';a:= 123.400999;

Which is it? Do you know? Even if you do know, do you really want to leave future programmers guessing and scratching their heads when they look at your code? It would be much clearer, and therefore better, to write the conversion explicitly:

a:= TO_NUMBER('123.400' || TO_CHAR(999));

This expression, by the way, represents how Oracle will evaluate the original example. Isn't it much easier to understand at a glance now that we've expressed the conversions explicitly?

Conversion Between Numeric Types PL/SQL supports three numeric types, NUMBER, PLS_INTEGER, and BINARY_INTEGER, and you may be wondering about conversions among these three types. There's no ambiguity when converting between numeric types, so PL/SQL handles such conversions implicitly. There are no functions that allow you to go between these types, and any such functions would be superfluous. Be aware, however, that conversions between the three numeric types can and do occur, and that they do exact a cost (albeit a small one) in terms of execution time. Try to minimize such conversions. Most, if not all, of the numeric functions discussed in the next section of this chapter expect NUMBER inputs. Invoke those functions with PLS_INTEGER or BINARY_INTEGER arguments, and you are forcing a conversion to take place. Isolate your use of PLS_INTEGER or BINARY_INTEGER from any NUMBER variables or numeric functions that your program may be using.