Oracle PL/SQL Programming, Third Edition

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.

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. ...