This week's book giveaways are in the Refactoring and Agile forums.
We're giving away four copies each of Re-engineering Legacy Software and Docker in Action and have the authors on-line!
See this thread and this one for details.
Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Error in Oracle like clause

 
Preethinj Math
Greenhorn
Posts: 10
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi,

Need help!

Here the like clause is not working,since the sql is itself in sinle quotes.
I don't know how to make the like clause work.
The error it is giving me is:

ORA-00920: invalid relational operator
ORA-06512: at "TABCOUNT", line 10
ORA-06512: at line 2


The code is
CREATE OR REPLACE FUNCTION tabcount (
tab IN VARCHAR2, field IN VARCHAR2 ,whr IN VARCHAR2)

RETURN INTEGER
IS
retval INTEGER;
BEGIN
DBMS_OUTPUT.PUT_LINE ('whr' ||whr);
DBMS_OUTPUT.PUT_LINE ('field1' ||field);
EXECUTE IMMEDIATE
' SELECT COUNT(*) FROM ' || tab ||
' WHERE ' || field || 'like ''%'||whr ||'%'''
INTO retval;
DBMS_OUTPUT.PUT_LINE ('countis!' ||retVal);

RETURN retval;
END tabcount;
/

Looking for some answer.

Thanks
 
Shital Supase
Greenhorn
Posts: 17
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
shital580@rediffmail.com

Hello preethinj,

the bug in ur query is the statement whatever u have written as || 'like ''%'||whr ||'%'''

should b

|| ' like ''%'||whr ||'%'''

u have skipped the blank space between the field values & like keyword
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic