ORACLE/PLSQL: TRUNC FUNCTION (WITH NUMBERS)

This Oracle tutorial explains how to use the Oracle/PLSQL TRUNC function (as it applies to numeric values) with syntax and examples.

DESCRIPTION

The Oracle/PLSQL TRUNC function returns a number truncated to a certain number of decimal places.

SYNTAX (WITH NUMBERS)

The syntax for the Oracle/PLSQL TRUNC function is:

TRUNC( number, [ decimal_places ] )

Parameters or Arguments

number is the number to truncate.

decimal_places is the number of decimal places to truncate to. This value must be an integer. If this parameter is omitted, the TRUNC function will truncate the number to 0 decimal places.

APPLIES TO

The TRUNC function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE (WITH NUMBERS)

Let's look at some Oracle TRUNC function examples and explore how to use the TRUNC function in Oracle/PLSQL.

For example:

TRUNC(125.815)
Result: 125

TRUNC(125.815, 0)
Result: 125

TRUNC(125.815, 1)
Result: 125.8

TRUNC(125.815, 2)
Result: 125.81

TRUNC(125.815, 3)
Result: 125.815

TRUNC(-125.815, 2)
Result: -125.81

TRUNC(125.815, -1)
Result: 120

TRUNC(125.815, -2)
Result: 100

TRUNC(125.815, -3)
Result: 0

ORACLE/PLSQL: GREATEST FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL GREATEST function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL GREATEST function returns the greatest value in a list of expressions.

SYNTAX

The syntax for the Oracle/PLSQL GREATEST function is:

GREATEST( expr1, expr2, ... expr_n )

Parameters or Arguments

expr1, expr2, . expr_n are expressions that are evaluated by the GREATEST function.

NOTE

If the datatypes of the expressions are different, all expressions will be converted to whatever datatype expr1 is.

If the comparison is based on a character comparison, one character is considered greater than another if it has a higher character set value.

APPLIES TO

The GREATEST function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle GREATEST function examples and explore how to use the GREATEST function in Oracle/PLSQL.

For example:

GREATEST(2, 5, 12, 3)
Result: 12

GREATEST('2', '5', '12', '3')
Result: '5'

GREATEST('apples', 'oranges', 'bananas')
Result: 'oranges'

GREATEST('apples', 'applis', 'applas')
Result: 'applis'

ORACLE/PLSQL: LEAST FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL LEAST function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL LEAST function returns the smallest value in a list of expressions.

SYNTAX

The syntax for the Oracle/PLSQL LEAST function is:

LEAST( expr1, expr2, ... expr_n )

Parameters or Arguments

expr1, expr2, . expr_n are expressions that are evaluated by the LEAST function.

NOTE

If the datatypes of the expressions are different, all expressions will be converted to whatever datatype expr1 is.
If the comparison is based on a character comparison, one character is considered smaller than another if it has a lower character set value.
Having a NULL value in one of the expressions will return NULL as the least value.

APPLIES TO

The LEAST function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle LEAST function examples and explore how to use the LEAST function in Oracle/PLSQL.

For example:

LEAST(2, 5, 12, 3)
Result: 2

LEAST('2', '5', '12', '3')
Result: '12'

LEAST('apples', 'oranges', 'bananas')
Result: 'apples'

LEAST('apples', 'applis', 'applas')
Result: 'applas'

LEAST('apples', 'applis', 'applas', null)
Result: NULL

ORACLE/PLSQL: SIGN FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL SIGN function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL SIGN function returns a value indicating the sign of a number.

SYNTAX

The syntax for the Oracle/PLSQL SIGN function is:

SIGN( number )

Parameters or Arguments

number is the number to test for its sign.

NOTE

If number < 0, then sign returns -1.

If number = 0, then sign returns 0.

If number > 0, then sign returns 1.

APPLIES TO

The SIGN function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle SIGN function examples and explore how to use the SIGN function in Oracle/PLSQL.

For example:

SIGN(-23)
Result: -1

SIGN(-0.001)
Result: -1

SIGN(0)
Result: 0

SIGN(0.001)
Result: 1

SIGN(23)
Result: 1

SIGN(23.601)
Result: 1

ORACLE/PLSQL: EXP FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL EXP function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL EXP function returns e raised to the nth power, where e = 2.71828183.

SYNTAX

The syntax for the Oracle/PLSQL EXP function is:

exp( number )

Parameters or Arguments

number is the power to raise e to.

APPLIES TO

The EXP function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle EXP function examples and explore how to use the EXP function in Oracle/PLSQL.

For example:

EXP(3)
Result: 20.0855369231877

EXP(3.1)
Result: 22.1979512814416

EXP(-3)
Result: 0.0497870683678639

ORACLE/PLSQL: POWER FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL POWER function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL POWER function returns m raised to the nth power.

SYNTAX

The syntax for the Oracle/PLSQL POWER function is:

POWER( m, n )

Parameters or Arguments

m is the base.

n is the exponent.

NOTE

If m is negative, then n must be an integer.

APPLIES TO

The POWER function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle POWER function examples and explore how to use the POWER function in Oracle/PLSQL.

For example:

POWER(3, 2)
Result: 9

POWER(5, 3)
Result: 125

POWER(-5, 3)
Result: -125

POWER(6.2, 3)
Result: 238.328

POWER(6.2, 3.5)
Result: 593.431934277892

ORACLE/PLSQL: MOD FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL MOD function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL MOD function returns the remainder of m divided by n.

SYNTAX

The syntax for the Oracle/PLSQL MOD function is:

MOD( m, n )

Parameters or Arguments

The MOD is calculated as:

m - n * floor(m/n)

NOTE

The MOD function uses the floor function in its formula, whereas the REMAINDER function uses the ROUND function in its formula.

The MOD function returns m if n is 0.

APPLIES TO

The MOD function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle MOD function examples and explore how to use the MOD function in Oracle/PLSQL.

For example:

MOD(15, 4)
Result: 3

MOD(15, 0)
Result: 15

MOD(11.6, 2)
Result: 1.6

MOD(11.6, 2.1)
Result: 1.1

MOD(-15, 4)
Result: -3

MOD(-15, 0)
Result: -15

ORACLE/PLSQL: REMAINDER FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL REMAINDER function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL REMAINDER function returns the remainder of m divided by n.

SYNTAX

The syntax for the Oracle/PLSQL REMAINDER function is:

REMAINDER( m, n )

Parameters or Arguments

The REMAINDER is calculated as:

m - (n * X) where X is the integer nearest m / n

NOTE

The REMAINDER function uses the round function in its formula, whereas the MOD function uses the floor function in its formula.

APPLIES TO

The REMAINDER function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g

EXAMPLE

Let's look at some Oracle REMAINDER function examples and explore how to use the REMAINDER function in Oracle/PLSQL.

For example:

REMAINDER(15, 6)
Result: 3

REMAINDER(15, 5)
Result: 0

REMAINDER(15, 4)
Result: -1

REMAINDER(11.6, 2)
Result: -0.4

REMAINDER(11.6, 2.1)
Result: -1

REMAINDER(-15, 4)
Result: 1

ORACLE/PLSQL: MEDIAN FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL MEDIAN function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL MEDIAN function returns the median of an expression.

SYNTAX

The syntax for the Oracle/PLSQL MEDIAN function is:

MEDIAN( expression ) [ OVER ( query partition clause ) ]

Parameters or Arguments

expression is the value used to calculate the median.

NOTE

The MEDIAN function is NEW to Oracle 10! In older versions of Oracle, try using the PERCENTILE_CONT function to calculate the median value.

APPLIES TO

The MEDIAN function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g

EXAMPLE

Let's look at some Oracle MEDIAN function examples and explore how to use the MEDIAN function in Oracle/PLSQL.

For example:

select MEDIAN(salary)
from employees
where department = 'Marketing';

The SQL statement above would return the median salary for all employees in the Marketing department.

ORACLE/PLSQL: CORR FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL CORR function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL CORR function returns the coefficient of correlation of a set of number pairs.

SYNTAX

The syntax for the Oracle/PLSQL CORR function is:

CORR( n ,m ) [ over (analytic_clause) ]

Parameters or Arguments

n and m are the numbers to use to calculate the cofficient of correlation.

NOTE

The CORR function requires at least two rows.

APPLIES TO

The CORR function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle CORR function examples and explore how to use the CORR function in Oracle/PLSQL.

For example:

select CORR(quantity, commission)
from sales;

Here is an example using the GROUP BY clause:

select max_entents, CORR(max_trans, initial_extent)
from data
group by max_extents;

ORACLE/PLSQL: EXTRACT FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL EXTRACT function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL EXTRACT function extracts a value from a date or interval value.

SYNTAX

The syntax for the Oracle/PLSQL EXTRACT function is:

EXTRACT (
{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }
| { TIMEZONE_HOUR | TIMEZONE_MINUTE }
| { TIMEZONE_REGION | TIMEZONE_ABBR }
FROM { date_value | interval_value } )

NOTE

You can only extract YEAR, MONTH, and DAY from a DATE.

You can only extract TIMEZONE_HOUR and TIMEZONE_MINUTE from a timestamp with a time zone datatype.

APPLIES TO

The EXTRACT function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

EXAMPLE

Let's look at some Oracle EXTRACT function examples and explore how to use the EXTRACT function in Oracle/PLSQL.

For example:

EXTRACT(YEAR FROM DATE '2003-08-22')
Result: 2003

EXTRACT(MONTH FROM DATE '2003-08-22')
Result: 8

EXTRACT(DAY FROM DATE '2003-08-22')
Result: 22

ORACLE/PLSQL: RANK FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL RANK function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL RANK function returns the rank of a value in a group of values. It is very similar to the DENSE_RANK function. However, the rank function can cause non-consecutive rankings if the tested values are the same. Whereas, theDENSE_RANK function will always result in consecutive rankings.

The RANK function can be used two ways - as an Aggregate function or as an Analytic function.

RANK FUNCTION SYNTAX #1 - USED AS AN AGGREGATE FUNCTION

As an Aggregate function, the RANK function returns the rank of a row within a group of rows.

The syntax for the RANK function when used as an Aggregate function is:

RANK( expression1, ... expression_n ) WITHIN GROUP ( ORDER BY expression1, ... expression_n )

Parameters or Arguments

expression1 .. expression_n can be one or more expressions which identify a unique row in the group.

NOTE

There must be the same number of expressions in the first expression list as there is in the ORDER BY clause.

The expression lists match by position so the data types must be compatible between the expressions in the first expression list as in the ORDER BY clause.

APPLIES TO

The RANK function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

EXAMPLE (AS AN AGGREGATE FUNCTION)

Let's look at some Oracle RANK function examples and explore how to use the RANK function in Oracle/PLSQL.

For example:

select RANK(1000, 500) WITHIN GROUP (ORDER BY salary, bonus)
from employees;

The SQL statement above would return the rank of an employee with a salary of $1,000 and a bonus of $500 from within the employees table.

RANK FUNCTION SYNTAX #2 - USED AS AN ANALYTIC FUNCTION

As an Analytic function, the RANK function returns the rank of each row of a query with respective to the other rows.

The syntax for the RANK function when used as an Analytic function is:

rank() OVER ( [ query_partition_clause] ORDER BY clause )

APPLIES TO

The RANK function can be used in the following versions of Oracle/PLSQL:

Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE (AS AN ANALYTIC FUNCTION)

select employee_name, salary,
RANK() OVER (PARTITION BY department ORDER BY salary)
from employees
where department = 'Marketing';

The SQL statement above would return all employees who work in the Marketing department and then calculate a rank for each unique salary in the Marketing department. If two employees had the same salary, the RANK function would return the same rank for both employees. However, this will cause a gap in the ranks (ie: non-consecutive ranks). This is quite different from the DENSE_RANK function which generates consecutive rankings.

ORACLE/PLSQL: SQRT FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL SQRT function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL SQRT function returns the square root of n.

SYNTAX

The syntax for the Oracle/PLSQL SQRT function is:

SQRT( n )

Parameters or Arguments

n is a positive number.

APPLIES TO

The SQRT function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle SQRT function examples and explore how to use the SQRT function in Oracle/PLSQL.

For example:

SQRT(9)
Result: 3

SQRT(37)
Result: 6.08276253029822

SQRT(5.617)
Result: 2.37002109695251

ORACLE/PLSQL: TO_CHAR FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL TO_CHAR function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL TO_CHAR function converts a number or date to a string.

SYNTAX

The syntax for the Oracle/PLSQL TO_CHAR function is:

TO_CHAR( value, [ format_mask ], [ nls_language ] )

Parameters or Arguments

