Date Format Element Prefixes & Suffixes
There are following table lists prefixes/suffixes which can be added to date format elements:
Prefix/Suffix Meaning Element Value
FM Spelled character fmMONTH ‘June’
TH Ordinal Number DDTH 4TH
SP Spelled Number DDSP FOUR
SPTH or Spelled, ordinal number DDSPTH FOURTH
THSP
TO_NUMBER
Syntax
TO_NUMBER(char [,fmt [, 'nlsparams'] ])
It should Converts char, a value of CHAR or VARCHAR2 datatype holding a number in the format specified through the optional format model fmt, to a value of NUMBER datatype.
Example
UPDATE emp SET sal = sal + TO_NUMBER ('89')
WHERE ename = 'BLAKE'
The given update statement updates the record where the employee name is BLAKE.
Other Functions
DUMP
Syntax
DUMP(expr [,return_format [, start_position [, length]] ] )
It should returns a VARCHAR2 value holding the datatype code and length in bytes, internal representation of expr. This argument return_format specifies the format of the return value and can have any of these values:
8 - this will returns result in octal notation.
10 - this will returns result in decimal notation.
16 - this will returns result in hexadecimal notation.
17 - this will returns result as single characters.
The argument start_position and length combine to determine that portion of the internal representation to return. Default is to return the entire internal representation in decimal notation.
If expr is null and this function returns 'NULL'.
Examples
SELECT DUMP(ename, 8, 3, 2) "OCTAL" FROM emp WHERE ename = 'SCOTT'
OCTAL
-------------------------------
Type=1 Len=5: 117,124
SELECT DUMP(ename, 10, 3, 2) "ASCII" FROM emp WHERE ename = 'SCOTT'
ASCII
----------------------------
Type=1 Len=5: 79,84
GREATEST
Syntax
GREATEST(expr [,expr] ...)
It will return the greatest of the list of exprs. Whole exprs after the first are implicitly converted to the datatype of the first prior to the comparison. The ORACLE compares the exprs by using non-padded comparison semantics. A Character comparison is based on the value of the character in the database character set. If it has a higher value one character is greater than another. If the value returned through this function is character data and its datatype is always VARCHAR2.
Example
SELECT GREATEST('HARRY','HARRIOT','HAROLD') "GREATEST" FROM DUAL
GREATEST
--------
HARRY
LEAST
Syntax
LEAST(expr [,expr] ...)
It will return the least of the list of exprs. The whole exprs after the first are implicitly converted to the datatype of the first prior to the comparison. An ORACLE compares the exprs using non-padded comparison semantics. Its datatype is always VARCHAR2 if the value returned through this function is character data.
Example
SELECT LEAST('HARRY','HARRIOT','HAROLD') "LEAST" FROM DUAL
LEAST
------
HAROLD
NVL
Syntax
NVL(expr1, expr2)
If the expr1 is null it returns expr2 and if expr1 is not null it returns expr1. An arguments expr1 and expr2 can have any datatype. If their datatypes are dissimilar an ORACLE converts expr2 to the datatype of expr1 before comparing them. Datatype of the return value is always the similar as the datatype of expr1 unless expr1 is character data in that case the return value's datatype is VARCHAR2.
Example
SELECT ename, NVL(TO_CHAR(COMM),'NOT APPLICABLE') "COMMISSION" FROM emp
WHERE deptno = 30
ENAME COMMISSION
--------- -----------
ALLEN 300
WARD 500
ARTIN 1400
BLAKE NOT APPLICABLE
TURNER 0
JAMES NOT APPLICABLE
UID
Syntax
UID
It should return an integer which uniquely identifies the current user.
USER
Syntax
USER
It will return the current ORACLE user with the datatype VARCHAR2. The ORACLE compares values of this function with blank-padded comparison semantics.
In a distributed SQL statement, the UID and USER functions identify the user on the local database. These functions cannot be used in the CHECK constraint.
Example
SELECT USER, UID FROM DUAL
USER UID
--------- -------
OPS$KING 9
SYSDATE
Syntax
SYSDATE
It will return the current date and time. Needs no arguments. In the distributed SQL statements these functions return the date and time on the local database. SYSDATE function cannot be used in the condition of a CHECK constraint.
Example
SELECT SYSDATE “TODAY” FROM DUAL
TODAY
-------------------
04-DEC-00
DECODE
Syntax:
DECODE(variable,<condition1>,<value1>,<condition2>,<value2>…)
DECODE function is used to check for the if..then condition and shows the corresponding values.
Example:
SELECT ENAME, DEPTNO, DECODE (deptno,10,’Accounting’,20,’Sales’,30,’Purchase’, ’others’) FROM EMP;
The output of that query will be:
ENAME DEPTNO DECODE(DEP
---------- --------- ----------
KING 10 Accounting
BLAKE 30 Purchase
CLARK 10 Accounting
JONES 20 Sales
MARTIN 30 Purchase
ALLEN 30 Purchase
TURNER 30 Purchase
JAMES 30 Purchase
WARD 30 Purchase
FORD 20 Sales
SMITH 20 Sales
SCOTT 20 Sales
ADAMS 20 Sales
MILLER 10 Accounting
By the record whose deptno is 10 their corresponding name in the DECODE function is displayed and where the deptno is 20 their corresponding values are displayed. Basically this function must not be used inside PL/SQL.
The subsequent Table is lists of all the functions:
e 