Oracle Character Functions

A character function is a function that takes one or more character values as parameters and returns either a character value or a number value.

The Built-In Character Functions

• ASCII
• CHR
• CONCAT
• INITCAP
• LOWER
• LPAD
• LTRIM
• NLS_INITCAP
• REGEXP_REPLACE
• REGEXP_SUBSTR
• REPLACE
• RPAD
• RTRIM
• SOUNDEX
• SUBSTR
• TRANSLATE
• TREAT
• TRIM
• UPPER

ASCII Function

Returns the ASCII code of a character.

Examples

SELECT ASCII('A') FROM DUAL;

ASCII('A')
----------
       65

SELECT ASCII('a') FROM DUAL;

ASCII('A')
----------
       97

SELECT ASCII('Z') FROM DUAL;

ASCII('Z')
----------
       90

CHR Function

Returns the character associated with the specified collating code.

The CHR function is the inverse of ASCII. It returns a VARCHAR2 character (length 1) that corresponds to the location in the collating sequence provided as a parameter.

Examples

SELECT CHR(65) FROM DUAL;

CHR(65)
-------
     A

SELECT CHR(97) FROM DUAL;

CHR(97)
-------
     a

SELECT CHR(67)||CHR(65)||CHR(84) "Cat" FROM DUAL;

Cat
---
CAT

CONCAT Function

Concatenates two strings into one.

The CONCAT function concatenates by taking two VARCHAR2 strings and returning those strings appended together in the order specified.

The CONCAT function is equivalent to the concatenation operator (||).

Examples

select CONCAT('abc', 'defg') from dual
 
CONCAT('abc', 'defg')
----------------------
abcdefg

SELECT CONCAT(FIRST_NAME, LAST_NAME) FULL_NAME, DEPARTMENT_ID 
  FROM EMPLOYEES;

FULL_NAME            DEPARTMENT_ID
------------------- -----------------
DonaldOConnell                  50
DouglasGrant                    50
JenniferWhalen                  10
MichaelHartstein                20
PatFay                          20

INITCAP Function

INITCAP function sets the first character in each word to uppercase and the rest to lowercase.

The return value is the same datatype as char.

Examples

SELECT INITCAP('the soap') "Initcap" FROM DUAL; 

Initcap
---------
The Soap

LOWER Function

LOWER function converts all letters in the specified string to lowercase. If there are characters in the string that are not letters, they are unaffected by this function.

Examples

SELECT LOWER('The Soap') "lower" FROM DUAL; 

lower
---------
the soap

SELECT LOWER('THE SOAP') "lower" FROM DUAL; 

lower
---------
the soap

LPAD Function

LPAD function pads the left-side of a string with a specific set of characters (when string1 is not null). This function is useful for formatting the output of a query.

Syntax

lpad( string1, padded_length, [ pad_string ] )

string1 is the string to pad characters to (the left-hand side).

padded_length is the number of characters to return. If the padded_length is smaller than the original string, the lpad function will truncate the string to the size of padded_length.

pad_string is optional. This is the string that will be padded to the left-hand side of string1. If this parameter is omitted, the lpad function will pad spaces to the left-side of string1.

Examples

SELECT LPAD('Oracle',10,'*') "LPAD example"   FROM DUAL;

LPAD example
---------------
  ****Oracle

SELECT LPAD('12345',10,'0') "LPAD example"   FROM DUAL;

LPAD example
---------------
  0000012345

LTRIM Function

In Oracle/PLSQL, the LTRIM Function removes all specified characters from the left-hand side of a string.

Syntax

ltrim( string1, [ trim_string ] )

string1 is the string to trim the characters from the left-hand side.

trim_string is the string that will be removed from the left-hand side of string1. If this parameter is omitted, the LTRIM Function will remove all leading spaces from string1.

Examples

select ltrim('123123Tech', '123') from dual

ltrim('123123Tech', '123')
--------------------------
Tech


SELECT product_name, LTRIM(product_name, 'Monitor ') "Short Name"
   FROM products
   WHERE product_name LIKE 'Monitor%';

PRODUCT_NAME         Short Name
-------------------- ---------------
Monitor 17/HR        17/HR
Monitor 17/HR/F      17/HR/F
Monitor 17/SD        17/SD
Monitor 19/SD        19/SD
Monitor 19/SD/M      19/SD/M


ltrim('000123', '0');           would return '123'
ltrim('123123Tech', '123');     would return 'Tech'
ltrim('123123Tech123', '123');  would return 'Tech123'

NLS_INITCAP Function

NLS_INITCAP Function returns string with the first letter of each word in uppercase using the special linguistic requirements of case conversions.

The following examples show how the linguistic sort sequence results in a different return value from the function.

Examples

SELECT NLS_INITCAP('ijsland') "InitCap" FROM DUAL;

InitCap
-------
Ijsland

SELECT NLS_INITCAP('ijsland', 'NLS_SORT = XDutch') "InitCap"
   FROM DUAL;

InitCap
-------
IJsland

