Hi
I have the following sql to search thru a BLOB field which has all the program code. The search is not working correctly and it does not return all the rows with the search string ( in this case its 'declare').
Can someone identify what could be wrong with the logic or suggest the correct way to search a string value in a BLOB field ? Thanks for your help.
WITH Srch
AS ( SELECT Str, SUBSTR (MAX (HexStr), 3) Srch_Str
FROM ( SELECT RNum,
Str,
Hexcode,
SYS_CONNECT_BY_PATH (HexCode, '00') HexStr
FROM (SELECT RNum,
Str,
CHR (
FLOOR (MOD (Code / 16, 16))
+ DECODE (
FLOOR (
FLOOR (MOD (Code / 16, 16))
/ 10),
0, 48,
55))
|| CHR (
MOD (Code, 16)
+ DECODE (FLOOR (MOD (Code, 16) / 10),
0, 48,
55))
HexCode
FROM (SELECT RNum,
Str,
ASCII (SUBSTR (Str, RNum, 1)) Code
FROM ( SELECT ROWNUM RNum
FROM DUAL
CONNECT BY LEVEL <=
LENGTH ('declare')),
(SELECT 'declare' Str FROM DUAL
UNION
SELECT UPPER ('declare') FROM DUAL
UNION
SELECT LOWER ('declare') FROM DUAL
UNION
SELECT INITCAP ('declare') FROM DUAL)))
START WITH RNum = 1
CONNECT BY PRIOR RNum = RNum - 1 AND PRIOR Str = Str)
GROUP BY Str)
SELECT Str, P.*
FROM PSPCMPROG P, Srch
WHERE DBMS_LOB.INSTR (PROGTXT, Srch_Str) > 0;