Python 数据库错误：ORA-01036：非法变量名/编号
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/16392288/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
DatabaseError: ORA-01036: illegal variable name/number
提问by venkat
I need to substitute the following values into select query. but i got the error as i mentioned below
我需要将以下值替换为选择查询。但我得到了下面提到的错误
self.jobNo = J-12060
qcActivity = C173
self.wrkArea = 1666339
cursor.execute("""SELECT A.MARKERID, D.COMMENTS,A.STATUS,A.X1,A.Y1,A.X2,A.Y2,C.ERRGROUP,C.ERRDESC,c.category
FROM MDP_ERR_MASTER A,(SELECT MARKERID, MAX(RECNO) maxRECNO FROM MDP_ERR_MASTER where project_code = ':jobno'
and errorcode like ':jobno_:qcActivity%' AND WORKAREA LIKE ':workarea%'
GROUP BY MARKERID) B,MDP_ERR_CONFIG C,(SELECT MARKERID, COMMENTS FROM MDP_ERR_MASTER WHERE PROJECT_CODE = ':jobno'
AND RECNO = 1 AND errorcode like ':jobno_:qcActivity%' AND WORKAREA LIKE ':workarea%') D
WHERE(A.MARKERID = B.MARKERID And A.RECNO = B.maxRECNO And A.Markerid = D.MARKERID)AND A.PROJECT_CODE = ':jobno'
AND A.ERRORCODE LIKE ':jobno_:qcActivity%' AND A.WORKAREA LIKE ':workarea%' AND A.ERRORCODE = C.ERRCODE""",
{"jobno" : str(self.jobNo),
"qcActivity" : str(qcActivity),
"workarea" : str(self.wrkArea)
})
Traceback (most recent call last):
File "D:\work\venkat\QGIS\Tools\GlobalErrorMarker\globalerrormarker.py", line 272, in btnDownloadError_Clicked
"workarea" : str(self.wrkArea)
DatabaseError: ORA-01036: illegal variable name/number
采纳答案by Luke Woodward
I think you have misunderstood how bind variables work with Oracle and cx_Oracle.
我认为您误解了绑定变量如何与 Oracle 和 cx_Oracle 一起使用。
Oracle recognises an expression such as :myvarin a SQL query as a bind variable placeholder. When it encounters this, it will make a note that it needs a value for this variable before it can run the query, but it can still continue parsing the query without this value.
Oracle:myvar将 SQL 查询中的表达式识别为绑定变量占位符。当它遇到这种情况时，它会注意到它在运行查询之前需要这个变量的值，但它仍然可以在没有这个值的情况下继续解析查询。
Bind variable placeholders do not work inside string literals. A condition such as project_code = ':jobno'will only match rows whose project_codeis the actual six-character string :jobno, regardless of whether you have a bind parameter with the name jobnodefined. Instead, you should write project_code = :jobno. Don't worry about telling Oracle about what type of value jobnomust contain; it will check you've got the correct type when you actually give it the value.
绑定变量占位符在字符串文字中不起作用。诸如此类的条件project_code = ':jobno'将仅匹配project_code实际为 6 个字符的 string 的行:jobno，而不管您是否具有jobno定义名称的绑定参数。相反，您应该编写project_code = :jobno. 不要担心告诉 Oraclejobno必须包含什么类型的值；当您实际为其赋值时，它会检查您是否获得了正确的类型。
There are a few places where you are attempting to build LIKEclauses by concatenating bind variable placeholders. This concatenation can still be done, but it must be done in SQL using the ||operator instead. So, instead of writing ':workarea%', write :workarea || '%', and insted of writing ':jobno_:qcActivity%', write :jobno || '_' || :qcActivity || '%'.
有几个地方您试图LIKE通过连接绑定变量占位符来构建子句。这种连接仍然可以完成，但必须在 SQL 中使用||运算符来完成。所以，与其写':workarea%'，写:workarea || '%'，不如写':jobno_:qcActivity%'，写:jobno || '_' || :qcActivity || '%'。
I made these changes to your SQL query, created a couple of tables with enough columns to make the query valid, and ran it. I had no data to run it on, so I got no results back, but the database at least parsed and ran the query successfully. I've also formatted the query to make it a bit easier to read:
我对您的 SQL 查询进行了这些更改，创建了几个具有足够列的表以使查询有效，然后运行它。我没有数据可以运行它，所以我没有得到任何结果，但数据库至少解析并成功运行了查询。我还对查询进行了格式化以使其更易于阅读：
cursor.execute("""
SELECT A.MARKERID, D.COMMENTS, A.STATUS, A.X1, A.Y1, A.X2, A.Y2, C.ERRGROUP, C.ERRDESC, c.category
FROM MDP_ERR_MASTER A,
(SELECT MARKERID, MAX(RECNO) maxRECNO
FROM MDP_ERR_MASTER
WHERE project_code = :jobno
AND errorcode like :jobno || '_' || :qcActivity || '%'
AND WORKAREA LIKE :workarea || '%'
GROUP BY MARKERID) B,
MDP_ERR_CONFIG C,
(SELECT MARKERID, COMMENTS
FROM MDP_ERR_MASTER
WHERE PROJECT_CODE = :jobno
AND RECNO = 1
AND errorcode like :jobno || '_' || :qcActivity || '%'
AND WORKAREA LIKE :workarea || '%') D
WHERE A.MARKERID = B.MARKERID
AND A.RECNO = B.maxRECNO
AND A.Markerid = D.MARKERID
AND A.PROJECT_CODE = :jobno
AND A.ERRORCODE LIKE :jobno || '_' || :qcActivity || '%'
AND A.WORKAREA LIKE :workarea || '%'
AND A.ERRORCODE = C.ERRCODE""",
{"jobno" : str(self.jobNo),
"qcActivity" : str(qcActivity),
"workarea" : str(self.wrkArea)
})
