René Nyffenegger's collection of things on the web
René Nyffenegger on Oracle - Most wanted - Feedback

extract [Oracle SQL]

extract with dates

Which year did we have 300 days ago?
select extract (year from sysdate-300) from dual;
What Month did we have 10 days ago?
select extract(month from sysdate-10) from dual;
What day is today?
select extract(day from sysdate) from dual;

extract with XML

extract(xml-type-instance, 'xpath-expression')
extract(xml-type-instance, 'xpath-expression', 'name-space')
extract is similar to existsNode()

An example

create table some_things of xmltype;
Three xml documents are inserted into the table.
insert into some_things values (xmltype('

insert into some_things values (xmltype('

insert into some_things values (xmltype('
    <cities><item>New York</item><item>Tokyo</item><item>Zurich</item></cities>
Selecting the 2nd animal in each document:
select extract(object_value,'/things/animals/item[position()=2]') "2nd Animal" from some_things;
2nd Animal

The 2nd inserted document doesn't have an animal, so null is returned instead.
Similar to existsNode()