I am trying to create a single search for multiple text strings within a stored procedure. The following code works for test1, but when I try both test1 & test2 I get no results; no error either.
The line for test2 is remarked-out now.
SELECT name, line, text
FROM user_source
WHERE type = 'PROCEDURE'
AND UPPER(text) LIKE UPPER('%test1%')
-- AND TEXT LIKE UPPER('%test2%')
ORDER BY NAME;
What am I doing incorrectly?
Use OR because if you use an AND you will only come out postive if you find both strings. Maybe you miss the upper(text) also
SELECT name, line, text
FROM user_source
WHERE type = 'PROCEDURE'
AND UPPER(text) LIKE UPPER('%test1%')
OR upper(TEXT) LIKE UPPER('%test2%')
Perhaps there isn't a line with both "text1" and "text2" strings. You would have to use an OR in that case.
SELECT NAME, LINE, TEXT
FROM USER_SOURCE
WHERE TYPE = 'PROCEDURE'
AND (UPPER(TEXT) LIKE UPPER('%TEST1%')
OR UPPER(TEXT) LIKE UPPER('%TEST2%'))
You're missing UPPER(TEXT) on the last line. Should be:
SELECT name, line, text
FROM user_source
WHERE type = 'PROCEDURE'
AND UPPER(text) LIKE UPPER('%test1%')
AND UPPER(TEXT) LIKE UPPER('%test2%')