REPLACE Function

REPLACE Function replaces a sequence of characters in a string with another set of characters.

REPLACE provides functionality related to that provided by the TRANSLATE function. TRANSLATE provides single-character, one-to-one substitution. REPLACE lets you substitute one string for another as well as to remove character strings.

Syntax

replace( string1, string_to_replace, [ replacement_string ] )

string1 is the string to replace a sequence of characters with another set of characters.

string_to_replace is the string that will be searched for in string1.

replacement_string is optional. All occurrences of string_to_replace will be replaced with replacement_string in string1. If the replacement_string parameter is omitted, the replace function simply removes all occurrences of string_to_replace, and returns the resulting string.

Examples

SELECT REPLACE('JACK and JUE','J','BL') "Changes" FROM DUAL;

Changes
--------------
BLACK and BLUE

replace('123123tech', '123');   would return 'tech'
replace('123tech123', '123');   would return 'tech'
replace('222tech', '2', '3');   would return '333tech'
replace('0000123', '0');        would return '123'
replace('0000123', '0', ' ');   would return '    123'

RPAD Function

RPAD Function pads the right-side of a string with a specific set of characters. This function is useful for formatting the output of a query.

Syntax

rpad( string1, padded_length, [ pad_string ] )

string1 is the string to pad characters to (the right-hand side), when string1 is not null.

padded_length is the number of characters to return. If the padded_length is smaller than the original string, the RPAD Function will truncate the string to the size of padded_length.

pad_string is optional. This is the string that will be padded to the right-hand side of string1. If this parameter is omitted, the RPAD Function will pad spaces to the right-side of string1.

Examples

rpad('tech', 7);   would return 'tech   '
rpad('tech', 2);   would return 'te'
rpad('tech', 8, '0');   would return 'tech0000'

RTRIM Function

RTRIM Function removes all specified characters from the right-hand side of a string.

Syntax

rtrim( string1, [ trim_string ] )

string1 is the string to trim the characters from the right-hand side.

trim_string is the string that will be removed from the right-hand side of string1. If this parameter is omitted, the RTRIM Function will remove all trailing spaces from string1.

Examples

SELECT RTRIM('BROWNING: ./=./=./=./=./=.=','/=.') "RTRIM example" 
FROM DUAL;
 
RTRIM exam
----------
BROWNING:

SOUNDEX Function

SOUNDEX Function lets you compare words that are spelled differently, but sound alike in English. This function returns a phonetic representation (the way it sounds) of a string.

SOUNDEX Algorithm

1. The soundex return value will always begin with the first letter of string1 and remove all other occurrences of the following letters: a, e, h, i, o, u, w, y.
2. Assign numbers to the remaining letters (after the first) as follows:

b, f, p, v = 1
c, g, j, k, q, s, x, z = 2
d, t = 3
l = 4
m, n = 5
r = 6

3. The soundex function is not case-sensitive. What this means is that both uppercase and lowercase characters will generate the same soundex return value.
4. Return the first four bytes padded with 0.

Examples

SELECT last_name, first_name
     FROM hr.employees
     WHERE SOUNDEX(last_name) = SOUNDEX('SMYTHE');

LAST_NAME  FIRST_NAME
---------- ----------
Smith      Lindsey
Smith      William

soundex('tech on the net'); would return 'T253'
soundex('TECH ON THE NET'); would return 'T253'
soundex('apples');         would return 'A142'
soundex('apples are great'); would return 'A142'
soundex('applus');         would return 'A142'

SUBSTR Function

SUBSTR Function allows you to extract a substring from a string.

Syntax

substr( string, start_position, [ substring_length ] )

string is the source string.

start_position is the position for extraction. The first position in the string is always 1.

substring_length is optional. It is the number of characters to extract. If this parameter is omitted, substr will return the entire string.

• If start_position is 0, then substr treats start_position as 1 (ie: the first position in the string).
• If start_position is a positive number, then substr starts from the beginning of the string.
• If start_position is a negative number, then substr starts from the end of the string and counts backwards.
• If substring_length is a negative number, then substr will return a NULL value.

Examples

SELECT SUBSTR('ABCDEFG',3,4) "Substring"
     FROM DUAL;
 
Substring
---------
CDEF

SELECT SUBSTR('ABCDEFG',-5,4) "Substring"
     FROM DUAL;

Substring
---------
CDEF

TRANSLATE Function

TRANSLATE Function replaces a sequence of characters in a string with another set of characters. However, it replaces a single character at a time. For example, it will replace the 1st character in the string_to_replace with the 1st character in the replacement_string. Then it will replace the 2nd character in the string_to_replace with the 2nd character in the replacement_string, and so on.

Syntax

translate( string1, string_to_replace, replacement_string )

string1 is the string to replace a sequence of characters with another set of characters.

string_to_replace is the string that will be searched for in string1.

replacement_string - All characters in the string_to_replace will be replaced with the corresponding character in the replacement_string.

Examples

