CODEWITHSUNDEEP
sqldatabaseoracle-database
Chamith De Costa
Chamith De Costa

Reputation: 21

How to debug "ORA-00911: invalid character" in this SQL Query?

I tried to run the following query on Toad for Oracle. And it gives the error "ORA-00911: invalid character". Can someone please help me to figure out the issue?

DELETE FROM `CC_AUDIT_TRAIL`
WHERE SEQ_NO NOT IN (
  SELECT SEQ_NO
  FROM (
    SELECT SEQ_NO
    FROM `CC_AUDIT_TRAIL`
    ORDER BY SEQ_NO DESC
    LIMIT 1000
  ) foo
);

Upvotes: 0

Views: 1267

Answers (2)

NOW I KNOW
NOW I KNOW

Reputation: 1

Couple of observations:

  1. To Aleksej's point, quotes around the table name are not necessary
  2. You are sending direct query to Oracle, so semi-colon (;) is probably not needed
  3. If above two point don't fix your issue, try retyping your code in Notepad editor and use it. This will eliminate any whitespace inconsistencies that may be present in your code and not visible to eyes (This is especially the case if you copied your code from another source)

P.S. Let the community know which one solved it for future reference.

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1269763

You have written a MySQL query. Perhaps you want this:

DELETE FROM CC_AUDIT_TRAIL
WHERE SEQ_NO NOT IN (SELECT SEQ_NO
                     FROM (SELECT SEQ_NO
                           FROM CC_AUDIT_TRAIL
                           ORDER BY SEQ_NO DESC
                          ) foo
                     WHERE rownum <= 1000
                    );

Upvotes: 1

Related Questions