DPY-4008: Bind variable not found if between two comment blocks

[npodewitz]

1. What versions are you using?

   
   platform.platform: Linux-5.4.0-131-generic-x86_64-with-glibc2.31
   sys.maxsize > 2**32: True
   platform.python_version: 3.9.15

oracledb.version: 1.1.1

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

3. What error(s) or behavior you are seeing?
`oracledb.exceptions.DatabaseError: DPY-4008: no bind placeholder named ":test_bind_var" was found in the SQL text`

4. Does your application call init_oracle_client()?
No

5. Include a runnable Python script that shows the problem.
```python
import oracledb

connection = oracledb.connect(user="<USER>", password="<PASSWORD>", dsn="<DSN>")

cur = connection.cursor()

sql = """
select /* comment 1 */
    :test_bind_var as test_value
    /* comment 2 */
from dual
"""

parameters = {'test_bind_var': '2022-11-23'}
cur.execute(statement=sql, parameters=parameters)

cur.close()
connection.close()

For the example to work one needs an oracle db and has to replace <USER>, <PASSWORD> and <DSN>.

The error seems to lie in oracledb/impl/thin/statement.pyx, where the regular expression should remove all comments. Since this expressions is greedy the afore mentioned code line removes everything from the start of the first comment to the end of the last comment. If the bind variable is only referenced between two comment blocks it is thus removed from the sql before the search for bind variables.

This can be fixed by making the pattern between start and stop of the comment to be non greedy, i.e.:

sql = re.sub(r"/\*[\S\n ]+?\*/", "", sql)

I would be happy to provide a pull request for this issue as a reference, however, I won't be able to sign the OCA.

[anthony-tuininga]

Thanks for the report and the diagnosis. This should be enough for me to correct the issue, but if I have further questions I'll ask!

[anthony-tuininga]

Problem resolved. Thanks again for the report.

[anthony-tuininga]

The fix for this bug has been included in python-oracledb 1.2.1 which was just released.