SELECT TRANSLATE('SQL*Plus User''s Guide', ' */''', '___') FROM DUAL;

TRANSLATE('SQL*PLUSU
--------------------
SQL_Plus_Users_Guide

TREAT Function

The Oracle TREAT function allows you to change the declared type of the expression used in TREAT. This function comes in handy when you have a subtype that is more specific to your data and you want to convert the parent type to the more specific one.

Examples

CREATE TYPE PERSON AS OBJECT (
FIRST_NAME VARCHAR2(15),
LAST_NAME VARCHAR2(15),
SALARY NUMBER(10));

CREATE TABLE PERSON_TABLE
(
ID      NUMBER(4) PRIMARY KEY,
PERSON_INFO  PERSON,
CREATED_DATE DATE
);

INSERT INTO PERSON_TABLE
VALUES(1,PERSON('SMITH','BLAKE',800),SYSDATE);

INSERT INTO PERSON_TABLE
VALUES(2,PERSON('ALLEN','WARD',800),SYSDATE);

SELECT * FROM PERSON_TABLE

ID   PERSON_INFO             CREATED_DATE
---  --------------------    --------------
1    (SMITH; BLAKE; 800)     06/05/2013 
2    (ALLEN; WARD; 900)      06/05/2013 

 SELECT ID,TREAT(PERSON_INFO AS PERSON).FIRST_NAME FIRST_NAME,
        TREAT(PERSON_INFO AS PERSON).LAST_NAME LAST_NAME,
        TREAT(PERSON_INFO AS PERSON).SALARY SALARY,
        CREATED_DATE 
  FROM PERSON_TABLE 
  
   ID   FIRST_NAME    LAST_NAME     SALARY   CREATED_DATE
-----   ------------  ------------  -------  ------------
    1   SMITH         BLAKE            800    06/05/2013
    2   ALLEN         WARD             900    06/05/2013

TRIM Function

TRIM Function enables you to remove leading or trailing characters (or both) from a character string.

Syntax

trim( [ leading | trailing | both [ trim_character ] string1 )

• LEADING- removes any leading characters equal to trim_character.
• TRAILING- removes any trailing characters equal to trim_character.
• BOTH- removes leading and trailing characters equal to trim_character.
• If trim_character parameter is omitted, TRIM Function will remove all leading and trailing spaces from string1. string1 is the string to trim.
• If you do not specify a value for the first parameter (leading, trailing, both), TRIM Function will remove trim_character from both the front and end of string1.

Examples

trim('   tech   ')                would return 'tech'
trim(' '  from  '   tech   ')     would return 'tech'
trim(leading '0' from '000123')   would return '123'
trim(trailing '1' from 'Tech1')   would return 'Tech'
trim(both '1' from '123Tech111')  would return '23Tech'

UPPER Function

UPPER Function converts all letters in the specified string to uppercase. If there are characters in the string that are not letters, they are unaffected by this function.

Examples

SELECT UPPER(last_name) "Uppercase"
   FROM employees;

upper('Tech on the Net'); would return 'TECH ON THE NET'
upper('george burns 123   '); would return 'GEORGE BURNS 123   '

Oracle Date and Time Functions

Date and Time functions operate on date (DATE), timestamp (TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE), and interval (INTERVAL DAY TO SECOND, INTERVAL YEAR TO MONTH) values.

Some of the datetime functions were designed for the Oracle DATE datatype (ADD_MONTHS, CURRENT_DATE, LAST_DAY, NEW_TIME, and NEXT_DAY). If you provide a timestamp value as their argument, Oracle Database internally converts the input type to a DATE value and returns a DATE value. The exceptions are the MONTHS_BETWEEN function, which returns a number, and the ROUND and TRUNC functions, which do not accept timestamp or interval values at all.

The remaining datetime functions were designed to accept any of the three types of data (date, timestamp, and interval) and to return a value of one of these types.

The datetime functions are:

• ADD_MONTHS
• CURRENT_DATE
• CURRENT_TIMESTAMP
• DBTIMEZONE
• EXTRACT (datetime)
• FROM_TZ
• LAST_DAY
• LOCALTIMESTAMP
• MONTHS_BETWEEN
• NEW_TIME
• NEXT_DAY
• ROUND (date)
• SESSIONTIMEZONE
• SYSDATE
• SYSTIMESTAMP
• TO_CHAR (datetime)
• TO_TIMESTAMP
• TO_TIMESTAMP_TZ
• TO_DSINTERVAL
• TO_YMINTERVAL
• TRUNC (date)

ADD_MONTHS

ADD_MONTHS returns the date date plus integer months. The date argument can be a datetime value or any value that can be implicitly converted to DATE. The integer argument can be an integer or any value that can be implicitly converted to an integer. The return type is always DATE, regardless of the datatype of date. If date is the last day of the month or if the resulting month has fewer days than the day component of date, then the result is the last day of the resulting month. Otherwise, the result has the same day component as date.

Examples

SELECT TO_CHAR(ADD_MONTHS(hire_date,1),'DD-MON-YYYY') "Next month"
     FROM employees 
     WHERE last_name = 'Baer';

Next Month
-----------
07-JUL-2012

add_months('01-Aug-12', 3) would return '01-Nov-12'
add_months('01-Aug-12', -3) would return '01-May-12'

CURRENT_DATE

CURRENT_DATE returns the current date in the session time zone, in a value in the Gregorian calendar of datatype DATE.

Examples

SELECT SESSIONTIMEZONE, CURRENT_DATE FROM DUAL;

SESSIONTIMEZONE CURRENT_DATE
--------------- --------------------
+04:00          15/04/2013 09:13:26

CURRENT_TIMESTAMP

CURRENT_TIMESTAMP returns the current date and time in the session time zone, in a value of datatype TIMESTAMP WITH TIME ZONE. The difference between CURRENT_TIMESTAMP and LOCALTIMESTAMP is that CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value while LOCALTIMESTAMP returns a TIMESTAMP value.

Examples

SELECT SESSIONTIMEZONE, CURRENT_TIMESTAMP FROM DUAL;

SESSIONTIMEZONE CURRENT_TIMESTAMP
--------------- ---------------------------------------------
+04:00          15/04/2013 09:20:22.333000 AM +04:00

DBTIMEZONE

DBTIMEZONE returns the value of the database time zone. The return type is a time zone offset (a character type in the format '[+|-]TZH:TZM') or a time zone region name, depending on how the user specified the database time zone value in the most recent CREATE DATABASE or ALTER DATABASE statement.

Examples

SELECT DBTIMEZONE FROM DUAL;

DBTIMEZONE
------
+00:00

EXTRACT (datetime)

EXTRACT extracts and returns the value of a specified datetime field from a datetime or interval value expression. When you extract a TIMEZONE_REGION or TIMEZONE_ABBR (abbreviation), the value returned is a string containing the appropriate time zone name or abbreviation. When you extract any of the other values, the value returned is in the Gregorian calendar. When extracting from a datetime with a time zone value, the value returned is in UTC. For a listing of time zone names and their corresponding abbreviations, query the V$TIMEZONE_NAMES dynamic performance view.

Examples

SELECT EXTRACT(month FROM order_date) "Month",
  COUNT(order_date) "No. of Orders"
  FROM orders
  GROUP BY EXTRACT(month FROM order_date)
  ORDER BY "No. of Orders" DESC;

     Month No. of Orders
---------- -------------
        11            15
         7            14
         6            14
         3            11
         5            10
         9             9
         2             9
         8             7
        10             6
         1             5
        12             4
         4             1
 
12 rows selected.

SELECT EXTRACT(YEAR FROM DATE '2013-04-10') FROM DUAL;

EXTRACT(YEARFROMDATE'1998-03-07')
---------------------------------
                             2013


SELECT EXTRACT(MONTH FROM DATE '2013-04-10') FROM DUAL;

EXTRACT(MONTHFROMDATE'1998-03-07')
---------------------------------
                               04

SELECT EXTRACT(DAY FROM DATE '2013-04-10') FROM DUAL;

EXTRACT(DAYFROMDATE'1998-03-07')
--------------------------------
                              10

FROM_TZ

FROM_TZ converts a timestamp value and a time zone to a TIMESTAMP WITH TIME ZONE value. time_zone_value is a character string in the format 'TZH:TZM' or a character expression that returns a string in TZR with optional TZD format.

Examples

SELECT FROM_TZ(TIMESTAMP '2013-03-28 08:00:00', '4:00') FROM DUAL;

FROM_TZ(TIMESTAMP'2013-03-2808:00:00','4:00')
-------------------------------------------------------
28-MAR-13 08.00.00 AM +04:00

LAST_DAY

LAST_DAY returns the date of the last day of the month that contains date. The return type is always DATE.

Examples

SELECT SYSDATE,
   LAST_DAY(SYSDATE) "Last",
   LAST_DAY(SYSDATE) - SYSDATE "Days Left"
   FROM DUAL;
 
SYSDATE   Last       Days Left
--------- --------- ----------
30-MAR-13 31-MAR-13          1

LOCALTIMESTAMP

LOCALTIMESTAMP returns the current date and time in the session time zone in a value of datatype TIMESTAMP. The difference between LOCALTIMESTAMP and CURRENT_TIMESTAMP is that LOCALTIMESTAMP returns a TIMESTAMP value while CURRENT_TIMESTAMP returns a TIMESTAMP WITH TIME ZONE value.

Examples

SELECT CURRENT_TIMESTAMP, LOCALTIMESTAMP FROM DUAL;

CURRENT_TIMESTAMP                    LOCALTIMESTAMP
-------------------------------------------------------------------
15/04/2013 10:45:01.535000 AM +04:00  15/04/2013 10:45:01.535000 AM

MONTHS_BETWEEN

MONTHS_BETWEEN returns number of months between dates date1 and date2. If date1 is later than date2, then the result is positive. If date1 is earlier than date2, then the result is negative. If date1 and date2 are either the same days of the month or both last days of months, then the result is always an integer. Otherwise Oracle Database calculates the fractional portion of the result based on a 31-day month and considers the difference in time components date1 and date2.

Examples

SELECT MONTHS_BETWEEN 
   (TO_DATE('03-03-2013','MM-DD-YYYY'),
    TO_DATE('01-01-2013','MM-DD-YYYY') ) "Months"
    FROM DUAL;

    Months
----------
2.06451612

NEW_TIME

NEW_TIME returns the date and time in time zone timezone2 when date and time in time zone timezone1 are date. The return type is always DATE, regardless of the datatype of date.

Examples

SELECT 
new_time (to_date ('2013/02/15 01:45', 'yyyy/mm/dd HH24:MI'), 'AST', 'MST') "New Date and Time"
FROM DUAL

New Date and Time
--------------------
14/02/2013 10:45:00 AM

NEXT_DAY

NEXT_DAY returns the date of the first weekday named by char that is later than the date date. The return type is always DATE.

Examples

SELECT NEXT_DAY('02-FEB-2013','TUESDAY') "NEXT DAY" FROM DUAL;

NEXT DAY
-----------
05-FEB-2013

ROUND (date)

ROUND returns date rounded to the unit specified by the format model fmt. The value returned is always of datatype DATE, even if you specify a different datetime datatype for date. If you omit fmt, then date is rounded to the nearest day. The date expression must resolve to a DATE value.

Examples

SELECT ROUND (TO_DATE ('27-07-12','DD-MM-YY'),'YEAR')  "New Year" FROM DUAL;
 
New Year
---------
01-01-13

SELECT ROUND (TO_DATE ('20-07-12','DD-MM-YY'),'MONTH')  "New Month" FROM DUAL;
 
New Month
---------
01-08-12

ROUND Function Valid Format Parameters:

Unit	Valid Format Parameters	Rounding Rule
Year	SYYYY, YYYY, YEAR, SYEAR, YYY, YY, Y	Rounds up on July 1st
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
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

SESSIONTIMEZONE

SESSIONTIMEZONE returns the time zone of the current session.

Examples

SELECT SESSIONTIMEZONE FROM DUAL;

SESSION
-------
+04:00

SYSDATE

SYSDATE returns the current date and time set for the operating system on which the database resides. The datatype of the returned value is DATE.

Examples

SELECT TO_CHAR(SYSDATE, 'MM-DD-YYYY HH24:MI:SS') "NOW"  FROM DUAL;

NOW
-------------------
04-16-2013 12:31:27

SYSTIMESTAMP

SYSTIMESTAMP returns the system date, including fractional seconds and time zone, of the system on which the database resides. The return type is TIMESTAMP WITH TIME ZONE.

Examples

SELECT SYSTIMESTAMP FROM DUAL;

SYSTIMESTAMP
-----------------------------------------------
16/04/2013 12:33:54.235000 PM +04:00

SELECT TO_CHAR(SYSTIMESTAMP, 'SSSSS.FF') "Fractional Seconds" FROM DUAL;

Fractional Seconds
---------------
45345.500000

TO_CHAR (datetime)

TO_CHAR (datetime) converts a datetime or interval value of DATE, TIMESTAMP, TIMESTAMP WITH TIME ZONE, or TIMESTAMP WITH LOCAL TIME ZONE datatype to a value of VARCHAR2 datatype in the format specified by the date format fmt.

Examples

to_char(sysdate, 'yyyy/mm/dd'); would return '2003/07/09'
to_char(sysdate, 'Month DD, YYYY'); would return 'July 09, 2003'
to_char(sysdate, 'FMMonth DD, YYYY'); would return 'July 9, 2003'
to_char(sysdate, 'MON DDth, YYYY'); would return 'JUL 09TH, 2003'
to_char(sysdate, 'FMMON DDth, YYYY'); would return 'JUL 9TH, 2003'
to_char(sysdate, 'FMMon ddth, YYYY'); would return 'Jul 9th, 2003'

List of Valid Parameters For the TO_CHAR (datetime) Function

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.

TO_TIMESTAMP

TO_TIMESTAMP converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP datatype.

Examples

SELECT TO_TIMESTAMP ('10-09-2012 14:10:10.123000', 'DD-MM-RR HH24:MI:SS.FF')
   FROM DUAL;

TO_TIMESTAMP('10-09-0214:10:10.123000','DD-MM-RRHH24:MI:SS.FF')
-------------------------------------------------------------------
10/09/2012 02:10:10.123000000 PM

TO_TIMESTAMP_TZ

TO_TIMESTAMP_TZ converts char of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to a value of TIMESTAMP WITH TIME ZONE datatype.

SELECT TO_TIMESTAMP_TZ('2012-12-01 11:00:00 +4:00',
   'YYYY-MM-DD HH:MI:SS TZH:TZM') FROM DUAL;

TO_TIMESTAMP_TZ('2012-12-0111:00:00+04:00','YYYY-MM-DDHH:MI:SSTZH:TZM')
--------------------------------------------------------------------
01/12/2012 11:00:00.000000000 AM +04:00

TO_DSINTERVAL

TO_DSINTERVAL converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to an INTERVAL DAY TO SECOND value.

Examples

SELECT employee_id, last_name FROM employees
   WHERE hire_date + TO_DSINTERVAL('100 10:00:00')
   <= DATE '2012-01-01';

EMPLOYEE_ID LAST_NAME
----------- ---------------
        100 King
        101 Kochhar
        200 Whalen

TO_YMINTERVAL

TO_YMINTERVAL converts a character string of CHAR, VARCHAR2, NCHAR, or NVARCHAR2 datatype to an INTERVAL YEAR TO MONTH type, where char is the character string to be converted.

Examples

SELECT hire_date, hire_date + TO_YMINTERVAL('01-02') "14 months"
   FROM employees;

HIRE_DATE       14 months
---------       ---------
11/01/2013 11/03/2014
03/01/2013 03/03/2014
10/01/2013 10/03/2014
11/01/2013 11/03/2014
20/12/2012 20/02/2014
20/12/2012 20/02/2014
02/01/2013 02/03/2014

TRUNC (date)

The TRUNC (date) function returns date with the time portion of the day truncated to the unit specified by the format model fmt. The value returned is always of datatype DATE.

Examples

SELECT TRUNC(TO_DATE('27-10-2012','DD-MM-YY'), 'YEAR') "Trunc Year" FROM DUAL;
 
Trunc Year
---------
01/01/2012

SELECT TRUNC(TO_DATE('27-10-2012','DD-MM-YY'), 'MONTH') "Trunc Month" FROM DUAL;
 
Trunc Month
---------
01/10/2012

For Trunc (date) Function 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

Oracle OPERATORS

Oracle provides operators to be used with data in order to perform some related action and return a result. An operator can act on a single operand (Unary Operators) or on two operands (Binary Operators).

The data items are called operands or arguments. Operators are represented by special characters or by keywords. For example, the multiplication operator is represented by an asterisk (*) and the operator that tests for nulls is represented by the keywords IS NULL.

Oracle provides following kinds of operators:

1. Arithmetic Operators
2. Concatenation Operators
3. Comparison Operators
4. Logical Operators
5. Hierarchical Operators
6. Set Operators

Arithmetic Operators

You can use an arithmetic operator in an expression to negate, add, subtract, multiply, and divide numeric values. The result of the operation is also a numeric value.

The available Arithmetic Operators are: +, -, *, and /.

Examples

The following are some examples.

SELECT sal + comm FROM emp
  WHERE SYSDATE - hiredate
  > 365;

UPDATE emp
  SET sal = sal * 1.1;

c := a + b;

Concatenation Operator

Concatenation Operator operate on two string operands to join them into a single string.

The available Concatenation Operator is ||.

Examples

SELECT 'Name is ' || ename
   FROM emp;

SELECT col1||col2||col3||col4 "Concatenation"
        FROM tab1;

Comparison Operators

Comparison Operators are used to build a comparable condition between two terms, thereby returning a logical result as TRUE, FALSE, or NULL.

The available Comparison operators are: =, <, >, <=, >=, (NOT)IN, (NOT) LIKE, (NOT) BETWEEN, (NOT) EXISTS, and IS (NOT) NULL.

Examples

The following are some examples.

=	Equality test.	SELECT * FROM emp WHERE sal = 1500;
>	Greater than test	SELECT * FROM emp WHERE sal > 1500;
<=	Less than or equal to tests	SELECT * FROM emp WHERE sal <= 1500;
NOT IN	Evaluates to FALSE if any member of the set is NULL.	SELECT * FROM emp WHERE job NOT IN ('CLERK', 'ANALYST');
EXISTS	TRUE if a subquery returns at least one row.	SELECT ename, deptno FROM dept WHERE EXISTS (SELECT * FROM emp WHERE dept.deptno = emp.deptno);

Logical Operators

Logical Operators operate on two operands and return a BOOLEAN output.

The available logical operators are: AND, OR, and NOT.

AND Operator

Returns TRUE if both component conditions are TRUE. Returns FALSE if either is FALSE. Otherwise returns UNKNOWN.

SELECT * FROM emp
  WHERE job = 'CLERK'
  AND deptno = 10;

OR Operator

Returns TRUE if either component condition is TRUE. Returns FALSE if both are FALSE. Otherwise returns UNKNOWN.

SELECT * FROM emp
  WHERE job = 'CLERK'
  OR deptno = 10;

NOT Operator

Returns TRUE if the following condition is FALSE. Returns FALSE if it is TRUE. If it is UNKNOWN, it remains UNKNOWN.

SELECT * FROM emp
  WHERE NOT (sal BETWEEN 1000 AND 2000);

Hierarchical Operators

Hierarchical Operators are used only in hierarchical queries.

The available Hierarchical Operators are: PRIOR and CONNECT_BY_ROOT.

Example

SELECT Lpad(ename,Length(ename) + LEVEL * 10 - 10,'-') 
FROM   emp 
START WITH mgr IS NULL 
CONNECT BY PRIOR empno = mgr;

KING
----------JONES
--------------------SCOTT
------------------------------ADAMS
--------------------FORD
------------------------------SMITH
----------BLAKE
--------------------ALLEN
--------------------WARD
--------------------MARTIN
--------------------TURNER
--------------------JAMES
----------CLARK
--------------------MILLER

Set Operators

Set Operators are used to compound multiple queries and return the combined result set.

The available SET Operators are: UNION, UNION ALL, MINUS, and INTERSECT.

UNION Example

The following statement combines the results with the UNION operator, which eliminates duplicate selected rows. This statement shows that you must match datatype (using the TO_DATE and TO_NUMBER functions) when columns do not exist in one or the other table:

SELECT part, partnum, to_date(null) date_in
    FROM orders_list1
UNION
SELECT part, to_number(null), date_in
    FROM orders_list2;

UNION ALL Example

The following statement combines the results with the UNION ALL operator, which does not eliminate duplicate selected rows:

SELECT part 
    FROM orders_list1 
UNION ALL 
SELECT part 
    FROM orders_list2;

PART 
---------- 
SPARKPLUG 
FUEL PUMP 
FUEL PUMP 
TAILPIPE 
CRANKSHAFT 
TAILPIPE 
TAILPIPE 

MINUS Example

The following statement combines results with the MINUS operator, which returns only rows returned by the first query but not by the second:

SELECT part FROM orders_list1 
MINUS 
SELECT part FROM orders_list2;

PART 
---------- 
SPARKPLUG 
FUEL PUMP 

INTERSECT Example

The following statement combines the results with the INTERSECT operator, which returns only those rows returned by both queries:

SELECT part FROM orders_list1 
INTERSECT 
SELECT part FROM orders_list2;

PART 
---------- 
TAILPIPE 

Orace Materialized Views

Materialized views facilitate you to execute a SQl query and save its results either locally or in a remote database.

After the Materialized view is initially populated it can be refreshed later on to store fresh results into the underlying table.

Materialized Views are mainly used for two reasons:

1) Replication of data to separate remote databases.

2) For improving the performance of queries by computing and storing the results of complex aggregations of data.

