This section describes the various conversion functions provided by PL/SQL.
FUNCTION CHARTOROWID (string_in IN CHAR) RETURN ROWID FUNCTION CHARTOROWID (string_in IN VARCHAR2) RETURN ROWID
In order for CHARTOROWID to successfully convert the string, it must be of the format:
where 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.
FUNCTION CONVERT (string_in IN VARCHAR2, new_char_set VARCHAR2 [, old_char_set VARCHAR2]) RETURN VARCHAR2
The 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).
FUNCTION HEXTORAW (string_in IN CHAR) RETURN RAW FUNCTION HEXTORAW (string_in IN VARCHAR2) RETURN RAW
FUNCTION RAWTOHEX (binary_value_in IN RAW) RETURN VARCHAR2
RAWTOHEX always returns a variable-length string value, even if its mirror conversion function is overloaded to support both types of input.
FUNCTION ROWIDTOCHAR (row_in IN ROWID ) RETURN VARCHAR2
The string returned by this function has the format:
where 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.
FUNCTION TO_CHAR (date_in IN DATE [, format_mask IN VARCHAR2 [, nls_language IN VARCHAR2]]) RETURN VARCHAR2
where 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'
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
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:
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-1988
Convert a date using the Spanish language:
TO_DATE ('Abril 12 1991', 'Month DD YYYY', 'NLS_DATE_LANGUAGE=Spanish') ==> 12-APR-1991
Any 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.
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:
The 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 (
The character(s) used to specify the local currency symbol. The currency character for the American language is a dollar sign (
The 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.
Copyright (c) 2000 O'Reilly & Associates. All rights reserved.