Link to home
Start Free TrialLog in
Avatar of AWarrenM
AWarrenM

asked on

Reading / Using BLOB Columns

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
Avatar of Dr_Billy
Dr_Billy
Flag of Canada image

Are you planning to use PLSQL to do so ?
Avatar of AWarrenM
AWarrenM

ASKER

That's the intent..
ASKER CERTIFIED SOLUTION
Avatar of Dr_Billy
Dr_Billy
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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