everythingOracle.bizhat.com

 

'Everything you wanted
to know about Oracle'

Training References Syntax Tell A Friend Contact Us

 

 

Oracle Functions


 

ABS(n)

Absolute value of number

 

ACOS(n)

arc cosine of n

 

ADD_MONTHS(date,num_months)

Returns date + num_months

 

ASCII(char)

Converts char into a decimal ascii code

 

ASIN(n)

arc sine of n.

 

ATAN(n)

arc tangent of n.

 

ATAN2(n.m)

arc tangent of n and m.

 

AVG([DISTINCT]n)

Averge value of 'n' ignoring NULLs

 

BETWEEN value AND value

Where 'x' between 25 AND 100

 

BFILENAME('directory','filename')

Get the BFILE locator associated with a physical LOB binary file

 

CEIL(n)

Round n up to next whole number

 

CHARTOROWID(char)

Converts a Char into a rowid value.

 

CHR(n)

Character with value n

 

CONCAT(s1,s2)

Concatenate string1 and string2

 

CONVERT (char_to_convert, new_char_set, old_char_set)

Convert character sets

 

COS(n)

Cosine of number

 

COSH(n)

Hyperbolic Cosine of number

 

COUNT(*)

Count the no of rows returned

 

COUNT([DISTINCT] expr)

Count the no of rows returned by expr

 

DECODE

IF x THEN return y ELSE return z

 

DENSE_RANK

Calculate the rank of a value in a group

 

DEREF(e)

Return the object reference of argument e.

 

DUMP(expr,fmt[,start,length])

Convert to dec/hex/oct and display char set

 

EMPTY_BLOB

Return an empty LOB locator (use to empty a column or variable)

 

EMPTY_CLOB

Return an empty LOB locator (use to empty a column or variable)

 

EXISTS

Return TRUE if a subquery returns at least one row

 

EXP(n)

Exponential (e to 'n'th power)

 

FLOOR(n)

Round n down to the next whole number.

 

GREATEST(expression, expression...)

Returns the largest in a list of expressions.

 

GROUPING

Grouping for superaggregate rows=NULL (see GROUP BY ROLLUP/CUBE)

 

HEXTORAW(char)

Convert char containing hex digits to a raw value.

 

IN (list of comma separated values)

Effectively a shorthand for ['x' = y OR 'x' = z...] i.e. Where 'x' IN ('sales','marketing','recruitment')

 

INITCAP(char)

String with Initial Capitals

 

INSTR(str, chars[,s[,n]])

Find the 'n'th occurence of 'chars' in 'str' Starting at position 's' n and s default to 1

 

INSTRB (str, chars[,s[,n]])

The same as INSTR, except that 's' and the return value are expressed in bytes, use for double-byte char sets

 

IS [NOT] NULL

Check for NULL (empty) values Where 'x' IS NULL;

 

LAST_DAY(date)

Returns the last day of month in Date

 

LEAST(expression, expression...)

Returns the smallest in a list of expressions

 

LENGTH(char)

Returns the number of characters in char

 

LENGTHB(char)

Returns the number of bytes in char (use for double-byte char sets)

 

LIKE wildcard/value

Wildcards are [% = any chars] [ _ = any one char]Where 'x' LIKE 'smith%' [will find 'Smithson'] Where 'x' LIKE 'smith_' [will find 'Smithy']

 

LN(n)

Natural Log of n, where n>0

 

LOG(b,n)

log of n, base b

 

LOWER(char)

Returns Chars in lowercase

 

LPAD(char, n[,PadChar])

Left Pad char with n spaces [or PadChars] LTRIM(char[,set])Left Trim char - remove leading spaces [or char set]

 

MAKE_REF(table,key)

Create a REF to a row of an OBJECT view/table

 

MAX([DISTINCT] expr)

Maximum value returned by expr

 

MIN([DISTINCT] expr)

Minimum value returned by expr

 

MOD(x,y)

Remainder of x divided by y

 

MONTHS_BETWEEN(end_date, start_date)

Number of months between the 2 dates (integer)

 

NEW_TIME (date, zone1, zone2)

Convert between GMT and US time zones (but not CET)

 

NEXT_DAY(date,day_of_week)

'12-OCT-01','Monday' will return the next Mon after 12 Oct

 

NLS_CHARSET_DECL_LEN  (bytecount,charset)

Returns the declaration width (no of chars) of an NCHAR column

 

NLS_CHARSET_ID (varchars)

Returns the char set ID given a charset name

 

NLS_CHARSET_NAME(charset_id)

Returns the char set name given a charset id

 

NLS_INITCAP(char[,'NLS_SORT = sort_sequence'])

Returns char in Initial Caps, using an NLS sort_sequence either the session default or specified directly

 

NLS_LOWER(char[,'NLS_SORT = sort_sequence'])

Returns char in lower case, using an NLS sort_sequence either the session default or specified directly

 

NLSSORT(char[,'NLS_SORT = sort_sequence'])

Return the string of bytes used to sort char, using an NLS sort_sequence either the session default or specified directly

 

