Oracle tips

A sneeky tool in oracle SQL developer to help u visually generate sql query is here


Printing a tree hierarchy using SQL connect by:

select
lpad('-', (level)*2,' ' ) || HIERARCHY_cat_short_name as padded_name, HIERARCHY_cat_id, HIERARCHY_cat_parent_id, level
from HIERARCHY_category
connect by prior HIERARCHY_cat_id =HIERARCHY_cat_parent_id
start with
HIERARCHY_cat_id in (select HIERARCHY_cat_id from HIERARCHY_category where
HIERARCHY_cat_parent_id is null)

quick reference here :http://philip.greenspun.com/sql/trees.html

Oracle CONTAINS function for XML based search
select count(*) from ATABLE where contains(xml_COLUMN, 'HASPATH(//filed="12568") ') >0

Extractvalue from xml column
SELECT distinct EXTRACTVALUE( xmltype(column_meta_xml), '//field',    'xmlns:ora="http://xmlns.oracle.com/xdb"') from atable