PL/SQL User's Guide and Reference
Datatype Conversion
Sometimes it is necessary to convert a value from one datatype to another. For example, if you want to examine a rowid, you must convert it to a character string. PL/SQL supports both explicit and implicit (automatic) datatype conversion.
Explicit Conversion
To specify conversions explicitly, you use built-in functions that convert values from one datatype to another. Table 2 - 1 shows which function to use in a given situation. For example, to convert a CHAR value
to a NUMBER value, you use the function TO_NUMBER. For more information about these functions, see Oracle7 Server SQL Reference.
| To
|
|
|
|
|
|
| From
| CHAR
| DATE
| NUMBER
| RAW
| ROWID
|
| CHAR
|
| TO_DATE
| TO_NUMBER
| HEXTORAW
| CHARTOROWID
|
| DATE
| TO_CHAR
|
|
|
|
|
| NUMBER
| TO_CHAR
| TO_DATE
|
|
|
|
| RAW
| RAWTOHEX
|
|
|
|
|
| ROWID
| ROWIDTOCHAR
|
|
|
|
|
Table 2 - 1. Conversion Functions
Implicit Conversion
When it makes sense, PL/SQL can convert the datatype of a value implicitly. This allows you to use literals, variables, and parameters of one type where another type is expected. In the example below, the CHAR variables start_time and finish_time hold string values representing the number of seconds past midnight. The difference between those values must be assigned to the NUMBER variable elapsed_time. So, PL/SQL converts the CHAR values to NUMBER
values automatically.
DECLARE
start_time CHAR(5);
finish_time CHAR(5);
elapsed_time NUMBER(5);
BEGIN
/* Get system time as seconds past midnight. */
SELECT TO_CHAR(SYSDATE,'SSSSS') INTO start_time FROM sys.dual;
-- do something
/* Get system time again. */
SELECT TO_CHAR(SYSDATE,'SSSSS') INTO finish_time FROM sys.dual;
/* Compute elapsed time in seconds. */
elapsed_time := finish_time - start_time;
INSERT INTO results VALUES (elapsed_time, ...);
END;
Before assigning a selected column value to a variable, PL/SQL will, if necessary, convert the value from the datatype of the source column to the datatype of the variable. This happens, for example, when you select a DATE column value into a VARCHAR2 variable. Likewise, before assigning the value of a variable to a database column, PL/SQL will, if necessary, convert the value from the datatype of the variable to the datatype of the target column.
If PL/SQL cannot determine which implicit conversion is needed, you get a compilation error. In such cases, you must use a datatype conversion function. Table 2 - 2 shows which implicit conversions PL/SQL can do.
| To
|
|
|
|
|
|
|
|
|
|
| From
| BIN_INT
| CHAR
| DATE
| LONG
| NUMBER
| PLS_INT
| RAW
| ROWID
| VARCHAR2
|
| BIN_INT
|
| _/
|
| _/
| _/
| _/
|
|
| _/
|
| CHAR
| _/
|
| _/
| _/
| _/
| _/
| _/
| _/
| _/
|
| DATE
|
| _/
|
| _/
|
|
|
|
| _/
|
| LONG
|
| _/
|
|
|
|
| _/
|
| _/
|
| NUMBER
| _/
| _/
|
| _/
|
| _/
|
|
| _/
|
| PLS_INT
| _/
| _/
|
| _/
| _/
|
|
|
| _/
|
| RAW
|
| _/
|
| _/
|
|
|
|
| _/
|
| ROWID
|
| _/
|
|
|
|
|
|
| _/
|
| VARCHAR2
| _/
| _/
| _/
| _/
| _/
| _/
| _/
| _/
|
|
Table 2 - 2. Implicit Conversions
It is your responsibility to ensure that values are convertible. For instance, PL/SQL can convert the CHAR value '02-JUN-92' to a DATE value, but PL/SQL cannot convert the CHAR value 'YESTERDAY' to a DATE value. Similarly, PL/SQL cannot convert a VARCHAR2 value containing alphabetic characters to a NUMBER value.
Implicit versus Explicit Conversion
Generally, it is poor programming practice to rely on implicit datatype conversions because they can hamper performance and might change from one software release to the next. Also, implicit conversions are context sensitive and therefore not always predictable. Instead, use datatype conversion functions. That way, your applications will be more reliable and easier to maintain.
DATE Values
When you select a DATE column value into a CHAR or VARCHAR2 variable, PL/SQL must convert the internal binary value to a character value. So, PL/SQL calls the function TO_CHAR, which returns a character string in the default date format. To get other information such as the time or Julian date, you must call TO_CHAR with a format mask.
A conversion is also necessary when you insert a CHAR or VARCHAR2 value into a DATE column. So, PL/SQL calls the function TO_DATE, which expects the default date format. To insert dates in other formats, you must call TO_DATE with a format mask.
RAW and LONG RAW Values
When you select a RAW or LONG RAW column value into a CHAR or VARCHAR2 variable, PL/SQL must convert the internal binary value to a character value. In this case, PL/SQL returns each binary byte of RAW or LONG RAW data as a pair of characters. Each character represents the hexadecimal equivalent of a nibble (half a byte). For example, PL/SQL returns the binary byte 11111111 as the pair of characters 'FF'. The function RAWTOHEX does the same conversion.
A conversion is also necessary when you insert a CHAR or VARCHAR2 value into a RAW or LONG RAW column. Each pair of characters in the variable must represent the hexadecimal equivalent of a binary byte. If either character does not represent the hexadecimal equivalent of a nibble, PL/SQL raises an exception.