In situations where complex sql queries are performed mainly in reporting or datawarehouse environments Materialized Views are really helpful in improving performance.

Whenever a SQL query is executed oracle database has to lot of work in order to retrieve the data. For example it may have to do sorting (Memory or Disk Based), it has to decide the execution plan for the sql statement (Do a full tables scan or a indexed based scan) and lots of other stuff before retrieving the requested data.

These type of queries if performed repeatedly will affect the performance of the server in a negative way.

But with Materialized Views the performance can be improved significantly, because when a materialized view is created it stores all the data along with the execution plans. So even if the query is executed repeatedly it will not eat up all the resources as it did earlier.

The Materialized view can be created on the base of tables, views or other materialized views.

When a Materialized View is created, oracle also create a table with the same name as that of the materialized view and also creates a materialized view object.

Types of Materialized Views

There are following types of Materialized views.

• Read-Only Materialized Views.
• Updatable Materialized Views.

Read-Only Materialized Views

You can make a materialized view read-only during creation by omitting the FOR UPDATE clause or disabling the equivalent option in the Advanced Replication interface in Oracle Enterprise Manager. Read-only materialized views use many of the same mechanisms as updatable materialized views, except that they do not need to belong to a materialized view group.

In addition, using read-only materialized views eliminates the possibility of a materialized view introducing data conflicts at the master site or master materialized view site, although this convenience means that updates cannot be made at the remote materialized view site. The following is an example of a read-only materialized view:

