Whenever PL/SQL performs an operation involving one or more values, it must first convert the data to be the right datatype for the operation. As Chapter 4, Variables and Program Data , describes, there are two kinds of conversion: implicit and explicit. An implicit conversion is performed by PL/SQL as needed to execute a statement. An explicit conversion takes place when you use a built-in conversion function to force the conversion of a value from one datatype to another. This chapter describes each of these functions.
If you do not use a conversion function to explicitly convert your data, or if you do use those functions and additional conversion is still needed, PL/SQL attempts to convert the data implicitly to the datatypes needed to perform the operation. I recommend that you avoid allowing either the SQL or PL/SQL languages to perform implicit conversions on your behalf. Whenever possible, use a conversion function to guarantee that the right kind of conversion takes place. Implicit conversion has a number of drawbacks, summarized in Section 126.96.36.199, "Implicit data conversions" in Chapter 4 .
Explicit conversions help you to avoid unpleasant surprises, maximize performance, and make your code more self-documenting. When you perform an explicit conversion involving dates or numbers (using TO_CHAR, TO_DATE, or TO_NUMBER), you can specify a conversion format mask. PL/SQL uses this mask to either interpret the input value or format the output value.
Table 14.1 summarizes the PL/SQL conversion functions described in this chapter.
Several of the conversion functions (TO_CHAR, TO_DATE, and TO_NUMBER) use format models to determine the format of the converted data. Format models convert between strings and dates, and strings and numbers. This section discusses these format models, which are then put to use in the function descriptions.
In Versions 6 and earlier of the Oracle RDBMS, the default format for dates as character values was DD-MON-YY, a cause of consternation for many developers and users. While this format is common in many parts of the world, very few people use it in the United States.
With Oracle7 you can specify your own default date format (which takes effect on the initialization or startup of the RDBMS instance) with the NLS_DATE_FORMAT[ 1 ] parameter as follows:
NLS_DATE_FORMAT = `MM/DD/YYYY'
The default date format is also set implicitly with another initialization parameter, NLS_TERRITORY. When you specify an NLS_TERRITORY value, you set conventions for date format, date language, numeric formats, currency symbols, and week start day.
Even with this flexibility, the database still supports only a single default date format in a given instance. Both developers and users must be aware of this format when working with dates. Later sections of this chapter explore approaches in PL/SQL that give the user much more flexibility when entering dates in their applications.
As you can see, format masks (such as MMDDYY and Month DD, YYYY) play an important role in the conversion of date and character data. Table 14.2 provides the full set of date format masks and explains how to use them in all their variations. You can use the format elements in any combination, in any order. You can even use the same format element more than once in your format mask. Following the table are examples showing these variations.
Note that whenever a date format returns a spelled value (words rather than numbers, as with MONTH, MON, DAY, DY, AM, and PM), the language used to spell these words is determined by the National Language Support parameters, NLS_DATE_LANGUAGE and NLS_LANGUAGE, or by the optional date language argument you can pass to both TO_CHAR and TO_DATE.
Here are some examples of date format masks composed of the above format elements:
'Month DD, YYYY' 'MM/DD/YY Day A.M.' 'Year Month Day HH24:MI:SS' 'J' 'SSSSS-YYYY-MM-DD' '"A beautiful summer morning on the" DDth" day of "Month'
The number formats are used in both TO_CHAR and TO_NUMBER. The number format in TO_CHAR translates a numeric value to a VARCHAR2 datatype. The number format in TO_NUMBER translates a VARCHAR2 value to a numeric datatype.
A number format mask can comprise one or more elements from Table 14.3 . The resulting format of the character string (or the converted numeric value) will reflect the combination of the format elements. You will find examples of different applications of the format models in the descriptions of both the TO_CHAR and TO_NUMBER functions.
Format elements with a description starting with "Prefix:" can be used only at the beginning of the complete format mask. Format elements with a description starting with "Suffix:" can be used only at the end of the complete format mask.
Here are some examples of numeric format masks built from these elements:
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.