Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle PL/SQL EXTRACT Function      [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

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.

  1. The Oracle PL/SQL EXTRACT(date time) function extracts a value from a date or interval value.
  2. 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>



Related Links:

Related Code Snippets:
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 50 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?