CREATE MATERIALIZED VIEW hr.employees AS
  SELECT * FROM hr.employees@orc1.example.com;

Updatable Materialized Views

You can make a materialized view updatable during creation by including the FOR UPDATE clause or enabling the equivalent option in the Advanced Replication interface in Oracle Enterprise Manager. For changes made to an updatable materialized view to be pushed back to the master during refresh, the updatable materialized view must belong to a materialized view group.

Updatable materialized views enable you to decrease the load on master sites because users can make changes to the data at the materialized view site. The following is an example of an updatable materialized view:

CREATE MATERIALIZED VIEW hr.departments FOR UPDATE AS
  SELECT * FROM hr.departments@orc1.example.com;

Materialized Views Refresh Types

Refresh is the operation that synchronizes the content of the materialized view with the data in base tables. Following are the types of refresh:

1. Complete - Refreshable Materialzed Views.
2. Fast-refresh Materialized Views.

COMPLETE REFERSH MATERIALIZED VIEWS

Complete: Involves truncating existing data & re-inserting all the data based on the detail tables by re-executing the query definition from the create command at periodic intervals.

CREATE MATERIALIZED VIEW hr.employees
  REFRESH COMPLETE
  NEXT SYSDATE + 1/1440 
  AS SELECT * FROM hr.employees@orc1.example.com;

