0239-0243

Previous Table of Contents Next

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 

Previous Table of Contents Next