Term: EXTRACT

Definition:
There are two EXTRACT functions available in Oracle. They both work with inputs of different types and return output of different types.

The Oracle PL/SQL EXTRACT(date time) function extracts a value from a date or interval value.
The Oracle PL/SQL EXTRACT(XML) function is used to select a node (or set of nodes) along with its leaf nodes.

EXTRACT(date time)

The Oracle EXTRACT(Date Time) function splits a datetime or interval expression and returns the value of the specified datetime field (YEAR, MONTH, DAY from DATE and TIMEZONE_HOUR, TIMEZONE_MINUTE from TIMEZONE). It is useful while working with datetime values in tables with large volumes of data.

Example Syntax:

EXTRACT (

{ YEAR | MONTH | DAY | HOUR | MINUTE | SECOND }

| { TIMEZONE_HOUR | TIMEZONE_MINUTE }

| { TIMEZONE_REGION | TIMEZONE_ABBR }

FROM { date_value | interval_value } )

Example Usage:

SELECT EXTRACT(YEAR FROM DATE '2010-01-12') FROM DUAL;



EXTRACT(YEARFROMDATE'2010-01-12')

---------------------------------

2010

SELECT EXTRACT(DAY FROM DATE '2003-08-22') FROM DUAL;



EXTRACT(DAYFROMDATE'2010-01-12')

---------------------------------

12

The SQL query below returns the month that was current (in effect) 40 days ago:

SELECT EXTRACT(MONTH FROM SYSDATE-40) FROM DUAL;

EXTRACT(xml)

The EXTRACT(XML) function is used to select a node (or set of nodes) along with its leaf nodes. It takes two parameters, object_value and XPath, and returns XMLType output. The XPath parameter can be an absolute or relative type.

Example Syntax:

EXTRACT(xml-type-instance, 'xpath')

EXTRACT(xml-type-instance, 'xpath', 'namespace')

Example Usage:

SQL> SELECT EXTRACT(OBJECT_VALUE, '/Orders/MailAddressTo')

FROM ORDERS_XML;



EXTRACT(OBJECT_VALUE,'/ORDERS/MAILADDRESSTO')

------------------------------------------------------------------------------

<MailAddressTo id="PA"><Company>Google</Company>

<Country>United States</Country>

<City>NewYork</City><State>NY</State><Zipcode>12345</Zipcode>

</MailAddressTo>



SQL> SELECT EXTRACT(OBJECT_VALUE,  './/Company')

FROM ORDERS_XML;



EXTRACT(OBJECT_VALUE,'.//COMPANY')

---------------------------------------------------------

<Company>Google</Company><Company>PSOUG</Company>