So above we have created a materialized view based on the sales table, which will completely refresh itself after every one minute.

FAST REFRESH MATERIALIZED VIEW

Fast refreshable materialized views work a little bit differently. When a fast refresh materialized view is created it initially populates the materialized view table with data from the base or master table.

After the initial data is populated only modified data is applied to the materialized view table after each refresh, Instead of a complete refresh like that in Complete refresh materialized views.

Fast refresh using materialized view logs.

Fast refresh using ROWID range.

Materialized View Logs

Materialized view logs are required if you want to use fast refresh. Materialized view logs are defined using a CREATE MATERIALIZED VIEW LOG statement on the base table that is to be changed.

Now lets create a materialized view log on the master table.

CREATE MATERIALIZED VIEW LOG ON hr.employees@orc1.example.com WITH 
PRIMARY KEY;

If your base table does not have a primary key then a following error will occur.

ORA-12014: table does not contain primary key constraint

In that case make materialized view log based on ROWID.

CREATE MATERIALIZED VIEW LOG ON hr.employees@orc1.example.com WITH 
ROWID;

We are creating a materialized view based on primary key refresh.

 CREATE MATERIALIZED VIEW hr.employees
  REFRESH WITH PRIMARY KEY
  NEXT SYSDATE + 3/1440 
  AS SELECT * FROM hr.employees@orc1.example.com;

