Oracle SQL grouping based on value in blob field

Question

This may not be possible

I have a table with a blob which contains XML and I want to get a count based on the content of the blob is there a way to do it in one query instead of doing

select count(*)
from MyTable
where dbms_lob.instr(bitstream, utl_raw.CAST_TO_RAW('ObjA'), 1, 1) > 0

select count(*)
from MyTable
where dbms_lob.instr(bitstream, utl_raw.CAST_TO_RAW('ObjB'), 1, 1) > 0

select count(*)
from MyTable
where dbms_lob.instr(bitstream, utl_raw.CAST_TO_RAW('ObjC'), 1, 1) > 0

select count(*)
from MyTable
where dbms_lob.instr(bitstream, utl_raw.CAST_TO_RAW('ObjD'), 1, 1) > 0

select count(*)
from MyTable
where dbms_lob.instr(bitstream, utl_raw.CAST_TO_RAW('ObjE'), 1, 1) > 0

Answer 1

Would something like this do?

Store values you're looking for into a CTE, and then join that CTE to your table.

WITH
   temp (obj)
   AS
      (SELECT *
         FROM TABLE (sys.odcivarchar2list ('ObjA',
                                           'ObjB',
                                           'ObjC',
                                           'ObjD',
                                           'ObjE')))
  SELECT t.obj, COUNT (*)
    FROM mytable e
         JOIN temp t
            ON DBMS_LOB.INSTR (bitstream,
                               UTL_RAW.cast_to_raw (t.obj),
                               1,
                               1) > 0
GROUP BY t.obj;