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 series of images describes the automatic conversation of data from one type to another.

1) Any NUMBER or DATE will be converted into a CHAR. As a consequence, any string function can be used on a NUMBER or DATE column. Literal NUMBERs do not have to be enclosed in single quotation marks when used in a string function; literal DATES do. For example,

FISCAL_YEAR := TO_CHAR(SYSDATE, 'MM-DD-YYY')

2)A CHAR string will be converted to a NUMBER if it contains only numbers, a decimal point, or a minus sign on the left. There must be no embedded spaces or other characters. — 2) A CHAR string will be converted to a NUMBER if it contains only numbers, a decimal point, or a minus sign on the left. There must be no embedded spaces or other characters.

3) A CHAR string will be converted to a DATE only if it is in the format DD-MON-YY, such as 16-JAN-73. This is true for all functions except GREATEST and LEAST, which will treat it as a string, and true for BETWEEN only if the column to the left of the word BETWEEN is a DATE. Otherwise, TO_DATE must be used with a proper format.

SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE Data Types

The SIMPLE_INTEGER, SIMPLE_FLOAT, and SIMPLE_DOUBLE data types are predefined subtypes of

PLS_INTEGER, 
BINARY_FLOAT, and 
BINARY_DOUBLE,

respectively. Each subtype has the same range as its base type and has a NOT NULL constraint.
SIMPLE_INTEGER differs significantly from PLS_INTEGER in its overflow semantics, but SIMPLE_FLOAT and SIMPLE_DOUBLE are identical to their base types, except for their NOT NULL constraint. You can use SIMPLE_INTEGER when the value will never be NULL and overflow checking is unnecessary. You can use SIMPLE_FLOAT and SIMPLE_DOUBLE when the value will never be NULL. Without the overhead of checking for nullness and overflow, these subtypes provide significantly better performance than their base types when PLSQL_CODE_TYPE='NATIVE', because arithmetic operations on SIMPLE_INTEGER values are done directly in the hardware. When PLSQL_CODE_ TYPE='INTERPRETED', the performance improvement is smaller.
A date will not be converted to a number, and vice versa.

Ad Oracle Database PL/SQL Programming

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.