Materialized Views Refresh Modes

Manual Refresh: Can be performed using DBMS_MVIEW package. (REFRESH, REFRESH_DEPENDENT, REFRESH_ALL_VIEWS)

Automatic Refresh: Can be performed in two ways:

ON COMMIT – Materialized view gets updated whenever changes to one of these tables are committed.

ON DEMMAND – At Specified Time – Refresh is scheduled to occur for specified time by using START WITH & NEXT clauses. For such refreshes, instance must initiate a process with JOB_QUEUE_PROCESSES.

Dropping Materialized Views

DROP MATERIALIZED VIEW hr.employees;

Oracle DATABASE LINK

Use the CREATE DATABASE LINK statement to create a database link. A database link is a schema object in one database that enables you to access objects on another database. The other database need not be an Oracle Database system. However, to access non-Oracle systems you must use Oracle Heterogeneous Services.

Once you have created a database link, you can use it to refer to tables and views on the other database. In SQL statements, you can refer to a table or view on the other database by appending @dblink to the table or view name. You can query a table or view on the other database with the SELECT statement. You can also access remote tables and views using any INSERT, UPDATE, DELETE, or LOCK TABLE statement.

Prerequisites

• To create a private database link, you must have the CREATE DATABASE LINK system privilege.
• To create a public database link, you must have the CREATE PUBLIC DATABASE LINK system privilege.
• You must have the CREATE SESSION system privilege on the remote Oracle database.
• You must be able to connect to the remote database.
• Oracle Net must be installed on both the local and remote Oracle databases.