value can either be a number or date that will be converted to a string.

format_mask is optional. This is the format that will be used to convert value to a string.

nls_language is optional. This is the nls language used to convert value to a string.

APPLIES TO

The TO_CHAR function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle TO_CHAR function examples and explore how to use the TO_CHAR function in Oracle/PLSQL.

with Numbers

For example:

The following are number examples for the TO_CHAR function.

TO_CHAR(1210.73, '9999.9')
Result: ' 1210.7'

TO_CHAR(-1210.73, '9999.9')
Result: '-1210.7'

TO_CHAR(1210.73, '9,999.99')
Result: ' 1,210.73'

TO_CHAR(1210.73, '$9,999.00')
Result: ' $1,210.73'

TO_CHAR(21, '000099')
Result: ' 000021'

with Dates

The following is a list of valid parameters when the TO_CHAR function is used to convert a date to a string. These parameters can be used in many combinations.

Parameter	Explanation
YEAR	Year, spelled out
YYYY	4-digit year
YYY YY Y	Last 3, 2, or 1 digit(s) of year.
IYY IY I	Last 3, 2, or 1 digit(s) of ISO year.
IYYY	4-digit year based on the ISO standard
Q	Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM	Month (01-12; JAN = 01).
MON	Abbreviated name of month.
MONTH	Name of month, padded with blanks to length of 9 characters.
RM	Roman numeral month (I-XII; JAN = I).
WW	Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W	Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW	Week of year (1-52 or 1-53) based on the ISO standard.
D	Day of week (1-7).
DAY	Name of day.
DD	Day of month (1-31).
DDD	Day of year (1-366).
DY	Abbreviated name of day.
J	Julian day; the number of days since January 1, 4712 BC.
HH	Hour of day (1-12).
HH12	Hour of day (1-12).
HH24	Hour of day (0-23).
MI	Minute (0-59).
SS	Second (0-59).
SSSSS	Seconds past midnight (0-86399).
FF	Fractional seconds.

The following are date examples for the TO_CHAR function.

TO_CHAR(sysdate, 'yyyy/mm/dd')
Result: '2003/07/09'

TO_CHAR(sysdate, 'Month DD, YYYY')
Result: 'July 09, 2003'

TO_CHAR(sysdate, 'FMMonth DD, YYYY')
Result: 'July 9, 2003'

TO_CHAR(sysdate, 'MON DDth, YYYY')
Result: 'JUL 09TH, 2003'

TO_CHAR(sysdate, 'FMMON DDth, YYYY')
Result: 'JUL 9TH, 2003'

TO_CHAR(sysdate, 'FMMon ddth, YYYY')
Result: 'Jul 9th, 2003'

You will notice that in some TO_CHAR function examples, the format_mask parameter begins with "FM". This means that zeros and blanks are suppressed. This can be seen in the examples below.

TO_CHAR(sysdate, 'FMMonth DD, YYYY')
Result: 'July 9, 2003'

TO_CHAR(sysdate, 'FMMON DDth, YYYY')
Result: 'JUL 9TH, 2003'

TO_CHAR(sysdate, 'FMMon ddth, YYYY')
Result: 'Jul 9th, 2003'

The zeros have been suppressed so that the day component shows as "9" as opposed to "09".

FREQUENTLY ASKED QUESTIONS

Question: Why doesn't this sort the days of the week in order?

SELECT ename, hiredate, TO_CHAR((hiredate),'fmDay') "Day"
FROM emp
ORDER BY "Day";

Answer: In the above SQL, the fmDay format mask used in the TO_CHAR function will return the name of the Day and not the numeric value of the day.

To sort the days of the week in order, you need to return the numeric value of the day by using the fmD format mask as follows:

SELECT ename, hiredate, TO_CHAR((hiredate),'fmD') "Day"
FROM emp
ORDER BY "Day";

ORACLE/PLSQL: TO_DATE FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL TO_DATE function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL TO_DATE function converts a string to a date.

SYNTAX

The syntax for the Oracle/PLSQL TO_DATE function is:

TO_DATE( string1, [ format_mask ], [ nls_language ] )

Parameters or Arguments

string1 is the string that will be converted to a date.

format_mask is optional. This is the format that will be used to convert string1 to a date.

nls_language is optional. This is the nls language used to convert string1 to a date.

The following is a list of options for the format_mask parameter. These parameters can be used in many combinations.

Parameter	Explanation
YEAR	Year, spelled out
YYYY	4-digit year
YYY YY Y	Last 3, 2, or 1 digit(s) of year.
IYY IY I	Last 3, 2, or 1 digit(s) of ISO year.
IYYY	4-digit year based on the ISO standard
RRRR	Accepts a 2-digit year and returns a 4-digit year. A value between 0-49 will return a 20xx year. A value between 50-99 will return a 19xx year.
Q	Quarter of year (1, 2, 3, 4; JAN-MAR = 1).
MM	Month (01-12; JAN = 01).
MON	Abbreviated name of month.
MONTH	Name of month, padded with blanks to length of 9 characters.
RM	Roman numeral month (I-XII; JAN = I).
WW	Week of year (1-53) where week 1 starts on the first day of the year and continues to the seventh day of the year.
W	Week of month (1-5) where week 1 starts on the first day of the month and ends on the seventh.
IW	Week of year (1-52 or 1-53) based on the ISO standard.
D	Day of week (1-7).
DAY	Name of day.
DD	Day of month (1-31).
DDD	Day of year (1-366).
DY	Abbreviated name of day.
J	Julian day; the number of days since January 1, 4712 BC.
HH	Hour of day (1-12).
HH12	Hour of day (1-12).
HH24	Hour of day (0-23).
MI	Minute (0-59).
SS	Second (0-59).
SSSSS	Seconds past midnight (0-86399).
FF	Fractional seconds. Use a value from 1 to 9 after FF to indicate the number of digits in the fractional seconds. For example, 'FF4'.
AM, A.M., PM, or P.M.	Meridian indicator
AD or A.D	AD indicator
BC or B.C.	BC indicator
TZD	Daylight savings information. For example, 'PST'
TZH	Time zone hour.
TZM	Time zone minute.
TZR	Time zone region.

APPLIES TO

The TO_DATE function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle TO_DATE function examples and explore how to use the TO_DATE function in Oracle/PLSQL.

For example:

TO_DATE('2003/07/09', 'yyyy/mm/dd')
Result: date value of July 9, 2003

TO_DATE('070903', 'MMDDYY')
Result: date value of July 9, 2003

TO_DATE('20020315', 'yyyymmdd')
Result: date value of Mar 15, 2002

ORACLE/PLSQL: TO_NUMBER FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL TO_NUMBER function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL TO_NUMBER function converts a string to a number.

SYNTAX

The syntax for the Oracle/PLSQL TO_NUMBER function is:

TO_NUMBER( string1, [ format_mask ], [ nls_language ] )

Parameters or Arguments

string1 is the string that will be converted to a number.

format_mask is optional. This is the format that will be used to convert string1 to a number.

nls_language is optional. This is the nls language used to convert string1 to a number.

APPLIES TO

The TO_NUMBER function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle TO_NUMBER function examples and explore how to use the TO_NUMBER function in Oracle/PLSQL.

For example:

TO_NUMBER('1210.73', '9999.99')
Result: 1210.73

TO_NUMBER('546', '999')
Result: 546

TO_NUMBER('23', '99')
Result: 23

Since the format_mask and nls_language parameters are optional, you can simply convert a text string to a numeric value as follows:

TO_NUMBER('1210.73')
Result: 1210.73

ORACLE/PLSQL: TO_TIMESTAMP FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL TO_TIMESTAMP function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL TO_TIMESTAMP function converts a string to a timestamp.

SYNTAX

The syntax for the Oracle/PLSQL TO_TIMESTAMP function is:

TO_TIMESTAMP( string1, [ format_mask ] [ 'nlsparam' ] )

Parameters or Arguments

string1 is the string that will be converted to a timestamp.

format_mask is optional. This is the format that will be used to convert string1 to a timestamp.

The following is a list of options for the format_mask parameter. These parameters can be used in many combinations.

Parameter	Explanation
YYYY	4-digit year
MM	Month (01-12; JAN = 01).
MON	Abbreviated name of month.
MONTH	Name of month, padded with blanks to length of 9 characters.
DD	Day of month (1-31).
HH	Hour of day (1-12).
HH12	Hour of day (1-12).
HH24	Hour of day (0-23).
MI	Minute (0-59).
SS	Second (0-59).

APPLIES TO

The TO_TIMESTAMP function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

EXAMPLE

Let's look at some Oracle TO_TIMESTAMP function examples and explore how to use the TO_TIMESTAMP function in Oracle/PLSQL.

For example:

TO_TIMESTAMP('2003/12/13 10:13:18', 'YYYY/MM/DD HH:MI:SS')

would return '13-DEC-03 10.13.18.000000000 AM' as a timestamp value.

TO_TIMESTAMP('2003/DEC/13 10:13:18', 'YYYY/MON/DD HH:MI:SS')

would also return '13-DEC-03 10.13.18.000000000 AM' as a timestamp value.

ORACLE/PLSQL: TO_TIMESTAMP_TZ FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL TO_TIMESTAMP_TZ function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL TO_TIMESTAMP_TZ function converts a string to a timestamp with time zone.

SYNTAX

The syntax for the Oracle/PLSQL TO_TIMESTAMP_TZ function is:

TO_TIMESTAMP_TZ( string1 , [ format_mask ] [ 'nlsparam' ] )

Parameters or Arguments

string1 is the string that will be converted to a timestamp with time zone.

format_mask is optional. This is the format that will be used to convert string1 to a timestamp with time zone.

The following is a list of options for the format_mask parameter. These parameters can be used in many combinations.

Parameter	Explanation
YYYY	4-digit year
MM	Month (01-12; JAN = 01).
MON	Abbreviated name of month.
MONTH	Name of month, padded with blanks to length of 9 characters.
DD	Day of month (1-31).
HH	Hour of day (1-12).
HH12	Hour of day (1-12).
HH24	Hour of day (0-23).
MI	Minute (0-59).
SS	Second (0-59).
TZM	Time zone minute.
TZH	Time zone hour.

APPLIES TO

The TO_TIMESTAMP_TZ function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

EXAMPLE

Let's look at some Oracle TO_TIMESTAMP_TZ function examples and explore how to use the TO_TIMESTAMP_TZ function in Oracle/PLSQL.

For example:

TO_TIMESTAMP_TZ('2003/12/13 10:13:18 -8:00', 'YYYY/MM/DD HH:MI:SS TZH:TZM')

would return '13-DEC-03 10.13.18.000000000 AM -08:00' as a timestamp with time zone value.

TO_TIMESTAMP_TZ('2003/DEC/13 10:13:18 -8:00', 'YYYY/MON/DD HH:MI:SS TZH:TZM')

would also return '13-DEC-03 10.13.18.000000000 AM -08:00' as a timestamp with time zone value.

ORACLE/PLSQL: TO_YMINTERVAL FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL TO_YMINTERVAL function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL TO_YMINTERVAL function converts a string to an INTERVAL YEAR TO MONTH type.

SYNTAX

The syntax for the Oracle/PLSQL TO_YMINTERVAL function is:

TO_YMINTERVAL( character )

Parameters or Arguments

character is the value to convert to an INTERVAL YEAR TO MONTH type. It can be a char, varchar2, nchar, or nvarchar2 value.

APPLIES TO

The TO_YMINTERVAL function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

EXAMPLE

Let's look at some Oracle TO_YMINTERVAL function examples and explore how to use the TO_YMINTERVAL function in Oracle/PLSQL.

For example:

TO_YMINTERVAL('03-11')
Result: 3 years 11 months   (as an INTERVAL YEAR TO MONTH type)

TO_YMINTERVAL('01-05')
Result: 1 year 5 months     (as an INTERVAL YEAR TO MONTH type)

TO_YMINTERVAL('00-01')
Result: 0 years 1 month     (as an INTERVAL YEAR TO MONTH type)

The TO_YMINTERVAL function is most commonly used to add an interval to a date field. For example, you may wish to add 1 year and 5 months to an order date.

select order_date, order_date + to_yminterval('01-05')
from orders;

This SQL statement would return the order date, as well as the order date plus 1 year and 5 months.

ORACLE/PLSQL: CAST FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL CAST function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL CAST function converts one datatype to another.

SYNTAX

The syntax for the Oracle/PLSQL CAST function is:

CAST ( { expr | ( subquery ) | MULTISET ( subquery ) } AS type_name )

NOTE

The following casts are allowed:

