[Chapter 14] 14.2 Conversion Function DescriptionsChapter 14Conversion Functions 14.2 Conversion Function DescriptionsThis section describes the various conversion functions provided by PL/SQL. 14.2.1 The CHARTOROWID functionThe CHARTOROWID function converts a string of either type CHAR or VARCHAR2 to a value of type ROWID. The specification of the CHARTOROWID function is: FUNCTION CHARTOROWID (string_in IN CHAR) RETURN ROWID FUNCTION CHARTOROWID (string_in IN VARCHAR2) RETURN ROWIDIn order for CHARTOROWID to successfully convert the string, it must be of the format: BBBBBBBB.RRRR.FFFFwhere BBBBBBBB is the number of the block in the database file, RRRR is the number of the row in the block, and FFFF is the number of the database file. All three numbers must be in hexadecimal format. If the input string does not conform to the above format, PL/SQL raises the VALUE_ERROR exception. 14.2.2 The CONVERT function The CONVERT function converts strings from one character set to another character set. The specification of the CONVERT function is: FUNCTION CONVERT (string_in IN VARCHAR2, new_char_set VARCHAR2 [, old_char_set VARCHAR2]) RETURN VARCHAR2The old_char_set is an optional argument. If this third argument is not specified, then the default character set for the database instance is used. The CONVERT function does not translate words or phrases from one language to another! CONVERT simply substitutes the letter or symbol in one character set with the corresponding letter or symbol in another character set. (A character set is not the same thing as a human language.) Two commonly used character sets are US7ASCII (U.S. 7-bit ASCII character set) and F7DEC (DEC French 7-bit character set). 14.2.3 The HEXTORAW function The HEXTORAW function converts a hexadecimal string from type CHAR or VARCHAR2 to type RAW. The specification of the HEXTORAW function is: FUNCTION HEXTORAW (string_in IN CHAR) RETURN RAW FUNCTION HEXTORAW (string_in IN VARCHAR2) RETURN RAW14.2.4 The RAWTOHEX functionThe RAWTOHEX function converts a value from type RAW to a hexadecimal string of type VARCHAR2. The specification of the RAWTOHEX function is: FUNCTION RAWTOHEX (binary_value_in IN RAW) RETURN VARCHAR2RAWTOHEX always returns a variable-length string value, even if its mirror conversion function is overloaded to support both types of input. 14.2.5 The ROWIDTOCHAR function The ROWIDTOCHAR function converts a binary value of type ROWID to a string of type VARCHAR2. The specification of the ROWIDTOCHAR function is: FUNCTION ROWIDTOCHAR (row_in IN ROWID ) RETURN VARCHAR2The string returned by this function has the format:BBBBBBBB.RRRR.FFFFwhere BBBBBBBB is the number of the block in the database file, RRRR is the number of the row in the block, and FFFF is the number of the database file. All three numbers are in hexadecimal format. 14.2.6 The TO_CHAR function (date conversion) The TO_CHAR function can be used to convert both dates and numbers to a variable-length string. The following specification describes TO_CHAR for dates: FUNCTION TO_CHAR (date_in IN DATE [, format_mask IN VARCHAR2 [, nls_language IN VARCHAR2]]) RETURN VARCHAR2where date_in is the date to be converted to character format, the format_mask is the mask made up of one or more of the date format elements, and nls_language is a string specifying a date language. Both the format mask and the NLS language parameters are optional. If the format mask is not specified, then the default date format for the database instance is used. This format is DD-MON-YY, unless the initialization parameter NLS_DATE_FORMAT is included in the initialization file. The format of the specification of an alternative date mask is: NLS_DATE_FORMAT = 'MM/DD/YYYY'If the NLS language parameter is not specified, then the default date language for the instance is used. This is either the language for the instance specified by the NLS_LANGUAGE parameter, or the date language specified in the initialization file with the parameter NLS_DATE_LANGUAGE. Note that if you want to specify a date language, you also must include a format mask. You cannot skip over the intervening parameters. Here are some examples of TO_CHAR for date conversion:Notice that there are two blanks between month and day and a leading zero for the fifth day: TO_CHAR (SYSDATE, 'Month DD, YYYY') ==> 'February 05, 1994'Use the FM fill mode element to suppress blanks and zeros:TO_CHAR (SYSDATE, 'FMMonth DD, YYYY') ==> 'February 5, 1994'Note the case difference on the month abbreviations of the next two samples. You get exactly what you ask for with Oracle date formats! TO_CHAR (SYSDATE, 'MON DDth, YYYY') ==> 'FEB 05th, 1994' TO_CHAR (SYSDATE, 'fmMon DDth, YYYY') ==> 'Feb 5th, 1994'Show the day of year, the month, and the week for the date:TO_CHAR (SYSDATE, 'DDD DD D ') ==> '036 05 7' TO_CHAR (SYSDATE, 'fmDDD DD D ') ==> '36 5 7'Some fancy formatting for reporting purposes:TO_CHAR (SYSDATE, '"In month "RM" of year "YEAR') ==> 'In month II of year NINETEEN NINETY FOUR'14.2.7 The TO_CHAR function (number conversion) The TO_CHAR function converts numbers as well as dates. The specification of the TO_CHAR (number) function is: FUNCTION TO_CHAR (number_in IN NUMBER [, format_mask IN VARCHAR2 [, nls_language IN VARCHAR2]]) RETURN VARCHAR2;where number_in is the number to be converted to character format, the format_mask is the mask made up of one of more of the number format elements, and nls_language is a string specifying one or more of the NLS parameters which affect the way numbers are displayed. Both the format mask and the NLS language parameters are optional. If the format mask is not specified, then the default number format for the database instance is used. Here are some examples of TO_CHAR for number conversion:TO_CHAR (564.70, '$999.9') ==> $564.7 TO_CHAR (564.70, '$0000999.9') ==> $0000564.7 14.2.8 The TO_DATE function The TO_DATE function converts a character string to a true DATE datatype. The specification of the TO_DATE function is overloaded for string and number input: FUNCTION TO_DATE (string_in IN VARCHAR2 [, format_mask IN VARCHAR2 [, nls_language IN VARCHAR2 ]] ) RETURN DATE; FUNCTION TO_DATE (number_in IN NUMBER [, format_mask IN VARCHAR2 [, nls_language IN VARCHAR2 ]]) RETURN DATE;The second version of TO_DATE can be used only with the format mask of J for Julian date. The Julian date is the number of days which have passed since January 1, 4712 B.C. Only in this use of TO_DATE can a number be passed as the first parameter of TO_DATE. For all other cases, string_in is the string variable, literal, named constant, or expression to be converted, format_mask is the format mask TO_DATE will use to convert the string, and nls_language is a string which specifies the language which is to be used to interpret the names and abbreviations of both months and days in the string. The format of nls_language is as follows: 'NLS_DATE_LANGUAGE=<language>'where <language> is a language recognized by your instance of the database. You can usually determine the acceptable languages by checking your installation guide. Here are some examples of the TO_DATE function:Convert the string `123188' to a date:TO_DATE ('123188', 'MMDDYY') ==> 31-DEC-1988Convert a date using the Spanish language:TO_DATE ('Abril 12 1991', 'Month DD YYYY', 'NLS_DATE_LANGUAGE=Spanish') ==> 12-APR-1991Any Oracle errors between ORA-01800 and ORA-01899 are related to the internal Oracle date function and can arise when you encounter date conversion errors. You can learn additional nuances of date conversion rules by perusing the different errors and reading about the documented causes of these errors. Some of these rules are: A date literal passed to TO_CHAR for conversion to a date cannot be longer than 220 characters. You cannot include both a Julian date element (J) and the day of year element (DDD) in a single format mask. You cannot include multiple elements for the same component of the date/time in the mask. For example, the format mask YYYY-YYY-DD-MM is illegal because it includes two year elements, YYYY and YYY. You cannot use the 24-hour time format (HH24) and a meridian element (e.g., AM) in the same mask. 14.2.9 The TO_NUMBER function The TO_NUMBER function converts both fixed- and variable-length strings to numbers using the associated format mask. The specification of the TO_NUMBER function is as follows: FUNCTION TO_NUMBER (string_in IN CHAR [, format_mask VARCHAR2 [, nls_language VARCHAR2 ]]) RETURN NUMBER; FUNCTION TO_NUMBER (string_in IN VARCHAR2 [, format_mask VARCHAR2 [, nls_language VARCHAR2 ]]) RETURN NUMBER;where string_in is the string containing a sequence of characters to be converted to a number, format_mask is the optional string directing TO_NUMBER how to convert the character bytes to a number, and nls_language is a string containing up to three specifications of National Language Support parameters, as follows: NLS_NUMERIC_CHARACTERSThe characters used to specify the decimal point and the group separator in a number. The decimal point character for the American language is a dot (.) while the group separator is a comma (,). NLS_CURRENCYThe character(s) used to specify the local currency symbol. The currency character for the American language is a dollar sign ($). NLS_ISO_CURRENCYThe character(s) used to specify the international currency symbol in the string. The format for nls_language in the call to TO_NUMBER is as follows:'NLS_NUMERIC_CHARACTERS = ''string''' 'NLS_CURRENCY = ''string''' 'NLS_ISO_CURRENCY = ''string'''Two contiguous single quotes are needed before and after the values for each string value so that PL/SQL will parse the entire parameter and leave behind a single quote around each value. 14.1 Conversion Formats14.3 Conversion Function Examples Copyright (c) 2000 O'Reilly & Associates. All rights reserved.