Oracle Development Unleashed (3rd Edition)
Page 239
Conversion Functions
Table 11.3 lists available conversion functions, along with a brief description, argument list, and return value. Optional arguments are enclosed in square brackets. All internal conversion functions take the following form:
function CHARTOROWID (str VARCHAR2) return ROWID
Table 11.3. Conversion functions.
| Function | Description | Arguments | Return Value |
| | | | |
| CHARTOROWID | Converts str to type ROWID. | str CHAR or str VARCHAR2 | ROWID |
| CONVERT | Converts str from character set1 to character set2. Character set1 and set2 can be a character set name or database column. | str VARCHAR2, set1 VARCHAR2, set2 VARCHAR2 | VARCHAR2 |
| HEXTORAW | Converts str from CHAR or VARCHAR2 to RAW. | str CHAR or str VARCHAR2 | RAW |
| RAWTOHEX | Opposite of HEXTORAW. | bin RAW | VARCHAR2 |
| ROWIDTOCHAR | Converts bin from ROWID to 18-byte hex string. | bin ROWID | VARCHAR2 |
| TO_CHAR (Dates) | Converts dte to VARCHAR2 based on fmt. You can specify a language for date conversion in nlsparms. | dte DATE [, fmt VARCHAR2 [, nlsparms] ] | VARCHAR2 |
| TO_CHAR (Numbers) | Converts num to VARCHAR2 based on fmt. You can specify the following format elements in nlsparms: decimal character, group separator, and a symbol for local or international currency. | num NUMBER [, fmt VARCHAR2 [, nlsparms] ] | VARCHAR2 |
| TO_CHAR (Labels) | Converts MLSLABEL type to VARCHAR2 based on fmt. | label [, fmt VARCHAR2] | VARCHAR2 |
continues
Page 240
Table 11.3. continued
| Function | Description | Arguments | Return Value |
| | | | |
| TO_DATE | Converts str or num to a DATE value based on fmt. The fmt argument is not optional when converting a number. You can specify a language for date conversion in nlsparms. | str VARCHAR2 or num NUMBER [, fmt VARCHAR2] [, nlsparms] | DATE |
| TO_LABEL | Converts str to MLSLABEL datatype. If fmt is omitted, str must be in default label format. TO_LABEL is a Trusted Oracle function. | str CHAR or str VARCHAR2 [, fmt VARCHAR2] | MLSLABEL |
| TO_MULTI_BYTE | Converts single-byte str to multi-byte equivalent, if it exists. | str CHAR str VARCHAR2 | CHAR VARCHAR2 |
| TO_NUMBER | Converts str to NUMBER value according to fmt. You can specify format elements in nlsparms as described in the TO_CHAR function. | str CHAR str VARCHAR2 | NUMBER NUMBER |
| TO_SINGLE_BYTE | Opposite of TO_MULTI_BYTE. | str CHAR str VARCHAR2 | CHAR VARCHAR2 |
Date Functions
All date functions return a DATE value unless otherwise specified in Table 11.4, which lists available date functions, along with a brief description, argument list, and return value. Optional arguments are enclosed in square brackets. All internal date functions take the following form:
function ADD_MONTHS (dte DATE, num NUMBER) return DATE
Page 241
Table 11.4. Date functions.
| Function | Description | Arguments | Return Value |
| | | | |
| ADD_MONTHS | Returns dte plus or minus num months. | dte DATE, num NUMBER | |
| LAST_DAY | Returns last day of the month for dte. | dte DATE | |
| MONTHS_BETWEEN | Returns month count between dte1 and dte2. NUMBER is < 0 if dte1 is earlier than dte2. | dte1 DATE, dte2 DATE | NUMBER |
| NEW_TIME | Returns date and time in zon2 based on dte date and time in time zone zon1. | dte DATE, zon1 VARCHAR2, zon2 VARCHAR2 | |
| NEXT_DAY | Returns first day of the week for day that is later than dte. | dte DATE, day VARCHAR2 | |
| ROUND | Returns dte rounded to specified unit in fmt. If no fmt is specified, dte is rounded to the nearest day. | dte DATE [, fmt VARCHAR2] | |
| SYSDATE | Returns current system date and time. | No arguments. | |
| TRUNC | Returns dte with the time of day truncated as specified by fmt. | dte DATE [, fmt VARCHAR2] |
Miscellaneous Functions
Table 11.5 lists miscellaneous functions, along with a brief description, argument list, and return value. Optional arguments are enclosed in square brackets.
Page 242
Table 11.5. Miscellaneous functions.
| Function | Description | Arguments | Return Value |
| | | | |
| DUMP | Returns internal representation of expr, based on one of the following fmt specifications: 8=octal 10=decimal 16=hexadecimal 17=single character Arguments pos and len specify the portion of the representation to return. | expr DATE or expr NUMBER or expr VARCHAR2 [, fmt BINARY_INTEGER [, pos BINARY_INTEGER [, len BINARY_INTEGER]]] | VARCHAR2 |
| GREATEST | Returns greatest value of list of exprn. All expressions must be datatype compatible with expr1. | expr1, expr2, expr3 | |
| GREATEST_LB | Returns greatest lower bound from list of labels. Each label must be type MLSLABEL. GREATEST_LB is a Trusted Oracle function. | label [, label] | MLSLABEL |
| LEAST | Returns least value from list of exprn. All expressions must be datatype compatible with expr1. | expr1, expr2, expr3 | |
| LEAST_UB | Returns least upper bound from list of labels. Each label must be type MLSLABEL. LEAST_UB is a Trusted Oracle function.
| label [, label] | MLSLABEL |
| NVL | Returns value of not null arg1 or value of arg2. arg1 and arg2 must be of the same datatype. | arg1, arg2 | Datatype of arg1 and arg2 |
| UID | Returns unique ID number of current Oracle user . | No arguments | NUMBER |
| USER | Returns username of current Oracle user. | No arguments | VARCHAR2 |
Page 243
| Function | Description | Arguments | Return Value |
| | | | |
| USERENV | Returns current session infor- mation based on str, which can be one of the following: `ENTRYID' Audit entry identifier `LABEL' Session label `LANGUAGE' Language, territory, and database character set `SESSIONID' Auditing session identifier `TERMINAL' Session terminal type | str VARCHAR2 | VARCHAR2 |
| VSIZE | Returns number of bytes in expr. | expr DATE or expr NUMBER or expr VARCHAR2 | NUMBER |
Number Functions
All number functions return a NUMBER value unless otherwise specified in Table 11.6, which lists available number functions, along with a brief description, argument list, and return value. Optional arguments are enclosed in square brackets. All internal number functions take the following form:
function ABS (n NUMBER) return NUMBER
Table 11.6. Number functions.
| Function | Description | Arguments | Return Value |
| | | | |
| ABS | Returns absolute value of n. | n NUMBER | |
| CEIL | Returns smallest integer >= n. | n NUMBER | |
| COS | Returns cosine of a. Angle a must be in radians. | a NUMBER | |
| COSH | Returns hyperbolic cosine of n. | n NUMBER | |
| EXP | Returns value of e n . | n NUMBER | |
| FLOOR | Returns largest integer <= n. | n NUMBER | |
| LN | Returns natural log of n where n > 0. | n NUMBER |
continues