Let's say you have a table "PICTURE" has a blob column "PIC_BLOB" which contains some image and you want to extract the meta data of the image, here is the query,
Don't ask me any more questions. I spilt my hair to get it working.
PS: If you are asking me why did I do it. Because Photos taken with iPhone are rotated randomly and learned that, rotation angle is stored in the meta data
select VALUE(xml_type).getstringval()
FROM table(
select ORDSYS.ORDImage.getMetadata(
ordsys.ordimage(ordsys.ordsource( PIC_BLOB, null, null, null, null, 1),null, null, null, null, null, null, null)
)
from PICTURE WHERE ID=1
)xml_type
Your Output will be something like this.
<ordImageAttributes xmlns="http://xmlns.oracle.com/ord/meta/ordimage" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://xmlns.oracle.com/ord/meta/ordimage http://xmlns.oracle.com/ord/meta/ordimage"></ordImageAttributes>
<iptcMetadata xmlns="http://xmlns.oracle.com/ord/meta/iptc" xsi:schemaLocation="http://xmlns.oracle.com/ord/meta/iptc http://xmlns.oracle.com/ord/meta/iptc" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"/>
<exifMetadata xmlns="http://xmlns.oracle.com/ord/meta/exif" xsi:schemaLocation="http://xmlns.oracle.com/ord/meta/exif http://xmlns.oracle.com/ord/meta/exif" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<TiffIfd>
<Orientation tag="274">top left</Orientation> -- This is what I am interested in
</TiffIfd>
<ExifIfd tag="34665">
<ColorSpace tag="40961">sRGB</ColorSpace>
<PixelXDimension tag="40962">3264</PixelXDimension>
<PixelYDimension tag="40963">2448</PixelYDimension>
</ExifIfd>
</exifMetadata>
Tip:If you are just interested in Orientation tag then apply Regular Expression like this
replace
VALUE(xml_type).getstringval()
with
max(REGEXP_SUBSTR(
VALUE(xml_type).getStringval(),'<Orientation (.*)>(.*)</Orientation>',1,1,'i',2))
