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.

Implicit conversions performed by PL/SQL

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

Get Oracle PL/SQL Programming, Third Edition now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.