0

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
);
halfer
  • 19,824
  • 17
  • 99
  • 186
Chamith De Costa
  • 21
  • 5
  • 2
    Possible duplicate of [java.sql.SQLSyntaxErrorException: ORA-00911: invalid character](http://stackoverflow.com/questions/38955102/java-sql-sqlsyntaxerrorexception-ora-00911-invalid-character) – PeeHaa Oct 28 '16 at 11:02
  • 3
    Why the quotes around the table names? – Aleksej Oct 28 '16 at 11:02
  • Have a look into this post - http://stackoverflow.com/questions/27987882/how-can-i-solve-ora-00911-invalid-character – Chirag Parmar Oct 28 '16 at 11:02
  • 2
    It seems you're trying to use MySQL syntax (the back quotes around table and field names) with Oracle and that is the source of the error. – axiac Oct 28 '16 at 11:04
  • 1
    The dreaded backticks are illegal in standard SQL and Oracle. –  Oct 28 '16 at 11:04
  • @a_horse_with_no_name - they're great in Unix shells though! {:-) –  Oct 28 '16 at 18:53

2 Answers2

1

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
                    );
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Because it's clearly a dupe of a dupe and as such would be easy to close as a dupe instead of dumping just yet another ---answer--- codedump without any explanation. But I guess it's about quantity... – PeeHaa Oct 29 '16 at 17:51
  • @PeeHaa . . . There should be an explanation, if that is indeed the reason for the downvote. However, I think it more appropriate to close the question as a duplicate. – Gordon Linoff Oct 29 '16 at 22:51
0

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.

NOW I KNOW
  • 1
  • 1