This entry falls under the "Something New I Learned Today" heading.

I needed to obtain a list of active users who had a particular string within a CLOB field in an auxillary table associated back to their account via a foreign key. You can't do "LIKE" statements against a CLOB, and we don't have Intermedia operational on the boxes where the query needed to run so I couldn't run a "contains" against an index. I say "boxes" because we have two distinct user databases -- one running Oracle 9i and another on 8i. So whatever solution I came up with had to play in both environments. Our DBA mentioned there was a package resident in both called DBMS_LOB which exposed methods for working with LOBs. I saw that one of these was InStr() and hoped that it would allow me to specify the substring I was concerned with, and perhaps return the index where the substring started within the CLOB.

Sometimes there IS reason in the universe. I was able to do this using the following PL/SQL (names have been changed to protect the innocent):

select u.user_uid, u.login_text, p.profile_name_text
  from profile p, user u
 where u.acct_closed_ind = 0
   and u.user_uid = p.user_uid
   and (
dbms_lob.instr(clob_field, 'first string to find',1,1) > 0 or
        dbms_lob.instr(clob_field, 'second string to find',1,1) > 0
);

Obviously you could stick as many substrings in there as you want using additional ORs. The DBMS_LOB package has all kinds of interesting functions for dealing LOBs. We could have even run a script to update/remove the offending strings automatically. But we opted for safety and will instead have the Customer Support team call the users so they can be educated.