1

I have a table with a BLOB field containing SOAP-serialised .NET objects (XML).

I want to search for records representing objects with specific values against known properties. I have a working .NET client that pulls back the objects and deserialises them one at a time to check the properties; this is user-friendly but creates a huge amount of network traffic and is very slow.

Now I would like to implement a server-side search by sending a regular expression to a stored procedure that will search the text inside the BLOB. Is this possible?

I have tried casting the column to varchar2 using utl_raw.cast_to_varchar2, but the length of the text is too long (in some cases 100KB).

dbms_lob.inst allows me to search the text field for a substring, but with such a complex XML structure I would like the additional flexibility offered by regular expressions.

Community
  • 1
  • 1
Chris B
  • 709
  • 2
  • 14
  • 32
  • Why are you storing XML as binary data? `XML` (or at least `CLOB`) would have been a much better choice then you could use XQuery or XPath to search for values. –  Sep 10 '14 at 10:31
  • I agree completely, but this is an old schema in use by a Production application. – Chris B Sep 10 '14 at 11:03
  • XMLTYPE constructor accepts a BLOB, so you could turn the BLOB into an XMLTYPE and use XMLTABLE or XMLQUERY to search for specific values of specific elements in the XML. That way you wouldn't need to write regular expressions but let the database parse and extract values from the XML. – Kim Berg Hansen Sep 10 '14 at 11:18
  • Thanks Kim. Going to XMLTYPE and then CLOB I can use `WHERE REGEXP_LIKE(XMLTYPE(XML_COL,0).getClobval(),'SEARCH_TERM')`. However, I don't want to rely on the fact that this column contains XML - it works most of the time but some records throw the error "ORA-31011 XML parsing failed ORA-19202 Error occurred in XML processing LPX-00217 invalid character 31 (U+100F)" when creating an XMLTYPE. Any other options that just allow me to search a BLOB column as text with a regex? – Chris B Sep 12 '14 at 14:38

0 Answers0