NLS_UPPER (char[,'NLS_SORT = sort_sequence'])

Returns char in UPPER  case, using an NLS sort_sequence either the session default or specified directly

 

NVL(expression, value_if_null)

If expression is null, returns value_if_null; if expression is not null, returns expression. The arguments can have any datatype (Oracle will perform implicit conversion where needed).

 

PERCENT_RANK

Calculate the percent rank of a value in a group.

 

POWER(m,n)

m raised to the nth power

 

RANK

Calculate the rank of a value in a group

 

RAWTOHEX(raw)

Convert raw to a character value containing its hex equivalent

 

REF(table_alias)

Returns a REF value for an object instance (bound to the variable or row.) The table alias (correlation variable) is associated with one row of an object table or an object view in an SQL statement.

 

REFTOHEX(ref)

Convert ref (object type) to a char value containing its hex equivalent.

 

REPLACE(char, search_str[, replace_str])

ANSI alternative to decode() Replace every occurrence of search_str with replace_str, replace_str defaults to null.

 

ROUND(n,d)

n rounded to d decimal places (d defaults to 0)

 

ROUND(date,fmt)

date rounded to fmt

 

ROWIDTOCHAR(rowid)

Convert a rowid value to VARCHAR2

 

ROW_NUMBER

Assign a unique number to each row of results.

 

RPAD(char, n[,PadChar])

Right Pad char with n spaces [or PadChars]

 

RTRIM(char[,set])

Right Trim char - remove trailing spaces [or char set]

 

SIGN(n)

positive = 1, zero = 0, negative = -1

 

SIN(n)

Sine of n in Radians

 

SINH(n)

Hyperbolic Sine of n in Radians

 

SOUNDEX(char)

Returns a char value representing the sound of the words

 

SQRT(n)

Square Root (returns NULL for negative no's)

 

STDDEV([DISTINCT] n)

Standard deviation of n

 

SUBSTR(char, s[,l])

A substring of char, starting at character s, length l

 

SUBSTRB(char, s[,l])

A substring of char, starting at character s, length l. The same as SUBSTR, except that 's', 'l' and the return value are expressed in bytes, use for double-byte char sets

 

SUM([DISTINCT] n)

Sum of values of n, ignoring NULLs

 

SYS_CONTEXT('namespace','attribute_name')

Examine the package associated with the context namespace.

Possible attributes are:

NLS_TERRITORY,

NLS_CURRENCY, NLS_CALENDAR,

NLS_DATE_FORMAT,

NLS_DATE_LANGUAGE,

NLS_SORT,

SESSION_USER ,

CURRENT_USER ,          

CURRENT SCHEMAID,

SESSION_USERID,

CURRENT_USERID,

CURRENT_SCHEMA

 

note: CURRENT_USER  may be different from SESSION_USER within a stored procedure (e.g an invoker-rights procedure). 

 

SYS_GUID()

Returns a globally unique identifier (16 byte RAW value)

 

SYSDATE

The current system date & time

 

TAN(n)

Tangent of n in Radians

 

TANH(n)

Hyperbolic tangent of n in Radians

 

TO_CHAR

Convert to character String

 

TO_DATE

Convert to date value

 

TO_LOB(long)

Convert LONG values to CLOB or NCLOB values or convert LONG RAW values to BLOB values. Use only as part of an "INSERT INTO ... SELECT..." subquery.

 

TO_MULTI_BYTE(char)

Convert single-byte char to multi-byte char.

 

TO_NUMBER

Convert to numeric format

 

TO_SINGLE_BYTE(char)

Convert multi-byte char to single-byte char.

 

TRANSLATE('char','search_str','replace_str')

Replace every occurrence of search_str with replace_str unlike REPLACE() if replace_str is NULL the function returns NULL

 

TRANSLATE (text USING charset)

Convert text into a specific character set. Use this instead of CONVERT () if either the input or output datatype is NCHAR or NVARCHAR2.

 

TRIM (LEADING|TRAILING|BOTH trim_char FROM trim_source)

Returns trim_source as a VARCHAR2 with leading/trailing items removed trim_char defaults to a space ' ' but may be numeric or char 'A' TRUNC (i,d) i truncated to d decimal places (d defaults to 0)

 

TRUNC (date,fmt)

date truncated to nearest fmt

 

UID

User id - unique number

 

UPPER (char)

Returns Chars in uppercase

 

USER

Returns the current Username

 

USERENV ('option')

Can return any of the options: ENTRYID, SESSIONID, TERMINAL, LANGUAGE, ISDBA, LANG, INSTANCE, CLIENT_INFO

 

VALUE (correlation_variable)

Return the object instance for a row of an object table as associated with the correlation_variable (table alias)

 

VARIANCE ([DISTINCT] n)

Variance of n, ignoring NULLs

 

VSIZE (expr)

Value Size - returns the number of bytes used by each row of expr.

     Reviews and Templates for FrontPage
     

Copyright � everythingOracle.bizhat.com 2006 All Rights Reserved.