CODEWITHSUNDEEP
sqloracle-databaseplsqlsql-update
FIFA oneterahertz
FIFA oneterahertz

Reputation: 720

ORA 00933 : SQL Command not properly ended with IN operator in oracle

I am trying to execute a query which is as follows

update L2l_Lov_Master set QUERY_STRING = 'com.jmr.profitto.los.app.retail.model.L2LLdmLeadinformation entity WHERE entity.status IN ('NEW','REJECTED','REJECTED_BY_CUSTOMER')' where LOV_ID = 'customerLov-leadcode';

I am using oracle DB and i am getting "ORA 00933: SQL command not properly ended". Need to know where it went wrong

Upvotes: 0

Views: 92

Answers (2)

Paul C.
Paul C.

Reputation: 177

As far as I can tell, you problem is related with the quotes --> to be more explicit, you have a problem when your IN clause starts because you close your first quote from query string

query_string = 'com.jmr.profitto.los.app.retail.model.L2LLdmLeadinformation entity WHERE entity.status IN ('

I think you need to encapsulate the quotes from the IN clause so that they get ignored by the first query.

UPDATE l2l_lov_master
SET
    query_string = 'com.jmr.profitto.los.app.retail.model.L2LLdmLeadinformation entity WHERE entity.status IN (''
new '','' rejected '','' rejected_by_customer '')'
WHERE
    lov_id = 'customerLov-leadcode';

Upvotes: 1

Littlefoot
Littlefoot

Reputation: 142788

One can easily get lost with too many single quotes. Therefore, use the q-quoting mechanism, like in the following example:

SQL> CREATE TABLE l2l_lov_master
  2  AS
  3     SELECT 'customerLov-leadcode' lov_id, LPAD ('x', 500, 'x') query_string
  4       FROM DUAL;

Table created.

See line #3 and the end of line #4, i.e. q'[blabla]'.

SQL> UPDATE L2l_Lov_Master
  2     SET QUERY_STRING =
  3            q'[com.jmr.profitto.los.app.retail.model.L2LLdmLeadinformation entity
  4   WHERE entity.status IN ('NEW','REJECTED','REJECTED_BY_CUSTOMER')]'
  5   WHERE LOV_ID = 'customerLov-leadcode';

1 row updated.

SQL> SELECT * FROM l2l_lov_master;

LOV_ID
--------------------
QUERY_STRING
--------------------------------------------------------------------------------
customerLov-leadcode
com.jmr.profitto.los.app.retail.model.L2LLdmLeadinformation entity
 WHERE entity.status IN ('NEW','REJECTED','REJECTED_BY_CUSTOMER')


SQL>

Upvotes: 0

Related Questions