You Asked

what is wrong with this where clause?

create or replace function fReserved(sString IN VARCHAR2) RETURN BOOLEAN IS
iCnt INTEGER; -- this is a count of the number of rows returned. 
BEGIN
 SELECT count(*)
 INTO iCnt
 FROM my_reserved_words
 WHERE UPPER(sString) LIKE '''%'||'''||keyword ||'''||'%''';
 -- if iCnt > 0 then we matched a reserved word in the string
 -- return true, otherwise return false.
 IF iCnt > 0 THEN
  RETURN TRUE;
 ELSE
  RETURN FALSE;
 END IF;
END fReserved;

I've created a function to test for keywords in a string. the function seems simple enough. Pass a string, if a keyword exists in the string, return true, else return false. I'm getting the keywords from v$reserved_words (my test case is using my_reserved_words). The issue seems to be in my where clause.

now, we would assume using LIKE '''%'||keyword||'%''' would match a given instance of a keyword in the string. I have tried using '''%'||keyword||'%''' and '''%'||'''||keyword ||'''||'%'''. if i check

select '''%'||'SELECT'||'%''' from dual;

I get '%SELECT%' that I assume would work.

with LiveSQL Test Case:

https://livesql.oracle.com/apex/livesql/s/e8qqdnepcpb91l9bvdphgbhso

and Connor said...

Hi Rob,

OK, a few bits and pieces here.

1) You've gone overboard on quotes :-) The string:

'''%'||'''||keyword ||'''||'%'''

actually returns a *static* string, ie

SQL> select '''%'||'''||keyword ||'''||'%''' from my_reserved_words;

'''%'||'''||KEYWOR
------------------
'%'||keyword ||'%'
'%'||keyword ||'%'
'%'||keyword ||'%'
'%'||keyword ||'%'

2) NULLS

So I've corrected that to be just:

WHERE UPPER(sString) LIKE '%'||keyword||'%';

but that still might cause you some grief, because of nulls, eg

SQL> create table my_reserved_words as select keyword from v$reserved_words;

Table created.

SQL> select * from my_reserved_words where keyword is null;

KEYWORD
------------------------------------------------------------------------------



2 rows selected.

So this means that *every* string you pass in will get a "hit" because it will try to match with:

'%' || null || '%'

which is an always true match.

3) DATA QUALITY

"BLAH" returned a match for me...which I thought "that's odd" until I look at the data in v$reserved_words.

SQL> select * from my_reserved_words where length(keyword) = 1;

KEYWORD
------------------------------------------------------------------
+
}
M
E
&
$
^
|
/
.
,
<
A
P
)
@
...
...
...

Yup.... "A" is a reserved word ! So BLAH "contains" a reserved word as does any other word that contains an A :-) or E for that matter :-)

So you might want to make some amendments to your algorithm, eg, set a minimum length etc.

Hope this helps.

Rating

(1 rating)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Good job

Rob, July 08, 2017 - 7:57 pm UTC

-- I really don't want to make a static list of reserved
-- words, and would prefer to not to have to build my 
-- own table of reserved words. Now, this is working
-- but I see the problem where the NOT IN part of the 
-- cursor would have to be maintained and I know there
-- is a column LENGTH in v$reserved_words. (why's that) 
-- did the engineer who defined the v$reserved_words view
-- know that LENGTH is a keyword? So, by prepending and
-- appending a ' ' to each side of the string and keyword
-- I was able to get the function to work. Moved to a 
-- cursor for loop for verbose debugging. I'll try
-- moving back to select count(*) into ... but I can see
-- already, where this may become a maintance nightmare. 
create or replace function fReserved(sString IN VARCHAR2) RETURN BOOLEAN IS
 CURSOR key_cur IS
 SELECT keyword
 FROM my_reserved_words
 WHERE keyword NOT IN ('IS','TEST', 'OF')
   AND length(keyword) > 1;
 BEGIN
  IF sString IS NULL THEN
 RETURN FALSE;
  END IF;
  FOR key_rec IN key_cur
  LOOP
   IF instr(' ' || upper(sString) || ' ', ' ' || key_rec.keyword || ' ') > 0 THEN
    sys.dbms_output.put_line(' ' || key_rec.keyword || ' ');
    RETURN TRUE;
   END IF;
  END LOOP;
  RETURN FALSE;
 END;
 /

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Good job

More to Explore

PL/SQL demos

PL/SQL docs

Issue using like in a where clause when the wild cards are applied to a column.

Breadcrumb

Question and Answer

You Asked

and Connor said...

Rating

Comments

Good job

More to Explore

PL/SQL demos

PL/SQL docs