I think you have misunderstood how bind variables work with Oracle and cx_Oracle.
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.
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.
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 || '%'
.
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)
})
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…