[oracle] ORA-01425: escape character must be character string of length 1

[Ludovico Magnocavallo]

Upgrading from 1.5 to 1.6 beta this error, which was fixed in #14149 crops up again. The simple fix is to add DatabaseError to the try/except block that wraps the query to test for Oracle operators when creating the connection. I'm on Python 2.7.3 cxOracle 5.1.2.

[Ludovico Magnocavallo]

diff against trunk

[Aymeric Augustin]

Marking as a release blocker since the report says it's a regression.

[Shai Berger]

What Oracle version are you using?

(obviously, starting connections on Oracle works for most of us -- further, master is continuously tested against Oracle, and ATM the Oracle backend is essentially the same in master and 1.6b1).

[anonymous]

Shai, of course I imagine I'm not the only one using Oracle, that's why the error threw me off guard. :)

Here is the output of "select * from product_component_version"

[('NLSRTL ', '10.2.0.2.0', 'Production'),

('Oracle Database 10g Enterprise Edition ', '10.2.0.2.0', 'Prod'),
('PL/SQL ', '10.2.0.2.0', 'Production'),
('TNS for Linux: ', '10.2.0.2.0', 'Production')]

[Shai Berger]

Make Oracle backend not use unicode literals to work around Oracle 10 bug

[Shai Berger]

I suspect this is a unicode-vs-oracle-10 problem, similar to #20292 (although that one shows up already with Django 1.5.1).

If this is indeed the case, applying the attached oracle-10.patch should probably fix both problems; I have no way to test this myself, so I'm asking you to test. I only tested on master against Oracle 11, to see on a preliminary level that it doesn't break things; if it helps, I'll test it more thoroughly (but still, only against Oracle 11; that's what I have).

[Ludovico Magnocavallo]

The patch does not work (and btw line numbers are relative to master, they are different for the second block in 1.6.x).

What I don't understand is why use utils.DatabaseError -- which does not work to trap the exception -- where you have a perfectly working local DatabaseError which works as intended.

[Shai Berger]

We could use the DatabaseError, but I think that would be the wrong fix -- as far as I understand, the operators this code checks for are available on Oracle 10, so you should only get the exception on Oracle 9. If the exception is thrown and caught, you will be limited in your "like" queries (contains, startswith, etc).

That's why I'm trying to fix it from the other direction -- preventing the exception from being raised in the first place.

And yes, I made the change in master -- the oracle backend on 1.6 is not significantly different, so I take the liberty to work where it is more convenient for me; the patch uses different line numbers, but does apply cleanly with patch -p1 < oracle-10.patch executed at the django root.

Is there any chance for you to test this on 10.2.0.5 ? I believe that users of that version do not encounter this problem (only #20292)

[Ludovico Magnocavallo]

Yes I know it applies cleanly, I was just nitpicking. :)

Unfortunately I have no way to test against 10.2.0.5...

[Ludovico Magnocavallo]

Replying to shai:

We could use the DatabaseError, but I think that would be the wrong fix -- as far as I understand, the operators this code checks for are available on Oracle 10, so you should only get the exception on Oracle 9. If the exception is thrown and caught, you will be limited in your "like" queries (contains, startswith, etc).

Might be there are two fixes needed: one to be able to use the right operators, and one to actually trap DatabaseError if it gets raised (which it shouldn't as per the first needed fix, but anyway...).

[Shai Berger]

Having read the related tickets and discussions (and code) more carefully, it seems I was a little confused, and your solution appears correct -- except for the part that utils.DatabaseError is actually not necessary at all there.

The regression was introduced in [59a352087591a26023412cbcb830cd1d34fc9b99] which refactored the wrapping of database exceptions -- and made it so that this code runs with a "bare" (non-wrapped) cursor.

[Shai Berger <shai@…>]

In 6ed579e7eb6e1eda3dd1b0afb306d58b9498bfa0:

Fixed #20785 -- Corrected exception caught for Oracle LIKE operator detection

The code that tests to see which LIKE expressions to use now runs
using non-error-wrapped cursor, so cx_Oracle exceptions need to be caught
rather than Django DatabaseErrors.

Thanks Trac user ludo for report and initial patch.

[Shai Berger <shai@…>]

In 17e632929cdf9575f538c1f98379adac8698c288:

[1.6.x] Fixed #20785 -- Corrected exception caught for Oracle LIKE operator detection

The code that tests to see which LIKE expressions to use now runs
using non-error-wrapped cursor, so cx_Oracle exceptions need to be caught
rather than Django DatabaseErrors.

Thanks Trac user ludo for report and initial patch.

[Aymeric Augustin]

Thank you for fixing my mess, and sorry.

[Shai Berger]

For the record, I don't think there was any reasonable way for you to avoid this, nothing you should apologize for. At some point I may try to get to the bottom of this and set up a test to validate it, but since Ian already dug into this a few years ago and came up with not-much, I'm not making it a high priority.