TO	FROM
TO	char, varchar2	number	datetime / interval	raw	rowid, urowid	nchar, nvarchar2
char, varchar2	X	X	X	X	X
number	X	X
datetime / interval	X		X
raw	X			X
rowid, urowid	X				X
nchar, nvarchar2		X	X	X	X	X

APPLIES TO

The CAST function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

EXAMPLE

Let's look at some Oracle CAST function examples and explore how to use the CAST function in Oracle/PLSQL.

For example:

select CAST( '22-Aug-2003' AS varchar2(30) )
from dual;

This would convert the date (ie: 22-Aug-2003) into a varchar2(30) value.

ORACLE/PLSQL: CURRENT_DATE FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL CURRENT_DATE function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL CURRENT_DATE function returns the current date in the time zone of the current SQL session as set by the ALTER SESSION command.

SYNTAX

The syntax for the Oracle/PLSQL CURRENT_DATE function is:

CURRENT_DATE

Parameters or Arguments

There are no parameters or arguments for the CURRENT_DATE function.

APPLIES TO

The CURRENT_DATE function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

EXAMPLE

The CURRENT_DATE function can be used in Oracle/PLSQL.

If the following ALTER SESSION command was issued:

ALTER SESSION SET TIME_ZONE = '-7:0';

And then the following SQL statement was executed:

select CURRENT_DATE
from dual;

You might get the following result:

9/10/2005 10:58:24 PM

You then modified the session time zone with the following ALTER SESSION command:

ALTER SESSION SET TIME_ZONE = '-2:0';

And then the following SQL statement was executed:

select CURRENT_DATE
from dual;

You would now get the following result:

9/11/2005 3:58:24 AM

The session time zone value has changed from -7:0 to -2:0, causing the CURRENT_DATE function to return the current date as a value 5 hours ahead.

ORACLE/PLSQL: CURRENT_TIMESTAMP FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL CURRENT_TIMESTAMP function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL CURRENT_TIMESTAMP function returns the current date and time in the time zone of the current SQL session as set by the ALTER SESSION command. It returns a TIMESTAMP WITH TIME ZONE value.

SYNTAX

The syntax for the Oracle/PLSQL CURRENT_TIMESTAMP function is:

CURRENT_TIMESTAMP

Parameters or Arguments

There are no parameters or arguments for the CURRENT_TIMESTAMP function.

NOTE

A similar function to the CURRENT_TIMESTAMP function is the LOCALTIMESTAMP function.
The difference between these two functions is that the CURRENT_TIMESTAMP function returns a TIMESTAMP WITH TIME ZONE value while the LOCALTIMESTAMP function returns a TIMESTAMP value.

APPLIES TO

The CURRENT_TIMESTAMP function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

EXAMPLE

The CURRENT_TIMESTAMP function can be used in Oracle/PLSQL.

If the following ALTER SESSION command was issued:

ALTER SESSION SET TIME_ZONE = '-7:0';

And then the following SQL statement was executed:

select CURRENT_TIMESTAMP
from dual;

You might get the following result:

10-Sep-05 10.58.24.853421 PM -07:00

You then modified the session time zone with the following ALTER SESSION command:

ALTER SESSION SET TIME_ZONE = '-2:0';

And then the following SQL statement was executed:

select CURRENT_TIMESTAMP
from dual;

You would now get the following result:

10-Sep-05 03.58.24.853421 AM -02:00

The session time zone value has changed from -7:0 to -2:0, causing the CURRENT_TIMESTAMP function to return the current date and time as a value 5 hours ahead.

ORACLE/PLSQL: SYSDATE FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL SYSDATE function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL SYSDATE function returns the current system date and time on your local database.

SYNTAX

The syntax for the Oracle/PLSQL SYSDATE function is:

SYSDATE

Parameters or Arguments

There are no parameters or arguments for the SYSDATE function.

APPLIES TO

The SYSDATE function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle SYSDATE function examples and explore how to use the SYSDATE function in Oracle/PLSQL.

For example:

select SYSDATE
into v_date
from dual;

The variable called v_date will now contain the current date and time value.

You could also use the SYSDATE function in any SQL statement. For example:

select supplier_id, SYSDATE
from suppliers
where supplier_id > 5000;

If you wanted to extract the date portion only (and exclude the time component), you could use the TO_CHAR function. For example:

select supplier_id, TO_CHAR(SYSDATE, 'yyyy/mm/dd')
from suppliers
where supplier_id > 5000;

ORACLE/PLSQL: SYSTIMESTAMP FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL SYSTIMESTAMP function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL SYSTIMESTAMP function returns the current system date and time (including fractional seconds and time zone) on your local database.

SYNTAX

The syntax for the Oracle/PLSQL SYSTIMESTAMP function is:

SYSTIMESTAMP

Parameters or Arguments

There are no parameters or arguments for the SYSTIMESTAMP function.

APPLIES TO

The SYSTIMESTAMP function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

EXAMPLE

Let's look at some Oracle SYSTIMESTAMP function examples and explore how to use the SYSTIMESTAMP function in Oracle/PLSQL.

For example:

SELECT SYSTIMESTAMP
FROM dual;

The systimestamp function might return a value like this:

03-11-21 20:4

You can also choose to use the TO_CHAR function with the SYSTIMESTAMP function. For example:

SELECT TO_CHAR(SYSTIMESTAMP, 'SSSS.FF')
FROM dual;

The function above may return a value such as:

4141.550774

ORACLE/PLSQL: LOCALTIMESTAMP FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL LOCALTIMESTAMP function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL LOCALTIMESTAMP function returns the current date and time in the time zone of the current SQL session as set by the ALTER SESSION command. It returns a TIMESTAMP value.

SYNTAX

The syntax for the Oracle/PLSQL LOCALTIMESTAMP function is:

LOCALTIMESTAMP

Parameters or Arguments

There are no parameters or arguments for the LOCALTIMESTAMP function.

NOTE

A similar function to the LOCALTIMESTAMP function is the CURRENT_TIMESTAMP function.
The difference between these two functions is that the LOCALTIMESTAMP function returns a TIMESTAMP value while the CURRENT_TIMESTAMP function returns a TIMESTAMP WITH TIME ZONE value.

APPLIES TO

The LOCALTIMESTAMP function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle LOCALTIMESTAMP function examples and explore how to use the LOCALTIMESTAMP function in Oracle/PLSQL.

For example:

If the following ALTER SESSION command was issued:

ALTER SESSION SET TIME_ZONE = '-7:0';

And then the following SQL statement was executed:

select LOCALTIMESTAMP
from dual;

You might get the following result:

10-Sep-05 10.58.24 PM

You then modified the session time zone with the following ALTER SESSION command:

ALTER SESSION SET TIME_ZONE = '-2:0';

And then the following SQL statement was executed:

select LOCALTIMESTAMP
from dual;

You would now get the following result:

10-Sep-05 03.58.24 AM

The session time zone value has changed from -7:0 to -2:0, causing the LOCALTIMESTAMP function to return the current date and time as a value 5 hours ahead.

ORACLE/PLSQL: LAST_DAY FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL LAST_DAY function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL LAST_DAY function returns the last day of the month based on a date value.

SYNTAX

The syntax for the Oracle/PLSQL LAST_DAY function is:

LAST_DAY( date )

Parameters or Arguments

date is the date value to use to calculate the last day of the month.

APPLIES TO

The LAST_DAY function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle LAST_DAY function examples and explore how to use the LAST_DAY function in Oracle/PLSQL.

For example:

LAST_DAY(TO_DATE('2003/03/15', 'yyyy/mm/dd'))
Result: Mar 31, 2003

LAST_DAY(TO_DATE('2003/02/03', 'yyyy/mm/dd'))
Result: Feb 28, 2003

LAST_DAY(TO_DATE('2004/02/03', 'yyyy/mm/dd'))
Result: Feb 29, 2004

ORACLE/PLSQL: NEXT_DAY FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL NEXT_DAY function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL NEXT_DAY function returns the first weekday that is greater than a date.

SYNTAX

The syntax for the Oracle/PLSQL NEXT_DAY function is:

NEXT_DAY( date, weekday )

Parameters or Arguments

date is used to find the next weekday.

weekday is a day of the week (ie: SUNDAY, MONDAY, TUESDAY, WEDNESDAY, THURSDAY, FRIDAY, SATURDAY)

APPLIES TO

The NEXT_DAY function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

EXAMPLE

Let's look at some Oracle NEXT_DAY function examples and explore how to use the NEXT_DAY function in Oracle/PLSQL.

For example:

NEXT_DAY('01-Aug-03', 'TUESDAY')
Result: '05-Aug-03'

NEXT_DAY('06-Aug-03', 'WEDNESDAY')
Result: '13-Aug-03'

NEXT_DAY('06-Aug-03', 'SUNDAY')
Result: '10-Aug-03'

ORACLE/PLSQL: ADD_MONTHS FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL ADD_MONTHS function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL ADD_MONTHS function returns a date plus n months.

SYNTAX

The syntax for the Oracle/PLSQL ADD_MONTHS function is:

ADD_MONTHS( date1, n )

Parameters or Arguments

date1 is the starting date (before the n months have been added).

n is the number of months to add to date1.

APPLIES TO

The ADD_MONTHS function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle ADD_MONTHS function examples and explore how to use the ADD_MONTHS function in Oracle/PLSQL.

For example:

ADD_MONTHS('01-Aug-03', 3)
Result: '01-Nov-03'

ADD_MONTHS('01-Aug-03', -3)
Result: '01-May-03'

ADD_MONTHS('21-Aug-03', -3)
Result: '21-May-03'

ADD_MONTHS('31-Jan-03', 1)
Result: '28-Feb-03'

ORACLE/PLSQL: MONTHS_BETWEEN FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL MONTHS_BETWEEN function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL MONTHS_BETWEEN function returns the number of months between date1 and date2.

SYNTAX

The syntax for the Oracle/PLSQL MONTHS_BETWEEN function is:

MONTHS_BETWEEN( date1, date2 )

Parameters or Arguments

date1 and date2 are the dates used to calculate the number of months.

If a fractional month is calculated, the MONTHS_BETWEEN function calculates the fraction based on a 31-day month.

APPLIES TO

The MONTHS_BETWEEN function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle MONTHS_BETWEEN function examples and explore how to use the MONTHS_BETWEEN function in Oracle/PLSQL.

For example:

MONTHS_BETWEEN (TO_DATE ('2003/01/01', 'yyyy/mm/dd'), TO_DATE ('2003/03/14', 'yyyy/mm/dd') )

would return -2.41935483870968

Another example using the MONTHS_BETWEEN function in Oracle/PLSQL is:

MONTHS_BETWEEN (TO_DATE ('2003/07/01', 'yyyy/mm/dd'), TO_DATE ('2003/03/14', 'yyyy/mm/dd') )

would return 3.58064516129032

Another example using the MONTHS_BETWEEN function is:

MONTHS_BETWEEN (TO_DATE ('2003/07/02', 'yyyy/mm/dd'), TO_DATE ('2003/07/02', 'yyyy/mm/dd') )

would return 0

A final example using the MONTHS_BETWEEN function in Oracle/PLSQL is:

MONTHS_BETWEEN (TO_DATE ('2003/08/02', 'yyyy/mm/dd'), TO_DATE ('2003/06/02', 'yyyy/mm/dd') )

would return 2

ORACLE/PLSQL: ROUND FUNCTION (WITH DATES)

This Oracle tutorial explains how to use the Oracle/PLSQL ROUND function (as it applies to date values) with syntax and examples.

DESCRIPTION

The Oracle/PLSQL ROUND function returns a date rounded to a specific unit of measure.

ROUND FUNCTION SYNTAX (WITH DATES)

The syntax for the Oracle/PLSQL ROUND function is:

ROUND( date, [ format ] )

Parameters or Arguments

date is the date to round.

format is the unit of measure to apply for rounding. If the format parameter is omitted, the ROUND function will round to the nearest day.

Below are the valid format parameters:

Unit	Valid format parameters	Rounding Rule
Year	SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y	Rounds up on July 1st
ISO Year	IYYY, IY, I
Quarter	Q	Rounds up on the 16th day of the second month of the quarter
Month	MONTH, MON, MM, RM	Rounds up on the 16th day of the month
Week	WW	Same day of the week as the first day of the year
IW	IW	Same day of the week as the first day of the ISO year
W	W	Same day of the week as the first day of the month
Day	DDD, DD, J
Start day of the week	DAY, DY, D
Hour	HH, HH12, HH24
Minute	MI

APPLIES TO

The ROUND function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE - WITH DATES

Let's look at some Oracle ROUND function examples and explore how to use the ROUND function in Oracle/PLSQL.

For example:

