英文:
Ora-00911 Invalid Character in Oracle Query
问题
我刚刚尝试了以下查询：
SELECT EXAM_NUMBER, DATE_EXAM, DATE_UNIV
FROM UNIVERSITY.C_CALENDAR cc2
WHERE EXAM_NUMBER = 'L'
AND LAST_FILE = 'OK'
AND MATTER = 'MATH'
AND STATE_CODE NOT IN ('FC')
AND TO_CHAR(DATE_EXAM, 'YYYY-MM-DD') = TO_CHAR(SYSDATE - 2, 'YYYY-MM-DD')
AND TO_CHAR(DATE_UNIV, 'YYYY-MM-DD HH24:MI:SS') < TO_CHAR(SYSDATE - 5, 'YYYY-MM-DD HH24:MI:SS')
ORDER BY DATE_EXAM DESC
我一直收到错误信息：
ORA-00911: 无效字符
你能帮助我吗？
英文:
I've just try the following query:
SELECT EXAM_NUMBER, DATE_EXAM, DATE_UNIV
FROM UNIVERSITY.C_CALENDAR cc2
WHERE EXAM_NUMBER = 'L'
AND LAST_FILE = 'OK'
AND MATTER = 'MATH'
AND STATE_CODE NOT IN ('FC')
AND TO_CHAR(DATE_EXAM, 'YYYY-MM-DD') = TO_CHAR(SYSDATE - 2, 'YYYY-MM-DD')
AND TO_CHAR(DATE_UNIV, 'YYYY-MM-DD HH24:MI:SS') < TO_CHAR(SYSDATE - 5, 'YYYY-MM-DD HH24:MI:SS')
ORDER BY DATE_EXAM DESC
I always get error
> ORA-00911: invalid character
could you help me?
答案1
得分: 0
查询看起来没问题。你的源代码中可能有不可见字符（但在我从你的问题中复制粘贴时并没有显示，即使查看标记也是如此）。
在过滤时不需要使用 TO_CHAR（因为它会阻止 Oracle 使用列上的索引）：
SELECT EXAM_NUMBER,
DATE_EXAM,
DATE_UNIV
FROM UNIVERSITY.C_CALENDAR cc2
WHERE EXAM_NUMBER = 'L'
AND LAST_FILE = 'OK'
AND MATTER = 'MATH'
AND STATE_CODE != 'FC'
AND DATE_EXAM >= TRUNC(SYSDATE - 2)
AND DATE_EXAM < TRUNC(SYSDATE - 1)
AND DATE_UNIV < SYSDATE - 5
ORDER BY
DATE_EXAM DESC
英文:
Your query appears to be fine. You may have an invisible character in the source code (but it is not showing when I copy-paste from your question, even looking at the mark-up).
You do not need to use TO_CHAR when filtering (as it will prevent Oracle from using an index on the column):
SELECT EXAM_NUMBER,
DATE_EXAM,
DATE_UNIV
FROM UNIVERSITY.C_CALENDAR cc2
WHERE EXAM_NUMBER = 'L'
AND LAST_FILE = 'OK'
AND MATTER = 'MATH'
AND STATE_CODE != 'FC'
AND DATE_EXAM >= TRUNC(SYSDATE - 2)
AND DATE_EXAM < TRUNC(SYSDATE - 1)
AND DATE_UNIV < SYSDATE - 5
ORDER BY
DATE_EXAM DESC
答案2
得分: 0
从 SQL 开发人员使用时看起来没问题。如何使用这个命令？根据我的经验，如果在使用 Execute Immediate 或与连接到 Oracle 数据库的其他软件运行命令时多加了分号，就会出现这个错误。例如，当从 Python 程序运行并且命令末尾有分号时，Oracle 返回：
cx_Oracle.DatabaseError: ORA-00911: 无效字符
去掉分号后一切正常。
英文:
It looks ok when used from sql developer.
How do you use this command? In my experience that error raises if I have extra semi-colon when running the command with Execute Immediate or with some other software connected to oracle db. For instance when run from python program and the command has semi-colon at the end oracle returns:
cx_Oracle.DatabaseError: ORA-00911: invalid character
Without semi-colon everything is ok.
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论