DatabaseError:ORA-01036:非法的变量名称/编号 [英] DatabaseError: ORA-01036: illegal variable name/number

问题描述

我需要将以下值替换为select查询.但是我收到了我在下面提到的错误

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

推荐答案

我认为您误解了绑定变量如何与Oracle和cx_Oracle一起使用.

I think you have misunderstood how bind variables work with Oracle and cx_Oracle.

Oracle在SQL查询中将诸如:myvar之类的表达式识别为绑定变量占位符.遇到这种情况时，它会记下该变量需要一个值才能运行查询，但是在没有该值的情况下仍可以继续解析查询.

Oracle recognises an expression such as :myvar in 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.

绑定变量占位符在字符串文字中不起作用.诸如project_code = ':jobno'之类的条件将仅匹配project_code是实际的六个字符字符串:jobno的行，而不管您是否具有定义了名称jobno的绑定参数.相反，您应该编写project_code = :jobno.不必担心告诉Oracle jobno必须包含哪种类型的值.实际为它提供值时，它将检查您是否具有正确的类型.

Bind variable placeholders do not work inside string literals. A condition such as project_code = ':jobno' will only match rows whose project_code is the actual six-character string :jobno, regardless of whether you have a bind parameter with the name jobno defined. Instead, you should write project_code = :jobno. Don't worry about telling Oracle about what type of value jobno must contain; it will check you've got the correct type when you actually give it the value.

在某些地方，您尝试通过连接绑定变量占位符来构建LIKE子句.此连接仍然可以完成，但是必须在SQL中使用||运算符来完成.因此，不要写':workarea%'，而是写:workarea || '%'，而不是写':jobno_:qcActivity%'，而是写:jobno || '_' || :qcActivity || '%'.

There are a few places where you are attempting to build LIKE clauses 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 || '%'.

我对您的SQL查询进行了这些更改，创建了几个表，其中有足够多的列以使查询有效，然后运行它.我没有任何数据可运行，因此没有返回任何结果，但是数据库至少已解析并成功运行了查询.我还对查询进行了格式化，以使其更易于阅读:

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:

    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)
        })

这篇关于DatabaseError:ORA-01036:非法的变量名称/编号的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！