Database Link Types

When you create a database link, you must decide who will have access to it. The following sections describe how to create the basic types of links.

Private Database Links

CREATE DATABASE LINK mylink
USING 'tns_service_name';

Above private link uses the userid/password of the connected user. So if scott uses the link in a query, the link establishes a connection to the remote database as scott.

CREATE DATABASE LINK mylink
CONNECT TO remote_username IDENTIFIED BY mypassword 
USING 'tns_service_name';

Above private fixed user link to the database with service name 'tns_service_name'. The link connects to the remote database with the remote_username / mypassword regardless of the connected user.

Public Database Links

CREATE PUBLIC DATABASE LINK mylink
USING 'tns_service_name';

Above public link to the remote database. The link uses the userid/password of the connected user. So if scott uses the link in a query, the link establishes a connection to the remote database as scott.

CREATE PUBLIC DATABASE LINK mylink
CONNECT TO remote_username IDENTIFIED BY mypassword 
USING 'tns_service_name';

Above public fixed user link. The link connects to the remote database with the remote_username / mypassword .

How to Use

Once Database Link is created you can access remote database objects by using @dblink.

Examples

SELECT * FROM employees@mylink;

UPDATE employees@mylink
   SET salary=salary*1.1
   WHERE last_name = 'Baer';

You can create a synonym to hide the fact that a particular table is on the remote database. The following statement causes all future references to emp_table to access the employees table owned by hr on the remote database:

CREATE SYNONYM emp_table 
   FOR oe.employees@mylink;

SELECT * FROM emp_table;

Drop Database Link

Use the DROP DATABASE LINK statement to remove a database link from the database.

A private database link must be in your own schema.

DROP DATABASE LINK mylink;

To drop a PUBLIC database link, you must have the DROP PUBLIC DATABASE LINK system privilege.

DROP PUBLIC DATABASE LINK mylink;