Oracle Articles Blog

The syntax for the case statement is:

CASE  [ expression ]
WHEN condition_1 THEN result_1
WHEN condition_2 THEN result_2
...
WHEN condition_n THEN result_n
ELSE result
END

Expression is optional. It is the value that you are comparing to the list of conditions. (ie: condition_1, condition_2, ... condition_n)

Condition_1 to Condition_n must all be the same datatype. Conditions are evaluated in the order listed. Once a condition is found to be true,
the case statement will return the result and not evaluate the conditions any further.

Result_1 to Result_n must all be the same datatype. This is the value returned once a condition is found to be true.

Source: http://www.techonthenet.com/oracle/functions/case.php

The decode function provides an IF-THEN-ELSE condition.

Syntax:

decode( Value1, Value2 , result [, value2, result]... [, default] )


Value1 is the one to be compared.
Value2 is the value that is compared against Value1.
Result is returned, if Value1 is equal to Value2.
If no matches are found, the decode will return Default.

Note: If default is not provided, the decode statement will return null (if no matches are found).

SYSDATE

The oracle sysdate function returns the current date/time.

ADD_MONTHS

Returns a date plus n months.

Example:

add_months('01-Jan-09', 3)     returns '01-Mar-09'



GREATEST

Returns the greatest value in a list of expressions.

Example:

Greatest(12, 50,  3)                                      would return 50

LEAST

Returns the smallest value in a list of expressions.

Example:

Least(12, 50, 3)                                           would return 3

LAST_DAY

Returns the last day of the month based on a date value.

Example:

Last_day(to_date('2009/11/09', 'yyyy/mm/dd'))                   would return Nov 30, 2009

MONTHS_BETWEEN

Returns the number of months between date1 and date2.

Example:

Months_between (to_date ('2009/12/10', 'yyyy/mm/dd'), to_date ('2009/07/02', 'yyyy/mm/dd') )

would return 5

NEXT_DAY

Returns the first weekday that is greater than a date

Example:

ROUND

Returns a number rounded to a certain number of decimal places.

Example:

Round(26634.4359, 1) would return 26634.4

TRUNC

Returns a date truncated to a specific unit of measure.

Example:

trunc(to_date('10-FEB-09'), 'MONTH') would return '10-FEB-09'

Finding grants on the objects:

Fire the below query on user_tab_privs table to find the grants and their details.

SELECT * FROM user_tab_privs;

How to find the Oracle version of a particular database?

Use of either of these queries below:

select * from v$version

select * from product_component_version