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
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
Are you planning to use PLSQL to do so ?
ASKER
That's the intent..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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.
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
http://www.rocket99.com/techref/oracle8610.html
This is another website which is useful in your case
http://searchoracle.techtarget.com/news/article/0,289142,sid41_gci969305,00.html
http://searchoracle.techtarget.com/news/article/0,289142,sid41_gci969305,00.html