ROUND(TO_DATE ('22-AUG-03'),'YEAR')
Result: '01-JAN-04'

ROUND(TO_DATE ('22-AUG-03'),'Q')
Result: '01-OCT-03'

ROUND(TO_DATE ('22-AUG-03'),'MONTH')
Result: '01-SEP-03'

ROUND(TO_DATE ('22-AUG-03'),'DDD')
Result: '22-AUG-03'

ROUND(TO_DATE ('22-AUG-03'),'DAY')
Result: '24-AUG-03'

ORACLE/PLSQL: TRUNC FUNCTION (WITH DATES)

This Oracle tutorial explains how to use the Oracle/PLSQL TRUNC function (as it applies to date values) with syntax and examples.

DESCRIPTION

The Oracle/PLSQL TRUNC function returns a date truncated to a specific unit of measure.

SYNTAX (WITH DATES)

The syntax for the Oracle/PLSQL TRUNC function is:

TRUNC ( date, [ format ] )

Parameters or Arguments

date is the date to truncate.

format is the unit of measure to apply for truncating. If the format parameter is omitted, the TRUNC function will truncate the date to the day value, so that any hours, minutes, or seconds will be truncated off.

Below are the valid format parameters:

Unit	Valid format parameters
Year	SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y
ISO Year	IYYY, IY, I
Quarter	Q
Month	MONTH, MON, MM, RM
Week	WW
IW	IW
W	W
Day	DDD, DD, J
Start day of the week	DAY, DY, D
Hour	HH, HH12, HH24
Minute	MI

APPLIES TO

The TRUNC function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE - WITH DATES

Let's look at some Oracle TRUNC function examples and explore how to use the TRUNC function in Oracle/PLSQL.

For example:

TRUNC(TO_DATE('22-AUG-03'), 'YEAR')
Result: '01-JAN-03'

TRUNC(TO_DATE('22-AUG-03'), 'Q')
Result: '01-JUL-03'

TRUNC(TO_DATE('22-AUG-03'), 'MONTH')
Result: '01-AUG-03'

TRUNC(TO_DATE('22-AUG-03'), 'DDD')
Result: '22-AUG-03'

TRUNC(TO_DATE('22-AUG-03'), 'DAY')
Result: '17-AUG-03'

ORACLE/PLSQL: USER FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL USER function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL USER function returns the user_id from the current Oracle session.

SYNTAX

The syntax for the Oracle/PLSQL USER function is:

USER

Parameters or Arguments

There are no parameters or arguments for the USER function.

APPLIES TO

The USER function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle USER function examples and explore how to use the USER function in Oracle/PLSQL.

For example:

parm_user_ID := USER;

select USER
into parm_user_ID
from dual;

The variable called parm_user_ID will now contain the Oracle user ID from the current Oracle session.

ORACLE/PLSQL: DECODE FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL DECODE function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL DECODE function has the functionality of an IF-THEN-ELSE statement.

SYNTAX

The syntax for the Oracle/PLSQL DECODE function is:

DECODE( expression , search , result [, search , result]... [, default] )

Parameters or Arguments

expression is the value to compare.

search is the value that is compared against expression.

result is the value returned, if expression is equal to search.

default is optional. If no matches are found, the DECODE function will return default. If default is omitted, then the DECODE function will return null (if no matches are found).

APPLIES TO

The DECODE function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

EXAMPLE

The DECODE function can be used in Oracle/PLSQL.

You could use the DECODE function in a SQL statement as follows:

SELECT supplier_name,
DECODE(supplier_id, 10000, 'IBM',
                    10001, 'Microsoft',
                    10002, 'Hewlett Packard',
                    'Gateway') result
FROM suppliers;

The above DECODE statement is equivalent to the following IF-THEN-ELSE statement:

IF supplier_id = 10000 THEN
   result := 'IBM';

ELSIF supplier_id = 10001 THEN
   result := 'Microsoft';

ELSIF supplier_id = 10002 THEN
   result := 'Hewlett Packard';

ELSE
   result := 'Gateway';

END IF;

The DECODE function will compare each supplier_id value, one by one.

FREQUENTLY ASKED QUESTIONS

Question: One of our viewers wanted to know how to use the DECODE function to compare two dates (ie: date1 and date2), where if date1 > date2, the DECODE function should return date2. Otherwise, the DECODE function should return date1.

Answer: To accomplish this, use the DECODE function as follows:

DECODE((date1 - date2) - ABS(date1 - date2), 0, date2, date1)

The formula below would equal 0, if date1 is greater than date2:

(date1 - date2) - ABS(date1 - date2)

Helpful Tip #1: One of our viewers suggested combining the SIGN function with the DECODE function as follows:

The date example above could be modified as follows:

DECODE(SIGN(date1-date2), 1, date2, date1)

The SIGN/DECODE combination is also helpful for numeric comparisons e.g. Sales Bonuses

DECODE(SIGN(actual-target), -1, 'NO Bonus for you', 0,'Just made it', 1, 'Congrats, you are a winner')

Helpful Tip #2: One of our viewers suggested using the LEAST function (instead of the DECODE function) as follows:

The date example above could be modified as follows:

LEAST(date1, date2)

Question: I would like to know if it's possible to use the DECODE function for ranges of numbers, ie 1-10 = 'category 1', 11-20 = 'category 2', rather than having to individually decode each number.

Answer: Unfortunately, you can not use the DECODE function for ranges of numbers. However, you can try to create a formula that will evaluate to one number for a given range, and another number for the next range, and so on.

For example:

SELECT supplier_id,
DECODE(TRUNC ((supplier_id - 1) / 10), 0, 'category 1',
                                       1, 'category 2',
                                       2, 'category 3',
                                       'unknown') result
FROM suppliers;

In this example, based on the formula:

