7.5 Conversion Between Datatypes

There are many different situations in which you need to convert data from one datatype to another. You can perform this conversion in two ways:

Implicitly

By allowing the PL/SQL runtime engine to take its "best guess" at performing the conversion

Explicitly

By calling a PL/SQL function or operator to do the conversion

In this section I will first review how and when PL/SQL performs implicit conversions, and will then focus attention on the programs available for explicit conversions.

7.5.1 Implicit Data Conversion

Whenever PL/SQL detects that a conversion is necessary, it attempts to change the values as required to perform the operation. You would probably be surprised to learn how often PL/SQL is performing conversions on your behalf. Figure 7-2 shows what kinds of implicit conversions PL/SQL can perform.

Figure 7-2. Implicit conversions performed by pl/sql

With implicit conversions you can specifyliteral values in place of data with the correct internal format, and PL/SQL will convert that literal as necessary. In the following example, PL/SQL converts the literal string "125" to the numeric value 125 in the process of assigning a value to the numeric variable:

DECLARE

a_number NUMBER;

BEGIN

a_number := '125';

END;

You can also pass parameters of one datatype into a module and then have PL/SQL convert that data into another format for use inside the program. In the following procedure, the second parameter is a date. When I call that procedure, I pass a string value in the form DD-MON-YY, and PL/SQL converts that string automatically to a date:

PROCEDURE change_hiredate

(emp_id_in IN INTEGER, hiredate_in IN DATE)

change_hiredate (1004, '12-DEC-94');

7.5.1.1 Limitations of implicit conversion

As shown in Figure 7-2, conversions are limited; PL/SQL cannot convert any arbitrary datatype to any other datatype. Furthermore, some implicit conversions raise exceptions. Consider the following assignment:

DECLARE

a_number NUMBER;

BEGIN

a_number := 'abc';

END;

PL/SQL cannot convert "abc" to a number and so will raise the VALUE_ERROR exception when it executes this code. It is up to you to make sure that if PL/SQL is going to perform implicit conversions, it is given values it can convert without error.

7.5.1.2 Drawbacks of implicit conversion

There are several drawbacks to implicit conversion:

• Each implicit conversion PL/SQL performs represents a loss, however small, in the control you have over your program. You do not expressly perform or direct the performance of the conversion; you simply make an assumption that it will take place and that it will have the intended effect. There is always a danger in making this assumption. If Oracle changes the way and circumstances under which it performs conversions, your code could then be affected.

• The implicit conversion that PL/SQL performs depends on the context in which the code occurs. As a result, a conversion might occur in one program and not in another, even though they appear to be the same. The conversion that PL/SQL performs is not necessarily the one you might expect.

• Your code is easier to read and understand if you explicitly convert data where needed. Such conversions provide documentation of variances in datatypes between tables or between code and tables. By removing an assumption and a hidden action from your code, you remove a potential misunderstanding as well.

As a consequence, I recommend that you avoid allowing either the SQL or PL/SQL languages to perform implicit conversions on your behalf. Instead, use a conversion function to guarantee that the right kind of conversion takes place.