Lesson 6 | Explicit versus implicit datatype conversions |
Objective | Identify implicit and explicit datatype conversions within PL/SQL. |
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.
Implicit Datatype Conversion
The following SlideShow describes the automatic conversation of data from one type to another.
Explicit versus implicit
A date will not be converted to a number, and vice versa.
Explicit Datatype Conversion
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.
Conversion Table
The following table provides a bird's eye view of all the
- conversion functions and
- 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.
FROM/TO | CHAR | DATE | NUMBER | RAW | ROWID |
CHAR | | TO_DATE | TO_NUMBER | HEXTORAW | CHARTOROWID |
DATE | TO_CHAR | | | | |
NUMBER | TO_CHAR | TO_DATE | | | |
RAW | RAWTOHEX | | | | |
ROWID | ROWIDTOCHAR | | | | |
In the next lesson, operators within PL/SQL will be discussed.