DPY-4010 python-oracledb misinterpreting string literal as BIND variable

[mikemulhearn]

1. What versions are you using?

python-oracledb version: 1.3.2

platform.platform: Windows-10-10.0.14393-SP0
sys.maxsize > 2**32: True
platform.python_version: 3.10.7

oracledb.__version__: 1.3.2

2. Is it an error or a hang or a crash?

Error

3. What error(s) or behavior you are seeing?

The library appears to be incorrectly interpreting a query that works as-is in SQL developer. One of the sub-queries within a decode function in the SELECT clause containing a string with a colon (enclosed between single quotes) appears to be attempting to be interpreted as a BIND variable, when it is only a hardcoded string.

Affected Clause:

DECODE((SELECT  1 
			FROM argus_app.case_classifications cc,
			     argus_app.lm_case_classification lcc
		   WHERE cc.classification_id = lcc.classification_id
		     AND UPPER(lcc.description) = 'CONSENT TO FU GRANTED'
			 AND cc.deleted is null
			 AND lcc.deleted is null
			 AND cc.case_id = cmas.case_id),1,'Yes',
		  (SELECT  1 
			FROM argus_app.case_classifications cc,
			     argus_app.lm_case_classification lcc
		   WHERE cc.classification_id = lcc.classification_id
		     AND UPPER(lcc.description) LIKE 'FU: CONSENT GRANTED'
			 AND cc.deleted is null
			 AND lcc.deleted is null
			 AND cc.case_id = cmas.case_id),1,'Yes') "FU_POSSIBLE"

Full Query:

https://gist.github.com/mikemulhearn/3fa6ab5a6a4636284d637ee6b5f305d8

Python Command (standard query execution):

conn = oracledb.connect(user=username, password=password, dsn=dsn_tns, threaded=True)
curs = conn.cursor()
curs.execute(query)

Error:

DPY-4010: a bind variable replacement value for placeholder ":CONSENT" was not provided
Traceback (most recent call last):
  File "C:\Users\mm3fa7782\PycharmProjects\oracle_extract\oracle_extract.py", line 286, in oracle_plsql_extract
    curs.execute(cnt_query)
  File "C:\Users\mm3fa7782\PycharmProjects\oracle_extract\venv\lib\site-packages\oracledb\cursor.py", line 378, in execute
    impl.execute(self)
  File "src\oracledb\impl/thin/cursor.pyx", line 135, in oracledb.thin_impl.ThinCursorImpl.execute
  File "src\oracledb\impl/thin/cursor.pyx", line 128, in oracledb.thin_impl.ThinCursorImpl._preprocess_execute
  File "C:\Users\mm3fa7782\PycharmProjects\oracle_extract\venv\lib\site-packages\oracledb\errors.py", line 118, in _raise_err
    raise exc_type(_Error(message)) from cause
oracledb.exceptions.DatabaseError: DPY-4010: a bind variable replacement value for placeholder ":CONSENT" was not provided

4. Does your application call init_oracle_client()?

Yes, I have attempted to use both the Thick client (default) and the Thin client, with the same result.

5. Include a runnable Python script that shows the problem.

The above full query should be able to run through the python-oracledb query interpreter to show that it is finding a BIND variable that should not exist. Due to the complexity of the above query, I would prefer not to provide a schema to create the example here, if at all possible. This error appears to be happening by the statement interpreter and hopefully should not need a database to query.

[anthony-tuininga]

I know the source of the issue: this line in your SQL

CCOM.COMMENT_TXT "Reporter's Comments",

has an embedded quote. Since constant strings are removed first (without regard to such quoting) everything is off by one and the FU: CONSENT value is treated as a bind variable. If you remove that embedded quote all will work as expected. Going the other way (looking at quoted values first) won't help the situation, unfortunately. This limitation has been known but was not considered too likely and the changes required to support it are considerable so it was not a priority. Is making the change I mentioned a reasonable workaround? I will look into what is needed to resolve it properly but I'm not sure when that will be accomplished at this point!

[mikemulhearn]

I know the source of the issue: this line in your SQL

CCOM.COMMENT_TXT "Reporter's Comments",

has an embedded quote. Since constant strings are removed first (without regard to such quoting) everything is off by one and the FU: CONSENT value is treated as a bind variable. If you remove that embedded quote all will work as expected. Going the other way (looking at quoted values first) won't help the situation, unfortunately. This limitation has been known but was not considered too likely and the changes required to support it are considerable so it was not a priority. Is making the change I mentioned a reasonable workaround? I will look into what is needed to resolve it properly but I'm not sure when that will be accomplished at this point!

This is absolutely a reasonable workaround. I didn't write this particular query, and I am strongly opposed to single quotes in aliases, so I will be sure that the team is aligned in this approach so we don't run into this issue any more. Thank you so much for finding this issue so quickly. I was at my wits end trying to find the issue here.

[anthony-tuininga]

@mikemulhearn, I have just pushed a patch that resolves the embedded quote situation as well as improves a number of other things.

[Stephano120]

I have a similar issue when oracle functions such as DECODE or NVL are put an even number of times before a string containing a ":". I also use oracledb 1.3.2, but with python 3.11.

Typically, a query like:

res = oraclelib.execute(
environment,
    """
    SELECT
    ser.name AS "Name"
    , DECODE(ser.is_best, 0, 'No', 1, 'Yes', '---') AS "Best"
    , DECODE(ser.is_reported, 0, 'No', 1, 'Yes', '---') AS "Reported"
    , NVL(ser.name, '---') AS "NameID"
    , '(Version Tool: ' || ser.name || ')' AS "Version with Name"
    FROM
              db.timeserie ser
    """
)

fails and return
oracledb.exceptions.DatabaseError: DPY-4010: a bind variable replacement value for placeholder ":S" was not provided

while

res = oraclelib.execute(
environment,
    """
    SELECT
    ser.name AS "Name"
    -- DECODE(ser.is_best, 0, 'No', 1, 'Yes', '---') AS "Best"
    , DECODE(ser.is_reported, 0, 'No', 1, 'Yes', '---') AS "Reported"
    , NVL(ser.name, '---') AS "NameID"
    , '(Version Tool: ' || ser.name || ')' AS "Version with Name"
    FROM
              db.timeserie ser
    """
)

does work. The single difference is the number of functions with opening and closing brackets, before the ":"

I did not try the "patched version" provided by @anthony-tuininga though.

[anthony-tuininga]

@Stephano120, I tried it and it works. :-)

[Stephano120]

@anthony-tuininga I installed oracledb 1.4 but the problem remains.
For the time being it is necessary to copy/paste manually the patched version available on e861b2b ?

[anthony-tuininga]

Yes, it is necessary. It will become part of python-oracledb 1.4.1 when that is released (and that date is unknown currently) but for now you have to build yourself with that patch.

[Stephano120]

Thanks for your swift replies @anthony-tuininga
Please apologize my newbiness regarding some of the concepts involved here, but my oracledb folder in my venv/Lib/site-packages does not contain the impl folder which I thought would contain the thin, thick, base folder, etc, ie the structure that I see on https://github.com/oracle/python-oracledb/tree/main/src/oracledb
I can't find the statement file neither, very likely because I'm not looking at the right place.
I guess I'll Google a bit and learn more about the concepts at stake here.
Thanks for the patch any way.

[anthony-tuininga]

You will need to build from source by following these instructions. You can't use a binary installation (which is what is found in site-packages).

[Stephano120]

Thanks !

[anthony-tuininga]

This was included in version 1.4.1 which was just released.