Explicit versus implicit Datatype Conversions

PL/SQL will automatically convert datatypes if the statement contains conflicting datatypes. For example, if you assign a number value to a CHAR variable, PL/SQL will convert it into a character representation. The reverse is also true, provided the character expression can be converted to a valid numeric value. This is also true for DATE datatypes. This will not always work, however. To have PL/SQL automatically convert one datatype into another, the first datatype must already “look” like the datatype to which it is being converted.

The following SlideShow describes the automatic conversation of data from one type to another.

If you have mixed datatypes within your expression and you need to convert them explicitly, PL/SQL provides a wide variety of datatype conversion functions. You can use them to manipulate variables and data as well as within your Oracle SQL statements.
MAX and MIN are the only two group functions that can be used with any datatypes.

The following table provides a bird's eye view of all the

1. conversion functions and
2. valid datatype conversions

that go along with them. The first row and column list the datatype, while the rest of the columns list the conversion functions that you can use for the respective datatype. See the table below containing data type conversion.