TRUNC ((supplier_id - 1) / 10

The formula will evaluate to 0, if the supplier_id is between 1 and 10.
The formula will evaluate to 1, if the supplier_id is between 11 and 20.
The formula will evaluate to 2, if the supplier_id is between 21 and 30.

and so on...

Question: I need to write a DECODE statement that will return the following:

If yrs_of_service < 1 then return 0.04
If yrs_of_service >= 1 and < 5 then return 0.04
If yrs_of_service > 5 then return 0.06

How can I do this?

Answer: You will need to create a formula that will evaluate to a single number for each one of your ranges.

For example:

SELECT emp_name,
DECODE(TRUNC (( yrs_of_service + 3) / 4), 0, 0.04,
                                          1, 0.04,
                                          0.06) as perc_value
FROM employees;

Question: Is there a limit to the number of arguments that you can have in one DECODE statement? I'm getting an error, "ORA-00939: too many arguments for function".

Answer: Yes, the maximum number of components that you can have in a DECODE function is 255. This includes theexpression, search, and result arguments.

ORACLE/PLSQL: NULLIF FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL NULLIF function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL NULLIF function compares expr1 and expr2. If expr1 and expr2 are equal, the NULLIF function returns NULL. Otherwise, it returns expr1.

SYNTAX

The syntax for the Oracle/PLSQL NULLIF function is:

NULLIF( expr1, expr2 )

Parameters or Arguments

expr1 and expr2 must be either numeric values or values that are of the same datatype.

NOTE

expr1 can be an expression that evaluates to NULL, but it can not be the literal NULL.

APPLIES TO

The NULLIF function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

EXAMPLE

Let's look at some Oracle NULLIF function examples and explore how to use the NULLIF function in Oracle/PLSQL.

For example:

NULLIF(12, 12)
Result: NULL

NULLIF(12, 13)
Result: 12

NULLIF('apples', 'apples')
Result: NULL

NULLIF('apples', 'oranges')
Result: 'apples'

NULLIF(NULL, 12)
Result: ORA-00932 error   (because expr1 can not be the literal NULL)

ORACLE/PLSQL: LISTAGG FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL LISTAGG function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL LISTAGG function concatenates values of the measure_column for each GROUP based on theorder_by_clause.

SYNTAX

The syntax for the Oracle/PLSQL LISTAGG function is:

LISTAGG (measure_column [, 'delimiter'])
  WITHIN GROUP (order_by_clause) [OVER (query_partition_clause)]

Parameters or Arguments

measure_column is the column whose values you wish to concatenate together in the result set. Null values in themeasure_column are ignored.

delimiter is optional. It is the delimiter to use when separating the measure_column values when outputting the results.

order_by_clause determines the order that the concatenated values (ie: measure_column) are returned.

APPLIES TO

The LISTAGG function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g Release 2

EXAMPLE

The LISTAGG function can be used in Oracle/PLSQL.

Since this is a more complicated function to understand, let's look at an example that includes data to demonstrate what the function outputs.

If you had a products table with the following data:

product_id	product_name
1001	Bananas
1002	Apples
1003	Pears
1004	Oranges

And then you executed a SELECT statement using the LISTAGG function:

SELECT LISTAGG(product_name, ', ') WITHIN GROUP (ORDER BY product_name) "Product_Listing"
FROM products;

You would get the following results:

Product_Listing
Apples, Bananas, Oranges, Pears

In this example, the results of the LISTAGG function are output in a single field with the values comma delimited.

You can change the ORDER BY clause to use the DESC keyword and change the SELECT statement as follows:

SELECT LISTAGG(product_name, ', ') WITHIN GROUP (ORDER BY product_name DESC) "Product_Listing"
FROM products;

This would give the following results:

Product_Listing
Pears, Oranges, Bananas, Apples

You could change the delimiter from a comma to a semi-colon as follows:

SELECT LISTAGG(product_name, '; ') WITHIN GROUP (ORDER BY product_name DESC) "Product_Listing"
FROM products;

This would change your results as follows:

Product_Listing
Pears; Oranges; Bananas; Apples

ORACLE/PLSQL: BFILENAME FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL BFILENAME function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL BFILENAME function returns a BFILE locator for a physical LOB binary file.

SYNTAX

The syntax for the Oracle/PLSQL BFILENAME function is:

BFILENAME( 'directory', 'filename' )

Parameters or Arguments

directory is a directory object that serves as an alias for the full path to where the file is located on the file server.

filename is the name of the file on the file server.

APPLIES TO

The BFILENAME function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle BFILENAME function examples and explore how to use the BFILENAME function in Oracle/PLSQL.

For example:

First, we need to create a directory object called exampleDir that points to /example/totn on the file server.

CREATE DIRECTORY exampleDir AS '/example/totn';

Then we can use the exampleDir directory object in the BFILENAME function as follows:

SELECT BFILENAME('exampleDir', 'totn_logo.png')
FROM dual;

ORACLE/PLSQL: BIN_TO_NUM FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL BIN_TO_NUM function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL BIN_TO_NUM function converts a bit vector to a number.

SYNTAX

The syntax for the Oracle/PLSQL BIN_TO_NUM function is:

BIN_TO_NUM( expr1, expr2, ... expr_n)

Parameters or Arguments

expr1, expr2, ... expr_n must be either 0 or 1 values. They represent bits in a bit vector.

APPLIES TO

The BIN_TO_NUM function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

EXAMPLE

Let's look at some Oracle BIN_TO_NUM function examples and explore how to use the BIN_TO_NUM function in Oracle/PLSQL.

For example:

BIN_TO_NUM(1)
Result: 1

BIN_TO_NUM(1,0)
Result: 2

BIN_TO_NUM(1,1)
Result: 3

BIN_TO_NUM(1,1,1,0)
Result: 14

BIN_TO_NUM(1,1,1,1)
Result: 15

ORACLE/PLSQL: BITAND FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL BITAND function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL BITAND function returns an integer representing an AND operation on the bits of expr1 and expr2.

SYNTAX

The syntax for the Oracle/PLSQL BITAND function is:

bitand( expr1, expr2 )

Parameters or Arguments

expr1 and expr2 must resolve to non-negative integers.

APPLIES TO

The BITAND function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle BITAND function examples and explore how to use the BITAND function in Oracle/PLSQL.

For example:

BITAND(5,3)
Result: 1

BITAND(15,7)
Result: 7

BITAND(5,2)
Result: 0

BITAND(5,0)
Result: 0

BITAND(6,2)
Result: 2

ORACLE/PLSQL: CARDINALITY FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL CARDINALITY function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL CARDINALITY function returns the number of elements in a nested table.

SYNTAX

The syntax for the Oracle/PLSQL CARDINALITY function is:

CARDINALITY( nested table column )

Parameters or Arguments

nested table column is the column in the nested table that you wish to return the cardinality for.

NOTE

If the nested table is empty, the CARDINALITY function will return NULL.

If the nested table is a null collection, the CARDINALITY function will return NULL.

APPLIES TO

The CARDINALITY function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g

EXAMPLE

Let's look at some Oracle CARDINALITY function examples and explore how to use the CARDINALITY function in Oracle/PLSQL.

For example:

select supplier_id, CARDINALITY(location)
from suppliers;

ORACLE/PLSQL: CHARTOROWID FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL CHARTOROWID function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL CHARTOROWID function converts a char, varchar2, nchar, or nvarchar2 to a rowid.

SYNTAX

The syntax for the Oracle/PLSQL CHARTOROWID function is:

CHARTOROWID( value1 )

Parameters or Arguments

value1 is the value to convert to a rowid.

The format of the rowid is:

BBBBBBB.RRRR.FFFFF

where:

BBBBBBB is the block in the database file;
RRRR is the row in the block;
FFFFF is the database file.

APPLIES TO

The CHARTOROWID function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle CHARTOROWID function examples and explore how to use the CHARTOROWID function in Oracle/PLSQL.

For example:

select * from suppliers
where rowid = CHARTOROWID('AAABoqAADAAAAwPAAA');

This would return a unique row from the suppliers table.

ORACLE/PLSQL: DBTIMEZONE FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL DBTIMEZONE function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL DBTIMEZONE function returns the database time zone as a time zone offset (in the following format: '[+|-]TZH:TZM') or a time zone region name.

SYNTAX

The syntax for the Oracle/PLSQL DBTIMEZONE function is:

DBTIMEZONE

Parameters or Arguments

There are no parameters or arguments for the DBTIMEZONE function.

APPLIES TO

The DBTIMEZONE function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

EXAMPLE

Let's look at some Oracle DBTIMEZONE function examples and explore how to use the DBTIMEZONE function in Oracle/PLSQL.

For example:

select DBTIMEZONE
from dual;

The DBTIMEZONE function might return a value like this:

+00:00

or a value such as this:

-07:00

The value returned depends on the time zone value set in your individual Oracle database.

ORACLE/PLSQL: EMPTY_BLOB FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL EMPTY_BLOB function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL EMPTY_BLOB function can be used to initialize a LOB column to EMPTY in an INSERT statement orUPDATE statement or it can be used initalize a LOB variable.

SYNTAX

The syntax for the Oracle/PLSQL EMPTY_BLOB function is:

EMPTY_BLOB ()

Parameters or Arguments

There are no parameters or arguments for the EMPTY_BLOB function.

APPLIES TO

The EMPTY_BLOB function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle EMPTY_BLOB function examples and explore how to use the EMPTY_BLOB function in Oracle/PLSQL.

For example:

UPDATE products
SET product_photo = EMPTY_BLOB();

This EMPTY_BLOB example would initialize the product_photo column to EMPTY. This is different than setting the column to a null LOB or a LOB with a string length of 0.

ORACLE/PLSQL: EMPTY_CLOB FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL EMPTY_CLOB function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL EMPTY_CLOB function can be used to initialize a LOB column to EMPTY in an INSERT statement orUPDATE statement or it can be used initalize a LOB variable.

SYNTAX

The syntax for the Oracle/PLSQL EMPTY_CLOB function is:

EMPTY_CLOB ()

Parameters or Arguments

There are no parameters or arguments for the EMPTY_CLOB function.

APPLIES TO

The EMPTY_CLOB function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle EMPTY_CLOB function examples and explore how to use the EMPTY_CLOB function in Oracle/PLSQL.

For example:

UPDATE customers
SET customer_photo = EMPTY_CLOB();

This EMPTY_CLOB example would initialize the customer_photo column to EMPTY. This is different than setting the column to a null LOB or a LOB with a string length of 0.

ORACLE/PLSQL: FROM_TZ FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL FROM_TZ function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL FROM_TZ function converts a TIMESTAMP value (given a TIME ZONE) to a TIMESTAMP WITH TIME ZONE value.

SYNTAX

The syntax for the Oracle/PLSQL FROM_TZ function is:

FROM_TZ( timestamp_value, time_zone_value )

Parameters or Arguments

timestamp_value is the value to convert to a TIMESTAMP WITH TIME ZONE value.

time_zone_value is the time zone used to convert timestamp_value to a TIMESTAMP WITH TIME ZONE value.

APPLIES TO

The FROM_TZ function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

EXAMPLE

Let's look at some Oracle FROM_TZ function examples and explore how to use the FROM_TZ function in Oracle/PLSQL.

For example:

If you executed the following SQL statement:

select FROM_TZ(TIMESTAMP '2005-09-11 01:50:42', '5:00')
from dual;

You would now get the following result:

11-Sep-05 01.50.42.000000 AM +05:00

If you executed the same SQL statement, but set the time zone to '-7:00' as follows:

select FROM_TZ(TIMESTAMP '2005-09-11 01:50:42', '-7:00')
from dual;

You would now get the following result:

11-Sep-05 01.50.42.000000 AM -07:00

ORACLE/PLSQL: GROUP_ID FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL GROUP_ID function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL GROUP_ID function assigns a number to each group resulting from a GROUP BY clause. The GROUP_ID function is most commonly used to identify duplicated groups in your query results.

For each unique group, the GROUP_ID function will return 0. When a duplicated group is found, the GROUP_ID function will return a value >0.

SYNTAX

The syntax for the Oracle/PLSQL GROUP_ID function is:

SELECT column1, column2, ... column_n, GROUP_ID()
FROM tables
WHERE conditions
GROUP BY column1, column2, ... column_n;

Parameters or Arguments

There are no parameters or arguments for the GROUP_ID function.

APPLIES TO

The GROUP_ID function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

EXAMPLE

Let's look at some Oracle GROUP_ID function examples and explore how to use the GROUP_ID function in Oracle/PLSQL.

For example:

SELECT SUM(salary), department, bonus, GROUP_ID()
FROM employees
WHERE bonus > 100
GROUP BY department,
ROLLUP (department, bonus);

You could use the HAVING clause to eliminated duplicated groups as follows:

SELECT SUM(salary), department, bonus, GROUP_ID()
FROM employees
WHERE bonus > 100
GROUP BY department,
ROLLUP (department, bonus)
HAVING GROUP_ID() < 1;

ORACLE/PLSQL: HEXTORAW FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL HEXTORAW function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL HEXTORAW function converts a hexadecimal value into a raw value.

SYNTAX

The syntax for the Oracle/PLSQL HEXTORAW function is:

HEXTORAW( char )

Parameters or Arguments

char is the hexademical value to convert to a raw value.

APPLIES TO

The HEXTORAW function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle HEXTORAW function examples and explore how to use the HEXTORAW function in Oracle/PLSQL.

For example:

HEXTORAW('45D')
Result: '045D'  (as a raw value)

HEXTORAW('7E')
Result: '7E'    (as a raw value)

ORACLE/PLSQL: LAG FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL LAG function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL LAG function is an analytic function that lets you query more than one row in a table at a time without having to join the table to itself. It returns values from a previous row in the table. To return a value from the next row, try using the LEAD function.

SYNTAX

The syntax for the Oracle/PLSQL LAG function is:

LAG ( expression [, offset [, default] ] )
over ( [ query_partition_clause ] order_by_clause )

Parameters or Arguments

expression is an expression that can contain other built-in functions, but can not contain any analytic functions.

offset is optional. It is the physical offset from the current row in the table. If this parameter is omitted, the default is 1.

default is optional. It is the value that is returned if the offset goes out of the bounds of the table. If this parameter is omitted, the default is null.

APPLIES TO

The LAG function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

The LAG function can be used in Oracle/PLSQL.

Let's look at an example. If we had an orders table that contained the following data:

ORDER_DATE	PRODUCT_ID	QTY
25/09/2007	1000	20
26/09/2007	2000	15
27/09/2007	1000	8
28/09/2007	2000	12
29/09/2007	2000	2
30/09/2007	1000	4

And we ran the following SQL statement:

select product_id, order_date,
LAG (order_date,1) over (ORDER BY order_date) AS prev_order_date
from orders;

It would return the following result:

PRODUCT_ID	ORDER_DATE	PREV_ORDER_DATE
1000	25/09/2007
2000	26/09/2007	25/09/2007
1000	27/09/2007	26/09/2007
2000	28/09/2007	27/09/2007
2000	29/09/2007	28/09/2007
1000	30/09/2007	29/09/2007

Since we used an offset of 1, the query returns the previous order_date.

If we had used an offset of 2 instead, it would have returned the order_date from 2 orders before. If we had used an offset of 3, it would have returned the order_date from 3 orders before....and so on.

If we wanted only the orders for a given product_id, we could run the following SQL statement:

select product_id, order_date,
LAG (order_date,1) over (ORDER BY order_date) AS prev_order_date
from orders
where product_id = 2000;

It would return the following result:

PRODUCT_ID	ORDER_DATE	PREV_ORDER_DATE
2000	26/09/2007
2000	28/09/2007	26/09/2007
2000	29/09/2007	28/09/2007

In this example, it returned the previous order_date for product_id = 2000 and ignored all other orders.

ORACLE/PLSQL: LEAD FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL LEAD function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL LEAD function is an analytic function that lets you query more than one row in a table at a time without having to join the table to itself. It returns values from the next row in the table. To return a value from a previous row, try using the LAG function.

SYNTAX

The syntax for the Oracle/PLSQL LEAD function is:

LEAD ( expression [, offset [, default] ] )
over ( [ query_partition_clause ] order_by_clause )

Parameters or Arguments

expression is an expression that can contain other built-in functions, but can not contain any analytic functions.

offset is optional. It is the physical offset from the current row in the table. If this parameter is omitted, the default is 1.

default is optional. It is the value that is returned if the offset goes out of the bounds of the table. If this parameter is omitted, the default is null.

APPLIES TO

The LEAD function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

The LEAD function can be used in Oracle/PLSQL.

Let's look at an example. If we had an orders table that contained the following data:

ORDER_DATE	PRODUCT_ID	QTY
25/09/2007	1000	20
26/09/2007	2000	15
27/09/2007	1000	8
28/09/2007	2000	12
29/09/2007	2000	2
30/09/2007	1000	4

And we ran the following SQL statement:

select product_id, order_date,
LEAD (order_date,1) over (ORDER BY order_date) AS next_order_date
from orders;

It would return the following result:

PRODUCT_ID	ORDER_DATE	NEXT_ORDER_DATE
1000	25/09/2007	26/09/2007
2000	26/09/2007	27/09/2007
1000	27/09/2007	28/09/2007
2000	28/09/2007	29/09/2007
2000	29/09/2007	30/09/2007
1000	30/09/2007

Since we used an offset of 1, the query returns the next order_date.

If we had used an offset of 2 instead, it would have returned the order_date from 2 orders later. If we had used an offset of 3, it would have returned the order_date from 3 orders later....and so on.

If we wanted only the orders for a given product_id, we could run the following SQL statement:

select product_id, order_date,
LEAD (order_date,1) over (ORDER BY order_date) AS next_order_date
from orders
where product_id = 2000;

It would return the following result:

PRODUCT_ID	ORDER_DATE	NEXT_ORDER_DATE
2000	26/09/2007	28/09/2007
2000	28/09/2007	29/09/2007
2000	29/09/2007

In this example, it returned the next order_date for product_id = 2000 and ignored all other orders.

ORACLE/PLSQL: LNNVL FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL LNNVL function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL LNNVL function is used in the WHERE clause of a SQL statement to evaluate a condition when one of the operands may contain a NULL value.

SYNTAX

The syntax for the Oracle/PLSQL LNNVL function is:

LNNVL( condition )

NOTE

The LNNVL function will return the following:

Condition Evaluates To	LNNVL Return Value
TRUE	FALSE
FALSE	TRUE
UNKNOWN	TRUE

So if we had two columns called qty and reorder_level where qty = 20 and reorder_level IS NULL, the LNNVL function would return the following:

Condition	Condition Evaluates To	LNNVL Return Value
qty = reorder_level	UNKNOWN	TRUE
qty IS NULL	FALSE	TRUE
reorder_level IS NULL	TRUE	FALSE
qty = 20	TRUE	FALSE
reorder_level = 20	UNKNOWN	TRUE

APPLIES TO

The LNNVL function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g

EXAMPLE

The LNNVL function can be used in Oracle/PLSQL.

Let's look at an example. If we had an products table that contained the following data:

PRODUCT_ID	QTY	REORDER_LEVEL
1000	20	NULL
2000	15	8
3000	8	10
4000	12	6
5000	2	2
6000	4	5

And we wanted to find all of the products whose qty was below their respective reorder levels, we would run the following SQL statement:

select * from products
where qty < reorder_level;

This would return the following result:

PRODUCT_ID	QTY	REORDER_LEVEL
3000	8	10
6000	4	5

However, if we wanted to see the products that were below their reorder levels as well as NULL reorder levels, we would use the LNNVL function as follows:

select * from products
where LNNVL(qty >= reorder_level);

This would return the following result:

PRODUCT_ID	QTY	REORDER_LEVEL
1000	20	NULL
3000	8	10
6000	4	5

In this example, the result set also contains the product_id of 1000 which has a NULL reorder level.

ORACLE/PLSQL: NANVL FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL NANVL function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL NANVL function lets you substitute a value for a floating point number such as BINARY_FLOAT or BINARY_DOUBLE, when a Nan (Not a number) value is encountered. This is most commonly used to convert Nan (Not a number) values into either NULL or 0.

SYNTAX

The syntax for the Oracle/PLSQL NANVL function is:

NANVL( value, replace_with )

Parameters or Arguments

value is the BINARY_FLOAT or BINARY_NUMBER to test for a Nan (Not a number).

replace_with is the value returned if value is Nan (not a number).

APPLIES TO

The NANVL function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g

EXAMPLE

The NANVL function can be used in Oracle/PLSQL.

For example:

select NANVL(binary1, 0)
from test_table;

The SQL statement above would return 0 if the binary1 field contained a Nan (Not a number) value. Otherwise, it would return the binary1 value.

Another example of the NANVL function in Oracle/PLSQL is:

select NANVL(binary1, NULL)
from test_table;

The SQL statement above would return NULL if the binary1 field contained a Nan (Not a number) value. Otherwise, it would return the binary1 value.

ORACLE/PLSQL: NEW_TIME FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL NEW_TIME function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL NEW_TIME function returns a date in time zone1 to a date in time zone2.

SYNTAX

The syntax for the Oracle/PLSQL NEW_TIME function is:

NEW_TIME( date, zone1, zone2 )

Parameters or Arguments

zone1 and zone2 can be any of the following values:

Value	Description
AST	Atlantic Standard Time
ADT	Atlantic Daylight Time
BST	Bering Standard Time
BDT	Bering Daylight Time
CST	Central Standard Time
CDT	Central Daylight Time
EST	Eastern Standard Time
EDT	Eastern Daylight Time
GMT	Greenwich Mean Time
HST	Alaska-Hawaii Standard Time
HDT	Alaska-Hawaii Daylight Time
MST	Mountain Standard Time
MDT	Mountain Daylight Time
NST	Newfoundland Standard Time
PST	Pacific Standard Time
PDT	Pacific Daylight Time
YST	Yukon Standard Time
YDT	Yukon Daylight Time

APPLIES TO

The NEW_TIME function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

EXAMPLE

Let's look at some Oracle NEW_TIME function examples and explore how to use the NEW_TIME function in Oracle/PLSQL.

For example:

The following NEW_TIME function example converts an Atlantic Standard Time into a Mountain Standard Time:

NEW_TIME (TO_DATE ('2003/11/01 01:45', 'yyyy/mm/dd HH24:MI'), 'AST', 'MST')

This example would return '2003/10/31 10:45:00 PM'.

ORACLE/PLSQL: NUMTODSINTERVAL FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL NUMTODSINTERVAL function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL NUMTODSINTERVAL function converts a number to an INTERVAL DAY TO SECOND literal.

SYNTAX

The syntax for the Oracle/PLSQL NUMTODSINTERVAL function is:

NUMTODSINTERVAL( number, expression )

Parameters or Arguments

number is the number to convert to an interval.

expression is the unit. It must be one of the following values: DAY, HOUR, MINUTE, or SECOND.

APPLIES TO

The NUMTODSINTERVAL function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle NUMTODSINTERVAL function examples and explore how to use the NUMTODSINTERVAL function in Oracle/PLSQL.

For example:

NUMTODSINTERVAL(150, 'DAY')
Result: '+000000150'

NUMTODSINTERVAL(1500, 'HOUR')
Result: '+000000062'

NUMTODSINTERVAL(15000, 'MINUTE')
Result: '+000000010'

NUMTODSINTERVAL(150000, 'SECOND')
Result: '+000000001'

ORACLE/PLSQL: NUMTOYMINTERVAL FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL NUMTOYMINTERVAL function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL NUMTOYMINTERVAL function converts a number to an INTERVAL YEAR TO MONTH literal.

SYNTAX

The syntax for the Oracle/PLSQL NUMTOYMINTERVAL function is:

NUMTOYMINTERVAL( number, expression )

Parameters or Arguments

number is the number to convert to an interval.

expression is the unit. It must be one of the following values: YEAR or MONTH.

APPLIES TO

The NUMTOYMINTERVAL function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle NUMTOYMINTERVAL function examples and explore how to use the NUMTOYMINTERVAL function in Oracle/PLSQL.

For example:

NUMTOYMINTERVAL(100000000, 'MONTH')
Result: '+0083'

NUMTOYMINTERVAL(100000, 'YEAR')
Result: '+0001'

ORACLE/PLSQL: NVL FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL NVL function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL NVL function lets you substitute a value when a null value is encountered.

SYNTAX

The syntax for the Oracle/PLSQL NVL function is:

NVL( string1, replace_with )

Parameters or Arguments

string1 is the string to test for a null value.

replace_with is the value returned if string1 is null.

APPLIES TO

The NVL function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle NVL function examples and explore how to use the NVL function in Oracle/PLSQL.

For example:

SELECT NVL(supplier_city, 'n/a')
FROM suppliers;

The SQL statement above would return 'n/a' if the supplier_city field contained a null value. Otherwise, it would return the supplier_city value.

Another example using the NVL function in Oracle/PLSQL is:

SELECT supplier_id,
NVL(supplier_desc, supplier_name)
FROM suppliers;

This SQL statement would return the supplier_name field if the supplier_desc contained a null value. Otherwise, it would return the supplier_desc.

A final example using the NVL function in Oracle/PLSQL is:

SELECT NVL(commission, 0)
FROM sales;

This SQL statement would return 0 if the commission field contained a null value. Otherwise, it would return the commissionfield.

FREQUENTLY ASKED QUESTIONS

Question: I tried to use the NVL function through VB to access Oracle DB.

To be precise,

SELECT NVL(Distinct (emp_name),'AAA'),................ 
FROM.................

I got an oracle error when I use distinct clause with NVL, but when I remove distinct it works fine.

Answer: It is possible to the use the DISTINCT clause with the NVL function. However, the DISTINCT must come before the use of the NVL function. For example:

SELECT distinct NVL(emp_name, 'AAA')
FROM employees;

Hope this helps!

Question: Is it possible to use the NVL function with more than one column with the same function call? To be clear, if i need to apply this NVL function to more than one column like this:

NVL(column1;column2 ...... , here is the default value for all )

Answer: You will need to make separate NVL function calls for each column. For example:

SELECT NVL(table_name, 'not found'), NVL(owner, 'not found')
FROM all_tables;

ORACLE/PLSQL: NVL2 FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL NVL2 function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL NVL2 function extends the functionality found in the NVL function. It lets you substitutes a value when a null value is encountered as well as when a non-null value is encountered.

SYNTAX

The syntax for the Oracle/PLSQL NVL2 function is:

NVL2( string1, value_if_NOT_null, value_if_null )

Parameters or Arguments

string1 is the string to test for a null value.

value_if_NOT_null is the value returned if string1 is not null.

value_if_null is the value returned if string1 is null.

APPLIES TO

The NVL2 function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle NVL2 function examples and explore how to use the NVL2 function in Oracle/PLSQL.

For example:

select NVL2(supplier_city, 'Completed', 'n/a')
from suppliers;

The SQL statement above would return 'n/a' if the supplier_city field contained a null value. Otherwise, it would return the 'Completed'.

Another example using the NVL2 function in Oracle/PLSQL is:

select supplier_id,
NVL2(supplier_desc, supplier_name, supplier_name2)
from suppliers;

This SQL statement would return the supplier_name2 field if the supplier_desc contained a null value. Otherwise, it would return the supplier_name field.

ORACLE/PLSQL: RAWTOHEX FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL RAWTOHEX function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL RAWTOHEX function converts a raw value into a hexadecimal value. One of our viewers says that this function comes in handy to move a varchar value to a blob field.

SYNTAX

The syntax for the Oracle/PLSQL RAWTOHEX function is:

RAWTOHEX( raw )

Parameters or Arguments

raw is the raw value to convert to a hexademical value.

NOTE

This function works differently when used as a PLSQL built-in function as opposed to running it in SQL. As a PLSQL function, the RAWTOHEX function may perform an implicit conversion before converting to a hexadecimal value. This may result in a different value being returned by this function between PLSQL and SQL.

For example, if you ran the following:

DECLARE
a varchar2(8);

BEGIN

   a := rawtohex('AB');
   dbms_output.put_line(a);

   SELECT RAWTOHEX('AB') INTO a FROM dual;

   dbms_output.put_line(a);

END;

The following would be output as the result:

AB
4142

The reason for the difference is that PLSQL is doing an implicit conversion of 'AB' into a RAW (treats 'AB' as a single byte equal to CHR(171)). A RAWTOHEX on that returns the string 'AB'.

Whereas, SQL is not doing that implicit conversion. 'AB' is 2 byte RAW already and a RAWTOHEX of that retuns 4142.

APPLIES TO

The RAWTOHEX function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle RAWTOHEX function examples and explore how to use the RAWTOHEX function in Oracle/PLSQL.

For example:

RAWTOHEX('AB')
Result: '4142' if run as a SQL function and 'AB' if run as a PLSQL function

RAWTOHEX('7E')
Result: '3745' if run as a SQL function and '7E' as a PLSQL function

ORACLE/PLSQL: SESSIONTIMEZONE FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL SESSIONTIMEZONE function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL SESSIONTIMEZONE function returns the current session's time zone as a time zone offset (in the following format: '[+|-]TZH:TZM') or a time zone region name.

SYNTAX

The syntax for the Oracle/PLSQL SESSIONTIMEZONE function is:

SESSIONTIMEZONE

Parameters or Arguments

There are no parameters or arguments for the SESSIONTIMEZONE function.

APPLIES TO

The SESSIONTIMEZONE function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

EXAMPLE

Let's look at some Oracle SESSIONTIMEZONE function examples and explore how to use the SESSIONTIMEZONE function in Oracle/PLSQL.

For example:

SELECT SESSIONTIMEZONE
FROM dual;

The SESSIONTIMEZONE function might return a value like this:

+00:00

or a value such as this:

-07:00

The value returned depends on how the user specified the session time zone value in the most recent ALTER SESSION statement. An example of an ALTER SESSION command is:

ALTER SESSION SET TIME_ZONE = '-7:0';

ORACLE/PLSQL: TZ_OFFSET FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL TZ_OFFSET function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL TZ_OFFSET function returns the time zone offset of a value.

SYNTAX

The syntax for the Oracle/PLSQL TZ_OFFSET function is:

TZ_OFFSET( timezone )

Parameters or Arguments

timezone can be a valid time zone name, a time zone offset from UTC, the SESSIONTIMEZONE function, or theDBTIMEZONE function.

Some of the more commonly used time zone names are:

Canada/Atlantic	Pacific/Easter
Canada/Central	Pacific/Honolulu
Canada/East-Saskatchewan	Pacific/Kwajalein
Canada/Eastern	Pacific/Pago_Pago
Canada/Mountain	Pacific/Samoa
Canada/Newfoundland	US/Alaska
Canada/Pacific	US/Aleutian
Canada/Saskatchewan	US/Arizona
Canada/Yukon	US/Central
Europe/Dublin	US/East-Indiana
Europe/Istanbul	US/Eastern
Europe/Lisbon	US/Hawaii
Europe/London	US/Michigan
Europe/Moscow	US/Mountain
Europe/Warsaw	US/Pacific
Greenwich	US/Pacific-New
Pacific/Auckland	US/Samoa
Pacific/Chatham

To find more time zone values, you can run the following query:

SELECT DISTINCT tzname
FROM V$TIMEZONE_NAMES;

APPLIES TO

The TZ_OFFSET function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

EXAMPLE

Let's look at some Oracle TZ_OFFSET function examples and explore how to use the TZ_OFFSET function in Oracle/PLSQL.

For example:

TZ_OFFSET('US/Michigan')
Result: '-05:00'

TZ_OFFSET('-08:00')
Result: '-08:00'

TZ_OFFSET(sessiontimezone)
Result: '-07:00'  (depending on your configuration)

TZ_OFFSET(dbtimezone)
Result: '-07:00'  (depending on your configuration)

ORACLE/PLSQL: SYS_CONTEXT FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL SYS_CONTEXT function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL SYS_CONTEXT function can be used to retrieve information about the Oracle environment.

SYNTAX

The syntax for the Oracle/PLSQL SYS_CONTEXT function is:

SYS_CONTEXT( namespace, parameter, [ length ] )

Parameters or Arguments

namespace is an Oracle namespace that has already been created. If the namespace of 'USERENV' is used, attributes describing the current Oracle session can be returned.

parameter is a valid attribute that has been set using the DBMS_SESSION.set_context procedure.

length is optional. It is the length of the return value in bytes. If this parameter is omitted or if an invalid entry is provided, the sys_context function will default to 256 bytes.

NOTE

The valid parameters for the namespace called 'USERENV' are as follows: (Note that not all parameters are valid in all versions of Oracle)

Parameter	Explanation	Oracle 9i	Oracle 10g	Oracle 11g
ACTION	Returns the position in the module	No	Yes	Yes
AUDITED_CURSORID	Returns the cursor ID of the SQL that triggered the audit	Yes	Yes	Yes
AUTHENTICATED_IDENTITY	Returns the identity used in authentication	No	Yes	Yes
AUTHENTICATION_DATA	Authentication data	Yes	Yes	Yes
AUTHENTICATION_METHOD	Returns the method of authentication	No	Yes	Yes
AUTHENTICATION_TYPE	Describes how the user was authenticated. Can be one of the following values: Database, OS, Network, or Proxy	Yes	No	No
BG_JOB_ID	If the session was established by an Oracle background process, this parameter will return the Job ID. Otherwise, it will return NULL.	Yes	Yes	Yes
CLIENT_IDENTIFIER	Returns the client identifier (global context)	Yes	Yes	Yes
CLIENT_INFO	User session information	Yes	Yes	Yes
CURRENT_BIND	Bind variables for fine-grained auditing	No	Yes	Yes
CURRENT_SCHEMA	Returns the default schema used in the current schema	Yes	Yes	Yes
CURRENT_SCHEMAID	Returns the identifier of the default schema used in the current schema	Yes	Yes	Yes
CURRENT_SQL	Returns the SQL that triggered the audit event	Yes	Yes	Yes
CURRENT_SQL_LENGTH	Returns the length of the current SQL statement that triggered the audit event	No	Yes	Yes
CURRENT_USER	Name of the current user	Yes	No	No
CURRENT_USERID	Userid of the current user	Yes	No	No
DB_DOMAIN	Domain of the database from the DB_DOMAIN initialization parameter	Yes	Yes	Yes
DB_NAME	Name of the database from the DB_NAME initialization parameter	Yes	Yes	Yes
DB_UNIQUE_NAME	Name of the database from the DB_UNIQUE_NAME initialization parameter	No	Yes	Yes
ENTRYID	Available auditing entry identifier	Yes	Yes	Yes
ENTERPRISE_IDENTITY	Returns the user's enterprise-wide identity	No	Yes	Yes
EXTERNAL_NAME	External of the database user	Yes	No	No
FG_JOB_ID	If the session was established by a client foreground process, this parameter will return the Job ID. Otherwise, it will return NULL.	Yes	Yes	Yes
GLOBAL_CONTEXT_MEMORY	The number used in the System Global Area by the globally accessed context	Yes	Yes	Yes
GLOBAL_UID	The global user ID from Oracle Internet Directory for enterprise security logins. Returns NULL for all other logins.	No	No	Yes
HOST	Name of the host machine from which the client has connected	Yes	Yes	Yes
IDENTIFICATION_TYPE	Returns the way the user's schema was created	No	Yes	Yes
INSTANCE	The identifier number of the current instance	Yes	Yes	Yes
INSTANCE_NAME	The name of the current instance	No	Yes	Yes
IP_ADDRESS	IP address of the machine from which the client has connected	Yes	Yes	Yes
ISDBA	Returns TRUE if the user has DBA privileges. Otherwise, it will return FALSE.	Yes	Yes	Yes
LANG	The ISO abbreviate for the language	Yes	Yes	Yes
LANGUAGE	The language, territory, and character of the session. In the following format: language_territory.characterset	Yes	Yes	Yes
MODULE	Returns the appplication name set through DBMS_APPLICATION_INFO package or OCI	No	Yes	Yes
NETWORK_PROTOCOL	Network protocol used	Yes	Yes	Yes
NLS_CALENDAR	The calendar of the current session	Yes	Yes	Yes
NLS_CURRENCY	The currency of the current session	Yes	Yes	Yes
NLS_DATE_FORMAT	The date format for the current session	Yes	Yes	Yes
NLS_DATE_LANGUAGE	The language used for dates	Yes	Yes	Yes
NLS_SORT	BINARY or the linguistic sort basis	Yes	Yes	Yes
NLS_TERRITORY	The territory of the current session	Yes	Yes	Yes
OS_USER	The OS username for the user logged in	Yes	Yes	Yes
POLICY_INVOKER	The invoker of row-level security policy functions	No	Yes	Yes
PROXY_ENTERPRISE_IDENTITY	The Oracle Internet Directory DN when the proxy user is an enterprise user	No	Yes	Yes
PROXY_GLOBAL_UID	The global user ID from Oracle Internet Directory for enterprise user security proxy users. Returns NULL for all other proxy users.	No	Yes	Yes
PROXY_USER	The name of the user who opened the current session on behalf of SESSION_USER	Yes	Yes	Yes
PROXY_USERID	The identifier of the user who opened the current session on behalf of SESSION_USER	Yes	Yes	Yes
SERVER_HOST	The host name of the machine where the instance is running	No	Yes	Yes
SERVICE_NAME	The name of the service that the session is connected to	No	Yes	Yes
SESSION_USER	The database user name of the user logged in	Yes	Yes	Yes
SESSION_USERID	The database identifier of the user logged in	Yes	Yes	Yes
SESSIONID	The identifier of the auditing session	Yes	Yes	Yes
SID	Session number	No	Yes	Yes
STATEMENTID	The auditing statement identifier	No	Yes	Yes
TERMINAL	The OS identifier of the current session	Yes	Yes	Yes

APPLIES TO

The SYS_CONTEXT function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle SYS_CONTEXT function examples and explore how to use the SYS_CONTEXT function in Oracle/PLSQL.

For example:

SYS_CONTEXT('USERENV', 'NLS_DATE_FORMAT')
Result: 'RR-MM-DD'

SYS_CONTEXT('USERENV', 'NLS_SORT')
Result: 'BINARY'

ORACLE/PLSQL: TO_CLOB FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL TO_CLOB function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL TO_CLOB function converts a LOB value from the national character set to the database character set.

SYNTAX

The syntax for the Oracle/PLSQL TO_CLOB function is:

TO_CLOB( expression )

Parameters or Arguments

expression can either be a lob_column, char, varchar2, nchar, nvarchar2, clob or nclob value.

APPLIES TO

The TO_CLOB function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

EXAMPLE

Let's look at some Oracle TO_CLOB function examples and explore how to use the TO_CLOB function in Oracle/PLSQL.

For example:

select TO_CLOB(nclob_column)
from suppliers;

This example would convert the value in the field called nclob_column to a CLOB value.

ORACLE/PLSQL: TO_DSINTERVAL FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL TO_DSINTERVAL function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL TO_DSINTERVAL function converts a string to an INTERVAL DAY TO SECOND type.

SYNTAX

The syntax for the Oracle/PLSQL TO_DSINTERVAL function is:

TO_DSINTERVAL( character [ nls_parameter ] )

Parameters or Arguments

character is the value to convert to an INTERVAL DAY TO SECOND type. It can be a char, varchar2, nchar, or nvarchar2 value.

nls_parameter is a decimal character and group separator value.

APPLIES TO

The TO_DSINTERVAL function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

EXAMPLE

Let's look at some Oracle TO_DSINTERVAL function examples and explore how to use the TO_DSINTERVAL function in Oracle/PLSQL.

For example:

TO_DSINTERVAL('150 08:30:00')
Result: '+000000150'

TO_DSINTERVAL('80 12:30:00')
Result: '+000000080'

TO_DSINTERVAL('95 18:30:00')
Result: '+000000095'

ORACLE/PLSQL: TO_LOB FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL TO_LOB function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL TO_LOB function converts LONG or LONG RAW values to LOB values.

SYNTAX

The syntax for the Oracle/PLSQL TO_LOB function is:

TO_LOB( long_column )

Parameters or Arguments

long_column can be a LONG or LONG RAW value.

APPLIES TO

The TO_LOB function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle TO_LOB function examples and explore how to use the TO_LOB function in Oracle/PLSQL.

For example:

insert into companies (lob_column)
select TO_LOB(long_column)
from suppliers;

This example would convert the value in the field called long_column to a LOB value.

ORACLE/PLSQL: TO_MULTI_BYTE FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL TO_MULTI_BYTE function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL TO_MULTI_BYTE function returns a character value with all of the single-byte characters converted to multibyte characters. To use this function, your database character set contains both single-byte and multibyte characters.

SYNTAX

The syntax for the Oracle/PLSQL TO_MULTI_BYTE function is:

TO_MULTI_BYTE( char )

Parameters or Arguments

char can be a char, varchar2, nchar, or nvarchar2 value. This function will return its result in the same character set as char.

APPLIES TO

The TO_MULTI_BYTE function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle TO_MULTI_BYTE function examples and explore how to use the TO_MULTI_BYTE function in Oracle/PLSQL.

For example:

select TO_MULTI_BYTE('Tech on the net')
from dual;

The SQL statement above would return a multibyte character value.

ORACLE/PLSQL: TO_NCLOB FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL TO_NCLOB function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL TO_NCLOB function converts a LOB value to a NCLOB value.

SYNTAX

The syntax for the Oracle/PLSQL TO_NCLOB function is:

TO_NCLOB( expression )

Parameters or Arguments

expression can be a char, varchar2, nchar, nvarchar2, clob or nclob value.

APPLIES TO

The TO_NCLOB function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

EXAMPLE

Let's look at some Oracle TO_NCLOB function examples and explore how to use the TO_NCLOB function in Oracle/PLSQL.

For example:

select TO_NCLOB(lob_column)
from suppliers;

This example would convert the value in the field called lob_column to an NCLOB value.

ORACLE/PLSQL: TO_SINGLE_BYTE FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL TO_SINGLE_BYTE function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL TO_SINGLE_BYTE function returns a character value with all of the multibyte characters converted to single-byte characters. To use this function, your database character set contains both single-byte and multibyte characters.

SYNTAX

The syntax for the Oracle/PLSQL TO_SINGLE_BYTE function is:

TO_SINGLE_BYTE( char )

Parameters or Arguments

char can be a char, varchar2, nchar, or nvarchar2 value. This function will return its result in the same character set as char.

APPLIES TO

The TO_SINGLE_BYTE function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle TO_SINGLE_BYTE function examples and explore how to use the TO_SINGLE_BYTE function in Oracle/PLSQL.

For example:

select TO_SINGLE_BYTE('Tech on the net')
from dual;

The SQL statement above would return a single-byte character value.

ORACLE/PLSQL: UID FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL UID function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL UID function returns the id number for a user's session (the user who is currently logged in).

SYNTAX

The syntax for the Oracle/PLSQL UID function is:

UID

Parameters or Arguments

There are no parameters or arguments for the UID function.

APPLIES TO

The UID function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle UID function examples and explore how to use the UID function in Oracle/PLSQL.

For example:

SELECT UID
INTO parm_uid
FROM dual;

The variable called parm_uid will now contain the id number for the user's session. The value could be 25, for example.

ORACLE/PLSQL: USERENV FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL USERENV function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL USERENV function can be used to retrieve information about the current Oracle session. Although this function still exists in Oracle for backwards compatibility, it is recommended that you use the sys_context function instead.

SYNTAX

The syntax for the Oracle/PLSQL USERENV function is:

USERENV( parameter )

Parameters or Arguments

parameter is the value to return from the current Oracle session. The possible values are:

Parameter	Explanation
CLIENT_INFO	Returns user session information stored using the DBMS_APPLICATION_INFO package
ENTRYID	Available auditing entry identifier
INSTANCE	The identifier number of the current instance
ISDBA	Returns TRUE if the user has DBA privileges. Otherwise, it will return FALSE.
LANG	The ISO abbreviation for the language
LANGUAGE	The language, territory, and character of the session. In the following format: language_territory.characterset
SESSIONID	The identifier of the auditing session
TERMINAL	The OS identifier of the current session

APPLIES TO

The USERENV function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle USERENV function examples and explore how to use the USERENV function in Oracle/PLSQL.

For example:

USERENV('ENTRYID')
Result: FALSE

USERENV('LANGUAGE')
Result: 'AMERICAN_AMERICA.WE8DEC'

ORACLE/PLSQL: VSIZE FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL VSIZE function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL VSIZE function returns the number of bytes in the internal representation of an expression.

SYNTAX

The syntax for the Oracle/PLSQL VSIZE function is:

VSIZE( expression )

Parameters or Arguments

expression is the string to evaluate.

APPLIES TO

The VSIZE function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle VSIZE function examples and explore how to use the VSIZE function in Oracle/PLSQL.

For example:

VSIZE('Tech on the net')
Result: 15

VSIZE('Tech on the net ')
Result: 16

VSIZE(null)
Result: 

VSIZE('')
Result: 

VSIZE(' ')
Result: 1

ORACLE/PLSQL: COS FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL COS function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL COS function returns the cosine of a number.

SYNTAX

The syntax for the Oracle/PLSQL COS function is:

COS( number )

Parameters or Arguments

number is the number used to calculate the cosine.

APPLIES TO

The COS function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle COS function examples and explore how to use the COS function in Oracle/PLSQL.

For example:

COS(0.2)
Result: 0.980066577841242

COS(0.35)
Result: 0.939372712847379

COS(-3.15)
Result: -0.999964658471342

COS(200)
Result: 0.487187675007006

ORACLE/PLSQL: COSH FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL COSH function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL COSH function returns the hyperbolic cosine of a number.

SYNTAX

The syntax for the Oracle/PLSQL COSH function is:

COSH( number )

Parameters or Arguments

number is the number used to calculate the hyperbolic cosine.

APPLIES TO

The COSH function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle COSH function examples and explore how to use the COSH function in Oracle/PLSQL.

For example:

COSH(0.2)
Result: 1.02006675561908

COSH(0.35)
Result: 1.06187781915599

COSH(-3.15)
Result: 11.6894583539049

COSH(200)
Result: 3.61298688406287E86

COSH(0)
Result: 1

ORACLE/PLSQL: ACOS FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL ACOS function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL ACOS function returns the arc cosine of a number.

SYNTAX

The syntax for the Oracle/PLSQL ACOS function is:

ACOS( number )

Parameters or Arguments

number is the number used to calculate the arc cosine.

APPLIES TO

The ACOS function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle ACOS function examples and explore how to use the ACOS function in Oracle/PLSQL.

For example:

ACOS(0.2)
Result: 1.36943840600457

ACOS(0.35)
Result: 1.21322522314939

ACOS(-0.15)
Result: 1.72136459957158

ORACLE/PLSQL: ASIN FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL ASIN function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL ASIN function returns the arc sine of a number.

SYNTAX

The syntax for the Oracle/PLSQL ASIN function is:

ASIN( number )

Parameters or Arguments

number is the number used to calculate the arc sine.

APPLIES TO

The ASIN function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle ASIN function examples and explore how to use the ASIN function in Oracle/PLSQL.

For example:

ASIN(0.2)
Result: 0.201357920790331

ASIN(0.35)
Result: 0.35757110364551

ASIN(-0.15)
Result: -0.150568272776686

ORACLE/PLSQL: TAN FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL TAN function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL TAN function returns the tangent of n.

SYNTAX

The syntax for the Oracle/PLSQL TAN function is:

TAN( n )

Parameters or Arguments

n is a number. It is an angle expressed in radians.

APPLIES TO

The TAN function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle TAN function examples and explore how to use the TAN function in Oracle/PLSQL.

For example:

TAN(3)
Result: -0.142546543074278

TAN(5.2)
Result: -1.88564187751976

TAN(-5.2)
Result: 1.88564187751976

ORACLE/PLSQL: TANH FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL TANH function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL TANH function returns the hyperbolic tangent of n.

SYNTAX

The syntax for the Oracle/PLSQL TANH function is:

TANH( n )

Parameters or Arguments

n is a number.

APPLIES TO

The TANH function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle TANH function examples and explore how to use the TANH function in Oracle/PLSQL.

For example:

TANH(3)
Result: 0.99505475368673

TANH(5.2)
Result: 0.999939136886199

TANH(-5.2)
Result: -0.999939136886199

ORACLE/PLSQL: ATAN FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL ATAN function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL ATAN function returns the arc tangent of a number.

SYNTAX

The syntax for the Oracle/PLSQL ATAN function is:

ATAN( number )

Parameters or Arguments

number is the number used to calculate the arc tangent.

APPLIES TO

The ATAN function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle ATAN function examples and explore how to use the ATAN function in Oracle/PLSQL.

For example:

ATAN(0.2)
Result: 0.197395559849881

ATAN(0.35)
Result: 0.336674819386727

ATAN(-0.15)
Result: -0.148889947609497

ORACLE/PLSQL: ATAN2 FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL ATAN2 function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL ATAN2 function returns the arc tangent of n and m.

SYNTAX

The syntax for the Oracle/PLSQL ATAN2 function is:

ATAN2( n, m )

Parameters or Arguments

n and m are the numbers used to calculate the arc tangent.

APPLIES TO

The ATAN2 function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle ATAN2 function examples and explore how to use the ATAN2 function in Oracle/PLSQL.

For example:

ATAN2(0.2, 0.3)
Result: 0.588002603547568

ATAN2(0.35, -0.15)
Result: 1.97568811307998

ATAN2(-0.3, -0.4)
Result: -2.49809154479651

ORACLE/PLSQL: SIN FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL SIN function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL SIN function returns the sine of n.

SYNTAX

The syntax for the Oracle/PLSQL SIN function is:

SIN( n )

Parameters or Arguments

n is a number. It is an angle expressed in radians.

APPLIES TO

The SIN function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle SIN function examples and explore how to use the SIN function in Oracle/PLSQL.

For example:

SIN(3)
Result: 0.141120008059867

SIN(5.2)
Result: -0.883454655720153

SIN(-5.2)
Result: 0.883454655720153

ORACLE/PLSQL: SINH FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL SINH function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL SINH function returns the hyperbolic sine of n.

SYNTAX

The syntax for the Oracle/PLSQL SINH function is:

SINH( n )

Parameters or Arguments

n is a number.

APPLIES TO

The SINH function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle SINH function examples and explore how to use the SINH function in Oracle/PLSQL.

For example:

SINH(3)
Result: 10.0178749274099

SINH(5.2)
Result: 90.6333626553652

SINH(-5.2)
Result: -90.6333626553652

ORACLE/PLSQL: COVAR_POP FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL COVAR_POP function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL COVAR_POP function returns the population covariance of a set of number pairs.

SYNTAX

The syntax for the Oracle/PLSQL COVAR_POP function is:

COVAR_POP( expression1, expression2 )

Parameters or Arguments

expression1 is a numeric expression.

expression2 is a numeric expression.

NOTE

The COVAR_POP function eliminates all pairs where expression1 or expression2 has a null value.

APPLIES TO

The COVAR_POP function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle COVAR_POP function examples and explore how to use the COVAR_POP function in Oracle/PLSQL.

For example:

select owner, COVAR_POP(avg_row_len, avg_space)
from all_tables
group by owner;

ORACLE/PLSQL: COVAR_SAMP FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL COVAR_SAMP function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL COVAR_SAMP function returns the sample covariance of a set of number pairs.

SYNTAX

The syntax for the Oracle/PLSQL COVAR_SAMP function is:

COVAR_SAMP( expression1, expression2 )

Parameters or Arguments

expression1 is a numeric expression.

expression2 is a numeric expression.

NOTE

The COVAR_SAMP function eliminates all pairs where expression1 or expression2 has a null value.

APPLIES TO

The COVAR_SAMP function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE

Let's look at some Oracle COVAR_SAMP function examples and explore how to use the COVAR_SAMP function in Oracle/PLSQL.

For example:

select owner, COVAR_SAMP(avg_row_len, avg_space)
from all_tables
group by owner;

ORACLE/PLSQL: CUME_DIST FUNCTION

This Oracle tutorial explains how to use the Oracle/PLSQL CUME_DIST function with syntax and examples.

DESCRIPTION

The Oracle/PLSQL CUME_DIST function returns the cumulative distribution of a value in a group of values. The CUME_DIST function will return a value that is >0 and <=1.

The CUME_DIST function can be used two ways - as an Aggregate function or as an Analytic function.

CUME_DIST FUNCTION SYNTAX #1 - USED AS AN AGGREGATE FUNCTION

As an Aggregate function, the CUME_DIST function returns the relative position of a row within a group of rows.

The syntax for the CUME_DIST function when used as an Aggregate function is:

CUME_DIST( expression1, ... expression_n ) WITHIN GROUP ( ORDER BY expression1, ... expression_n )

Parameters or Arguments

expression1 .. expression_n can be one or more expressions which identify a unique row in the group.

NOTE

There must be the same number of expressions in the first expression list as there is in the ORDER BY clause.

The expression lists match by position so the data types must be compatible between the expressions in the first expression list as in the ORDER BY clause.

APPLIES TO

The CUME_DIST function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i

EXAMPLE (AS AN AGGREGATE FUNCTION)

Let's look at some Oracle CUME_DIST function examples and explore how to use the CUME_DIST function in Oracle/PLSQL.

For example:

select CUME_DIST(1000, 500) WITHIN GROUP (ORDER BY salary, bonus)
from employees;

The SQL statement above would return the cumulative distribution of an employee with a salary of $1,000 and a bonus of $500 from within the employees table.

CUME_DIST FUNCTION SYNTAX #2 - USED AS AN ANALYTIC FUNCTION

As an Analytic function, the CUME_DIST function returns the relative position of a value within a group of values.

The syntax for the CUME_DIST function when used as an Analytic function is:

CUME_DIST() OVER ( [ query_partition_clause] ORDER BY clause )

APPLIES TO

The CUME_DIST function can be used in the following versions of Oracle/PLSQL:

Oracle 12c, Oracle 11g, Oracle 10g, Oracle 9i, Oracle 8i

EXAMPLE (AS AN ANALYTIC FUNCTION)

Let's look at some Oracle CUME_DIST function examples and explore how to use the CUME_DIST function in Oracle/PLSQL.

For example:

select employee_name, salary,
CUME_DIST() OVER (PARTITION BY department ORDER BY salary)
from employees
where department = 'Marketing';

The SQL statement above would return the salary percentile for each employee in the Marketing department.

Discover Millions of Products your daily Need

Amazon::::Discover Millions of Products your daily Need

Amway Products in Hyderabad. All India Distributors (Certified Dietitian / Nutrition Advisor )

Amway Products in Kakinada. All India Distributors ( Certified Dietitian / Nutrition Advisor)

Monday, December 1, 2014

Oracle Tutorial 9

ORACLE/PLSQL: TRUNC FUNCTION (WITH NUMBERS)

DESCRIPTION

SYNTAX (WITH NUMBERS)

Parameters or Arguments

APPLIES TO

EXAMPLE (WITH NUMBERS)

ORACLE/PLSQL: GREATEST FUNCTION

DESCRIPTION

SYNTAX

Parameters or Arguments

NOTE

APPLIES TO

EXAMPLE

ORACLE/PLSQL: LEAST FUNCTION

DESCRIPTION

SYNTAX

Parameters or Arguments

NOTE

APPLIES TO

EXAMPLE

ORACLE/PLSQL: SIGN FUNCTION

DESCRIPTION

SYNTAX

Parameters or Arguments

NOTE

APPLIES TO

EXAMPLE

ORACLE/PLSQL: EXP FUNCTION

DESCRIPTION

SYNTAX

Parameters or Arguments

APPLIES TO

EXAMPLE

ORACLE/PLSQL: POWER FUNCTION

DESCRIPTION

SYNTAX

Parameters or Arguments

NOTE

APPLIES TO

EXAMPLE

ORACLE/PLSQL: MOD FUNCTION

DESCRIPTION

SYNTAX

Parameters or Arguments

NOTE

APPLIES TO

EXAMPLE

ORACLE/PLSQL: REMAINDER FUNCTION

DESCRIPTION

SYNTAX

Parameters or Arguments

NOTE

APPLIES TO

EXAMPLE

ORACLE/PLSQL: MEDIAN FUNCTION

DESCRIPTION

SYNTAX

Parameters or Arguments

NOTE

APPLIES TO

EXAMPLE

ORACLE/PLSQL: CORR FUNCTION

DESCRIPTION

SYNTAX

Parameters or Arguments

NOTE

APPLIES TO

EXAMPLE

ORACLE/PLSQL: EXTRACT FUNCTION

DESCRIPTION

SYNTAX

NOTE

APPLIES TO

EXAMPLE