RelationalDBDesign 





Programming PL/SQL  «Prev  Next»
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
  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.
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.