Re: ORA-00920 invalid relational operator

olivier.duhart_at_gmail.com wrote:
>
> I have a very strange problem, the following query
> raise an exception when executed from Java (JDBC) but is successfully
> executed within TOAD.
> SELECT e.region, e.subregion, e.holding, e.id, e.deleted,
> e.id_mergefile id_frf, t.sizeclass, t.subdivision
> FROM r1_typology t, r1_farmreturn_entry e
> WHERE id_entry = id AND id_mergefile=1392 AND id_basefile IS NOT NULL
> AND t.sgmversion = 1996 AND t.typologyversion = 1996393 AND
> e.region||'.'||e.subregion||'.'||e.holding LIKE '%.24.%' ORDER BY
> e.region, e.subregion, e.holding

You actually have a more serious problem. Not using bind variables. This will lead to fragmentation of the SQL shared pool as you're SQL is not sharable. That in turn will lead to memory errors (insufficient contiguous free memory). Not to mention the heavy impact on performance caused by hard parses.

Use bind variables. It is non-negotiable. Mandatory. Unless you want to write crap Oracle applications like Cisco and others.

> I guess the problem come from the
> [e.region||'.'||e.subregion||'.'||e.holding LIKE '%.24.%'] part but I
> need it and I don't know how to correct it. Could you help me ?

Make sure that the quotes are correct. When assigning a string to a variable (like a SQL statement), you need two quotes to create a single quote in the string itself.

E.g. (pseudo language)
string = 'the words ''bind variables'' are in single quotes'; print string
> the words 'bind variables' are in single quotes

--
Billy