We help IT Professionals succeed at work.

Reading / Using BLOB Columns

AWarrenM
AWarrenM asked
on
1,804 Views
Last Modified: 2013-12-18
Hi All:
So, I've never really worked with Oracle BLOBs and I've come into a situation where the research I've done hasn't really helped. So I figured I'd ask here as it would save me time.

I have a table with a BLOB column. Inside the blob is XML. I'd like the best ways to look into the blob and extract a piece of the XML within an Oracle function.

So for example sake, lets say the name of the table is table_blob and it has two columns:
1. id, number (pri)
2. zBlob, BLOB

I'd like to have a function that takes in a string to search for in the BLOB, which has XML, an return the corresponding value that matches my search string. So, the XML structure is something like this:
<xml>
...
<object id="theValueIWant">
<name>Search Name</name>
</object>
</xml>

So the function would tak in "Search Name" and return "theValueIWant" if found, or null otherwise.

I have some ideas about how to do this, but it involves a function that reads the BLOB data using things like instr.. which I don't think will be very efficient.

Thoughts?

Thanks,
Anthony
Comment
Watch Question

Commented:
Are you planning to use PLSQL to do so ?

Author

Commented:
That's the intent..
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
I know about the XML parser.. the issue is that it's in a BLOB and not a CLOB which your first link provides. CLOBs, from my knowledge, are different to work with than BLOBs.

So essentially, i need to know how to get the information out of the BLOB, maybe create that info as an XML object, and then parse it.. efficiently.

Commented:
Have a look at this url , it helps you understand how to manipulate data included in a BLOB columns

http://www.rocket99.com/techref/oracle8610.html

Commented:
This is another website which is useful in your case
http://searchoracle.techtarget.com/news/article/0,289142,sid41_gci969305,00.html
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.